变量定义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
DUALOracle: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
exceptionOracle: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型的变量，在返回时一定要赋值，否则会出错 
