DECLARE

From SQL PL Guide for DB2

1 DECLARE statement

DECLARE statement

In a DB2 SQL PL stored procedure you can declare variables, conditions, handlers and cursors.

DECLARE VARIABLES

The declaration of variables is local to the compound statement (q.v. Variable name scoping). Variables are not case sensitive because DB2 converts all SQL variable names to uppercase. It's necessary to declare all variables which you're using later in your procedural logic. Furthermore you can specify a default value for a variable in the declare statement. After the declaration part in your code you can modify the declared variables with the SET statement.

Syntax:

DECLARE <variable-name> <datatype> [DEFAULT <value>];

DECLARE HANDLER

SQL PL supports three types of handlers: EXIT, CONTINUE, and UNDO.

The EXIT handler will execute the SQL PL statements in the handler. After that the handler will continue with execution at the end of the compound statement in which it was declared.

An UNDO handler is similar to the EXIT handler and continues with execution at the end of the compound statement in which it was declared. But instead of the EXIT handler each executed statement will be rolled back in this compound statement. UNDO handlers are just possible in ATOMIC compound statements.

In opposite to the EXIT handler, the CONTINUE handler will continue the execution at the statement which is following the statement that raised the exeption.

Syntax:

DECLARE CONTINUE|EXIT|UNDO HANDLER FOR <condition> <SQL PL statement or block>;

Example with a continue handler:

CREATE PROCEDURE DEL_DEPT (IN deldept VARCHAR(3))
 LANGUAGE SQL
 BEGIN ATOMIC
   DECLARE currentDEPT CHAR(3);
   DECLARE exitcode INTEGER DEFAULT 0;
   DECLARE mycur CURSOR FOR
       select DEPTNO as v_dept
       from DEPARTMENT
       where deptno = deldept;
   DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET exitcode = 1;

   OPEN mycur;
   REPEAT
     FETCH FROM mycur INTO currentDEPT;
     delete from EMPLOYEE where workdept = currentDEPT;
     UNTIL exitcode = 1
   END REPEAT;
   CLOSE mycur;
   delete from DEPARTMENT where deptno = deldept;
 END!

DECLARE CONDITION

In SQL PL are three general conditions: SQLEXCEPTION, SQLWARNING, and NOT FOUND. Furthermore you can declare your own conditions for a specific SQLSTATE by a DECLARE statement. There are two possibilities:

1) Use a condition handler:

DECLARE CONTINUE|EXIT|UNDO HANDLER FOR SQLSTATE '<state>' ....

2) Use a named condition:

DECLARE <condition-name> CONDITION FOR SQLSTATE [VALUE] '<sqlstate>';

Example with a named condition (similar to the continue handler example):

CREATE PROCEDURE DEL_DEPT (IN deldept VARCHAR(3))
 LANGUAGE SQL
 BEGIN ATOMIC
   DECLARE currentDEPT CHAR(3);
   DECLARE exitcode INTEGER DEFAULT 0;
   DECLARE no_more_rows CONDITION FOR SQLSTATE '02000';
   DECLARE mycur CURSOR FOR
       select DEPTNO as v_dept
       from DEPARTMENT
       where deptno = deldept;
   DECLARE CONTINUE HANDLER FOR no_more_rows
        SET exitcode = 1;

   OPEN mycur;
   REPEAT
     FETCH FROM mycur INTO currentDEPT;
     delete from EMPLOYEE where workdept = currentDEPT;
     UNTIL exitcode = 1
   END REPEAT;
   CLOSE mycur;
   delete from DEPARTMENT where deptno = deldept;
 END!

Retrieved from "http://sqlpl-guide.com/DECLARE"

Navigation