Database Management System - Chapter 11: Stored Procedures and Triggers

Chapter 11Stored Procedures and TriggersOutline Database programming language backgroundStored proceduresTriggersMotivation for Database Programming LanguagesA procedural language with an interface to one or more DBMSs. Interface allows a program to combine procedural statements with nonprocedural database access. CustomizationBatch processingComplex operationsEfficiency and portabilityDesign IssuesLanguage style: call-level vs. statement-level interfaceBinding: static vs. dynamicDatabase connection: implicit vs. explicitResult processing: data types and processing orientationLanguage StyleCall-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 statementsMost DBMSs support both interfacesODBC and JDBC are widely used call-level interfaces BindingAssociation of access plan with an SQL statement Static binding: association at compile timeDynamic binding: association at run timeBinding options:Static and dynamic for statement-level interfaceDynamic for call-level interfaceReuse of access plans for repetitively executed statements in a program Database ConnectionImplicit for stored procedures and triggers because they are part of a database External programs: explicit connectionCONNECT statement or procedureWeb address or database identifierDatabase identifier is more flexible Results ProcessingData type mapping Processing orientationSELECT USING for single row resultsCursor for multiple row resultsCursor is similar to a dynamic arrayInterface provides statements or procedures to declare, open, close, iterate (position), and retrieve valuesOverview of PL/SQLProprietary database programming language for Oracle Widely used languageJava style syntax with a statement level interfaceUse PL/SQL for writing stored procedures and triggersUser Identifiers in PL/SQLProvide names for variables and constants Not case sensitiveRestrictionsAt most 30 charactersMust begin with a letterMust be uniqueAllowable characters are letters, numbers, _, #, and $PL/SQL ConstantsNumeric constants: whole numbers, fixed decimal numbers, and scientific notationString constants: use single quotes; case sensitiveBoolean constants: TRUE, FALSENULL: constant for every data typeNo string constants: use the To_Date function to create string constantsPL/SQL Data TypesString: CHAR(L), VARCHAR2(L)Numeric: INTEGER, DECIMAL(W,D), FLOAT(P). SMALLINTLogical: BOOLEANDATE: stores both date and time Variable Declaration ExamplesDECLARE aFixedLengthString CHAR(6) DEFAULT 'ABCDEF'; aVariableLengthString VARCHAR2(30); anIntegerVariable INTEGER := 0; aFixedPrecisionVariable DECIMAL(10,2); -- Uses the SysDate function for the default value aDateVariable DATE DEFAULT SysDate;-- Anchored declarations anOffTerm Offering.OffTerm%TYPE; anOffYear Offering.OffYear%TYPE; aCrsUnits Course.CrsUnits%TYPE; aSalary1 DECIMAL(10,2); aSalary2 aSalary1%TYPE; Assignment ExamplesaFixedLengthString := 'XYZABC';-- || is the string concatenation functionaVariableLengthString := aFixedLengthString || 'ABCDEF';anIntegerVariable := anAge + 1;aFixedPrecisionVariable := aSalary * 0.10;-- To_Date is the date conversion functionaDateVariable := To_Date('30-Jun-2006'); IF Statement FormatIF-THEN Statement: IF condition THEN sequence of statements END IF; IF-THEN-ELSE Statement: IF condition THEN sequence of statements 1ELSE sequence of statements 2END IF; CASE Statement FormatCASE Statement (Oracle 9i/10g only): CASE selector WHEN expression1 THEN sequence of statements 1 WHEN expression2 THEN sequence of statements 2 WHEN expressionN THEN sequence of statements N [ ELSE sequence of statements N+1 ]END CASE; Formats of Iteration StatementsFOR LOOP Statement: FOR variable IN BeginExpr .. EndExpr LOOP sequence of statementsEND LOOP; WHILE LOOP Statement: WHILE condition LOOP sequence of statementsEND LOOP; LOOP Statement: LOOP sequence of statements containing an EXIT statementEND LOOP; Common SQL *Plus CommandsCONNECT: login to a databaseDESCRIBE: list table detailsEXECUTE: execute statementsHELP: lists column detailsSET: assigns values to SQL *Plus environment variablesSHOW: displays error detailsSPOOL: send output to a file PL/SQL BlocksAnonymous blocks to test procedures and triggersNamed blocks for stored proceduresBlock Structure: [ DECLARE sequence of declarations ]BEGIN sequence of statements[ EXCEPTION sequence of statements to respond to exceptions ]END; Anonymous Block ExampleSET SERVEROUTPUT ON; -- SQL Plus command-- Anonymous blockDECLARE TmpSum INTEGER; TmpProd INTEGER; Idx INTEGER;BEGIN TmpSum := 0; TmpProd := 1; -- Use a loop to compute the sum and product FOR Idx IN 1 .. 10 LOOP TmpSum := TmpSum + Idx; TmpProd := TmpProd * Idx; END LOOP;Dbms_Output.Put_Line('Sum is ' || To_Char(TmpSum)); Dbms_Output.Put_Line('Product is ' || To_Char(TmpProd));END;/Motivation for Stored ProceduresCompilation of programming language statements and SQL statements Management of dependencies by the DBMSCentralized management of proceduresDevelopment of more complex functions and proceduresUsage of DBMS security system for stored procedures Format of PL/SQL ProceduresCREATE [OR REPLACE] PROCEDURE ProcedureName [ (Parameter1, , ParameterN) ]IS [ sequence of declarations ]BEGIN sequence of statements[ EXCEPTION sequence of statements to respond to exceptions ]END; Simple Procedure ExampleCREATE OR REPLACE PROCEDURE pr_InsertRegistration(aRegNo IN Registration.RegNo%TYPE, aStdSSN IN Registration.StdSSN%TYPE, aRegStatus IN Registration.RegStatus%TYPE, aRegDate IN Registration.RegDate%TYPE, aRegTerm IN Registration.RegTerm%TYPE, aRegYear IN Registration.RegYear%TYPE) IS-- Create a new registrationBEGININSERT INTO Registration (RegNo, StdSSN, RegStatus, RegDate, RegTerm, RegYear)VALUES (aRegNo, aStdSSN, aRegStatus, aRegDate, aRegTerm, aRegYear);dbms_output.put_line('Added a row to the table');END;/ Exception ExampleCREATE OR REPLACE PROCEDURE pr_InsertRegistration(aRegNo IN Registration.RegNo%TYPE, aStdSSN IN Registration.StdSSN%TYPE, aRegStatus IN Registration.RegStatus%TYPE, aRegDate IN Registration.RegDate%TYPE, aRegTerm IN Registration.RegTerm%TYPE, aRegYear IN Registration.RegYear%TYPE, aResult OUT BOOLEAN ) IS-- aResult is TRUE if successful, false otherwise.BEGINaResult := TRUE;INSERT INTO Registration (RegNo, StdSSN, RegStatus, RegDate, RegTerm, RegYear)VALUES (aRegNo, aStdSSN, aRegStatus, aRegDate, aRegTerm, aRegYear);EXCEPTION WHEN OTHERS THEN aResult := FALSE;END;Common Predefined ExceptionsCursor_Already_Open Dup_Val_On_IndexInvalid_CursorNo_Data_FoundRowtype_MismatchTimeout_On_ResourceToo_Many_Rows Format of PL/SQL FunctionsCREATE [OR REPLACE] FUNCTION FunctionName [ (Parameter1, , ParameterN) ]RETURN DataTypeIS [ sequence of declarations ]BEGIN sequence of statements including a RETURN statement[ EXCEPTION sequence of statements to respond to exceptions ]END; Simple Function ExampleCREATE OR REPLACE FUNCTION fn_RetrieveStdName(aStdSSN IN Student.StdSSN%type) RETURN VARCHAR2 ISaFirstName Student.StdFirstName%type;aLastName Student.StdLastName%type;BEGINSELECT StdFirstName, StdLastName INTO aFirstName, aLastName FROM Student WHERE StdSSN = aStdSSN;RETURN(aLastName || ', ' || aFirstName);EXCEPTIONWHEN No_Data_Found THEN RETURN(NULL); WHEN OTHERS THEN raise_application_error(-20001, 'Database error');END;PL/SQL CursorsSupports usage of SQL statements that return a collection of rows Declaration statementsSpecialized FOR statementCursor attributesActions on cursorsClassification of CursorsStatement binding: Static: SQL statement specified at compile-timeDynamic: SQL statement specified at executionDeclaration statusImplicit: declared, opened, and iterated inside a FOR statementExplicit: declared with the CURSOR statement in the DECLARE section Common Cursor Attributes%ISOpen: true if cursor is open %Found: true if cursor is not empty following a FETCH statement %NotFound: true if cursor is empty following a FETCH statement %RowCount: number of rows fetched PL/SQL PackagesLarger unit of modularityImproved reusabilityGroups procedures, functions, exceptions, variables, constants, types, and cursors. Public interface Private body: implementation of packageOracle provides predefined packages Format of Package InterfaceCREATE [OR REPLACE] PACKAGE PackageName IS[ Constant, variable, and type declarations ][ Cursor declarations ][ Exception declarations ][ Procedure definitions ][ Function definitions ]END PackageName; Format of Package BodyCREATE [OR REPLACE] PACKAGE BODY PackageName IS[ Variable and type declarations ][ Cursor declarations ][ Exception declarations ][ Procedure implementations ][ Function implementations ][ BEGIN sequence of statements ][ EXCEPTION exception handling statements ]END PackageName; Trigger OverviewEvent-Condition-Action (ECA) rulesManaged by DBMSExecution controlled by inference engineDBMS extended with inference enginePart of SQL:1999 and SQL:2003Widely implemented before SQL:1999 Typical Usage of TriggersComplex integrity constraintsTransition constraintsUpdate propagationException reportingAudit trailClassification of TriggersGranularityRow: fire for each modified rowStatement: fire once per statement Timing: before or afterEventManipulation statementsUpdate event with a list of columns Format of Oracle TriggersCREATE [OR REPLACE] TRIGGER TriggerName TriggerTiming TriggerEvent[ Referencing clause ][ FOR EACH ROW ][ WHEN ( Condition ) ][ DECLARE sequence of declarative statements ]BEGIN sequence of statements [ EXCEPTION exception handling statements ]END; AFTER ROW Trigger ExampleCREATE OR REPLACE TRIGGER tr_Enrollment_IA-- This trigger updates the number of enrolled-- students the related offering row.AFTER INSERTON EnrollmentFOR EACH ROWBEGIN UPDATE Offering SET OffNumEnrolled = OffNumEnrolled + 1 WHERE OfferNo = :NEW.OfferNo;EXCEPTION WHEN OTHERS THEN RAISE_Application_Error(-20001, 'Database error');END;Guide to Trigger ExamplesBEFORE ROW: Complex integrity constraintsTransition constraintsStandardization of dataAFTER ROWUpdate propagationAudit trailException reportingCompound Events in TriggersCompound events Use OR to specify multiple eventsTrigger body can detect the eventMultiple triggers versus compound event triggersMore triggers but less complexFewer, more complex triggersTrigger interaction increases with the number of triggersNo clear preferenceTrigger Execution ProcedureInference engine that controls trigger firing Specifies execution order among triggers, integrity constraints, and manipulation statementsTrigger body execution can cause other triggers to fireSQL: standard trigger execution procedureMost DBMSs deviate from the standard Simplified Oracle Trigger Execution Procedure1. 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. Overlapping TriggersDefinition:Two or more triggers with the same timing, granularity, and applicable eventSame SQL statement causes both triggers to fireSQL:2003 firing order based on trigger creation timeOracle: arbitrary firing orderCarefully analyze overlapping triggersRecursive Trigger Execution1. Execute the applicable BEFORE STATEMENT triggers.2. For each row affected by the SQL manipulation statement2.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. Controlling Trigger ComplexityAvoid 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. Mutating Table ErrorsRestriction on trigger execution in Oracle Mutating tables of a trigger:Table in which trigger is definedRelated tables affected by CASCADE DELETEOracle prohibits SQL statements in a trigger body on mutating tablesRun-time error during trigger executionResolving Mutating Table ErrorsAvoid by using new and old values Sometimes unavoidableTrigger to enforce integrity among rows of the same tableTrigger to insert a related row in a child table with DELETE CASCADEResolutionsPackage and a collection of triggersUse INSTEAD OF trigger for a viewSummaryStored procedures and triggers are important for database application development and database administrationBenefits for DBMS management of stored proceduresClassification of triggers by granularity, timing, event, and purposeKnowledge of trigger execution procedures