5.2-Oralce_to_Informix移植指南.ppt

变量定义 Oracle: v_table_name USER_TABLES.table_name%TYPE; Informix: define v_table_name like systables.tabname; 判断某张表是否被锁住 Oracle: select count(*) into v_count from v$locked_object where object_id in (select object_id from user_objects where object_name=upper(fact_g_gprs_settle) ); Informix: select count(*) into v_count from sysmaster:syslocks where owner0 and waiter0 and tabname=lower(‘fact_G_gprs_settle) ); to_char和to_date函数 Oracle： to_char(sysdate,yyyymmdd) to_date(,’ yyyymmdd’) Informix： to_char(today,’%Y%m%d‘) ； to_char(current,’%Y%m%d’); to_date(,%Y%m%d); 在日期中增加某个单元值 Oracle： 增加1天： sysdate +1 增加一分钟：SYSDATE+1/24/60 Informix： 增加1天： today + 1 units day (增加其他时间单元方法类似)； 增加1分钟：current + 1 units minute 判断是否是星期六 Oracle: to_char(to_date(sampling_date,yyyymmdd),fmday)=星期六 informix： weekday(to_date(sampling_date,‘%Y%m%d))=6 DUAL Oracle: select to_char(sysdate - i, yyyymmdd) into v_date from dual; Informix: let v_date = to_char(today – i units day,’%Y%m%d’) 不需要和数据库交互； 或者 select to_char(today – i units day, ‘%Y%m%d) into v_date from dual; 其中dual是sysmaster:sysdual的同义词 其中：i是整形变量 others exception 的转换 Oracle: exception when others then o_returncode := -1; o_returnmsg := substr([01] || p_bi_control_call_code错误告警 ||sqlerrm,1,255); rollback; Informix: define sql_err int; define isam_err int; define err_info varchar(255) on exception set sql_err,isam_err,err_info -- informix依次把sqlcode,isam code和错误信息传递给上述3个变量； let o_return_code=-1 let = substr([01] || p_bi_control_call_code错误告警 ||err_info,1,255); rollback; return o_returncode,o_returnmsg; end exception; 人工触发exception Oracle: e_error exception; /* exception是一个变量*/ if v_flag not in(a,b) then raise e_error; end if; exc