DEFAULT   值 – 如果没有指定，在声明时将赋值为 NULL。

下面是变量声明的一些例子：

• DECLARE v_salary DEC(9,2) DEFAULT 0.0;
• DECLARE v_status char(3) DEFAULT ‘YES’;
• DECLARE v_descrition VARCHAR(80);
• DECLARE v1, v2 INT DEFAULT 0;

请注意，从 DB2 version 9.5 开始才支持在一个 DECLARE 语句中声明多个相同数据类型的变量。

数组数据类型

SQL 过程从 9.5 版开始支持数组类型的变量和参数。要定义一个数组类型的变量，需要先在数据库中创建该类型，然后在过程或应用程序中声明它。数组是临时的值，可以在存储过程和应用程序中操纵它，但是不能将它存储到表中。

DB2 支持以下创建数组数据类型的语法：

清单 2. 创建数组数据类型的语法

>>-CREATE TYPE—array-type-name--AS--| data-type |--ARRAY--[---------->

   .-2147483647-------.      
>--+------------------+--]-------------------------------------><
    '-integer-constant-' 

数组类型的名称应该用模式加以限定，并且在当前服务器上应该是惟一的。LONG VARCHAR、LONG VARGRPAHIC、XML 和用户定义类型不能作为数组元素的数据类型。

下面是数组类型的例子：

CREATE TYPE numbers as INTEGER ARRAY[100];
CREATE TYPE names as VARCHAR(30) ARRAY[]; 
CREATE TYPE MYSCHEMA.totalcomp as DECIMAL(12,2) ARRAY[]; 

请注意，整数 “constant” 指定数组的最大基数，它是可选的。数组元素可以通过 ARRAY-VARIABLE(subindex) 来引用，其中 subindex 必须介于 1 到数组的基数之间。

现在可以在 SQL 过程中使用这个数据类型：

清单 3. 在过程中使用数组数据类型

CREATE PROCEDURE PROC_VARRAY_test (out mynames names) 
 BEGIN
DECLARE v_pnumb numbers; 
SET v_pnumb = ARRAY[1,2,3,5,7,11];
SET mynames(1) =’MARINA’;

…
END

DB2 支持一些操作数组的方法。例如，函数 CARDINALITY(myarray) 返回一个数组中元素的个数。

赋值

SQL PL 提供了 SET 语句来为变量和数组元素赋值。

下面是一个 SET 语句的简化的语法：

SET variable_name = value/expression/NULL;

这个变量名可以是一个本地变量、全局变量或数组元素的名称。

下面是一些例子：

SET var1 = 10; 
SET total = (select sum(c1) from T1);
SET var2 = POSSTR(‘MYTEST’,’TEST’);
SET v_numb(10) = 20;  -- assign value of 20 to the 10th

 element
                         of the  array v_numb
SET v_numb = ARRAY[1,2,3,4];  -- fill up array with values

为变量赋值的其他方法有：

VALUES INTO
SELECT (or FETCH) INTO

下面的例子演示了这些方法的使用：

VALUES 2 INTO v1;
VALUES ‘TEST’ INTO var2;

SELECT SUM(c1) INTO var1 FROM T1;
SELECT POSSTR(‘MYTEST’,’TEST’) INTO v1 FROM SYSIBM.SYSDUMMY1;

专用寄存器

专用寄存器（special register）   是 DBA 定义的一个存储块，供一个应用程序过程使用。寄存器中的值可以在 SQL 语句或 SQL PL 语句中访问和引用。在 IBM DB2 database for Linux, UNIX, and Windows Information Center 可以找到所有的专用寄存器。

最常用的专用寄存器有：

• CURRENT DATE
• CURRENT TIME
• CURRENT TIMESTAMP
• CURRENT USER
• CURRENT PATH

所有这些寄存器都可以通过在名称中加下划线来引用。例如，CURRENT_DATE。

下面的过程返回当前日期和时间：

CREATE PROCEDURE get_datetime (out cdate date, out ctime time )
P1: BEGIN
	VALUES CURRENT DATE INTO cdate;
	VALUES CURRENT TIME INTO ctime;
END P1

执行后，该过程返回：

Name	Input	Output	
cdate		 2008-08-28	
ctime		 13:47:41	 

　有些专用寄存器的值可以通过 SET 语句来更新。例如，为了更新正在访问的模式，需要像下面这样更改专用寄存器 CURRENT SCHEMA。

SET CURRENT_SCHEMA = MYSCHEMA

若要更改默认函数路径，则需要更新专用寄存器 CURRENT PATH。

游标

声明

