A Preview of Oracle Database 20c PLSQL Enhancements

Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

Slide 3

Slide 3 text

Slide 4

Slide 4 text

select ex_rate from exrates where from_currency_code = … and to_currency_code = …

Slide 5

Slide 5 text

Can we cache the exrates?

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

price := ex_rates ( 'USD' ) * gbp_value;

Slide 8

Slide 8 text

Show me all the ex rates!

Slide 9

Slide 9 text

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;

Slide 10

Slide 10 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Extended Iterators

Slide 11

Slide 11 text

for curr in indices of exrates loop dbms_output.put_line ( curr || ' = ' || exrates ( curr ).exrate ); end loop;

Slide 12

Slide 12 text

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

How about populating the array?

Slide 15

Slide 15 text

ex_rates ( 'USD' ) := 1.2; ex_rates ( 'EUR' ) := 1.1; ex_rates ( 'INR' ) := 96;

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Aggregate Qualified Expressions

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

arr := dbms_sql.number_table ( for i in 1 .. 10 => i );

Slide 20

Slide 20 text

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

price := ex_rates ( 'USD' ) * gbp_value;

Slide 23

Slide 23 text

Slide 24

Slide 24 text

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;

Slide 25

Slide 25 text

price := convert_currency ( ex_rates ( 'USD' ), gbp_value );

Slide 26

Slide 26 text

insert into … values ( …, convert_currency ( ex_rates ( 'USD' ), gbp_value ), … );

Slide 27

Slide 27 text

Context switch?!

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

Slide 32

Slide 32 text

Slide 33

Slide 33 text

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;

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

Slide 36

Slide 36 text

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

Slide 39

Slide 39 text

Slide 40

Slide 40 text

Slide 41

Slide 41 text

exception when ZERO_DIVIDE then … when others then null;

Slide 42

Slide 42 text

exception when ZERO_DIVIDE then … when others then null; PLW-06009: procedure … OTHERS handler does not end in RAISE

Slide 43

Slide 43 text

exception when ZERO_DIVIDE then … when others then log_error ();

Slide 44

Slide 44 text

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

Slide 45

Slide 45 text

Slide 46

Slide 46 text

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