Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Create reusable SQL expressions with SQL macros

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.

Chris

May 15, 2023
Tweet

More Decks by Chris

Other Decks in Technology

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