SFDV3002

1. SFDV3002 Chapter 4: Programming with SQL SFDV3002

2. Overview • Database and application layers • Server-side SQL • Oracle’s PL/SQL • Cursors • Integrity checking using triggers • Interfacing with external applications • SQL interfaces & dynamic SQL • Call-level interfaces SFDV3002

3. Database and application layers Data & metadata External application code Interaction between external applications and database (middleware layer) “DATABASE” Internal application code (stored procedures) Integrity constraints (domain, PKs, FKs, CHECKs, triggers) DBMS SFDV3002

4. Server-side SQL Storing application code in the database SFDV3002

5. Overview • The internal application layer • Procedural vs. declarative • Oracle’s PL/SQL: • basic language constructs • retrieving data into variables (cursors) • stored code • exception handling • packages SFDV3002

6. The internal application layer • Application code stored in database. • Used by DBMS and external programs. • Procedural vs. declarative. • Major uses: • arbitrarily complex (procedural) business rules • commonly used application code (not business rules), esp. large intermediate results or CPU intensive SFDV3002

7. Procedural vs. declarative(Kifer p. 127; Example 4–1) • SQL generally declarative: “what”. • “Conventional” programming languages (C++, Java, Visual Basic, C#, …) procedural: “how”. • Oracle10g’s PL/SQL is “procedural SQL”: • block-structured, similar to Ada or Pascal • other DBMSs have similar • some standardisation in SQL:1999 SFDV3002

8. PL/SQL(Oracle10g Concepts ch. 24; PL/SQL User’s Guide & Reference; Example 4–2) A PL/SQL block DECLARE <declarations>← variables, constants, etc. BEGIN <code>← SQL statements, loops, if/then, etc. EXCEPTION← optional exception-handling section <exception handlers> END; SFDV3002

9. PL/SQL Declarations(PL/SQL User’s Guide & Reference ch. 2) • Variables & constants • Cursors • Procedures, functions • Exceptions SFDV3002

10. Flow of control(PL/SQL User’s Guide & Reference ch. 4) Conditional IF <condition-1> THEN <code-1> [ELSIF <condition-2> THEN <code-2>]... ELSE <code-n> END IF; Loops WHILE <condition> LOOP <code> END LOOP; FOR <var> IN [REVERSE] <start>..<end> LOOP <code> END LOOP; EXIT & EXIT WHEN SFDV3002

11. Retrieving data into variables(PL/SQL User’s Guide & Reference ch. 13 ▷ “SELECT INTO Statement”; Example 4–3) SELECT…INTO for single values or rows DECLARE Sal NUMBER(6); ID NUMBER(5); BEGIN ID := 7659; SELECT E.Salary INTO Sal FROM Employee E WHERE (E.Employee_ID = ID); END; SFDV3002

12. %ROWTYPE(PL/SQL User’s Guide & Reference ch. 13 ▷ “%ROWTYPE Attribute”) “Data type” corresponding to row of table DECLARE Emp_Row Employee%ROWTYPE; ID NUMBER(4); BEGIN ID := 7659; SELECT E.* INTO Emp_Row FROM Employee E WHERE (E.Employee_ID = ID); END; SFDV3002

13. Cursors(Kifer §8.2.4; PL/SQL User’s Guide & Reference ch. 6; Example 4–4) • Table is basic relational structure. • Procedural languages oriented toward files of records: • open file • read and process records one at a time • close file • Cursors enable procedural access to tables in SQL. SFDV3002

14. Declaring a cursor DECLARE CURSOR <name> [<arguments>] IS <SELECT statement>; Example DECLARE CURSOR Order_Cursor (OrdDate DATE) IS SELECT SO.Order_ID, SO.Customer_ID, SO.Ship_date FROM Sales_order SO WHERE SO.Ship_date > OrdDate ; SFDV3002

15. Using cursors OPEN <cursor> [(<arguments>)]; FETCH <cursor> INTO <variable>; CLOSE <cursor>; Example (usually FETCH in a loop) DECLARE This_order Sales_order%ROWTYPE; ... OPEN Order_Cursor ('12-DEC-2007'); FETCH Order_Cursor INTO This_order; CLOSE Order_Cursor; SFDV3002

16. Cursor for loop FOR <row variable> IN <cursor> LOOP <code> END LOOP; • Don’t need to declare <row variable> in advance. • OPEN, FETCH and CLOSE automatically. • Rows fetched one at a time into <row variable>. SFDV3002

17. Cursor properties %FOUND:true after FETCH if row retrieved, otherwise false (inverse %NOTFOUND) %ROWCOUNT: number of rows retrieved so far %ISOPEN:true if cursor is open Examples IF Order_Cursor%ISOPEN THEN ... ; WHILE Order_Cursor%FOUND LOOP ... ; SFDV3002

18. Procedures(Example 4–5; Concepts ch. 24; PL/SQL User’s Guide & Reference ch. 8; Oracle10g Application Developer’s Guide—Fundamentals ch. 7) PROCEDURE <name> [(<arguments>)] IS [<local declarations>] BEGIN <code> [EXCEPTION <exception handlers>] END; SFDV3002

19. Functions FUNCTION <name> [(<arguments>)] RETURN <datatype> IS [<local declarations>] ... (remainder as for procedures) SFDV3002

20. Stored procedures & functions(Kifer §8.2.5; Example 4–6) • CREATE OR REPLACE PROCEDURE <name> [(<arguments>)] IS ... (remainder as for non-stored procedure) CREATE OR REPLACE FUNCTION <name> [(<arguments>)] RETURN <datatype> IS ... (remainder as for non-stored function) • Stored permanently in database. • Once stored, can call from both inside (SELECT) and outside database. SFDV3002

21. Exceptions(Oracle10g Application Developer’s Guide—Fundamentals ch. 7 ▷ “Handling Run-Time PL/SQL Errors”;PL/SQL User’s Guide & Reference ch. 10) • Unusual situations or errors, e.g.: • divide by zero • integrity constraint violated • lock conflict • SELECT returned no data • Oracle10g errors unfriendly, so need to “trap” exceptions. • Compare Java. SFDV3002

22. The EXCEPTION block(Example 4–7) Optional EXCEPTION section in every PL/SQL block (cf. Java CATCH) EXCEPTION WHEN <exception-1> THEN <do something> WHEN <exception-2> THEN <do something else> [WHEN OTHERS THEN <do default something>] END; Exception handler SFDV3002

23. Types of exception(PL/SQL User’s Guide & Reference, ch. 10) Built-in • Division by zero • No data found • Duplicate index key value • etc… User-defined DECLARE The_Server_is_on_Fire EXCEPTION; Total_Meltdown EXCEPTION; SFDV3002

24. Raising exceptions manually IF <something really bad happens> THEN RAISE Total_Meltdown; user defined FETCH Order_Cursor INTO Order_row; IF Order_Cursor%NOTFOUND THEN RAISE No_Data_Found; built-in SFDV3002

25. Exception flow of control(Example 4–8) • After exception handler completes, control returns to calling block. (cf. Java) • To avoid this, check for possible error conditions before exception handler is activated. SFDV3002

26. Packages(Example 4–9; Concepts ch. 24; Oracle10g Application Developer’s Guide—Fundamentals ch. 7; PL/SQL User’s Guide & Reference ch. 9) • Collection of cursors, variables, constants, exceptions, code blocks, procedures, functions… • Using any item loads entire package into memory. • Many built-in or add-on packages: • web access • data replication • exception handling • screen I/O (DBMS_Output) • XML • etc… SFDV3002

27. Package specification CREATE OR REPLACE PACKAGE <name> IS <public declarations> <signatures of public procedures/functions> END <name>; SFDV3002

28. Package body CREATE OR REPLACE PACKAGE BODY <name> IS <private declarations> <implementations of public procs/fns> [BEGIN <optional "static" initialisation> ] END <name>; SFDV3002

29. Package visibility Public • Accessible from outside package • Declared in package specification • Implemented in package body Private • Not accessible from outside package • Not declared in package specification • Implemented in package body SFDV3002

30. Overloading • Define same procedure several times with different arguments (number and data type). • For example, print customer orders in different ways: • all orders that include product • all orders placed on date • all orders worth more than amount SFDV3002

31. Summary • PL/SQL is a full programming language providing many useful features: • flow of control • procedures & functions • cursors • Other DBMSs use similar languages; some standardisation in SQL:1999. • Ideal for complex stored code or business rules. Next: Triggers SFDV3002

32. Triggers Complex automated integrity constraints SFDV3002

33. Overview • Definition and use • Specification • Triggers in Oracle10g SFDV3002

34. Automated actions: triggers(Kifer §3.3.5, 7.1) • Specific operation(s) on table automatically trigger other operation(s). • Typical uses: • automatically computed columns (e.g., update sale total when line items added/removed) • setting status values in response to updates • maintaining referential integrity (cf. ON DELETE CASCADE) • Standardised in SQL:1999, still wide variation. SFDV3002

35. Trigger specification(Kifer §7.2–7.3) Activation condition(s) • Timing: BEFORE, AFTER • Type of operation: INSERT, DELETE, UPDATE • Column affected (UPDATE only, optional) • Table affected • Other conditions (optional) • ⇓ • Triggered operation • What to do when trigger is activated SFDV3002

36. Oracle10g triggers(SQL Reference ch. 16 ▷ “CREATE TRIGGER”; Oracle10g Application Developer’s Guide—Fundamentals ch. 9; Concepts ch. 22; Example 4–10(a); see also Kifer §7.3) CREATE [OR REPLACE] TRIGGER <name> <timing><operation> ON <table/view> [<referencing clause>] [FOR EACH ROW|STATEMENT] [WHEN <condition>] BEGIN <PL/SQL code> END; SFDV3002

37. Oracle10g triggers(SQL Reference ch. 16 ▷ “CREATE TRIGGER”; Oracle10g Application Developer’s Guide—Fundamentals ch. 9; Concepts ch. 22; Example 4–10(a); see also Kifer §7.3) <timing>BEFORE, AFTER, INSTEAD OF <operation> any combination of INSERT, UPDATE, DELETE • Also triggers on events (e.g., system startup & shutdown, user logon & logoff, DDL statements, etc.) SFDV3002

38. Update triggers(Example 4–10(b), (c)) Triggers activated only if particular columns modified CREATE TRIGGER Update_Salary BEFORE UPDATE OF Salary ON Employee ... SFDV3002

39. Row vs. statement triggers(Kifer pp. 254–255; Concepts ch. 22 ▷ “Types of Trigger”; Example 4–10(e)) FOR EACH ROW • Trigger executed once per row affected by activating operation. FOR EACH STATEMENT (default) • Trigger executed once only, no matter how many rows affected. SFDV3002

40. Accessing row values in triggers • :OLD and :NEW versions of affected row, depending on activating operation. • Rename :OLD and :NEW using REFERENCING clause. (see Example 4–10(d)) SFDV3002

41. Conditional triggers: WHEN(Example 4–10(f)) • Extra condition(s) (no subqueries). • Not for: • statement triggers • INSTEAD OF triggers (see slide 42) SFDV3002

42. INSTEAD OF triggers(Concepts ch. 22 ▷ “Types of Trigger”; Kifer Example 7.3.4; Example 4–10(g)) • Modify data “stored in” views that cannot normally be updated. • Example: instead of DELETEing from view, rewrite to DELETE from underlying base tables. SFDV3002

43. Triggers & referential integrity(Kifer pp. 262–264) • Four possible reactions to PK value affected by UPDATE or DELETE: • Disallow change • Cascade change (ON <xxx> CASCADE) • Referencing FKs ⇒ null (ON <xxx> SET NULL) • Referencing FKs ⇒ default (ON <xxx> SET DEFAULT) • Oracle10g: ON DELETE CASCADE and ON DELETE SET NULL. SFDV3002

44. Mutating tables (Oracle only)(Oracle10g Application Developer’s Guide—Fundamentals ch. 9 ▷ “Restrictions on Creating Triggers”; Example 4–11) • Table(s) being modified by activating operation of trigger. • Includes effects of ON DELETE CASCADE but not changes by other users. • Triggers cannot access mutating tables. • Row triggers only. SFDV3002

45. Summary • Triggers provide means of automatically executing certain actions before or after some specific operation occurs. • Typically used for automatic calculations or referential integrity maintenance. SFDV3002