SQL Procedure
The CREATE PROCEDURE statement for SQL procedures:
* Names the procedure
* Defines the parameters and their attributes
* Provides other information about the procedure which will be used when the procedure is called
* Defines the procedure body. The procedure body is the executable part of the procedure and is a single SQL statement.
Consider the following simple example that takes as input an employee number and a rate and updates the employee's salary:
CREATE PROCEDURE UPDATE_SALARY_1
(IN EMPLOYEE_NUMBER CHAR(10),
IN RATE DECIMAL(6,2))
LANGUAGE SQL MODIFIES SQL DATA
UPDATE CORPDATA.EMPLOYEE
SET SALARY = SALARY * RATE
WHERE EMPNO = EMPLOYEE_NUMBER
This CREATE PROCEDURE statement:
* Names the procedure UPDATE_SALARY_1.
* Defines parameter EMPLOYEE_NUMBER which is an input parameter and is a character data type of length 6 and parameter RATE which is an input parameter and is a decimal data type.
* Indicates the procedure is an SQL procedure that modifies SQL data.
* Defines the procedure body as a single UPDATE statement. When the procedure is called, the UPDATE statement is executed using the values passed for EMPLOYEE_NUMBER and RATE.
Instead of a single UPDATE statement, logic can be added to the SQL procedure using SQL control statements. SQL control statements consist of the following:
* an assignment statement
* a CALL statement
* a CASE statement
* a compound statement
* a FOR statement
* a GET DIAGNOSTICS statement
* a GOTO statement
* an IF statement
* an ITERATE statement
* a LEAVE statement
* a LOOP statement
* a REPEAT statement
* a RESIGNAL statement
* a RETURN statement
* a SIGNAL statement
* a WHILE statement
The following example takes as input the employee number and a rating that was received on the last evaluation. The procedure uses a CASE statement to determine the appropriate increase and bonus for the update:
CREATE PROCEDURE UPDATE_SALARY_2
(IN EMPLOYEE_NUMBER CHAR(6),
IN RATING INT)
LANGUAGE SQL MODIFIES SQL DATA
CASE RATING
WHEN 1 THEN
UPDATE CORPDATA.EMPLOYEE
SET SALARY = SALARY * 1.10,
BONUS = 1000
WHERE EMPNO = EMPLOYEE_NUMBER;
WHEN 2 THEN
UPDATE CORPDATA.EMPLOYEE
SET SALARY = SALARY * 1.05,
BONUS = 500
WHERE EMPNO = EMPLOYEE_NUMBER;
ELSE
UPDATE CORPDATA.EMPLOYEE
SET SALARY = SALARY * 1.03,
BONUS = 0
WHERE EMPNO = EMPLOYEE_NUMBER;
END CASE
This CREATE PROCEDURE statement:
* Names the procedure UPDATE_SALARY_2.
* Defines parameter EMPLOYEE_NUMBER which is an input parameter and is a character data type of length 6 and parameter RATING which is an input parameter and is an integer data type.
* Indicates the procedure is an SQL procedure that modifies SQL data.
* Defines the procedure body. When the procedure is called, input parameter RATING is checked and the appropriate update statement is executed.
Multiple statements can be added to a procedure body by adding a compound statement. Within a compound statement, any number of SQL statements can be specified. In addition, SQL variables, cursors, and handlers can be declared.
The following example takes as input the department number. It returns the total salary of all the employees in that department and the number of employees in that department who get a bonus.
CREATE PROCEDURE RETURN_DEPT_SALARY
(IN DEPT_NUMBER CHAR(3),
OUT DEPT_SALARY DECIMAL(15,2),
OUT DEPT_BONUS_CNT INT)
LANGUAGE SQL READS SQL DATA
P1: BEGIN
DECLARE EMPLOYEE_SALARY DECIMAL(9,2);
DECLARE EMPLOYEE_BONUS DECIMAL(9,2);
DECLARE TOTAL_SALARY DECIMAL(15,2)DEFAULT 0;
DECLARE BONUS_CNT INT DEFAULT 0;
DECLARE END_TABLE INT DEFAULT 0;
DECLARE C1 CURSOR FOR
SELECT SALARY, BONUS FROM CORPDATA.EMPLOYEE
WHERE WORKDEPT = DEPT_NUMBER;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET END_TABLE = 1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SET DEPT_SALARY = NULL;
OPEN C1;
FETCH C1 INTO EMPLOYEE_SALARY, EMPLOYEE_BONUS;
WHILE END_TABLE = 0 DO
SET TOTAL_SALARY = TOTAL_SALARY + EMPLOYEE_SALARY + EMPLOYEE_BONUS;
IF EMPLOYEE_BONUS > 0 THEN
SET BONUS_CNT = BONUS_CNT + 1;
END IF;
FETCH C1 INTO EMPLOYEE_SALARY, EMPLOYEE_BONUS;
END WHILE;
CLOSE C1;
SET DEPT_SALARY = TOTAL_SALARY;
SET DEPT_BONUS_CNT = BONUS_CNT;
END P1
This CREATE PROCEDURE statement:
* Names the procedure RETURN_DEPT_SALARY.
* Defines parameter DEPT_NUMBER which is an input parameter and is a character data type of length 3, parameter DEPT_SALARY which is an output parameter and is a decimal data type, and parameter DEPT_BONUS_CNT which is an output parameter and is an integer data type.
* Indicates the procedure is an SQL procedure that reads SQL data
* Defines the procedure body.
o Declares SQL variables EMPLOYEE_SALARY and TOTAL_SALARY as decimal fields.
o Declares SQL variables BONUS_CNT and END_TABLE which are integers and are initialized to 0.
o Declares cursor C1 that selects the columns from the employee table.
o Declares a continue handler for NOT FOUND, which, when called sets variable END_TABLE to 1. This handler is called when the FETCH has no more rows to return. When the handler is called, SQLCODE and SQLSTATE are reinitialized to 0.
o Declares an exit handler for SQLEXCEPTION. If called, DEPT_SALARY is set to NULL and the processing of the compound statement is terminated. This handler is called if any errors occur, that is, the SQLSTATE class is not '00', '01' or '02'. Since indicators are always passed to SQL procedures, the indicator value for DEPT_SALARY is -1 when the procedure returns. If this handler is called, SQLCODE and SQLSTATE are reinitialized to 0.
If the handler for SQLEXCEPTION is not specified and an error occurs that is not handled in another handler, execution of the compound statement is terminated and the error is returned in the SQLCA. Similar to indicators, the SQLCA is always returned from SQL procedures.
o Includes an OPEN, FETCH, and CLOSE of cursor C1. If a CLOSE of the cursor is not specified, the cursor is closed at the end of the compound statement since SET RESULT SETS is not specified in the CREATE PROCEDURE statement.
o Includes a WHILE statement which loops until the last record is fetched. For each row retrieved, the TOTAL_SALARY is incremented and, if the employee's bonus is more than 0, the BONUS_CNT is incremented.
o Returns DEPT_SALARY and DEPT_BONUS_CNT as output parameters.
Compound statements can be made atomic so if an error occurs that is not expected, the statements within the atomic statement are rolled back. The atomic compound statements are implemented using SAVEPOINTS. If the compound statement is successful, the transaction is committed. For more information about using SAVEPOINTS, see Savepoints.
The following example takes as input the department number. It ensures the EMPLOYEE_BONUS table exists, and inserts the name of all employees in the department who get a bonus. The procedure returns the total count of all employees who get a bonus.
CREATE PROCEDURE CREATE_BONUS_TABLE
(IN DEPT_NUMBER CHAR(3),
INOUT CNT INT)
LANGUAGE SQL MODIFIES SQL DATA
CS1: BEGIN ATOMIC
DECLARE NAME VARCHAR(30) DEFAULT NULL;
DECLARE CONTINUE HANDLER FOR SQLSTATE '42710'
SELECT COUNT(*) INTO CNT
FROM DATALIB.EMPLOYEE_BONUS;
DECLARE CONTINUE HANDLER FOR SQLSTATE '23505'
SET CNT = CNT - 1;
DECLARE UNDO HANDLER FOR SQLEXCEPTION
SET CNT = NULL;
IF DEPT_NUMBER IS NOT NULL THEN
CREATE TABLE DATALIB.EMPLOYEE_BONUS
(FULLNAME VARCHAR(30),
BONUS DECIMAL(10,2),
PRIMARY KEY (FULLNAME));
FOR_1:FOR V1 AS C1 CURSOR FOR
SELECT FIRSTNME, MIDINIT, LASTNAME, BONUS
FROM CORPDATA.EMPLOYEE
WHERE WORKDEPT = CREATE_BONUS_TABLE.DEPT_NUMBER
DO
IF BONUS > 0 THEN
SET NAME = FIRSTNME CONCAT ' ' CONCAT
MIDINIT CONCAT ' 'CONCAT LASTNAME;
INSERT INTO DATALIB.EMPLOYEE_BONUS
VALUES(CS1.NAME, FOR_1.BONUS);
SET CNT = CNT + 1;
END IF;
END FOR FOR_1;
END IF;
END CS1
This CREATE PROCEDURE statement:
* Names the procedure CREATE_BONUS_TABLE.
* Defines parameter DEPT_NUMBER which is an input parameter and is a character data type of length 3 and parameter CNT which is an input/output parameter and is an integer data type.
* Indicates the procedure is an SQL procedure that modifies SQL data
* Defines the procedure body.
o Declares SQL variable NAME as varying character.
o Declares a continue handler for SQLSTATE 42710, table already exists. If the EMPLOYEE_BONUS table already exists, the handler is called and retrieves the number of records in the table. The SQLCODE and SQLSTATE are reset to 0 and processing continues with the FOR statement.
o Declares a continue handler for SQLSTATE 23505, duplicate key. If the procedure attempts to insert a name that already exists in the table, the handler is called and decrements CNT. Processing continues on the SET statement following the INSERT statement.
o Declares an UNDO handler for SQLEXCEPTION. If called, the previous statements are rolled back, CNT is set to 0, and processing continues after the compound statement. In this case, since there is no statement following the compound statement, the procedure returns.
o Uses the FOR statement to declare cursor C1 to read the records from the EMPLOYEE table. Within the FOR statement, the column names from the select list are used as SQL variables that contain the data from the row fetched. For each row, data from columns FIRSTNME, MIDINIT, and LASTNAME are concatenated together with a blank in between and the result is put in SQL variable NAME. SQL variables NAME and BONUS are inserted into the EMPLOYEE_BONUS table. Because the data type of the select list items must be known when the procedure is created, the table specified in the FOR statement must exist when the procedure is created.
An SQL variable name can be qualified with the label name of the FOR statement or compound statement in which it is defined. In the example, FOR_1.BONUS refers to the SQL variable that contains the value of column BONUS for each row selected. CS1.NAME is the variable NAME defined in the compound statement with the beginning label CS1. Parameter names can also be qualified with the procedure name. CREATE_BONUS_TABLE.DEPT_NUMBER is the DEPT_NUMBER parameter for the procedure CREATE_BONUS_TABLE. If unqualified SQL variable names are used in SQL statements where column names are also allowed, and the variable name is the same as a column name, the name will be used to refer to the column.
You can also use dynamic SQL in an SQL procedure. The following example creates a table that contains all employees in a specific department. The department number is passed as input to the procedure and is concatenated to the table name.
CREATE PROCEDURE CREATE_DEPT_TABLE (IN P_DEPT CHAR(3))
LANGUAGE SQL
BEGIN
DECLARE STMT CHAR(1000);
DECLARE MESSAGE CHAR(20);
DECLARE TABLE_NAME CHAR(30);
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET MESSAGE = 'ok';
SET TABLE_NAME = 'CORPDATA.DEPT_' CONCAT P_DEPT CONCAT '_T';
SET STMT = 'DROP TABLE ' CONCAT TABLE_NAME;
PREPARE S1 FROM STMT;
EXECUTE S1;
SET STMT = 'CREATE TABLE ' CONCAT TABLE_NAME CONCAT
'(EMPNO CHAR(6) NOT NULL,
FIRSTNME VARCHAR(12) NOT NULL,
MIDINIT CHAR(1) NOT NULL,
LASTNAME CHAR(15) NOT NULL,
SALARY DECIMAL(9,2))';
PREPARE S2 FROM STMT;
EXECUTE S2;
SET STMT = 'INSERT INTO ' CONCAT TABLE_NAME CONCAT
'SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, SALARY
FROM CORPDATA.EMPLOYEE
WHERE WORKDEPT = ?';
PREPARE S3 FROM STMT;
EXECUTE S3 USING P_DEPT;
END
This CREATE PROCEDURE statement:
* Names the procedure CREATE_DEPT_TABLE
* Defines parameter P_DEPT which is an input parameter and is a character data type of length 3.
* Indicates the procedure is an SQL procedure.
* Defines the procedure body.
o Declares SQL variable STMT and an SQL variable TABLE_NAME as character.
o Declares a CONTINUE handler. The procedure attempts to DROP the table in case it already exists. If the table does not exist, the first EXECUTE fails. With the handler, processing will continue.
o Sets variable TABLE_NAME to 'DEPT_' followed by the characters passed in parameter P_DEPT, followed by '_T'.
o Sets variable STMT to the DROP statement, and prepares and executes the statement.
o Sets variable STMT to the CREATE statement, and prepares and executes the statement.
o Sets variable STMT to the INSERT statement, and prepares and executes the statement. A parameter marker is specified in the where clause. When the statement is executed, the variable P_DEPT is passed on the USING clause.
If the procedure is called passing value 'D21' for the department, table DEPT_D21_T is created and the table is initialized with all the employees that are in department 'D21'.
No comments:
Post a Comment