1 / 45

SFDV3002

SFDV3002. Chapter 4: Programming with SQL. 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. Database and application layers.

cachez
Download Presentation

SFDV3002

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related