Chapter 11 Stored Procedures and Triggers Mc GrawHillIrwin

Chapter 11 Stored Procedures and Triggers Mc. Graw-Hill/Irwin Copyright © 2007 by The Mc. Graw-Hill Companies, Inc. All rights reserved.

Outline § Database programming language background § Stored procedures § Triggers 11 -2

Motivation for Database Programming Languages § A procedural language with an interface to one or more DBMSs. § Interface allows a program to combine procedural statements with nonprocedural database access. § Customization § Batch processing § Complex operations § Efficiency and portability 11 -3

Design Issues § Language style: call-level vs. statementlevel interface § Binding: static vs. dynamic § Database connection: implicit vs. explicit § Result processing: data types and processing orientation 11 -4

Language Style § Call-level interface: a set of procedures and a set of type definitions for manipulating the results of SQL statements § Statement-level interface: changes to the syntax of a host programming language to accommodate embedded SQL statements § Most DBMSs support both interfaces § ODBC and JDBC are widely used calllevel interfaces 11 -5

Binding § Association of access plan with an SQL statement § Static binding: association at compile time § Dynamic binding: association at run time § Binding options: § Static and dynamic for statement-level interface § Dynamic for call-level interface § Reuse of access plans for repetitively executed statements in a program 11 -6

Database Connection § Implicit for stored procedures and triggers because they are part of a database § External programs: explicit connection § CONNECT statement or procedure § Web address or database identifier § Database identifier is more flexible 11 -7

Results Processing § Data type mapping § Processing orientation § § SELECT USING for single row results Cursor for multiple row results Cursor is similar to a dynamic array Interface provides statements or procedures to declare, open, close, iterate (position), and retrieve values 11 -8

Overview of PL/SQL § Proprietary database programming language for Oracle § Widely used language § Java style syntax with a statement level interface § Use PL/SQL for writing stored procedures and triggers 11 -9

User Identifiers in PL/SQL § Provide names for variables and constants § Not case sensitive § Restrictions § § At most 30 characters Must begin with a letter Must be unique Allowable characters are letters, numbers, _, #, and $ 11 -10

PL/SQL Constants § Numeric constants: whole numbers, fixed decimal numbers, and scientific notation § String constants: use single quotes; case sensitive § Boolean constants: TRUE, FALSE § NULL: constant for every data type § No string constants: use the To_Date function to create string constants 11 -11

PL/SQL Data Types § String: CHAR(L), VARCHAR 2(L) § Numeric: INTEGER, DECIMAL(W, D), FLOAT(P). SMALLINT § Logical: BOOLEAN § DATE: stores both date and time 11 -12

Variable Declaration Examples DECLARE a. Fixed. Length. String CHAR(6) DEFAULT 'ABCDEF'; a. Variable. Length. String VARCHAR 2(30); an. Integer. Variable INTEGER : = 0; a. Fixed. Precision. Variable DECIMAL(10, 2); -- Uses the Sys. Date function for the default value a. Date. Variable DATE DEFAULT Sys. Date; -- Anchored declarations an. Off. Term Offering. Off. Term%TYPE; an. Off. Year Offering. Off. Year%TYPE; a. Crs. Units Course. Crs. Units%TYPE; a. Salary 1 DECIMAL(10, 2); a. Salary 2 a. Salary 1%TYPE; 11 -13

Assignment Examples a. Fixed. Length. String : = 'XYZABC'; -- || is the string concatenation function a. Variable. Length. String : = a. Fixed. Length. String || 'ABCDEF'; an. Integer. Variable : = an. Age + 1; a. Fixed. Precision. Variable : = a. Salary * 0. 10; -- To_Date is the date conversion function a. Date. Variable : = To_Date('30 -Jun-2006'); 11 -14

IF Statement Format IF-THEN Statement: IF condition THEN sequence of statements END IF; IF-THEN-ELSE Statement: IF condition THEN sequence of statements 1 ELSE sequence of statements 2 END IF; 11 -15

