Tweet
Tweet

Slide 1

Slide 1 text

Create reusable SQL expressions with SQL macros How to make maintainable SQL Chris Saxon @ChrisRSaxon & @SQLDaily https://blogs.oracle.com/sql https://www.youtube.com/c/TheMagicofSQL

Slide 2

Slide 2 text

Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Get the first three hires in each department Ryan McGuire / Gratisography

Slide 3

Slide 3 text

Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL with rws as ( select t.*, row_number () over ( partition by department_id order by hire_date ) rn from hr.employees t ) select * from rws where rn <= 3 order by department_id, rn;

Slide 4

Slide 4 text

Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL with rws as ( select t.*, row_number () over ( partition by department_id order by hire_date ) rn from hr.employees t ) select * from rws where rn <= 3 order by department_id, rn;

Slide 5

Slide 5 text

Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Get the last three orders for each customer Ryan McGuire / Gratisography

Slide 6

Slide 6 text

Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL with rws as ( select t.*, row_number () over ( partition by department_id order by hire_date ) rn from hr.employees t ) select * from rws where rn <= 3 order by department_id, rn;

Slide 7

Slide 7 text

Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL with rws as ( select t.*, row_number () over ( partition by customer_id order by order_date desc ) rn from co.orders t ) select * from rws where rn <= 3 order by customer_id, rn;

Slide 8

Slide 8 text

Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Can you make that reusable? Ryan McGuire / Gratisography

Slide 9

Slide 9 text

Ryan McGuire / Gratisography no? Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 10

Slide 10 text

Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL open cur for 'with rws as ( select …, row_number () over ( partition by ? order by ? ) rn from ? ) … Can't bind identifiers

Slide 11

Slide 11 text

Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL open cur for 'with rws as ( select …, row_number () over ( partition by ' || group_cols || ' order by ' || sort_cols || ' ) rn from ' || tab || ' ) … '; SQL

Slide 12

Slide 12 text

SQL Macros Create reusable SQL expressions Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 13

Slide 13 text

Create reusable SQL expressions with SQL macros How to make maintainable SQL Chris Saxon @ChrisRSaxon & @SQLDaily https://blogs.oracle.com/sql https://www.youtube.com/c/TheMagicofSQL

Slide 14

Slide 14 text

SQL Macros table scalar Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 15

Slide 15 text

SQL Macros table scalar in from clause "everywhere" else Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 16

Slide 16 text

SQL Macros table scalar in from clause "everywhere" else 19.6 21c Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 17

Slide 17 text

create function sql_macro ( … ) return clob sql_macro ( [ scalar | table ] ) as begin return ' string picture '; end sql_macro; Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 18

Slide 18 text

create function sql_macro ( … ) return clob sql_macro ( [ scalar | table ] ) as begin return ' string picture '; end sql_macro; Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 19

Slide 19 text

create function sql_macro ( … ) return clob deterministic sql_macro ( [ scalar | table ] ) as begin return ' string picture '; end sql_macro; Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Implicit

Slide 20

Slide 20 text

Table Macros Template queries for polymorphic and parameterized views Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 21

Slide 21 text

create function top_n ( tab dbms_tf.table_t, num_rows integer ) return varchar2 sql_macro as begin return 'select * from tab fetch first num_rows rows only'; end top_n; Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 22

Slide 22 text

create function top_n ( tab dbms_tf.table_t, num_rows integer ) return varchar2 sql_macro as begin return 'select * from tab fetch first num_rows rows only'; end top_n; Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 23

Slide 23 text

create function top_n ( tab dbms_tf.table_t, num_rows integer ) return varchar2 sql_macro as begin return 'select * from tab fetch first num_rows rows only'; end top_n; Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 24

Slide 24 text

create function top_n ( tab dbms_tf.table_t, num_rows integer ) return varchar2 sql_macro as begin return 'select * from tab fetch first num_rows rows only'; end top_n; Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Parse time "Find & replace"

Slide 25

Slide 25 text

