oracle开发 VS INIX开发.docx

变量定义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_idin (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(20080910, yyyymmdd)Informix： to_char(today,%Y%m%d) ；to_char(current,%Y%m%d);to_date(20080910,%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;人工触发exception Oracle: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_smserror:|vo_errmsg;vo_return := 1161;Informix: on exception in (30000) rollback;let vo_errmsg = p_expdb_acc_d_smserror:|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）exception的错误号必须是一个smallint的数字； 2）informix通过return语句获得返回值；而oracle通过头文件的声明来获得；3) 无论是informix还是oracle在触发exception之后，缺省的情况下程序都退出过 程；Oracle NO_DATA_FOUND exceptionOracle:EXCEPTIONWHEN NO_DATA_FOUND THENo_returncode := 1;o_returnmsg := SUBSTR(01 | 没有 | v_date | 数据 | SQLERRM, 1, 255);ROLLBACK;Informix: 在select into/ insert into tabname) select 之后,通过dbinfo函数来判断如果查询到的记录行数为0，则人工触发exceptionif dbinfo(sqlca.sqlerrd2)= 0 thenraise exception 100;end if;Oracle中捕捉记录重复的exceptione_unique EXCEPTION;WHEN e_unique THENRAISE DUP_VAL_ON_INDEX;END;WHEN DUP_VAL_ON_INDEX THENROLLBACK;v_errmsg := SUBSTR(规则:|v_val_name|已存在!,1,500);v_errcode := 1161;Informix中捕捉记录重复的exceptionon EXCEPTION in (-268) -重复记录,-268是插入重复记录的出错号ROLLBACK;let v_errmsg = SUBSTR(规则:|v_val_name|已存在!, 1,500);let v_errcode = 1161;return v_errcode;end exception;exception的嵌套处理oracle:o_returncode=:1;beginif (substr(i_dealdate,7,2)=21) thenv_sql:=insert into jtfx_to_tsp_callnumber_bak(tsp_code,area_code,brand_code)select tsp_code,area_code,brand_codefrom jtfx_to_tsp_callnumber t ;execute immediate v_sql;commit;end if;exception when NO_DATA_FOUND o_returncode := 1;rollback;end ;o_returncode:=0; -将继续执行这条语句Oracle在begin/end块之间如果触发了NO_DATA_FOUND的意外,有两种处理情况：1.如果在本块内定义了NO_DATA_FOUND的意外处理程序，将继续执行end之后的语句；2.如果在本块内没有定义NO_DATA_FOUND的意外处理程序，将执行程序末尾的exception处理程序，主过程返回。informix由于exception必须定义在begin之后，因此需要按下面的方式处理：let v_returncode=1;beginon exception in (100)let i=i;end exception;if (substr(i_dealdate,7,2)=01) thenlet v_sql=insert into bi_to_tsp_callnumber_bak(tsp_code,area_code,brand_code,call_code,call_head,first_date,last_date)select tsp_code,area_code,brand_code,call_code,call_head,first_date,last_datefrom bi_to_tsp_callnumber t ;execute immediate v_sql;if dbinfo(sqlca.sqlerrd2)=0 thenraise exception 100;end if;end if;end ;let v_returncode=0; -将继续执行这条语句Oracle的partitoin定义create table TRW_REPORT_CTJS(DAY_ID NUMBER(2) not null,PROV_CODE NUMBER default 0 not null )partition by range (DAY_ID)(partition P_01 values less than (2)tablespace ZHJS_STATpctfree 10pctused 40initrans 1maxtrans 255storage(initial 64Kminextents 1maxextents unlimited),partition P_02 values less than (3)tablespace ZHJS_STATpctfree 10pctused 40initrans 1maxtrans 255storage(initial 64Kminextents 1maxextents unlimited),Oracle指定到特定的partition上读取数据v_sql:= insert into tmp_qh_voice_hlht_yyfx_rep3 | select substr(t.start_datetime,1,6)| from tl_y_voice_list_|vi_month| partition(p_|vi|) t | where t.called_brand_code in (41,51,55,82,83,84,85,89) ;execute immediate (v_sql);informix按日期分片 create table ( day_id int) fragment by expression parttion part1 day_id =1 in datadbs,parttion part2 day_id =2 in datadbs,parttion part3 day_id =3 in datadbs ,.parttion part31 day_id=31 in datadbs;-也可以指定放在同一个dbspace上informix指定到特定的partition上读取数据 v_sql:= insert into tmp_qh_voice_hlht_yyfx_rep3 | select substr(t.start_datetime,1,6)| from tl_y_voice_list_|vi_month| where day_id=v_day_id) | where t.called_brand_code in (41,51,55,82,83,84,85,89) ;execute immediate (v_sql);informix是通过跳过片的方式来实现merge oraclemerge INTO TRUNK_ERROR DUSING (SELECT * FROM TMP_TRUNK_ERROR) SON( S.source_id = D.source_id ANDS.trunk_code = D.trunk_code ANDS.trunk_side = D.trunk_side)WHEN matched THEN UPDATE SET D.num_cdrs = D.num_cdrs + S.num_cdrsWHEN NOT matched THEN INSERT(D.source_id,D.trunk_code,D.trunk_side,D.num_cdrs,D.STAT_FLAG) VALUES(S.source_id,S.trunk_code,S.trunk_side,S.num_cdrs,0 );Informix通过cursor来实现merge的功能在TMP_TRUNK_ERROR上定义cursor对fetch出的每一行记录按条件判断在TRUNK_ERROR 是否存在相同的记录如果存在，则将trunk_error中的相应记录值修改为tmp_trunk_error中的值或进行其他的处理；如果不存在，则将此记录插入到trunk_error表中truncateOracle:EXECUTE IMMEDIATE truncate table JTFX_TMP_TSP_CALLNUMBER;Informix :truncate table JTFX_TMP_TSP_CALLNUMBER;执行动态SQL Oracle ：具有下面的语法：1.exceute immediate sqlcmd into :var1,:var2 ;2.execute immediate sqlcmd using :var1,:var2;Informix:目前在过程中没有into和using的选项；需要通过游标来实现；需要将var1和var2以及sqlcmd连接为一个字符串，然后在通过execute immediate来执行cursor -oraclecursor c_cur2(v_task_id number) isselect format_id,format_item,format_item_val,deal_date from tg_stat_result_temporarilywhere task_id=v_task_id;for v_cur2 in c_cur2(v_cur1.task_id)loop select wg_model_code,wg_log_code,wg_recycle_code,wg_record_codeinto v_wg_model_code,v_wg_log_code,v_wg_recycle_code,v_wg_record_codefrom zhjs_param_tj.tg_format_itemwhere format_id=v_cur2.format_id and format_item=v_cur2.format_item;cursor -informix foreachselect format_id,format_item,format_item_val,deal_date into v_format_id, v_format_item,v_format_item_val,v_deal_date from tg_stat_result_temporarily where task_id=v_task_idend foreach;rownum -oracleOracle:select sum(a.monitor_val) into v_total_valuefrom (select monitor_val from flux_monitor_result where area_code=v_area and source_id=v_sourceand account_item=v_accountand to_char(to_date(sampling_date,yyyymmdd),fmday)=星期六 and check_flag=1order by sampling_date desc) awhere rownum = v_count;rownum -informix Informix:select sum(a.monitor_val) into v_total_valuefrom (select first v_count-1 monitor_val from flux_monitor_result where area_code=v_area and source_id=v_sourcea