A Preview of Oracle Database 20c PLSQL Enhancements

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

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