DB2 9.5 SQL Procedure Developer 認證考試 735 备考指南：第五部分核心技術深度解析

本文為 DB2 9.5 SQL Procedure Developer 認證考試 (Exam 735) 第五部分備考指南，重點覆蓋全局聲明式臨時表 (DGTT)、層次化查詢、MERGE 語句、ADMIN_CMD 過程、GET DIAGNOSTICS、保存點與 PureXML 在存儲過程中的應用等核心考點。

全局聲明式臨時表 (DGTT)

DGTT 存儲臨時數據與中間結果，無需日誌、不入系統編目、單連接可見、無鎖開銷，性能優勢顯著。會話結束自動清理。

創建語法要點

DECLARE GLOBAL TEMPORARY TABLE temp_proj
 (projno CHAR(6), projname VARCHAR(24), projsdate DATE, projedate DATE)
 WITH REPLACE
 ON COMMIT PRESERVE ROWS
 NOT LOGGED
 IN usr_tbsp;

• WITH REPLACE：同名表存在時替換定義
• ON COMMIT PRESERVE/DELETE ROWS：提交時保留/刪除行（默認 DELETE）
• ON ROLLBACK PRESERVE/DELETE ROWS：回滾時保留/刪除行（默認 DELETE）
• NOT LOGGED：無日誌開銷
• 模式固定為 SESSION，引用時必須加前綴 SESSION.temp1
• 不支持 BLOB/CLOB/XML/結構化類型等列類型

三步驟使用流程

1. 確保存在用戶臨時表空間 (CREATE USER TEMPORARY TABLESPACE)
2. 執行 DECLARE GLOBAL TEMPORARY TABLE
3. 存儲過程中引用加 SESSION. 前綴

層次化查詢：遞歸 CTE 實戰

關係數據庫表達樹狀結構（如物料清單 BOM）需遞歸 SQL。核心機制：WITH RECURSIVE (DB2 用 WITH 隱含遞歸) + UNION ALL。

經典 BOM 遞歸查詢

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
  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
)
SELECT assembly_id, sub_assembly_id, assembly_nm FROM assembly;

• 上半部（種子查詢）執行一次，填充根節點
• 下半部遞歸執行，直至無匹配行
• 最終輸出完整層次結構（10 行結果）

MERGE 語句：單次操作實現 UPSERT

MERGE 結合條件性 UPDATE/INSERT/DELETE，適用於數據同步場景（如分支機構數據合併到總部）。

語法範例

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 (...) VALUES (...);

• ON 子句定義匹配鍵
• WHEN MATCHED 處理更新/刪除
• WHEN NOT MATCHED 處理插入
• 關聯名 (e, m) 消除列引用歧義

系統過程 ADMIN_CMD：SQL 內執行管理命令

CALL SYSPROC.ADMIN_CMD(command_string) 允許從應用/過程調用管理命令（不支持 UDF/觸發器）。輸入參數 CLOB(2M)。

常用支持命令

• DESCRIBE, EXPORT, FORCE APPLICATION, IMPORT, LOAD
• REORG INDEXES/TABLE, RUNSTATS, UPDATE DATABASE CONFIGURATION

實戰範例

CREATE PROCEDURE test_admin_cmd()
P1: BEGIN
  DECLARE sql_string VARCHAR(200);
  SET sql_string = 'LOAD FROM C:DB9.5_testorg_exp.txt OF DEL
    METHOD P (1,2,3,4,5) INSERT INTO DB2ADMIN.ORG1 ...';
  CALL SYSPROC.ADMIN_CMD(sql_string);
END P1

GET DIAGNOSTICS：捕獲上一條 SQL 執行信息

三大核心用途：

1. ROW_COUNT：受影響行數（INTEGER 變量）
2. DB2_RETURN_STATUS：調用過程返回狀態（0 成功，非 0 失敗）
3. MESSAGE_TEXT：錯誤/警告消息文本（VARCHAR(70) 變量）

語法結構

GET DIAGNOSTICS var = ROW_COUNT | DB2_RETURN_STATUS | MESSAGE_TEXT

異常處理範例

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
  GET DIAGNOSTICS EXCEPTION 1 p_err_mess = MESSAGE_TEXT;
  SET v_trunc = 2;
END;

保存點：事務內的子事務控制

SAVEPOINT name ON ROLLBACK RETAIN CURSORS 創建回滾點。支持嵌套，回滾到指定點不影響之前工作，COMMIT 後所有保存點失效。

嵌套回滾演示

INSERT INTO T1 VALUES ('A');
SAVEPOINT SP1 ON ROLLBACK RETAIN CURSORS;
INSERT INTO T1 VALUES ('B');
SAVEPOINT SP2 ON ROLLBACK RETAIN CURSORS;
INSERT INTO T1 VALUES ('C');
SAVEPOINT SP3 ON ROLLBACK RETAIN CURSORS;
INSERT INTO T1 VALUES ('D');
ROLLBACK TO SAVEPOINT SP3;  -- 刪除 D
ROLLBACK TO SAVEPOINT SP1;  -- 刪除 C, B
COMMIT WORK;                -- 保存點失效
INSERT INTO T1 VALUES ('E'); -- 最終表含 A, E

PureXML 與存儲過程整合

DB2 9.5 為混合型數據庫，SQL 過程原生支持 XML 類型參數/變量，可傳入 XMLEXISTS、XMLQUERY、XMLTABLE 表達式。

返回 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

接收 XML 參數插入

CREATE PROCEDURE PROC_INS_XML1(v_cid INT, v_info XML)
DYNAMIC RESULT SETS 1
P1: BEGIN
  INSERT INTO CUSTOMER (CID, INFO) VALUES (v_cid, v_info);
END P1

IBM Data Studio 可直接解析 XML 字符串作為輸入參數（見圖 2）。

考試重點提煉

• DGTT 語法選項與會話隔離特性
• 遞歸 CTE 兩部分結構與終止條件
• MERGE 三大子句 (ON/MATCHED/NOT MATCHED) 組合邏輯
• ADMIN_CMD 支持命令範圍與調用限制
• GET DIAGNOSTICS 三大信息項對應變量類型
• 保存點嵌套回滾行為與 COMMIT 影響
• XML 類型在 SQL 過程參數/變量/游標中的使用