| 選項 | 行為 |
|---|
| ON COMMIT DELETE ROWS | Commit 時清空資料 (預設，若無 WITH HOLD 游標) |
| ON COMMIT PRESERVE ROWS | Commit 保留資料 |
| ON ROLLBACK DELETE ROWS | Rollback 時清空 (預設) |
| ON ROLLBACK PRESERVE ROWS | Rollback 保留資料 |
| NOT LOGGED | 不記錄重做/回溯日誌，效能最佳 |
| WITH REPLACE | 同名 DGTT 存在時覆蓋 |
限制： 不支援 BLOB/CLOB/DBCLOB/LONG VARCHAR/LONG VARGRAPHIC/XML/REF/結構化型別。
2. 階層式遞迴查詢 (Recursive CTE)
語法架構
WITH assembly (sub_assembly_id, assembly_nm, assembly_id) AS (
SELECT sub_assembly_id, assembly_nm, assembly_id
FROM bill_of_materials
WHERE assembly_id = 1000 -- Anchor member (執行一次)
UNION ALL
SELECT child.sub_assembly_id, child.assembly_nm, child.assembly_id
FROM bill_of_materials child, assembly p
WHERE child.assembly_id = p.sub_assembly_id -- Recursive member (遞迴直到無匹配)
)
SELECT * FROM assembly;
關鍵點
UNION ALL 上半部為種子集，下半部自我引用遞迴。- 適用於 BOM、組織架構、單據分攤等樹狀結構。
3. MERGE 陳述式 - 單指令完成 UPSERT
MERGE INTO employee AS e
USING (SELECT empno, firstnme, ..., salary FROM my_emp) AS m
ON e.empno = m.empno
WHEN MATCHED THEN UPDATE SET salary = m.salary
WHEN NOT MATCHED THEN INSERT (empno, firstnme, ..., salary)
VALUES (m.empno, m.firstnme, ..., m.salary);
- 目標表可為可更新視圖。
- 關聯名 (
e, m) 消除欄位歧義。
- 一次掃描源表即可完成條件更新/插入/刪除。
4. 系統預存程序 ADMIN_CMD
- 呼叫語法：
CALL SYSPROC.ADMIN_CMD('command_string');
- 輸入參數：
CLOB(2M) 包含管理命令。
- 支援命令：
DESCRIBE, EXPORT, FORCE APPLICATION, IMPORT, LOAD, REORG, RUNSTATS, UPDATE DATABASE CONFIGURATION 等。
- 限制： 不可在 UDF 或 Trigger 中呼叫。
範例：從檔案載入資料
CREATE PROCEDURE test_admin_cmd()
P1: BEGIN
DECLARE sql_string VARCHAR(200);
SET sql_string = 'LOAD FROM C:\DB9.5_test\org_exp.txt OF DEL
METHOD P (1,2,3,4,5) INSERT INTO DB2ADMIN.ORG1
(DEPTNUMB, DEPTNAME, MANAGER, DIVISION, LOCATION)
COPY NO INDEXING MODE AUTOSELECT';
CALL SYSPROC.ADMIN_CMD(sql_string);
END P1
5. GET DIAGNOSTICS - 捕捉前一條 SQL 狀態
| 選項 | 回傳值 | 變數型別 |
|---|
| ROW_COUNT | 影響列數 (INSERT/UPDATE/DELETE) | INTEGER |
| DB2_RETURN_STATUS | CALL 預存程序傳回碼 (0=成功) | INTEGER |
| MESSAGE_TEXT | 錯誤/警告訊息文字 | VARCHAR(70) |
| DB2_TOKEN_STRING | 訊息參數代碼 | VARCHAR(70) |
範例 1：取得更新列數
CREATE PROCEDURE UPDATE_RCOUNT(IN sales_corr INT, IN qtr INT, OUT row_updated INT)
P1: BEGIN
UPDATE SALESBYQUARTER SET sales = sales + sales_corr WHERE y < YEAR(CURRENT DATE) AND q = qtr;
GET DIAGNOSTICS row_updated = ROW_COUNT;
END P1
範例 2：例外處理中取得訊息
CREATE PROCEDURE mess_text1(IN num INT, IN new_status VARCHAR(10), OUT p_err_mess VARCHAR(70))
P1: BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS EXCEPTION 1 p_err_mess = MESSAGE_TEXT;
END;
INSERT INTO tab1 VALUES (num, new_status);
END P1
6. Savepoint - 事務內的子事務檢查點
SAVEPOINT sp1 ON ROLLBACK RETAIN CURSORS;
-- ... DML ...
ROLLBACK TO SAVEPOINT sp1; -- 僅回滾到 sp1 之後的變更
COMMIT; -- 釋放所有 savepoint
巢狀範例
INSERT 'A'; SAVEPOINT sp1;
INSERT 'B'; SAVEPOINT sp2;
INSERT 'C'; SAVEPOINT sp3;
INSERT 'D';
ROLLBACK TO sp3; -- 移除 D
ROLLBACK TO sp1; -- 移除 C, B
COMMIT; -- 只剩 A
INSERT 'E'; -- 最終 A, E
7. PureXML 在 SQL Procedure 中的應用
- 支援
XML 型別參數與變數。
- 可傳遞給
XMLEXISTS、XMLQUERY、XMLTABLE 的 XQuery 運算式。
範例 1：回傳 XML 屬性結果集
CREATE PROCEDURE proc_wXML()
DYNAMIC RESULT SETS 1
P1: BEGIN
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT cid,
XMLQUERY('declare default element namespace "https://posample.org";
$cu/customerinfo/name/text()' PASSING d.info AS "cu")
FROM customer d;
OPEN cursor1;
END P1
範例 2：接受 XML 參數插入資料
CREATE PROCEDURE PROC_INS_XML1(IN v_cid INT, IN v_info XML)
P1: BEGIN
INSERT INTO CUSTOMER (CID, INFO) VALUES (v_cid, v_info);
END P1
Data Studio 可直接解析 XML 字串作為輸入參數測試。
考試重點提示
- DGTT 必須在 User Temporary Tablespace 建立，Schema 為 SESSION。
- Recursive CTE: Anchor + UNION ALL + Recursive member，防止無限遞迴需確保終止條件。
- MERGE 適用於資料同步、ETL UPSERT 場景。
- ADMIN_CMD 讓 SQL Procedure 具備 DBA 命令能力，但不可用於 UDF/Trigger。
- GET DIAGNOSTICS 不改變 SQLCODE/SQLSTATE，變數型別務必對應。
- Savepoint 僅在當前事務有效，COMMIT/ROLLBACK 釋放。
- PureXML 讓關聯與階層資料在同一 Procedure 中無縫處理。
- 上一篇文章： DB2 25 年演進史：從關聯式先鋒到混合型資料服務器，領航企業資訊議程
- 下一篇文章： DB2 9 應用開發 (733) 認證指南第 2 部分：資料存取、MQT、SQL 函數、CTE、游標完整解析
Copyright ©
程式碼之家 All Rights Reserved
