Oracle 與 DB2 兼容開發核心注意事項總結

前言

在同時支援 Oracle 與 DB2（本文基於 DB2 9.7 + PLSQL 編譯選項）的雙資料庫架構開發中，語法差異常導致移植失敗。本文歸納三大核心不兼容場景及統一寫法，助你規避開發陷阱。

一、LIKE 條件中使用表欄位：統一改用 LOCATE 函數

場景 1：前綴匹配

SELECT * FROM fw_right a WHERE '03' LIKE a.rightid || '%';

SELECT * FROM fw_right a WHERE LOCATE('03', a.rightid) = 1;

場景 2：包含匹配

SELECT * FROM fw_right a WHERE '03' LIKE '%' || a.rightid || '%';

SELECT * FROM fw_right a WHERE LOCATE('03', a.rightid) > 0;

原理： LOCATE(substr, str) 返回子串在字串中的起始位置（從 1 開始），不存在返回 0。等同於 Oracle 的 INSTR。

二、視圖別名不可與基表欄位同名

錯誤示範 (DB2 報 SQL0153N)

CREATE OR REPLACE VIEW V_WF_TODOLIST AS
SELECT c.process_def_id,
       c.process_def_name,
       a.action_def_id,
       a.work_item_id,
       a.bae007,
       a.action_def_name,
       a.state,
       a.pre_wi_id,
       a.work_type,
       a.operid,
       a.x_oprator_ids,
       b.process_key_info,
       TO_CHAR(TO_DATE(a.start_time, 'yyyymmddhh24miss'), 'yyyy-mm-dd hh24:mi:ss') AS start_time,  -- 衝突：基表有 start_time 欄位
       TO_CHAR(TO_DATE(a.complete_time, 'yyyymmddhh24miss'), 'yyyy-mm-dd hh24:mi:ss') AS complete_time, -- 衝突：基表有 complete_time 欄位
       a.filter_opr, a.memo, a.bae002, a.bae003, a.bae006, c.x_action_def_ids
FROM wf_work_item a, wf_process_instance b, wf_action_def c
WHERE a.action_def_id = c.action_def_id
  AND b.process_def_id = c.process_def_id
  AND a.bae007 = b.bae007
  AND a.state IN ('0','2');

修正版：別名加後綴區分

CREATE OR REPLACE VIEW V_WF_TODOLIST AS
SELECT c.process_def_id,
       c.process_def_name,
       a.action_def_id,
       a.work_item_id,
       a.bae007,
       a.action_def_name,
       a.state,
       a.pre_wi_id,
       a.work_type,
       a.operid,
       a.x_oprator_ids,
       b.process_key_info,
       TO_CHAR(TO_DATE(a.start_time, 'yyyymmddhh24miss'), 'yyyy-mm-dd hh24:mi:ss') AS start_time_0,
       TO_CHAR(TO_DATE(a.complete_time, 'yyyymmddhh24miss'), 'yyyy-mm-dd hh24:mi:ss') AS complete_time_0,
       a.filter_opr, a.memo, a.bae002, a.bae003, a.bae006, c.x_action_def_ids
FROM wf_work_item a, wf_process_instance b, wf_action_def c
WHERE a.action_def_id = c.action_def_id
  AND b.process_def_id = c.process_def_id
  AND a.bae007 = b.bae007
  AND a.state IN ('0','2');

規則： 視圖 SELECT 列表中的別名，不得與 FROM 子句涉及的任何基表欄位名稱相同。

三、視圖/表函數/物化查詢/子查詢中 ORDER BY 與 FETCH FIRST 限制

DB2 禁止在以下物件的最外層直接使用 ORDER BY 或 FETCH FIRST n ROWS ONLY：

• 外層完整查詢視圖
• SQL 表函數 RETURN 語句的外層查詢
• 物化查詢表定義
• 未用括號括起的子查詢

違規將報錯：SQL20211N 規範 ORDER BY 或 FETCH FIRST n ROWS ONLY 無效。

錯誤視圖定義

CREATE OR REPLACE VIEW V_FW_BLANK_BULLETIN AS
SELECT id, bae001, operunitid, operunittype, unitsubtype, ifergency,
       title, content, digest, duetime, validto, aae100,
       bae006, bae002, bae003, id AS colid,
       SUBSTR(digest,1,20) AS digest2
FROM fw_bulletin
WHERE duetime <= TO_CHAR(SYSDATE,'yyyymmddhh24miss')
  AND (TO_CHAR(validto) >= TO_CHAR(SYSDATE,'yyyymmddhh24miss') OR validto IS NULL)
  AND aae100 = '1'
ORDER BY ifergency DESC, id DESC, duetime DESC;  -- 直接在視圖最外層 ORDER BY，DB2 報錯

兼容寫法：包裝子查詢

CREATE OR REPLACE VIEW V_FW_BLANK_BULLETIN AS
SELECT * FROM (
    SELECT id, bae001, operunitid, operunittype, unitsubtype, ifergency,
           title, content, digest, duetime, validto, aae100,
           bae006, bae002, bae003, id AS colid,
           SUBSTR(digest,1,20) AS digest2
    FROM fw_bulletin
    WHERE duetime <= TO_CHAR(SYSDATE,'yyyymmddhh24miss')
      AND (TO_CHAR(validto) >= TO_CHAR(SYSDATE,'yyyymmddhh24miss') OR validto IS NULL)
      AND aae100 = '1'
    ORDER BY ifergency DESC, id DESC, duetime DESC
);

四、總結

1. LIKE + 欄位 → LOCATE：統一字串匹配邏輯
2. 視圖別名避開基表欄位：加後綴（_0, _v 等）區分
3. ORDER BY/FETCH FIRST 需包裝子查詢：符合 DB2 語法規範

遵循上述三大原則，可顯著降低雙資料庫開發的移植風險與維護成本。