Oracle 遷移 DB2 SQL 改寫完整對照表：DECODE、CONNECT BY、DUAL、NVL、外部聯結、日期、LIKE、分頁

1. DECODE → CASE

-- Oracle
SELECT DECODE(org_type, 'D', parent_id, 'S', parent_id, org_id) FROM a_organ;

-- DB2
SELECT CASE org_type
         WHEN 'D' THEN parent_id
         WHEN 'S' THEN parent_id
         ELSE org_id
       END
FROM a_organ;

2. CONNECT BY 遞歸 → WITH RECURSIVE (CTE)

-- Oracle
SELECT id FROM a_organ
START WITH id IN (SELECT DECODE(org_type,'D',parent_id,'S',parent_id,org_id) FROM a_organ WHERE id=35)
CONNECT BY PRIOR id = parent_id;

-- DB2
WITH RECURSIVE FKK(id) AS (
  SELECT id FROM a_organ WHERE id=35
  UNION ALL
  SELECT CASE x.org_type
           WHEN 'D' THEN x.parent_id
           WHEN 'S' THEN x.parent_id
           ELSE x.org_id
         END
  FROM FKK fk, a_organ x
  WHERE fk.id = x.parent_id
)
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. 日期轉換與比較

-- Oracle: TO_DATE('2023-01-01','YYYY-MM-DD')
-- DB2:    DATE('2023-01-01')  或  TIMESTAMP('2023-01-01')

-- 範圍查詢
-- Oracle: blqsrq >= TO_DATE(:p1,'YYYY-MM-DD') AND blqsrq < TO_DATE(:p2,'YYYY-MM-DD')+1
-- DB2:    DATE(blqsrq) >= DATE(:p1) AND DATE(blqsrq) < DATE(:p2) + 1 DAY

5. NVL → COALESCE / VALUE

-- Oracle: NVL(comm, 0)
-- DB2:    COALESCE(comm, 0)  -- 標準 SQL，參數型別需一致
-- DB2:    VALUE(comm, 0)     -- DB2 相容函數，同樣要求型別一致

注意：DB2 VALUE 兩參數型別必須相容，不如 Oracle NVL 寬鬆。

6. 外部聯結：(+) → ANSI JOIN

-- Oracle 右外聯結:  WHERE a.dept_no = b.dept_no(+)
-- DB2:              RIGHT OUTER JOIN b ON a.dept_no = b.dept_no

-- Oracle 左外聯結:  WHERE a.dept_no(+) = b.dept_no
-- DB2:              LEFT OUTER JOIN b ON a.dept_no = b.dept_no

-- 全外聯結: FULL OUTER JOIN

7. LIKE 謂詞限制

DB2 LIKE 右運算元不支援欄位參考，僅支援：常數、特殊暫存器、主機變數、標量函數、上述串接運算式。
Oracle 允許 WHERE col1 LIKE col2，DB2 需改寫為 WHERE col1 LIKE '%' || col2 || '%'（效能較差）或應用層處理。

8. 隔離級別與 FOR UPDATE 鎖行行為

* RR 下普通索引掃描不鎖行（因非唯一鍵可能產生幻讀，但 DB2 優化選擇不鎖），RS 則鎖行。

9. 分頁查詢

-- DB2 標準分頁 (ROW_NUMBER)
SELECT * FROM (
  SELECT col1, col2, ROW_NUMBER() OVER (ORDER BY id ASC) AS rn
  FROM my_table
) WHERE rn BETWEEN 10 AND 20;

10. 其他常用對照

• SYSDATE → CURRENT TIMESTAMP
• TRUNC(date) → DATE(date)
• ROWNUM → ROW_NUMBER() OVER() 或 FETCH FIRST n ROWS ONLY
• INSTR → LOCATE / POSSTR
• SUBSTR 語法相同，參數順序一致