oralce和db2兼容开发注意事项

山有山的沉稳厚重,一年四季却能以不同的色彩风光展示山的美丽。活泼的有单调的一面,安静的有变换的色彩。静与动的搭配,单调与精彩的结合,也就组成了最美的风景。

数据库兼容,在开发项目过程中,难免会遇到 更改数据库,或者后期 项目升级,也可能会遇到这种情况,这里就说明下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和db2兼容开发注意事项。对于世界而言,你只是一粒尘埃,但是对于父母而言,你是他们的整个世界。更多关于oralce和db2兼容开发注意事项请关注haodaima.com其它相关文章!

您可能有感兴趣的文章
oralce计算时间差的如何实现

Oralce中VARCHAR2()与NVARCHAR2()的区别介绍

Oralce归档日志开启与关闭示例

设置oralce自动内存管理执行步骤

Oralce数据导入出现(SYSTEM.PROC_AUDIT)问题处理方法