最近看了些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

DB2開發系列之一——基本語法

相關文章