CASE Statement Format CASE Statement (Oracle 9 i/10 g only): CASE selector WHEN expression 1 THEN sequence of statements 1 WHEN expression 2 THEN sequence of statements 2 WHEN expression. N THEN sequence of statements N [ ELSE sequence of statements N+1 ] END CASE; 11 -16

Formats of Iteration Statements FOR LOOP Statement: FOR variable IN Begin. Expr. . End. Expr LOOP sequence of statements END LOOP; WHILE LOOP Statement: WHILE condition LOOP sequence of statements END LOOP; LOOP Statement: LOOP sequence of statements containing an EXIT statement END LOOP; 11 -17

Common SQL *Plus Commands § § § CONNECT: login to a database DESCRIBE: list table details EXECUTE: execute statements HELP: lists column details SET: assigns values to SQL *Plus environment variables § SHOW: displays error details § SPOOL: send output to a file 11 -18

PL/SQL Blocks • Anonymous blocks to test procedures and triggers • Named blocks for stored procedures Block Structure: [ DECLARE sequence of declarations ] BEGIN sequence of statements [ EXCEPTION sequence of statements to respond to exceptions ] END; 11 -19

Anonymous Block Example SET SERVEROUTPUT ON; -- SQL Plus command -- Anonymous block DECLARE Tmp. Sum INTEGER; Tmp. Prod INTEGER; Idx INTEGER; BEGIN Tmp. Sum : = 0; Tmp. Prod : = 1; -- Use a loop to compute the sum and product FOR Idx IN 1. . 10 LOOP Tmp. Sum : = Tmp. Sum + Idx; Tmp. Prod : = Tmp. Prod * Idx; END LOOP; Dbms_Output. Put_Line('Sum is ' || To_Char(Tmp. Sum)); Dbms_Output. Put_Line('Product is ' || To_Char(Tmp. Prod)); END; / 11 -20

Motivation for Stored Procedures § Compilation of programming language statements and SQL statements § Management of dependencies by the DBMS § Centralized management of procedures § Development of more complex functions and procedures § Usage of DBMS security system for stored procedures 11 -21

Format of PL/SQL Procedures CREATE [OR REPLACE] PROCEDURE Procedure. Name [ (Parameter 1, …, Parameter. N) ] IS [ sequence of declarations ] BEGIN sequence of statements [ EXCEPTION sequence of statements to respond to exceptions ] END; 11 -22

Simple Procedure Example CREATE OR REPLACE PROCEDURE pr_Insert. Registration (a. Reg. No IN Registration. Reg. No%TYPE, a. Std. SSN IN Registration. Std. SSN%TYPE, a. Reg. Status IN Registration. Reg. Status%TYPE, a. Reg. Date IN Registration. Reg. Date%TYPE, a. Reg. Term IN Registration. Reg. Term%TYPE, a. Reg. Year IN Registration. Reg. Year%TYPE) IS -- Create a new registration BEGIN INSERT INTO Registration (Reg. No, Std. SSN, Reg. Status, Reg. Date, Reg. Term, Reg. Year) VALUES (a. Reg. No, a. Std. SSN, a. Reg. Status, a. Reg. Date, a. Reg. Term, a. Reg. Year); dbms_output. put_line('Added a row to the table'); END; / 11 -23

Exception Example CREATE OR REPLACE PROCEDURE pr_Insert. Registration (a. Reg. No IN Registration. Reg. No%TYPE, a. Std. SSN IN Registration. Std. SSN%TYPE, a. Reg. Status IN Registration. Reg. Status%TYPE, a. Reg. Date IN Registration. Reg. Date%TYPE, a. Reg. Term IN Registration. Reg. Term%TYPE, a. Reg. Year IN Registration. Reg. Year%TYPE, a. Result OUT BOOLEAN ) IS -- a. Result is TRUE if successful, false otherwise. BEGIN a. Result : = TRUE; INSERT INTO Registration (Reg. No, Std. SSN, Reg. Status, Reg. Date, Reg. Term, Reg. Year) VALUES (a. Reg. No, a. Std. SSN, a. Reg. Status, a. Reg. Date, a. Reg. Term, a. Reg. Year); EXCEPTION WHEN OTHERS THEN a. Result : = FALSE; END; 11 -24

