informix

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_STAT pctfree 10 pctused 40 initrans 1 maxtrans 255 storage (

initial 64K minextents 1

maxextents unlimited ),

partition P_02 values less than (3) tablespace ZHJS_STAT pctfree 10 pctused 40 initrans 1 maxtrans 255 storage (

initial 64K minextents 1

maxextents 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 oracle

merge INTO TRUNK_ERROR D

USING (SELECT * FROM TMP_TRUNK_ERROR) S ON( S.source_id = D.source_id AND S.trunk_code = D.trunk_code AND S.trunk_side = D.trunk_side)

WHEN matched THEN UPDATE SET D.num_cdrs = D.num_cdrs + S.num_cdrs WHEN 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表中

truncate Oracle:

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 -oracle

cursor c_cur2(v_task_id number) is

select format_id,format_item,format_item_val,deal_date from tg_stat_result_temporarily where 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_code

into v_wg_model_code,v_wg_log_code,v_wg_recycle_code, v_wg_record_code

from zhjs_param_tj.tg_format_item

where format_id=v_cur2.format_id and format_item=v_cur2.format_item; cursor -informix foreach

select 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_id end foreach; rownum -oracle Oracle:

select sum(a.monitor_val) into v_total_value

from (select monitor_val from flux_monitor_result

where area_code=v_area and source_id=v_source and account_item=v_account

and to_char(to_date(sampling_date,'yyyymmdd'),'fmday') ='星期六' and check_flag=1 order by sampling_date desc) a where rownum <= v_count;

rownum -informix Informix:

select sum(a.monitor_val) into v_total_value from (select first v_count-1 monitor_val from flux_monitor_result

where area_code=v_area and source_id=v_source and account_item=v_account

and weekday(to_date(sampling_date,' %Y%m%d '))=6 and check_flag=1

order by sampling_date desc);

标号语句 Oracle： Loop

if v_expression1 is null then goto next2; end if

<> null;

end loop; informix： loop

if v_expression1 is null then contiune; end if; end loop;

informix的continue/exit语句在loop/while/for/foreach中都可以使用

Oracle CONNECT BY语句

将具有层次关系的表展示为树形结构：

select a.tnode tnode,a.tprior_node tprior_node,sys_connect_by_path(tname,'*') by_path

from tp_relation a

start with a.tprior_node is null

connect by prior a.tnode = a.tprior_node； connect by为关联条件；

start with为树形结构的进入点； Informix CONNECT BY实现方法 start with为树形结构的进入点；

Informix CONNECT BY实现方法

通过底层递归调用存储过程，完成层次关联处理 提供封装的存储过程实现CONNECT BY功能 定义异常的顺序需要注意！（informix）

对OTHERS的定义必须位于其他异常定义的最后面，要不然会出现异常代码没有定义的错误信息

另外，对于返回的varchar型的变量，在返回时一定要赋值，否则会出错