Create reusable SQL expressions with SQL macros

A common criticism of SQL is it lacks composability - the ability to define small units you can combine into larger functions. Oracle Database addresses this with the introduction of SQL macros. These return SQL expressions, enabling you to define reusable components.

Using SQL macros you can SQL templates which you can pass tables, columns, and other values to at runtime. This enables you to build a library of functions to solve common problems, such as finding consecutive rows or the top-N rows per group.

This presentation explains how to use SQL macros.

Transcript

1. 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
2. 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
3. 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;
4. 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;
5. 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
6. 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;
7. 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;
8. 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
9. Ryan McGuire / Gratisography no? Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
10. 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
11. 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
12. SQL Macros Create reusable SQL expressions Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
13. 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
14. SQL Macros table scalar Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
15. 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
16. 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
17. 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
18. 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
19. 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
20. 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
21. 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
22. 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
23. 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
24. 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"
25. 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
26. 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
27. 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
28. 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
29. 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
30. 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
31. 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
32. 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
33. 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
34. 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
35. 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
36. 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
37. Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL What about SQL Ryan McGuire / Gratisography
38. 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 <null>
39. 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
40. 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
41. Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Find invoice items > EUR100 Ryan McGuire / Gratisography
42. 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
43. Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Make it a function Ryan McGuire / Gratisography
44. 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
45. 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
46. 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
47. 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
48. 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
49. 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
50. Scalar Macros Create reusable SQL expressions Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
51. 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)
52. 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
53. 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?
54. 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
55. 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
56. 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
57. 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
58. 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
59. 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
60. 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
61. DEMO Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
62. 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
63. select * from crazy_complex_sql Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
64. 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
65. Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Yes we can! Ryan McGuire / Gratisography
66. 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
67. 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