oracle开发 VS INFORMIX开发.docxVIP

变量定义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:syslockswhere 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/60Informix：增加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,yyyymmdd))=6DUALOracle: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的同义词，已经在zhjs_app中创建。其中：i是整形变量others?? exception 的转换Oracle:exception?? when others theno_returncode := -1;o_returnmsg?? := substr([01] || p_bi_control_call_code错误告警 ||sqlerrm,1,255);rollback;Informix:define sql_err int;define isqm_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=-1let = substr([01] || p_bi_control_call_code错误告警 ||err_info,1,255);rollback;return o_returncode,o_returnmsg;end exception;人工触发exceptionOracle:e_error exception; /* exception是一个变量*/if v_flag not in(a,b) thenraise e_error;end if;exceptionwhen e_error thenrollback;vo_errmsg := [p_expdb_acc_d_sms]error:||vo_errmsg;vo_return := 1161;Informix: ?? on exception in (30000) rollback;let vo_errmsg = [p_expdb_acc_d_sms]error:||vo_errmsg;let vo_return = 1161;return vo_errrms,vo_returnend exception;if v_flag not in(a,b) thenraise exception 30000;end if;注意: 1）exce