SQL PL 提供 DECLARE cursor 语句来定义一个游标，并提供其他语句来支持返回其他结果集和游标处理。

下面是游标声明的语法：

 >>-DECLARE--cursor-name

--CURSOR---------->

 >--FOR--+-select-statement

-+-------------><

   .-WITHOUT HOLD-.   
|--+--------------+---------------------------------------------|
   '-WITH HOLD----'   
					   .-WITHOUT RETURN-------------.   
|--+----------------------------+-------------------------------|

   |              .-TO CALLER-. |   
					   '-WITH RETURN--+-----------+-'
   
					                      '-TO CLIENT-'

Select-statement   是一条有效的 SQL SELECT 语句。可以指定 FOR UPDATE 子句，以便将游标用于定位更新或删除。

WITHOUT HOLD/WITH HOLD   选项定义 COMMIT 操作之后的游标状态（open/close）。默认情况下为 WITHOUT HOLD。如果使用了 WITH HOLD 选项定义一个游标，那么在 COMMIT 操作之后，该游标保持 OPEN 状态。在 ROLLBACK 操作之后，所有游标都将被关闭。

下面是一个显式声明游标的例子，它可以用于过程中后面的迭代处理：

DECLARE mycur1 CURSOR 
  FOR SELECT e.empno, e.lastname, e.job
      FROM employee e, department d
      WHERE e.workdept = d.deptno
        AND deptname =’PLANNING’;

虽然 SQL 语句不能包含参数占位符，但是它可以引用在游标之前声明的本地变量。例如：

					
DECLARE v_dept CHAR(3) DEAFULT ‘ ‘;

DECLARE myres_set CURSOR  
   FOR SELECT empno, lastname, job, salary, comm.
       FROM employee
       WHERE workdept = v_dept;

游标和结果集

在 SQL 过程中，除了迭代结果集中的行以外，游标还可以做更多的事情。游标还可用于将结果集返回给调用程序或其他过程。

• WITHOUT RETURN/WITH return   选项指定游标的结果表是否用于作为从一个过程中返回的结果集。
• WITH RETURN TO CALLER   选项指定将来自游标的结果集返回给调用者，后者可以是另一个过程或一个客户机应用程序。这是默认选项。
• WITH RETURN TO CLIENT   选项指定将来自游标的结果集返回给客户机应用程序，绕过任何中间的嵌套过程。

若要从一个过程中返回结果集，需要：

1. 创建一个过程，创建时指定 DYNAMIC RESULT SETS 子句。
2. 声明游标，声明时指定 WITH RETURN 子句。
3. 打开该游标，并使之保持 open 状态。

如果关闭该游标，则结果集将不能返回给调用者应用程序。

清单 10 演示了一个游标的声明，该游标从一个过程中返回一个结果集：

CREATE PROCEDURE emp_from_dept()
 DYNAMIC RESULT SETS 1
 P1: BEGIN
  DECLARE c_emp_dept CURSOR WITH RETURN
   FOR SELECT empno, lastname, job, salary, comm.
       FROM employee
       WHERE workdept = ‘E21’;

   OPEN c_emp_dept;
  END P1

游标处理

为了在一个过程中处理一个游标的结果，需要做以下事情：

1. 在存储过程块的开头部分 DECLARE 游标。
2. 打开该游标。
3. 将游标的结果取出到之前已声明的本地变量中（隐式游标处理除外，在下面的 FOR 语句中将对此加以解释）。
4. 关闭该游标。（注意：如果现在不关闭游标，当过程终止时将隐式地关闭游标）。 　　

条件语句

SQL PL 中支持两种类型的条件语句 — IF 语句和 CASE 语句。

IF 语句

通过 IF 语句可以根据一个条件的状态来实现逻辑的分支。IF 语句支持使用可选的  ELSEIF   子句和默认的  ELSE   子句。END IF   子句是必需的，它用于表明 IF 语句的结束。

清单 11 展示了一个示例 IF 语句。

   IF years_of_serv > 30 THEN 
       SET gl_sal_increase = 15000;
   ELSEIF years_of_serv > 20 THEN
       SET gl_sal_increase = 12000; 
   ELSE 
       SET gl_sal_increase = 10000;
   END IF; 

CASE 语句

SQL PL 支持两种类型的 CASE 语句，以根据一个条件的状态实现逻辑的分支：

• simple   CASE 语句用于根据一个字面值进入某个逻辑。
• searched   CASE 语句用于根据一个表达式的值进入某个逻辑。

清单 12 显示了使用 searched CASE 语句的一个存储过程的例子。

