Oracle SQL 到 DB2 SQL 移植全攻略：語法映射、函數對照與鎖行為差異

Oracle SQL 到 DB2 SQL 移植全攻略：語法映射、函數對照與鎖行為差異

異構遷移中 SQL 重寫是核心工作量。本文系統對照 Oracle 與 DB2 在條件表達、遞歸查詢、虛擬表、日期處理、空值函數、外連接、LIKE 謂詞、隔離級別/鎖行為、分頁查詢等 9 大維度的語法差異，提供可直接套用的改寫模式與實測結論。

1. DECODE → CASE 條件表達式

Oracle DECODE(expr, search1, result1, ..., default) 對應 DB2 兩種 CASE 形式：

-- 簡單 CASE
CASE expr
  WHEN search1 THEN result1
  ...
  ELSE default
END

-- 搜索 CASE
CASE
  WHEN condition1 THEN result1
  ...
  ELSE default
END

實戰對照

-- Oracle
SELECT DECODE(t.organtypecode, 'D', t.parent, 'S', t.parent, t.id)
FROM a_organ t WHERE t.parent = 35;

-- DB2
SELECT CASE x.organtypecode
         WHEN 'D' THEN x.parent
         WHEN 'S' THEN x.parent
         ELSE x.id
       END
FROM a_organ x WHERE x.parent = 35;

2. START WITH ... CONNECT BY → WITH RECURSIVE (CTE)

Oracle 層級查詢改寫為 DB2 遞歸公用表表達式：

-- Oracle
SELECT t.id FROM a_organ t
START WITH t.id IN (SELECT DECODE(...) FROM a_organ WHERE t.id = 35)
CONNECT BY t.parent = PRIOR t.id;

-- DB2
WITH FKK(id) AS (
  SELECT o.id FROM a_organ o WHERE o.id = 35
  UNION ALL
  SELECT CASE x.organtypecode
           WHEN 'D' THEN x.parent
           WHEN 'S' THEN x.parent
           ELSE x.id
         END
  FROM FKK fk, a_organ x
  WHERE fk.id = x.parent
)
SELECT DISTINCT id FROM FKK;

3. DUAL 表 → SYSIBM.SYSDUMMY1

-- Oracle
SELECT 15 AS ttt FROM dual;

-- DB2
SELECT 15 AS ttt FROM SYSIBM.SYSDUMMY1;

4. 日期轉換函數對照

WHERE 條件改寫示例

-- Oracle
WHERE m.blqsrq >= TO_DATE('$P', 'yyyy-mm-dd')
  AND m.blqsrq < TO_DATE('$P', 'yyyy-mm-dd') + 1

-- DB2
WHERE DATE(m.blqsrq) >= DATE('1900-01-01')
  AND DATE(m.blqsrq) < DATE('2050-01-01') + 1 DAY

5. NVL → COALESCE / VALUE

• NVL(expr1, expr2) → COALESCE(expr1, expr2)（標準 SQL，參數類型可異）或 VALUE(expr1, expr2)（DB2 專用，要求兩參數同類型）。
• Oracle NVL(num_col, '') 允許字符串默認值，DB2 VALUE(num_col, '') 會報類型錯誤，需用 COALESCE(num_col, 0) 或顯式轉換。

6. 外連接語法標準化

7. LIKE 謂詞限制差異

DB2 pattern-expression（右側模式）不支持列引用，僅允許：常量、特殊寄存器、宿主變量、標量函數、上述項的拼接表達式。Oracle 允許列作為模式。遷移時需將動態模式改為應用層拼接後綁定參數。

8. 隔離級別與 SELECT ... FOR UPDATE 鎖行為實測（DB2 9）

測試表 RRTest：主鍵 pkID、唯一索引 unID1、普通索引 unID2、無索引列 TSID。

⁽¹⁾ RR 下普通索引掃描未加鎖，原理待深究（可能因非唯一鍵範圍鎖機制差異）。
⁽²⁾ RS 表掃描同 RR，鎖全表。

四大隔離級別特性

• RR：掃描到的所有行加鎖（共享/排他），防止幻讀，鎖最多，並發最低。
• RS：僅鎖定符合條件的行（無條件則全表），允許幻讀。
• CS (默認)：僅鎖當前處理行（游標穩定性），並發最高。
• UR：讀操作無行鎖（髒讀），寫操作同 CS。

9. 分頁查詢標準寫法

-- DB2 (ROW_NUMBER 標準)
SELECT * FROM (
  SELECT col1, col2, col3,
         ROW_NUMBER() OVER (ORDER BY sort_col ASC) AS rn
  FROM tab
) AS t WHERE t.rn BETWEEN 10 AND 20;

-- 簡化：SELECT * FROM table FETCH FIRST 20 ROWS ONLY (僅首頁)

遷移檢查清單

• [ ] DECODE/CASE 表達式全量轉換
• [ ] CONNECT BY 遞歸改寫為 WITH RECURSIVE
• [ ] DUAL 替換為 SYSIBM.SYSDUMMY1
• [ ] 日期函數/運算符調整
• [ ] NVL → COALESCE/VALUE 並驗證類型兼容
• [ ] (+) 外連接改寫為 ANSI JOIN
• [ ] LIKE 右側列引用改為參數化
• [ ] 隔離級別語義對齊（Oracle 默認 RC ≈ DB2 CS）
• [ ] 分頁查詢改用 ROW_NUMBER 或 FETCH FIRST
• [ ] 測試鎖行為差異對並發業務的影響