select * from top_n ( hr.employees, 3 ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL select * from hr.employees fetch first 3 rows only

Slide 26

Slide 26 text

select * from top_n ( co.orders, :N ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL select * from co.orders fetch first :N rows only

Slide 27

Slide 27 text

select * from top_n ( sh.sales, get_n() ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL select * from sh.sales fetch first get_n() rows only

Slide 28

Slide 28 text

create function top_n_per_group ( tab dbms_tf.table_t, group_cols dbms_tf.columns_t, order_cols dbms_tf.columns_t, num_rows integer ) return clob sql_macro as grouping_cols clob; ordering_cols clob; stmt clob; begin Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 29

Slide 29 text

stmt := 'with rws as ( select t.*, row_number () over ( partition by @group_cols order by @order_cols ) rn from tab t ) select * from rws where rn <= num_rows'; Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL These are substituted These are not

Slide 30

Slide 30 text

table_t columns_t What table/view/CTE columns list Macro parameters Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 31

Slide 31 text

table_t columns_t What table/view/CTE columns list Auto-replaced?   Macro parameters Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 32

Slide 32 text

table_t columns_t What table/view/CTE columns list Auto-replaced?   Non-existent objects?   Macro parameters Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 33

Slide 33 text

for col in 1 .. group_cols.count loop grouping_cols := grouping_cols || group_cols ( col ) || ','; end loop; for col in 1 .. order_cols.count loop ordering_cols := ordering_cols || order_cols ( col ) || ',' ; end loop; Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 34

Slide 34 text

stmt := replace ( stmt, '@group_cols', rtrim ( grouping_cols, ',' ) ); stmt := replace ( stmt, '@order_cols', rtrim ( ordering_cols, ',' ) ); return stmt; Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 35

Slide 35 text

select * from top_n_per_group ( hr.employees, columns ( department_id ), columns ( hire_date ), 3 ); Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 36

Slide 36 text

select * from top_n_per_group ( co.orders, columns ( customer_id ), columns ( order_datetime ), :num_rows ); Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 37

Slide 37 text

Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL What about SQL Ryan McGuire / Gratisography

Slide 38

Slide 38 text

create function inject_me ( tab dbms_tf.table_t, dodgy_string varchar2 ) return clob sql_macro as stmt clob; begin stmt := 'select ' || dodgy_string || ' c from tab'; return stmt; end inject_me; Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL This is

Slide 39

Slide 39 text

create function inject_me ( tab dbms_tf.table_t, dodgy_string varchar2 ) return clob sql_macro as stmt clob; begin stmt := 'select dodgy_string c from tab'; return stmt; end inject_me; Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL This is a literal

Slide 40

Slide 40 text

Finding consecutive rows CSV to rows Date generator Table macro use-cases Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 41

Slide 41 text

Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Find invoice items > EUR100 Ryan McGuire / Gratisography

Slide 42

Slide 42 text

Rounding? Multiply or divide? select * from invoice_items where total_paid ( ( quantity * unit_price ) / ex_rate ) > :total Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 43

Slide 43 text

Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Make it a function Ryan McGuire / Gratisography

Slide 44

Slide 44 text

create function total_paid ( quantity integer, unit_price number, ex_rate number ) return number as begin return ( quantity * unit_price ) / ex_rate; end total_paid; Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 45

Slide 45 text

select * from invoice_items where total_paid ( quantity, unit_price, ex_rate ) > :total Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 46

Slide 46 text

Ryan McGuire / Gratisography The query is 100x slooooower! Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 47

Slide 47 text

create index item_total on invoices ( total_paid ( quantity, unit_price, ex_rate ) ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 48

Slide 48 text

create function total_paid ( quantity integer, unit_price number, ex_rate number ) return number as pragma udf; begin return ( quantity * unit_price ) / ex_rate; end total_paid; Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL (12.1) Faster context switches

Slide 49

Slide 49 text

Ryan McGuire / Gratisography It's still 10x slooower! Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 50

Slide 50 text

Scalar Macros Create reusable SQL expressions Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 51

Slide 51 text

create function total_paid ( quantity integer, unit_price number, ex_rate number ) return varchar2 sql_macro ( scalar ) as begin return ' ( quantity * unit_price ) / ex_rate '; end total_paid; Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL (21c)

Slide 52

Slide 52 text

select * from invoice_items where total_paid ( quantity, unit_price, ex_rate ) > :total Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL No context switch Optimizer can find better plans

Slide 53

Slide 53 text

Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Ryan McGuire / Gratisography So make all functions macros?

Slide 54

Slide 54 text

create or replace function first_not_null ( v1 int, v2 int ) return int as begin return coalesce ( v1, v2 ); end first_not_null; / Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 55

Slide 55 text

create or replace function first_not_null ( v1 int, v2 int ) return int as begin return coalesce ( v1, v2 ); end first_not_null; / Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL select first_not_null ( 1, 1/0 ) from dual; ORA-01476: divisor is equal to zero

Slide 56

Slide 56 text

create or replace function first_not_null ( v1 int, v2 int ) return varchar2 sql_macro ( scalar ) as begin return ' coalesce ( v1, v2 ) '; end first_not_null; / Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 57

Slide 57 text

create or replace function first_not_null ( v1 int, v2 int ) return varchar2 sql_macro ( scalar ) as begin return ' coalesce ( v1, v2 ) '; end first_not_null; / Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL select first_not_null ( 1, 1/0 ) from dual; FIRST_NOT_NULL(1,1/0) 1

Slide 58

Slide 58 text

begin dbms_output.put_line ( total_paid ( 1, 2, 4 ) ); end; Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL ( quantity * unit_price ) / ex_rate

Slide 59

Slide 59 text

Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL How do you debug these? Ryan McGuire / Gratisography

Slide 60

Slide 60 text

dbms_utility.expand_sql_text ( input_sql_text => q'!select * from macro … !', output_sql_text => stmt ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Only expands table macros

Slide 61

Slide 61 text

DEMO Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 62

Slide 62 text

SQL template Table => from Scalar => PL/SQL function Find/replace Injection safe Handle column names manually Parse time Runtime = pure SQL => better plans & faster SQL SQL Macros Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 63

Slide 63 text

select * from crazy_complex_sql Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 64

Slide 64 text

Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Can you make that reusable? Ryan McGuire / Gratisography

Slide 65

Slide 65 text

Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Yes we can! Ryan McGuire / Gratisography

Slide 66

Slide 66 text

Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Further reading SQL Macros - Creating parameterized views https://livesql.oracle.com/apex/livesql/file/ tutorial_KQNYERE8ZF07EZMRR6KJ0RNIR.html How to select the top-N rows per group with SQL in Oracle Database https://blogs.oracle.com/sql/post/how-to-select-the- top-n-rows-per-group-with-sql-in-oracle-database

Slide 67

Slide 67 text

Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Ryan McGuire / Gratisography See you soon! devgym.oracle.com #AskTOMOfficeHours Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL