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
   

   View Slide

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

   View Slide

3. Copyright © 2020, Oracle and/or its affiliates. All rights reserved. |
   We need $£€¥
   conversions!
   

   View Slide

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

   View Slide

5. Can we cache
   the exrates?
   

   View Slide

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
   

   View Slide

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

   View Slide

8. Show me all
   the ex rates!
   

   View Slide

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;
   

   View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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
    

    View Slide

14. How about
    populating
    the array?
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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
    

    View Slide

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

    View Slide

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

    View Slide

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;
    

    View Slide

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

    View Slide

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

    View Slide

27. Context
    switch?!
    

    View Slide

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

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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;
    

    View Slide

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;
    

    View Slide

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
    

    View Slide

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
    )
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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

    View Slide

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
    

    View Slide

41. exception
    when ZERO_DIVIDE then
    …
    when others then
    null;
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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
    

    View Slide

47. Copyright © 2020, Oracle and/or its affiliates. All rights reserved. |
    asktom.oracle.com
    #MakeDataGreatAgain
    Ryan McGuire / Gratisography
    

    View Slide