本系列共 6 篇教學，涵蓋 SQL Procedural Language (SQL PL) 所有核心結構與技巧，包含變數、條件、處理器宣告、控制流程、迴圈、錯誤處理、部署、結果集回傳、巢狀呼叫、資料共享，以及 DB2 9.5 進階特性（樂觀鎖、階層查詢、宣告式全域暫存表）、開發工具 (IBM Data Studio)。適合已通過 Exam 730 (DB2 9 Family Fundamentals) 的考生準備 Exam 735。
SQL PL 是 SQL/PSM (Persistent Stored Modules) 標準子集，結合 SQL 資料存取便利性與程式語言流程控制，可開發函數、存儲過程、觸發器，將業務邏輯封裝於資料庫物件提升應用效能。
DECLARE variable-name data-type [DEFAULT NULL | DEFAULT constant];
DEFAULT 省略時為 NULL。DECLARE 可宣告多個同型別變數，如 DECLARE v1, v2 INT DEFAULT 0;。需先在資料庫建立型別，再於過程/應用中宣告使用。陣列為暫時值，不可持久化存表。
CREATE TYPE type-name AS element-type ARRAY [max-cardinality];
-- 範例
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_array_test (OUT mynames names)
BEGIN
DECLARE v_nums numbers;
SET v_nums = ARRAY[1,2,3,5,7,11];
SET mynames[1] = 'MARINA';
-- 存取元素：array-var[subindex]，下標 1..cardinality
END
支援 CARDINALITY(array) 取得元素個數等操作函數。
SET variable = expression | NULL; 適用區域變數、全域變數、陣列元素。其他指派方式：VALUES INTO、SELECT/FETCH INTO。
SET v_sal = 50000;
SET v_total = (SELECT SUM(salary) FROM emp);
SET v_pos = POSSTR('MYTEST','TEST');
SET v_arr[10] = 20;
SET v_arr = ARRAY[1,2,3,4];
VALUES CURRENT DATE INTO v_date;
SELECT hiredate INTO v_hired FROM emp WHERE empno = '000010';
DBA 定義的儲存區塊，供應用程式存取。常用：CURRENT DATE、CURRENT TIME、CURRENT TIMESTAMP、CURRENT USER、CURRENT PATH。可加底線引用（如 CURRENT_DATE）。部分暫存器可用 SET 更新（如 SET CURRENT SCHEMA = 'MYSCHEMA'）。
DECLARE cursor-name CURSOR [WITHOUT HOLD | WITH HOLD]
[WITHOUT RETURN | WITH RETURN [TO CALLER | TO CLIENT]]
FOR select-statement;
WITH HOLD：COMMIT 後游標保持開啟（ROLLBACK 仍關閉）。WITH RETURN TO CALLER：結果集回傳給直接呼叫者（預設）。WITH RETURN TO CLIENT：穿透巢狀過程直接回傳給用戶端應用。回傳結果集三步驟：
1. CREATE PROCEDURE ... DYNAMIC RESULT SETS n
2. DECLARE cursor ... WITH RETURN ...
3. OPEN cursor 並保持開啟（關閉則無法回傳）。
DECLARE CURSOROPENFETCH INTO 區域變數（FOR 迴圈除外）CLOSE（結束時隱式關閉）IF condition THEN ...
[ELSEIF condition THEN ...]
[ELSE ...]
END IF;
CASE
WHEN years > 30 THEN SET inc = 0.08;
WHEN years > 20 THEN SET inc = 0.07;
WHEN years > 10 THEN SET inc = 0.05;
ELSE SET inc = 0.04;
END CASE;
| 類型 | 語法骨架 | 特點 |
|---|---|---|
| LOOP | label: LOOP ... LEAVE label; END LOOP label; | 簡單無條件迴圈，需自行 LEAVE |
| WHILE | WHILE condition DO ... END WHILE; | 前測試，條件為真才進入 |
| REPEAT | REPEAT ... UNTIL condition END REPEAT; | 後測試，至少執行一次 |
| FOR | FOR loop_name AS SELECT ... DO ... END FOR; | 隱式游標迴圈，自動 OPEN/FETCH/CLOSE，可用 loop_name.col 直接引用欄值 |
從 EMPLOYEE 讀取指定部門員工姓名、到職日、生日，寫入 REPORT_INFO_DEPT。
-- 1. LOOP + FETCH + CONTINUE HANDLER
CREATE PROCEDURE leave_loop (deptin CHAR(3), OUT cnt INT)
L1: BEGIN
DECLARE v_end, v_cnt INT DEFAULT 0;
DECLARE v_lname VARCHAR(15); DECLARE v_hired, v_birth DATE;
DECLARE c1 CURSOR FOR SELECT lastname, hiredate, birthdate FROM employee WHERE workdept = deptin;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_end = 1;
OPEN c1;
fetch_loop: LOOP
FETCH c1 INTO v_lname, v_hired, v_birth;
IF v_end <> 0 THEN LEAVE fetch_loop; END IF;
SET v_cnt = v_cnt + 1;
INSERT INTO report_info_dept VALUES (v_lname, v_hired, v_birth);
END LOOP fetch_loop;
SET cnt = v_cnt;
END L1
-- 2. WHILE 迴圈
CREATE PROCEDURE dept_rept (deptin CHAR(3), OUT cnt INT)
P1: BEGIN
DECLARE v_end, v_cnt INT DEFAULT 0;
DECLARE v_lname VARCHAR(15); DECLARE v_hired, v_birth DATE;
DECLARE c1 CURSOR FOR SELECT lastname, hiredate, birthdate FROM employee WHERE workdept = deptin;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_end = 1;
OPEN c1;
FETCH c1 INTO v_lname, v_hired, v_birth;
WHILE v_end = 0 DO
INSERT INTO report_info_dept VALUES (v_lname, v_hired, v_birth);
SET v_cnt = v_cnt + 1;
FETCH c1 INTO v_lname, v_hired, v_birth;
END WHILE;
SET cnt = v_cnt;
END P1
-- 3. FOR 迴圈（最簡潔）
CREATE PROCEDURE dept_rept1 (deptin CHAR(3), OUT cnt INT)
P1: BEGIN
DECLARE v_cnt 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_cnt = v_cnt + 1;
END FOR;
SET cnt = v_cnt;
END P1
DECLARE condition-name CONDITION FOR SQLSTATE 'state-string';
-- 例
DECLARE foreign_key_violation CONDITION FOR SQLSTATE '23503';
DECLARE overflow CONDITION FOR SQLSTATE '22003';
DECLARE [CONTINUE | EXIT | UNDO] HANDLER FOR condition-value
SQL-procedure-statement;
condition-value ::= SQLSTATE 'xxxxx' | condition-name | SQLEXCEPTION | SQLWARNING | NOT FOUND
CONTINUE：處理後繼續下一條敘述。EXIT：處理後離開當前複合敘述區塊。UNDO：回滾區塊內變更後離開（需區塊標記為 ATOMIC）。CREATE PROCEDURE simple_error (IN new_job CHAR(8), IN p_empno CHAR(6),
OUT p_state CHAR(5), OUT p_code INT)
BEGIN
DECLARE SQLCODE INT DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SELECT SQLSTATE, SQLCODE INTO p_state, p_code 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 INT DEFAULT 0; DECLARE SQLSTATE CHAR(5) DEFAULT '';
DECLARE v_trunc INT DEFAULT 0;
DECLARE overflow CONDITION FOR SQLSTATE '22001'; -- 字串截斷
DECLARE CONTINUE HANDLER FOR overflow
BEGIN
INSERT INTO tab1 VALUES (num, SUBSTR(new_status,1,5));
SET v_trunc = 2;
END;
INSERT INTO tab1 VALUES (num, new_status);
RETURN v_trunc;
END P1
輸入過長字串觸發 22001，處理器截斷後寫入並設旗標，過程正常結束。
SIGNAL SQLSTATE '72001' SET MESSAGE_TEXT = 'INPUT VALUE TOO LONG';
-- 或使用具名條件
DECLARE over_len CONDITION FOR SQLSTATE '72001';
SIGNAL over_len SET MESSAGE_TEXT = '自訂錯誤訊息';
SET MESSAGE_TEXT 附加診斷文字。掌握 SQL PL 核心：變數/陣列宣告與指派、特殊暫存器、游標與結果集、IF/CASE 分支、四種迴圈（特別是 FOR 隱式游標）、條件與處理器宣告、SIGNAL 主動拋出。這是撰寫健壯存儲過程、函數、觸發器的基石，也是 Exam 735 的必考重點。
