Universal Database (netDB2) Introductory Guide

Stored Procedure Language (SPL)

An SQL procedure has a procedure body, which contains the source statements for the stored procedure. The following example shows a CREATE PROCEDURE statement for a simple stored procedure. The procedure name, the list of parameters that are passed to or from the procedure, and the LANGUAGE parameter are common to all stored procedures. However, the LANGUAGE value of SQL and the BEGIN...END block, which forms the procedure body, are particular to an SQL procedure. Once you have created stored procedures, you can call them from within application programs using the SQL CALL statement. You can also call SQL CALL statement from within the script center.

Syntax: 
CREATE PROCEDURE UPDATE_SALARY_1          (1)
(IN EMPLOYEE_NUMBER integer,             (2)
IN RATE INTEGER)                          (2)
LANGUAGE SQL                              (3)
BEGIN
UPDATE EMPLOYEE                           (4)
SET SALARY = SALARY * (1.0 * RATE / 100.0 )
WHERE EMPNO = EMPLOYEE_NUMBER;
END
@

  1. The stored procedure name is UPDATE_SALARY_1.
  2. The two parameters are of data type INTEGER. Both are input parameters.
  3. LANGUAGE SQL indicates that this is an SQL procedure, so a procedure body follows the other parameters.
  4. The procedure body consists of a single SQL UPDATE statement, which updates rows in the employee table.

('@') is used as the terminating character for stored procedures in NetDB2.

To test this stored procedure, first create the Employee table with the required attributes. You can then call the stored procedure from the script center using the following CALL statement. 

 CALL UPDATE_SALARY_1 (10666, 500);

The above procedure call will raise the salary of the employee with employee number 10666 to 500% of his/her current salary 

CREATE PROCEDURE UPDATE_SALARY_IF
  (IN employee_number INTEGER, IN rating SMALLINT)
  LANGUAGE SQL
  BEGIN
    IF (rating = 1)
      THEN UPDATE employee
        SET salary = salary * 1.10, bonus = 1000
        WHERE empno = employee_number;
    ELSEIF (rating = 2)
      THEN UPDATE employee
        SET salary = salary * 1.05, bonus = 500
        WHERE empno = employee_number;
    ELSE UPDATE employee
        SET salary = salary * 1.03, bonus = 0
        WHERE empno = employee_number;
    END IF;
  END
@

The following example receives a department number as an input parameter. A WHILE statement in the procedure body fetches the salary and bonus for each employee in the department. An IF statement within the WHILE statement updates salaries for each employee depending on number of years of service and current salary. When all employee records in the department have been processed, the FETCH statement that retrieves employee records receives SQLSTATE 20000. A not_found condition handler makes the search condition for the WHILE statement false, so execution of the WHILE statement ends. 

CREATE PROCEDURE BUMP_SALARY_IF (IN deptnumber SMALLINT) 
  LANGUAGE SQL 
  BEGIN 
    DECLARE v_salary DOUBLE;
    DECLARE v_years SMALLINT;
    DECLARE v_id SMALLINT;
    DECLARE at_end INT DEFAULT 0;
    DECLARE not_found CONDITION FOR SQLSTATE '02000';

-- CAST salary as DOUBLE because SQL procedures do not support DECIMAL

    DECLARE C1 CURSOR FOR
      SELECT id, CAST(salary AS DOUBLE), years 
      FROM staff;
    DECLARE CONTINUE HANDLER FOR not_found 
      SET at_end = 1;
    OPEN C1;
    FETCH C1 INTO v_id, v_salary, v_years;
    WHILE at_end = 0 DO
      IF (v_salary < 2000 * v_years)
        THEN UPDATE staff 
          SET salary = 2150 * v_years 
          WHERE id = v_id;
      ELSEIF (v_salary < 5000 * v_years)
        THEN IF (v_salary < 3000 * v_years) 
          THEN UPDATE staff 
            SET salary = 3000 * v_years 
            WHERE id = v_id;
          ELSE UPDATE staff 
            SET salary = v_salary * 1.10 
            WHERE id = v_id;
        END IF;
      ELSE UPDATE staff 
        SET job = 'PREZ' 
        WHERE id = v_id;
    END IF;
    FETCH C1 INTO v_id, v_salary, v_years;
  END WHILE;
  CLOSE C1;
END
@

For any problems, questions or suggestions about this page please contact,
Mario (espinoza@ics.uci.edu)		 rev. Dec. 24, 2001