Common Predefined Exceptions § § § § Cursor_Already_Open Dup_Val_On_Index Invalid_Cursor No_Data_Found Rowtype_Mismatch Timeout_On_Resource Too_Many_Rows 11 -25

Format of PL/SQL Functions CREATE [OR REPLACE] FUNCTION Function. Name [ (Parameter 1, …, Parameter. N) ] RETURN Data. Type IS [ sequence of declarations ] BEGIN sequence of statements including a RETURN statement [ EXCEPTION sequence of statements to respond to exceptions ] END; 11 -26

Simple Function Example CREATE OR REPLACE FUNCTION fn_Retrieve. Std. Name (a. Std. SSN IN Student. Std. SSN%type) RETURN VARCHAR 2 IS a. First. Name Student. Std. First. Name%type; a. Last. Name Student. Std. Last. Name%type; BEGIN SELECT Std. First. Name, Std. Last. Name INTO a. First. Name, a. Last. Name FROM Student WHERE Std. SSN = a. Std. SSN; RETURN(a. Last. Name || ', ' || a. First. Name); EXCEPTION WHEN No_Data_Found THEN RETURN(NULL); WHEN OTHERS THEN raise_application_error(-20001, 'Database error'); END; 11 -27

PL/SQL Cursors § Supports usage of SQL statements that return a collection of rows § Declaration statements § Specialized FOR statement § Cursor attributes § Actions on cursors 11 -28

Classification of Cursors § Statement binding: § Static: SQL statement specified at compiletime § Dynamic: SQL statement specified at execution § Declaration status § Implicit: declared, opened, and iterated inside a FOR statement § Explicit: declared with the CURSOR statement in the DECLARE section 11 -29

Common Cursor Attributes § %ISOpen: true if cursor is open § %Found: true if cursor is not empty following a FETCH statement § %Not. Found: true if cursor is empty following a FETCH statement § %Row. Count: number of rows fetched 11 -30

PL/SQL Packages § Larger unit of modularity § Improved reusability § Groups procedures, functions, exceptions, variables, constants, types, and cursors. § Public interface § Private body: implementation of package § Oracle provides predefined packages 11 -31

Format of Package Interface CREATE [OR REPLACE] PACKAGE Package. Name IS [ Constant, variable, and type declarations ] [ Cursor declarations ] [ Exception declarations ] [ Procedure definitions ] [ Function definitions ] END Package. Name; 11 -32

Format of Package Body CREATE [OR REPLACE] PACKAGE BODY Package. Name IS [ Variable and type declarations ] [ Cursor declarations ] [ Exception declarations ] [ Procedure implementations ] [ Function implementations ] [ BEGIN sequence of statements ] [ EXCEPTION exception handling statements ] END Package. Name; 11 -33

Trigger Overview § § § Event-Condition-Action (ECA) rules Managed by DBMS Execution controlled by inference engine DBMS extended with inference engine Part of SQL: 1999 and SQL: 2003 Widely implemented before SQL: 1999 11 -34

Typical Usage of Triggers § § § Complex integrity constraints Transition constraints Update propagation Exception reporting Audit trail 11 -35

Classification of Triggers § Granularity § Row: fire for each modified row § Statement: fire once per statement § Timing: before or after § Event § Manipulation statements § Update event with a list of columns 11 -36

Format of Oracle Triggers CREATE [OR REPLACE] TRIGGER Trigger. Name Trigger. Timing Trigger. Event [ Referencing clause ] [ FOR EACH ROW ] [ WHEN ( Condition ) ] [ DECLARE sequence of declarative statements ] BEGIN sequence of statements [ EXCEPTION exception handling statements ] END; 11 -37

