程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> 關於Oracle數據庫 >> oralce和db2兼容開發注意事項

oralce和db2兼容開發注意事項

編輯:關於Oracle數據庫

數據庫兼容,在開發項目過程中,難免會遇到 更改數據庫,或者後期 項目升級,也可能會遇到這種情況,這裡就說明下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)

 

感謝閱讀,希望能幫助到大家,謝謝大家對本站的支持!

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved