DB2 9.5 SQL Procedure Developer認證考試 735 准備，第1部分

請注意，FOR語句不同於其他的迭代語句，因為它用於迭代一個定義好的結果集中的行。

為了演示這些循環技巧的使用，我們來編寫一個過程，該過程從一個EMPLOYEE 表中獲取每個雇員的姓氏、工作年限和年齡，並將其插入到新表 REPORT_INFO_DEPT 中，這些信息分別被聲明為 lname varchar(15)、hiredate date 和 birthdate date。

請注意，使用一個簡單的SQL語句也可以做同樣的事情，但是在這個例子中我們使用 3 種不同的循環語句。

清單 13. 簡單的循環例子

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循環語句來做同樣的事情。

清單 14. 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 表。

清單 15. FOR循環的例子

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 條件處理程序

如果發生一個錯誤，存儲過程的行為是根據條件處理程序來決定的。在一個存儲過程中，可以為一個普通的或有名稱的條件和特定的SQLSTATE 聲明一個或多個條件處理程序。當一個SQL語句產生一個SQLEXCEPTION 或 SQLWARNING（SQLCODE <> 0）時，控制被轉移到為一個聲明的處理程序中，以獲取普通的異常或特定的SQLSTATE 值。

清單 17 顯示了受支持的處理程序聲明的語法。

清單 17.處理程序聲明的語法

|--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-----------------------'

下面是演示它如何工作的一些例子。在下面的過程中，如果 UPDATE語句失敗，則控制被轉移到 EXIT處理程序。結果，該過程被終止，但是它的輸出參數包含SQLCODE 和 SQLSTATE 的值。

清單 18. 返回 SQLCODE 和 SQLSTATE 的處理程序的例子

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

請注意，SQLCODE 和 SQLSTATE 應該被顯式地聲明為本地變量。

清單 19 演示了一個場景，在此場景中，當一個給定的錯誤發生時，不是退出過程，而是繼續某個動作。為了理解這個例子，請注意表 TAB1 被定義為（col1 int, col2 char(5)），在默認情況下，如果發生值溢出，DB2不是截短一個值，而是產生SQLSTATE ‘22001’。

清單 19. 具有CONTINUE處理程序的過程

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