AFTER ROW Trigger Example CREATE OR REPLACE TRIGGER tr_Enrollment_IA -- This trigger updates the number of enrolled -- students the related offering row. AFTER INSERT ON Enrollment FOR EACH ROW BEGIN UPDATE Offering SET Off. Num. Enrolled = Off. Num. Enrolled + 1 WHERE Offer. No = : NEW. Offer. No; EXCEPTION WHEN OTHERS THEN RAISE_Application_Error(-20001, 'Database error'); END; 11 -38

Guide to Trigger Examples § BEFORE ROW: § Complex integrity constraints § Transition constraints § Standardization of data § AFTER ROW § Update propagation § Audit trail § Exception reporting 11 -39

Compound Events in Triggers § Compound events § Use OR to specify multiple events § Trigger body can detect the event § Multiple triggers versus compound event triggers § More triggers but less complex § Fewer, more complex triggers § Trigger interaction increases with the number of triggers § No clear preference 11 -40

Trigger Execution Procedure § Inference engine that controls trigger firing § Specifies execution order among triggers, integrity constraints, and manipulation statements § Trigger body execution cause other triggers to fire § SQL: standard trigger execution procedure § Most DBMSs deviate from the standard 11 -41

Simplified Oracle Trigger Execution Procedure 1. Execute the applicable BEFORE STATEMENT triggers. 2. For each row affected by the SQL manipulation statement: 2. 1 Execute the applicable BEFORE ROW triggers. 2. 2 Perform the data manipulation operation on the row. 2. 3 Perform integrity constraint checking. 2. 4 Execute the applicable AFTER ROW triggers. 3. Perform deferred integrity constraint checking. 4. Execute the applicable AFTER statement triggers. 11 -42

Overlapping Triggers § Definition: § Two or more triggers with the same timing, granularity, and applicable event § Same SQL statement causes both triggers to fire § SQL: 2003 firing order based on trigger creation time § Oracle: arbitrary firing order § Carefully analyze overlapping triggers 11 -43

Recursive Trigger Execution 1. Execute the applicable BEFORE STATEMENT triggers. 2. For each row affected by the SQL manipulation statement 2. 1. Execute the applicable BEFORE ROW triggers. Recursively execute the procedure for data manipulation statements in a trigger. 2. 2. Perform the data manipulation operation on the row. 2. 3. Perform integrity constraint checking. Recursively execute the procedure for actions on referenced rows. 2. 4. Execute the applicable AFTER ROW triggers. Recursively execute the procedure for data manipulation statements in a trigger. 3. Perform deferred integrity constraint checking. 4. Execute the applicable AFTER statement triggers. 11 -44

Controlling Trigger Complexity § Avoid data manipulation statements in BEFORE triggers § Limit data manipulation statements in AFTER triggers. § For triggers that fire on UPDATE statements, always list the columns. § Ensure that overlapping triggers do not depend on a specific order to fire. § Be cautious about triggers on tables affected by actions on referenced rows. 11 -45

Mutating Table Errors § Restriction on trigger execution in Oracle § Mutating tables of a trigger: § Table in which trigger is defined § Related tables affected by CASCADE DELETE § Oracle prohibits SQL statements in a trigger body on mutating tables § Run-time error during trigger execution 11 -46

Resolving Mutating Table Errors § Avoid by using new and old values § Sometimes unavoidable § Trigger to enforce integrity among rows of the same table § Trigger to insert a related row in a child table with DELETE CASCADE § Resolutions § Package and a collection of triggers § Use INSTEAD OF trigger for a view 11 -47

Summary § Stored procedures and triggers are important for database application development and database administration § Benefits for DBMS management of stored procedures § Classification of triggers by granularity, timing, event, and purpose § Knowledge of trigger execution procedures 11 -48