CREATE PROCEDURE sal_increase_lim1 (empid CHAR(6))
BEGIN
   DECLARE years_of_serv INT DEFAULT 0;
   DECLARE v_incr_rate DEC(9,2) DEFAULT 0.0;
   
   SELECT YEAR(CURRENT DATE) - YEAR(hiredate)
     INTO years_of_serv
     FROM empl1
     WHERE empno = empid;
     
   CASE  
      WHEN  years_of_serv > 30 THEN 
       SET v_incr_rate = 0.08;
      WHEN  years_of_serv > 20 THEN 
       SET v_incr_rate = 0.07; 
      WHEN  years_of_serv > 10 THEN 
       SET v_incr_rate = 0.05;
      ELSE
       SET v_incr_rate = 0.04;         
    END CASE;  
    
    UPDATE empl1 
        SET salary = salary+salary*v_incr_rate
    WHERE empno = empid;      

END 

迭代语句

SQL PL 支持一些重复执行某个逻辑的方法，包括简单的 LOOP、WHILE 循环、REPEAT 循环和 FOR 循环：

• LOOP 循环   -- 简单的循环
  • L1: LOOP
  •   SQL statements;
  •   LEAVE L1;
  • END LOOP L1;

• WHILE 循环   -- 进入前检查条件
  • WHILE  condition
  • DO
  •   SQL statements
  • END WHILE;

• REPEAT 循环   -- 退出前检查条件
  • REPEAT
  •   SQL statements;
  •   UNTIL  condition
  • END REPEAT;

• FOR 循环   -- 结果集上的隐式循环
  • FOR  loop_name   AS
  •   SELECT … FROM
  • DO
  •   SQL statements;
  • END FOR;

请注意，FOR 语句不同于其他的迭代语句，因为它用于迭代一个定义好的结果集中的行。

为了演示这些循环技巧的使用，我们来编写一个过程，该过程从一个 EMPLOYEE 表中获取每个雇员的姓氏、工作年限和年龄，并将其插入到新表 REPORT_INFO_DEPT 中，这些信息分别被声明为 lname varchar(15)、hiredate date 和 birthdate date。

请注意，使用一个简单的 SQL 语句也可以做同样的事情，但是在这个例子中我们使用 3 种不同的循环语句。

CREATE PROCEDURE LEAVE_LOOP (DEPTIN char(3), OUT p_counter INTEGER)
Ll: BEGIN
   DECLARE v_at_end , v_counter INTEGER DEFAULT 0;
   DECLARE v_lastname VARCHAR(15);
   DECLARE v_birthd, v_hired DATE;

   DECLARE c1 CURSOR
     FOR SELECT lastname, hiredate, birthdate FROM employee
         WHERE WORKDEPT = deptin;

   DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_at_end = 1;

   OPEN c1;
   FETCH_LOOP: LOOP
   FETCH c1 INTO v_lastname, v_hired, v_birthd;
     IF v_at_end <> 0 THEN    -- loop until last row of the cursor
       LEAVE FETCH_LOOP;
      END IF;
     SET v_counter = v_counter + 1;
     INSERT INTO REPORT_INFO_DEPT 
         values(v_lastname, v_hired, v_birthd);		
   END LOOP FETCH_LOOP;
   SET p_counter = v_counter;
  END Ll

现在，我们使用 WHILE 循环语句来做同样的事情。

CREATE PROCEDURE DEPT_REPT (DEPTIN char(3), OUT p_counter INTEGER)
Pl: BEGIN
   DECLARE v_at_end , v_counter INTEGER DEFAULT 0;
   DECLARE v_lastname VARCHAR(15);
   DECLARE v_birthd, v_hired DATE;

   DECLARE c1 CURSOR
     FOR SELECT lastname, hiredate, birthdate FROM employee
         WHERE WORKDEPT = deptin;

   DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_at_end = 1;

   OPEN c1;
   FETCH c1 INTO v_lastname, v_hired, v_birthd;
   WHILE (v_at_end = 0)
   DO  
     INSERT INTO REPORT_INFO_DEPT
                values(v_lastname, v_hired, v_birthd); 
     SET v_counter = v_counter + 1;     	
     FETCH c1 INTO v_lastname, v_hired, v_birthd;	
   END WHILE;
   SET p_counter = v_counter;
  END P1

REPEAT 循环非常类似于 WHILE 循环，只不过条件是在最后检查的（因此，它实际上是一个 UNTIL 循环）。

现在，我们使用包含 FOR 循环语句的一个过程来填充 REPORT_INFO_DEPT 表。

