DB2 9.5 SQL Procedure Developer 認證考試 735 精要指南：第一部分——SQL PL 基礎語法與核心構件

系列簡介

本 6 篇教程系列涵蓋 SQL Procedural Language (SQL PL) 所有基本結構與方法，示範如何在存儲過程、用戶定義函數、觸發器中使用 SQL PL，含錯誤處理與部署。還介紹 DB2 9.5 進階特性：樂觀鎖、分層查詢、已聲明全域臨時表、如何調用存儲過程、過程與函數間共享數據、IBM Data Studio 等開發工具。為考試每部分打下堅實基礎，但不應作為唯一備考資料。

本教程目標

• 學習 SQL PL 基本要素
• 理解變量、條件、處理程序聲明
• 掌握控制流語句
• 學習游標處理與結果集返回
• 理解錯誤處理機制

先決條件

參加考試必須先通過 DB2 9 Family Fundamentals 考試 (Exam 730)。需具備關聯資料庫運作原理、資料庫與資料庫編程背景知識。無需安裝 DB2 9.5 可完成教程，但建議下載免費試用版 DB2 Express-C 配合學習。

SQL PL 簡介

DB2 SQL Procedural Language (SQL PL) 是 SQL Persistent Stored Module 語言標準的子集。結合 SQL 存取數據便利性與程式語言流程控制。透過 SQL PL 當前語句集與語言特性，可用 SQL 開發綜合、高階程序（函數、存儲過程、觸發器），將業務邏輯封裝進易維護資料庫物件，提升資料庫應用效能。SQL PL 支援本地/全域變量（含聲明賦值）、條件/迭代語句、控制轉移、錯誤管理、返回結果集方法。

變量聲明

SQL 過程允許用本地變量賦予/獲取 SQL 值支援所有 SQL 邏輯。使用前必須聲明。語法：

DECLARE SQL-variable-name data-type [DEFAULT constant | NULL];

• SQL-variable-name：本地變量名，不可與其他變量/參數/列名重複
• 支援 DB2 所有數據類型（見圖 1）
• DEFAULT 未指定時聲明時賦 NULL

範例

DECLARE v_salary DEC(9,2) DEFAULT 0.0;
DECLARE v_status CHAR(3) DEFAULT 'YES';
DECLARE v_description VARCHAR(80);
DECLARE v1, v2 INT DEFAULT 0;  -- DB2 9.5 新支援：單語句聲明多同類型變量

陣列數據類型（DB2 9.5 新增）

SQL 過程支援陣列類型變量與參數。需先在資料庫創建類型，再在過程/應用聲明。陣列為暫時值，可在存儲過程與應用操作，但不可存入表。語法：

CREATE TYPE array-type-name AS data-type ARRAY [integer-constant];

• 類型名需模式限定，當前伺服器上唯一
• LONG VARCHAR/VARGRAPHIC、XML、用戶定義類型不可作為陣列元素類型
• integer-constant 指定最大基數，可選，預設 2147483647
• 元素引用：ARRAY-VARIABLE(subindex)，subindex 介於 1 到基數間

範例

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

支援陣列操作方法，如 CARDINALITY(myarray) 返回元素個數。

賦值語句

SQL PL 提供 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;  -- 陣列第 10 元素賦 20
SET v_numb = ARRAY[1,2,3,4];  -- 填充陣列

其他賦值方式：VALUES INTO、SELECT (或 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;

特殊暫存器

特殊暫存器 是 DBA 定義的儲存塊，供應用程序過程使用。值可在 SQL 或 SQL PL 語句中存取引用。常用者：

• 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

部分暫存器值可用 SET 更新，如 SET CURRENT_SCHEMA = MYSCHEMA、SET CURRENT PATH = ...。

游標

聲明

語法：

DECLARE cursor-name CURSOR [WITHOUT HOLD | WITH HOLD]
  [WITHOUT RETURN | WITH RETURN [TO CALLER | TO CLIENT]]
  FOR select-statement;

• select-statement：有效 SQL SELECT，可指定 FOR UPDATE 用於定位更新/刪除
• WITHOUT HOLD/WITH HOLD：COMMIT 後游標狀態（open/close），預設 WITHOUT HOLD。WITH HOLD 時 COMMIT 後保持 OPEN，ROLLBACK 後所有游標關閉
• WITH RETURN：指定游標結果表作為從過程返回的結果集。TO CALLER（預設）返回給調用者（另一過程或客戶端應用），TO CLIENT 直接返回給客戶端應用繞過中間嵌套過程

範例：顯式聲明供迭代處理

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) DEFAULT ' ';
DECLARE myres_set CURSOR FOR
SELECT empno, lastname, job, salary, comm
FROM employee
WHERE workdept = v_dept;

游標與結果集

從過程返回結果集需：

1. 創建過程指定 DYNAMIC RESULT SETS 子句
2. 聲明游標指定 WITH RETURN 子句
3. 打開游標並保持 OPEN
4. 若關閉游標，結果集無法返回

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

游標處理

在過程中處理游標結果需：聲明游標 → 打開 → FETCH 到預先聲明的本地變量（隱式游標處理除外，見 FOR 語句）→ 關閉（若不關閉，過程終止時隱式關閉）。

條件語句

IF 語句

根據條件狀態實現邏輯分支，支援可選 ELSEIF 與預設 ELSE，END 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 語句

兩類型：

• Simple CASE：根據字面值進入邏輯
• Searched CASE：根據表達式值進入邏輯

範例：使用 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：簡單循環，需 LEAVE 跳出
• WHILE：進入前檢查條件
• REPEAT：類似 WHILE 但條件在最後檢查（實為 UNTIL 循環）
• FOR：針對查詢結果集迭代，隱式處理游標

WHILE 循環範例

CREATE PROCEDURE DEPT_REPT (DEPTIN CHAR(3), OUT p_counter INTEGER)
P1: 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

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