Step-by-step PL/SQL: Anonymous & Named Blocks, Conditional logic, Iterative logic, Collections, Object types, Triggers

1. Step-by-step PL/SQL By Michael McLaughlin

2. Objectives • Anonymous & Named Blocks • Conditional logic – IF and CASE statements • Iterative logic – Simple, FOR, and WHILE loops • Collections – PL/SQL and SQL Types • Object types • Triggers – Logging results to single table

3. Anonymous & Named Blocks • Anonymous blocks • Available inside scripts • Available inside other blocks • Linked by session-level bind variables • The foundation of database triggers • Named blocks • Standalone functions and procedures • Packages • Object methods

4. Standalone Script Deployment VARIABLE whom VARCHAR2(10)SET VERIFY OFFBEGIN -- Single line comment. /* Multiple line comment. */ /* Assign a session variable inside PL/SQL. */:whom := '&input1'; -- Substitutions are numbers by default.END;/SET SERVEROUTPUT ON SIZE UNLIMITEDDECLARE /* Create a constant variable. */ salutation CONSTANT VARCHAR2(5) := 'Hello';BEGIN dbms_output.put_line(salutation||' '||:whom||'!');EXCEPTION /* Catches the raw exception. */ WHEN OTHERS THEN /* Rethrows the raw exception. */ RAISE;END;/

5. Basic Anonymous BlocksDeclare, assign, and print session variable

6. Dynamic Assignments Can Fail?Bad assignments in the declaration block are thrown to calling scope

7. Dynamic Assignments Do Fail!Capture bad assignments in the execution block in the exception block

8. Functions return one thing when?The parameter mode of operation is IN-only

9. Functions return one thing when?Parameter should list optional parameters at the end always

10. Packages host stored programs?Packages can implement named functions and procedures

11. Object types host?Object types host constructor functions, member procedures and functions

12. Conditional LogicThe IF and CASE statements • IF statements • If one condition is true • If two or more conditions true with an AND • If one or more of several are true with an OR • Supports an ELSIF and ELSE block • CASE statements • Supports simple and searched cases • Supports a default case with the ELSE block • Doesn’t support principal of fall through

13. If TRUE or FALSE?You can evaluate two-value logic

14. If TRUE or FALSE?You can evaluate three-value logic

15. If TRUE or FALSE?You can evaluate three-value logic

16. Simple CASE?You can evaluate a number, string, or date

17. Searched CASE?You can evaluate a Boolean expression

18. Iterative Logic • Loops guard on entry or exit? • Simple loop guards on exit • FOR loop guards on exit • WHILE loop guards on entry

19. Simple loopsOne element of the SELECT-list mapped to one local variable

20. Simple loopsOne structure anchored to a SELECT-list

21. Cursor FOR loopsSimplified and implicit operations

22. Cursor WHILE loopsNot simplified and they require two FETCH statements

23. Collections – PL/SQL and SQL TypesAlways use SQL Types unless you’re managing old code • Collection types • The VARRAY or ARRAY type • The Nested table or list • A PL/SQL Table or Associative Array • Rules for use • VARRAY requires a constructor • Nested Table requires a constructor • Associative Arrays don’t use a constructor

24. Object type and collectionCollections inherit behavior of the Collection API and objects base types

25. Object type and bulk collectionGenerally the most effective method to populate collections

26. Extending collectionAdd an element to an existing collection

27. Navigating a CollectionLists start with a densely populated index that may become sparsely populated Always traverse with a WHILE loop

28. Triggers – Logging to a single tableUses object types/subtypes and SQL substitutability Logging Trigger Results to a Single Table

29. Review • Anonymous & Named Blocks • Conditional logic – IF and CASE statements • Iterative logic – Simple, FOR, and WHILE loops • Collections – PL/SQL and SQL Types • Object types • Triggers – Logging results to single table

30. Questions & Answers