Defining an SQL procedure

 

The CREATE PROCEDURE statement for an SQL procedure names the procedure, defines the parameters and their attributes, provides other information about the procedure that is used when the procedure is called, and 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:

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 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:

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:

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.

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:

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:

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'.

 

Parent topic:

Stored procedures