CREATE PROCEDURE DEPT_REPT1 (DEPTIN char(3), OUT p_counter INT)
P1:BEGIN
   DECLARE v_counter INT DEFAULT 0;
   FOR dept_loop AS
      SELECT lastname, hiredate, birthdate FROM employee
         WHERE WORKDEPT = deptin 
   DO   
     INSERT INTO REPORT_INFO_DEPT values
(dept_loop.lastname,dept_loop.hiredate,dept_loop.birthdate); 
     SET v_counter = v_counter + 1;  	
   END FOR;
   SET p_counter = v_counter;
  END P1

请注意，最后一个过程没有打开游标、从游标中取数据或关闭游标 — 所有这些都是由 FOR 循环语句隐式进行的。而且，可以引用循环中隐式地获取的值，使用循环名称限定列（例如dept_loop.lastname ）— 而不必使用本地变量来存储这些值。

异常处理机制

DECLARE 有名称的条件

SQL PL 允许为给定的 SQLSTATE 声明用户命名的条件，以用于之后的错误处理。条件名称在整个复合语句中必须是惟一的，并且只能在明它的复合语句中引用它。

清单 16 显示了声明一个有名称的条件的语法。
清单 16. 声明一个有名称的条件的语法

|--DECLARE--condition-name

--CONDITION--FOR---------------------->

               .-VALUE-.                      
					   .-SQLSTATE--+-------+-.                    
>--+---------------------+--string-constant

---------------------|

DECLARE FOREIGN_KEY_VIOLATION CONDITION FOR SQLSTATE ‘23503’;
DECLARE overflow CONDITION FOR SQLSTATE '22003';

|--DECLARE--+-CONTINUE-+--HANDLER--FOR-------------------------->
            +-EXIT-----+                 
					            '-UNDO-----'                 

>--+-specific-condition-value

-+--| SQL-procedure-statement |----|
   '-general-condition-value

--'                                

WHERE specific-condition-value

   .-,----------------------------------------.   
					   V             .-VALUE-.                    |   
|----+-SQLSTATE--+-------+--string-constant

-+-+-----------------|
     '-condition-name

-----------------------'     
				

CREATE PROCEDURE simple_error 
      (IN new_job CHAR(8), IN p_empno CHAR(6), 
       OUT p_state_out CHAR(5),OUT p_code_out INT)
SPECIFIC simple_error1
BEGIN
      DECLARE SQLCODE INT DEFAULT 0;
      DECLARE SQLSTATE CHAR(5) DEFAULT ‘00000’;

      DECLARE EXIT HANDLER FOR SQLEXCEPTION
            SELECT SQLSTATE, SQLCODE 
              INTO p_sqlstate_out, p_sqlcode_out
              FROM SYSIBM.SYSDUMMY1;

      UPDATE EMPLOYEE 
         SET job = new_job 
       WHERE empno = p_empno;
END

CREATE PROCEDURE proc1 (IN num int, IN new_status varchar(10))                     
P1: BEGIN
    DECLARE SQLCODE INTEGER default 0;
    DECLARE SQLSTATE CHAR(5) default ‘ ‘;
    DECLARE v_trunc INTEGER default 0; 
    DECLARE overflow CONDITION FOR SQLSTATE '22001';

    DECLARE CONTINUE HANDLER FOR overflow 
       BEGIN
            INSERT INTO tab1 VALUES (num, substr (new_sataus,1,5));
            SET v_trunc = 2;             
       END;                                                           
   INSERT INTO tab1 VALUES(num, new_status);
   RETURN v_trunc;
 END P1 

>>-SIGNAL------------------------------------------------------->

               .-VALUE-.                                   
>--+-SQLSTATE--+-------+--+-sqlstate-string-constant

-+-+-------->
   |                      '-variable-name

------------' |   
					   '-condition-name


------------------------------------'   

>--+------------------------+----------------------------------><
  '|--+-SET MESSAGE_TEXT-- = --diagnostic-string-expression

-+------|

DECLARE condition overflow for SQLSTATE ‘22001’;
….

SIGNAL overflow SET MESSAGE_TEXT = ‘Too many characters, truncated’;

CREATE PROCEDURE sign_test (IN num int, IN new_status varchar(10))                 
P1: BEGIN
  DECLARE SQLCODE INTEGER default 0;
  DECLARE SQLSTATE CHAR(5) default '';
    
  IF length (new_status) > 5 THEN 
     SIGNAL SQLSTATE '72001' SET MESSAGE_TEXT = 'INPUT VALUE TOO LONG';
  END IF;                                                     
  INSERT  INTO  TAB1 VALUES (num, new_status);   
 END P1