我们在做兼容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"错误。
- 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 无效。"错误。
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)
了解了以上Oracle、DB2开发时的注意事项，我们在做开发的时候就能够尽量避免一些不兼容得状况了。本文就介绍到这里，希望能对各位有所帮助。
【编辑推荐】
