DB2 9 應用開發 (733) 認證指南第 2 部分：資料存取、MQT、SQL 函數、CTE、游標完整解析

一、資料變更與存取的決策框架

設計資料庫應用時，資料變更邏輯的放置位置取決於四大因素：

1. 資料模型與中繼資料 (目錄約束、型別、檢查)
2. 業務需求 (如何識別、修改資料)
3. 權限與安全性 (使用者/表/欄位層級)
4. 存取介面 (Embedded SQL, CLI/ODBC/JDBC, .NET, PHP 等)

將邏輯下沉至資料庫層的優勢與風險

最佳實務： 資料驗證 (輸入階段) 放應用端；參考完整性、跨表業務規則、複雜計算放資料庫端 (SP/UDF/Trigger)。

二、跨表查詢與優化器運作

• SELECT 僅宣告「要什麼」，不指定「怎麼取」。
• 優化器依系統目錄統計資訊產生存取計劃。
• 範例：相關子查詢找出「最高薪水低於其他部門平均薪水」的部門。

三、物化查詢表 (MQT) - 查詢加速利器

分類

• 系統維護 (System-maintained)：REFRESH IMMEDIATE (基表異動即時同步) / REFRESH DEFERRED (手動 REFRESH TABLE)。
• 使用者維護 (User-maintained)：應用程式自行維護資料，彈性最高。

建立 REFRESH IMMEDIATE MQT 範例

CONNECT TO SAMPLE;
ALTER TABLE EMPLOYEE ADD UNIQUE (EMPNO);
ALTER TABLE DEPARTMENT ADD UNIQUE (DEPTNO);
CREATE TABLE EMP AS (
  SELECT E.EMPNO, E.FIRSTNME, E.LASTNAME, E.PHONENO,
         D.DEPTNO, SUBSTR(D.DEPTNAME,1,12) AS DEPARTMENT, D.MGRNO
  FROM EMPLOYEE E, DEPARTMENT D
  WHERE E.WORKDEPT = D.DEPTNO
) DATA INITIALLY DEFERRED REFRESH IMMEDIATE;
SET INTEGRITY FOR EMP IMMEDIATE CHECKED NOT INCREMENTAL;

前提： 參與查詢的每個基表至少有一個唯一鍵出現在 SELECT 清單中。

四、SQL 函數體系

內建函數

• 屬於 SYSIBM Schema：彙總 (AVG)、運算子 (+)、型別轉換 (DECIMAL)、字串 (SUBSTR) 等。

使用者定義函數 (UDF)

• 註冊於 SYSCAT.FUNCTIONS，CREATE FUNCTION 定義。
• 語言：C/C++、Java、OLE、SQL PL。
• FENCED：獨立 db2udf 進程，共享記憶體通訊，安全穩定，效能稍低。
• NOT FENCED：運行於 DB2 代理記憶體，效能高，但錯誤可能導致引擎崩潰，開發階段必須先用 FENCED 驗證。

常用範例

-- 空值轉預設
SELECT TITLE, COALESCE(PRICE, 0.00) FROM TITLES;
-- 字串長度
SELECT COMPANYNAME, LENGTH(COMPANYNAME) FROM CUSTOMERS;
-- 取右 5 字元
SELECT RIGHT(AU_FNAME, 5) FROM AUTHORS;
-- 宿主變數接收彙總值
SELECT AVG(PRSTAFF) INTO :AVERAGE FROM PROJECT WHERE DEPTNO = 'D11';

五、公用表運算式 (CTE) - 臨時結果集複用

WITH PROD_QUANTITY AS (
  SELECT PRODUCT_ID, SUM(QUANTITY) AS QUANTITY
  FROM CUSTOMER_ORDER_ITEM GROUP BY PRODUCT_ID
),
TOTALS AS (
  SELECT -1 AS PRODUCT_ID, SUM(QUANTITY) AS TOTAL
  FROM PROD_QUANTITY
)
SELECT PRODUCT_ID, QUANTITY FROM PROD_QUANTITY
UNION
SELECT PRODUCT_ID, TOTAL FROM TOTALS
ORDER BY 1 DESC;

• 生命週期僅限定義它的單一 SQL 陳述式。
• 可巢狀引用 (TOTALS 引用 PROD_QUANTITY)。
• 同一陳述式中多次引用結果一致。

六、游標 - 處理多列結果集的標準機制

何時需要游標？

1. 查詢回傳多列，SELECT INTO 僅支援單列。
2. 需在更新/刪除前檢查列內容 (Positioned UPDATE/DELETE: WHERE CURRENT OF cursor)。

游標三大特徵

介面差異速查

Embedded SQL 範例 (C)

EXEC SQL DECLARE C0 CURSOR FOR SELECT DEPTNUMB, DEPTNAME FROM ORG;
EXEC SQL OPEN C0;
EXEC SQL FETCH C0 INTO :DEPTNUMB, :DEPTNAME;
while (SQLCA.SQLCODE != 100) {
  printf("%8d %-14s\n", DEPTNUMB, DEPTNAME);
  EXEC SQL FETCH C0 INTO :DEPTNUMB, :DEPTNAME;
}
EXEC SQL CLOSE C0;

七、考試重點彙整

1. 資料邏輯放置決策樹：應用端 vs 資料庫端 (SP/UDF/Trigger/RI/Check)。
2. MQT 建立語法、REFRESH 模式差異、唯一鍵前提條件。
3. UDF: FENCED vs NOT FENCED、語言選擇、安全/效能權衡。
4. CTE: WITH 子句語法、巢狀引用、臨時性質。
5. 游標: 三大特徵、四介面差異、Positioned UPDATE/DELETE 語法、SQLCODE=100 終止條件。