oracle开发 VS INFORMIX开发 实战总结

变量定义
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 owner>0 and waiter>0
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/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,'yyyymmdd'))=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的同义词，已经在zhjs_app中创建。
其中：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 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=-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;
exception
when e_error then
rollback;
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_return
end exception;
if v_flag not in('a','b') then
raise exception 30000;
end if;
注意: 1）exception的错误号必须是一个smallint的数字；
2）informix通过return语句获得返回值；而oracle通过头文件的声明来获得；
3) 无论是informix还是oracle在触发exception之后，缺省的情况下程序都退出过 程；

Oracle NO_DATA_FOUND exception
Oracle:
EXCEPTION
WHEN NO_DATA_FOUND THEN
o_returncode := 1;
o_returnmsg := SUBSTR('[01]' || '没有' || v_date || '数据 ' || SQLERRM, 1, 255);
ROLLBACK;
Informix:
在select … into/ insert into 之后,
通过dbinfo函数来判断如果查询到的记录行数为0，则人工触发exception
if dbinfo(‘sqlca.sqlerrd2’)= 0 then
raise exception 100;
end if;

Oracle中捕捉记录重复的exception
e_unique EXCEPTION;
WHEN e_unique THEN
RAISE DUP_VAL_ON_INDEX;
END;
WHEN DUP_VAL_ON_INDEX THEN
ROLLBACK;
v_errmsg := SUBSTR('规则:'||v_val_name||'已存在!',
1,
500);
v_errcode := 1161;

Informix中捕捉记录重复的exception
on 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;
begin
if (substr(i_dealdate,7,2)='21') then
v_sql:='insert into jtfx_to_tsp_callnumber_bak(tsp_code,area_code,brand_code)
select tsp_code,area_code,brand_code
from 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;
begin
on exception in (100)
let i=i;
end exception;
if (substr(i_dealdate,7,2)='01') then
let v_sql='insert into bi_to_tsp_callnumber_bak(tsp_code,area_code,brand_code,ca
ll_code,call_head,first_date,last_date)
select tsp_code,area_code,brand_code,call_code,call_head,first_date,last_date
from bi_to_tsp_callnumber t' ;
execute immediate v_sql;
if dbinfo("sqlca.sqlerrd2")=0 then
raise 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_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型的变量，在返回时一定要赋值，否则会出错