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

A Preview of Oracle Database 20c PLSQL Enhancements

D7b6e701f0155fc189bbca6c89223b3c?s=47 Chris
August 04, 2020

A Preview of Oracle Database 20c PLSQL Enhancements

An overview of new PL/SQL features coming in Oracle Database 20c

D7b6e701f0155fc189bbca6c89223b3c?s=128

Chris

August 04, 2020
Tweet

Transcript

  1. Your PL/SQL Office Hours begins soon… What's new in 20c

    Chris Saxon @ChrisRSaxon & @SQLDaily https://www.youtube.com/c/TheMagicofSQL https://blogs.oracle.com/sql Steven Feuerstein @sfonplsql https://www.youtube.com/c/PracticallyPerfectPLSQL https://stevenfeuersteinonplsql.blogspot.com
  2. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Welcome to Ask TOM Office Hours!
  3. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | We need $£€¥ conversions!
  4. select ex_rate from exrates where from_currency_code = … and to_currency_code

    = …
  5. Can we cache the exrates?

  6. for currs in ( select to_currency_code, exrate from currency_exrates where

    from_currency_code = 'GBP' ) loop ex_rates ( currs.to_currency_code ) := currs.exrate; end loop; Index by varchar2
  7. price := ex_rates ( 'USD' ) * gbp_value;

  8. Show me all the ex rates!

  9. curr := ex_rates.first; while curr is not null loop dbms_output.put_line

    ( curr || ' = ' || ex_rates ( curr ) ); curr := ex_rates.next ( curr ); end loop;
  10. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Extended Iterators

  11. for curr in indices of exrates loop dbms_output.put_line ( curr

    || ' = ' || exrates ( curr ).exrate ); end loop;
  12. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | DEMO
  13. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Extended Iterators • Custom increments • by constant or repeat expression • mutable index variables • Better array looping • Can chain iterators
  14. How about populating the array?

  15. ex_rates ( 'USD' ) := 1.2; ex_rates ( 'EUR' )

    := 1.1; ex_rates ( 'INR' ) := 96;
  16. ex_rates := ex_arr ( 'USD' => 1.2, 'EUR' => 1.1,

    'INR' => 96 );
  17. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Aggregate Qualified Expressions

  18. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (Array Type Constructors)

  19. arr := dbms_sql.number_table ( for i in 1 .. 10

    => i );
  20. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | DEMO
  21. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Aggregate Qualified Expressions • Initialize arrays with (extended) iterators • Combine arrays • Cursor iterator to load index by varchar2
  22. price := ex_rates ( 'USD' ) * gbp_value;

  23. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Do we multiply exrates or divide?
  24. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon create function convert_currency ( ex_rate number, amount number ) return number is begin return amount * ex_rate; end convert_currency;
  25. price := convert_currency ( ex_rates ( 'USD' ), gbp_value );

  26. insert into … values ( …, convert_currency ( ex_rates (

    'USD' ), gbp_value ), … );
  27. Context switch?!

  28. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon SQL Macros

  29. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon SQL Macros table scalar
  30. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon SQL Macros table scalar in from clause "everywhere" else only these in 19.6
  31. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon SQL Macros table scalar in from clause "everywhere" else only these in 19.6
  32. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon create function convert_currency ( ex_rate number, amount number ) return number is begin return amount * ex_rate; end convert_currency;
  33. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon create function convert_currency ( ex_rate number, amount number ) return varchar2 sql_macro ( scalar ) is begin return ' ex_rate * amount '; end convert_currency;
  34. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon create function convert_currency ( ex_rate number, amount number ) return varchar2 sql_macro ( scalar ) is begin return ' ex_rate * amount '; end convert_currency; Find/replace
  35. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon convert_currency ( ex_rate, 10 )
  36. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon convert_currency ( ex_rate, 10 ) ex_rate * 10
  37. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon convert_currency ( ex_rate, 10 ) convert_currency ( ex_rates('USD'), :val ) ex_rate * 10
  38. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon convert_currency ( ex_rate, 10 ) convert_currency ( ex_rates('USD'), :val ) ex_rate * 10 ex_rates('USD') * :val
  39. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | DEMO
  40. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | (Scalar) SQL Macros • Return a SQL fragment • Find/replace of parameter text • Eliminates context switch • Can only call in SQL
  41. exception when ZERO_DIVIDE then … when others then null;

  42. exception when ZERO_DIVIDE then … when others then null; PLW-06009:

    procedure … OTHERS handler does not end in RAISE
  43. exception when ZERO_DIVIDE then … when others then log_error ();

  44. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon pragma suppresses_warning _6009

  45. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | DEMO
  46. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 20c PL/SQL New Features https://docs.oracle.com/en/database/oracle/oracle -database/20/lnpls/release-changes.html Oracle Database 20c Preview https://blogs.oracle.com/database/oracle- database-20c-preview Further Reading
  47. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | asktom.oracle.com #MakeDataGreatAgain Ryan McGuire / Gratisography