數(shù)據(jù)庫(kù)兼容，在開(kāi)發(fā)項(xiàng)目過(guò)程中，難免會(huì)遇到 更改數(shù)據(jù)庫(kù)，或者后期 項(xiàng)目升級(jí)，也可能會(huì)遇到這種情況，這里就說(shuō)明下oralce和db2兼容開(kāi)發(fā)注意事項(xiàng)。
兼容oralce、db2開(kāi)發(fā)注意事項(xiàng)（前提是db2版本是9.7，且是開(kāi)啟PLSQL編譯選項(xiàng)之后創(chuàng)建的數(shù)據(jù)庫(kù))：
1. 在like 之后若使用了表字段，應(yīng)統(tǒng)一改成使用locate函數(shù)
如：
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. 視圖中使用的別名不應(yīng)該與當(dāng)前表字段同名
如以下語(yǔ)句，在Oracle中不會(huì)有問(wèn)題，但在db2中會(huì)報(bào)"SQL0153N"錯(cuò)誤：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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_idsfrom 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') |
兼容寫法：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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 表函數(shù)"的 RETURN 語(yǔ)句中的外層全查詢
* 具體化查詢表定義
* 未用圓括號(hào)括起來(lái)的子查詢
否則會(huì)報(bào)"SQL20211N 規(guī)范 ORDER BY 或 FETCH FIRST n ROWS ONLY 無(wú)效。"錯(cuò)誤.
e.g:
oralce寫法：
|
1
2
3
4
5
6
7
8
9
10
|
CREATE OR REPLACE VIEW V_FW_BLANK_BULLETIN asselect 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_bulletinwhere 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
2
3
4
5
6
7
8
9
10
11
12
|
CREATE OR REPLACE VIEW V_FW_BLANK_BULLETIN asselect * 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 digest2from fw_bulletinwhere 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) |
感謝閱讀，希望能幫助到大家，謝謝大家對(duì)本站的支持！
