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 <tabname) select <column_list>之后,
通过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 <tablename>
( …
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
<<next2>>
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型的变量,在返回时一定要赋值,否则会出错
变量定义
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 <tabname) select <column_list>之后,
通过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 <tablename>
( …
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
<<next2>>
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型的变量,在返回时一定要赋值,否则会出错