數據庫兼容，在開發項目過程中，難免會遇到 更改數據庫，或者后期 項目升級，也可能會遇到這種情況，這里就說明下oralce和db2兼容開發注意事項。
兼容oralce、db2開發注意事項（前提是db2版本是9.7，且是開啟PLSQL編譯選項之后創建的數據庫)：
1. 在like 之后若使用了表字段，應統一改成使用locate函數
如：
oralce寫法：
select * from fw_right a where '03' like a.rightid||'%';
兼容寫法：
select * from fw_right a where locate('03',a.rightid) = 1;
oralce寫法：
select * from fw_right a where '03' like '%'||a.rightid||'%';
兼容寫法：
select * from fw_right a where locate('03',a.rightid) > 0;
2. 視圖中使用的別名不應該與當前表字段同名
如以下語句，在Oracle中不會有問題，但在db2中會報"SQL0153N"錯誤：
e.g:
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,
to_char(to_date(a.complete_time,'yyyymmddhh24miss'),'yyyy-mm-dd hh24:mi:ss') as 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')
3.在下列情況下不允許 ORDER BY 或 FETCH FIRST n ROWS ONLY：
* 外層全查詢視圖
* "SQL 表函數"的 RETURN 語句中的外層全查詢
* 具體化查詢表定義
* 未用圓括號括起來的子查詢
否則會報"SQL20211N 規范 ORDER BY 或 FETCH FIRST n ROWS ONLY 無效。"錯誤.
e.g:
oralce寫法：
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
兼容寫法：
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)
感謝閱讀，希望能幫助到大家，謝謝大家對本站的支持！
您可能感興趣的文章:- Oralce中VARCHAR2()與NVARCHAR2()的區別介紹
- Oralce 歸檔日志開啟與關閉示例
- 提取oralce當天的alert log的shell腳本代碼
- oralce 計算時間差的實現
