最近看了些db2开发方面的资料，现做摘要，以供自己和大家参考：

1、变量声明

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;

2、数组数据类型

CREATE TYPE numbers as INTEGER ARRAY[100];

CREATE TYPE names as VARCHAR(30) ARRAY[];

CREATE TYPE MYSCHEMA.totalcomp as DECIMAL(12,2) ARRAY[];

 

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

3、赋值

1）一般方法

SET var1 = 10; 

SET total = (select sum(c1) from T1);

SET var2 = POSSTR(‘MYTEST’,’TEST’);

SET v_numb(10) = 20; 

SET v_numb = ARRAY[1,2,3,4]; 

2）其他方法

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;

4、专用寄存器

1）常用寄存器

CURRENT DATE

CURRENT TIME

CURRENT TIMESTAMP

CURRENT USER

CURRENT PATH

2）示例

CREATE PROCEDURE get_datetime (out cdate date, out ctime time )

P1: BEGIN

 VALUES CURRENT DATE INTO cdate;

 VALUES CURRENT TIME INTO ctime;

END P1

 

SET CURRENT_SCHEMA = MYSCHEMA

5、游标

1）声明

 DECLARE mycur1 CURSOR 

  FOR SELECT e.empno, e.lastname, e.job

      FROM employee e, department d

      WHERE e.workdept = d.deptno

        AND deptname =’PLANNING’;

DECLARE v_dept CHAR(3) DEAFULT ‘ ‘;

DECLARE myres_set CURSOR  

   FOR SELECT empno, lastname, job, salary, comm.

       FROM employee

       WHERE workdept = v_dept;

 

2）游标和结果集

 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

6、条件语句

1）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;

2）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 

3）迭代语句

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; 

 

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

 

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

 

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

 7、异常处理机制

1）DECLARE 有名称的条件

DECLARE FOREIGN_KEY_VIOLATION CONDITION FOR SQLSTATE ‘23503’;

DECLARE overflow CONDITION FOR SQLSTATE '22003';

2）DECLARE 条件处理程序

 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 

 3）强制发出异常 -- SIGNAL SQLSTATE

 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