当前位置:  首页  >  PHP资讯  >  业界资讯

oralce和db2兼容开发注意事项

这篇文章主要介绍了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) 

感谢阅读,希望能帮助到大家,谢谢大家对本站的支持!

内容推荐:免费高清PNG素材下载
吐了个 "CAO" !
扫码关注 PHP1 官方微信号
PHP1.CN | 中国最专业的PHP中文社区 | PNG素材下载 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved PHP1.CN 第一PHP社区 版权所有 京ICP备19059560号-4