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

A Preview of Oracle Database 20c PLSQL Enhancem...

Chris
August 04, 2020

A Preview of Oracle Database 20c PLSQL Enhancements

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

Chris

August 04, 2020
Tweet

More Decks by Chris

Other Decks in Technology

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. 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
  3. 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;
  4. for curr in indices of exrates loop dbms_output.put_line ( curr

    || ' = ' || exrates ( curr ).exrate ); end loop;
  5. 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
  6. ex_rates ( 'USD' ) := 1.2; ex_rates ( 'EUR' )

    := 1.1; ex_rates ( 'INR' ) := 96;
  7. 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
  8. 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;
  9. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon SQL Macros table scalar
  10. 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
  11. 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
  12. 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;
  13. 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;
  14. 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
  15. 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 )
  16. 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
  17. 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
  18. 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
  19. 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
  20. exception when ZERO_DIVIDE then … when others then null; PLW-06009:

    procedure … OTHERS handler does not end in RAISE
  21. 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
  22. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

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