Transcript chapter11_rev

Chapter 11
Stored Procedures and Triggers
McGraw-Hill/Irwin
Copyright © 2007 by The McGraw-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
MySQL Data Types
 String: CHAR(L), VARCHAR(L)
 Numeric: INTEGER, DECIMAL(L,D),
FLOAT(L,D). SMALLINT
 Logical: BOOLEAN
 DATETIME: stores both date and time
11-12
Variable Declaration Examples





DECLARE aFixedLengthString CHAR(6) DEFAULT
'ABCDEF';
DECLARE aVariableLengthString VARCHAR(30);
DECLARE anIntegerVariable
INTEGER DEFAULT
0;
DECLARE aFixedPrecisionVariable DECIMAL(10,2);
DECLARE aDateVariable
DATE DEFAULT
Now();
11-13
Assignment Examples
 SET aFixedLengthString = 'XYZABC';
 SET aVariableLengthString =
aFixedLengthString + 'ABCDEF';
 -- To_Date is the date conversion function
 SET aDateVariable = To_Date('30-Jun2006');
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 (MySQL syntax):
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;
11-16
Formats of Iteration Statements
WHILE Statement:
WHILE condition DO
sequence of statements;
END WHILE;
LOOP Statement:
LOOP
sequence of statements containing a LEAVE statement;
END LOOP;
11-17
Common MySQL Commands





DESCRIBE: list table details
CALL: Executes a procedure
HELP: displays help for a topic
SET: assigns values to variables
SHOW: displays information (13.5.4)
11-18
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-19
Format of MySQL Procedures
CREATE PROCEDURE ProcedureName
[ (Parameter1, …, ParameterN) ]
BEGIN
sequence of statements
END;
11-20
Simple Procedure Example
DELIMITER $$
CREATE PROCEDURE pr_InsertProduct
(IN sProdNo char(8), IN vcProdName varchar(50),in vcProdMfg varchar(20),
in iProdQOH int, in dProdPrice decimal(12,2), in dtProdNextShipDate date)
BEGIN
INSERT INTO Product
(ProdNo, ProdName,ProdMfg, ProdQOH, ProdPrice, ProdNextShipDate)
VALUES
(sProdNo, vcProdName,vcProdMfg, iProdQOH, dProdPrice, dtProdNextShipDate);
END $$
DELIMITER ;
11-21
Format of PL/SQL Functions
CREATE [OR REPLACE] FUNCTION FunctionName
[ (Parameter1, …, ParameterN) ]
RETURN DataType
BEGIN
sequence of statements including a RETURN statement
END;
11-22
Simple Function Example
CREATE FUNCTION hello (s CHAR(20)) RETURNS
CHAR(50) NO SQL
RETURN CONCAT('Hello, ',s,'!');
11-23
MySQL Cursors
 Supports usage of SQL statements that
return a collection of rows
 Declaration statements
 DECLARE cursor_name CURSOR FOR
select_statement
 Actions on cursors
11-24
Cursor Example
CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a CHAR(16);
DECLARE b,c INT;
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
OPEN cur2;
REPEAT
FETCH cur1 INTO a, b;
FETCH cur2 INTO c;
IF NOT done THEN
IF b < c THEN
INSERT INTO test.t3 VALUES (a,b);
ELSE
INSERT INTO test.t3 VALUES (a,c);
END IF;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
CLOSE cur2;
END
11-25
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-26
Typical Usage of Triggers





Complex integrity constraints
Transition constraints
Update propagation
Exception reporting
Audit trail
11-27
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-28
Format of MySQL Triggers
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
11-29
AFTER ROW Trigger Example
DELIMITER $$
CREATE 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 OffNumEnrolled = OffNumEnrolled + 1
WHERE OfferNo = NEW.OfferNo;
END $$
DELIMITER ;
11-30
Guide to Trigger Examples
 BEFORE ROW:
 Complex integrity constraints
 Transition constraints
 Standardization of data
 AFTER ROW
 Update propagation
 Audit trail
 Exception reporting
11-31
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-32
Trigger Execution Procedure
 Inference engine that controls trigger firing
 Specifies execution order among triggers,
integrity constraints, and manipulation
statements
 Trigger body execution can cause other
triggers to fire
 SQL: standard trigger execution procedure
 Most DBMSs deviate from the standard
11-33
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-34
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-35
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-36
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-37
Questions & Discussion
11-38