This section contains examples of how to use each of the statements that can appear in an SQL procedure body. For these and other example SQL procedures, including client applications that call the SQL procedures, refer to the following directories:
Example 1: CASE statement: The following SQL procedure demonstrates how to use a CASE statement. The procedure receives the ID number and rating of an employee as input parameters. The CASE statement modifies the salary and bonus for the employee, using a different UPDATE statement for each of the possible ratings.
CREATE PROCEDURE UPDATE_SALARY
(IN employee_number CHAR(6), IN rating INT)
LANGUAGE SQL
BEGIN
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE EXIT HANDLER FOR not_found
SIGNAL SQLSTATE '02444';
CASE rating
WHEN 1 THEN
UPDATE employee
SET salary = salary * 1.10, bonus = 1000
WHERE empno = employee_number;
WHEN 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 CASE;
END
Example 2: Compound statement with nested IF and WHILE statements: The following example shows a compound statement that includes nested IF statements, a WHILE statement, and assignment statements. The example also shows how to declare SQL variables, cursors, and handlers for classes of error codes.
The procedure 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
Example 3: Using Nested SQL Procedures with Global Temporary Tables and Result Sets:
The following example shows how to use the ASSOCIATE RESULT SET LOCATOR and ALLOCATE CURSOR statements to return a result set from the called SQL procedure, temp_table_insert, to the calling SQL procedure, temp_table_create. The example also shows how a called SQL procedure can use a global temporary table that is created by a calling SQL procedure.
In the example, a client application or another SQL procedure calls temp_table_create, which creates the global temporary table SESSION.TTT and in turn calls temp_table_insert.
To use the SESSION.TTT global temporary table, temp_table_insert contains a DECLARE GLOBAL TEMPORARY TABLE statement identical to the statement that temp_table_create issues to create SESSION.TTT. The difference is that temp_table_insert contains the DECLARE GLOBAL TEMPORARY TABLE statement within an IF statement that is always false. The IF statement prevents DB2 from attempting to create the global temporary table for a second time, but enables the SQL procedure to use the global temporary table in subsequent statements.
To return a result set from a global temporary table that was created by a different SQL procedure, temp_table_insert must issue the DECLARE CURSOR statement within a new scope. temp_table_insert issues the DECLARE CURSOR and OPEN CURSOR statements within a compound SQL block, which satisfies the requirement for a new scope. The cursor is not closed before the SQL procedure exits, so DB2 passes the result set back to the caller, temp_table_create.
To accept the result set from the called SQL procedure, temp_table_create issues an ASSOCIATE RESULT SET LOCATOR statement that identifies temp_table_insert as the originator of the result set. temp_table_create then issues an ALLOCATE CURSOR statement for the result set locator to open the result set. If the ALLOCATE CURSOR statement succeeds, the SQL procedure can work with the result set as usual. In this example, temp_table_create fetches every row from the result set, adding the values of the columns to its output parameters.
| Note: | Before issuing a CREATE PROCEDURE statement for an SQL procedure that uses
global temporary tables, you must create a user temporary tablespace.
To create a user temporary tablespace, issue the following SQL
statement:
CREATE USER TEMPORARY TABLESPACE ts1
MANAGED BY SYSTEM USING ('ts1file');
where ts1 represents the name of the user temporary
tablespace, and ts1file represents the name of the container used by
the tablespace.
|
CREATE PROCEDURE temp_table_create(IN parm1 INTEGER, IN parm2 INTEGER,
OUT parm3 INTEGER, OUT parm4 INTEGER)
LANGUAGE SQL
BEGIN
DECLARE loc1 RESULT_SET_LOCATOR VARYING;
DECLARE total3,total4 INTEGER DEFAULT 0;
DECLARE rcolumn1, rcolumn2 INTEGER DEFAULT 0;
DECLARE result_set_end INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND, SQLEXCEPTION, SQLWARNING
BEGIN
SET result_set_end = 1;
END;
--Create the temporary table that is used in both this SQL procedure
--and in the SQL procedure called by this SQL procedure.
DECLARE GLOBAL TEMPORARY TABLE ttt(column1 INT, column2 INT)
NOT LOGGED;
--Insert rows into the temporary table.
--The result set includes these rows.
INSERT INTO session.ttt(column1, column2) VALUES ( parm1+1, parm2+1);
INSERT INTO session.ttt(column1, column2) VALUES ( parm1+2, parm2+2);
--Make a nested call to the 'temp_table_insert' SQL procedure.
CALL temp_table_insert(parm1, parm2);
--Issue the ASSOCIATE RESULT SET LOCATOR statement to
--accept a single result set from 'temp_table_insert'.
--If 'temp_table_insert' returns multiple result sets,
--you must declare one locator variable (for example,
--ASSOCIATE RESULT SET LOCATOR(loc1, loc2, loc3) for each result set.
ASSOCIATE RESULT SET LOCATOR(loc1) WITH PROCEDURE temp_table_insert;
--The ALLOCATE statement is similar to the OPEN statement.
--It makes the result set available in this SQL procedure.
ALLOCATE cursor1 CURSOR FOR RESULT SET loc1;
--Insert rows into the temporary table.
--The result set does not include these rows.
INSERT INTO session.ttt(column1, column2) VALUES ( parm1+5, parm2+5);
INSERT INTO session.ttt(column1, column2) VALUES ( parm1+6, parm2+6);
SET result_set_end = 0;
--Fetch the columns from the first row of the result set.
FETCH FROM cursor1 INTO rcolumn1, rcolumn2;
WHILE (result_set_end = 0) DO
SET total3 = total3 + rcolumn1;
SET total4 = total4 + rcolumn2;
--Fetch columns from the result set for the
--next iteration of the WHILE loop.
FETCH FROM cursor1 INTO rcolumn1, rcolumn2;
END WHILE;
CLOSE cursor1;
SET parm3 = total3;
SET parm4 = total4;
END @
CREATE PROCEDURE temp_table_insert (IN parm1 INTEGER, IN parm2 INTEGER )
LANGUAGE SQL
BEGIN
DECLARE result_set_end INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN
SET result_set_end = 1;
END;
--To use a temporary table that is created by a different stored
--procedure, include a DECLARE GLOBAL TEMPORARY TABLE statement
--inside a condition statement that always evaluates to false.
IF (1 = 0) THEN
DECLARE GLOBAL TEMPORARY TABLE ttt(column1 INT, column2 INT)
NOT LOGGED;
END IF;
--Insert rows into the temporary table.
--The result set includes these rows.
INSERT INTO session.ttt(column1, column2) VALUES ( parm1+3, parm2+3);
INSERT INTO session.ttt(column1, column2) VALUES ( parm1+4, parm2+4);
--To return a result set from the temporary table, issue
--the DECLARE CURSOR statement inside a new scope, such as
--a compound SQL statement (BEGIN...END block).
--Issue the DECLARE CURSOR statement after the DECLARE
--GLOBAL TEMPORARY TABLE statement.
BEGIN
--The WITH RETURN TO CALLER clause causes the SQL procedure
--to return its result set to the calling procedure.
DECLARE cur1 CURSOR WITH RETURN TO CALLER
FOR SELECT * FROM session.ttt;
--To return a result set, open a cursor without closing the cursor.
OPEN cur1 ;
END;
END