INFORMIX和ORACLE的存储过程的异同
的有关信息介绍如下:1、建立存储过程的语法
1)Informix
create procedure proc_name( [....in_parameter_list])
returning out_para_list / out_result_set;
2)oracle
create [or replace] procedure procedue_name
[ (arg1 [ {in | out | in out }] type
(argn [ {in | out | in out }] type,)]
{is | as} --代替DECLARE关键字
[ 变量定义区]
begin
end procedure_name;
2、错误捕捉
1)Informix
使用
on exception
end exception
2)Oracle
使用
exception
when others then
3、对游标的处理
1)Informix
create procedure pHasCursor()
define v_f1 integer;
begin
on exception
rollback work;
return;
end exception
begin work;
foreach curt1 with hold for
select f1 into v_f1 from t1 -- 注意这里没有分号
if (v_f1 = 1) then
update t1 set f2 = 'one' where current of curt1;
elif (v_f1 = 2) then
update t1 set f2 = 'two' where current of curt1;
else
update t1 set f2 = 'others' where current of curt1;
end if;
end foreach;
commit work;
end;
end procedure;
2)Oracle
create or replace procedure pHasCursor
as
v_f1 number(10,0);
cursor curt1 is
select f1 from t1 for update;
begin
open curt1;
loop
fetch curt1 into v_f1;
exit when curt1%notfound;
if (v_f1 = 1) then
update t1 set f2 = 'one' where current of curt1;
elsif (v_f1 = 2) then
update t1 set f2 = 'two' where current of curt1;
else
update t1 set f2 = 'others' where current of curt1;
end if;
end loop;
commit;
return;
exception
when others then
begin
rollback;
end;
end pHasCursor;
3、在存储过程中调用另外一个存储过程
1)Informix
Call pNoParam();
Call pNormalParam(1, ‘a’) returning v_Result;
2)Oracle
pNoParam;
pNormalParam(1, ‘a’, v_Result);
4、日期操作
1)Informix
define cur_dtime_var datetime year to second;
当前日期时间: let cur_dtime_var = current; -- datetime
2) Oracle
Currtime date;
Currtime := sysdate;
5、关于参数的说明
如果存储过程想返回一个参数,在informix中是通过返回值的形式实现的,而在oracle是通过输出参数或者输入输出参数实现的。
举例:
1)Informix:
create procedure p1() returning integer;
return 0;
end procedure;
2)oracle:
create or replace procedure p1(x out number)
as
begin
x := 0;
end p1;
6、赋值
1)informix
let v_1 = 100;
2)oracle
v_1 := 100;
7、if语句
1)informix
if (v_1 =100) then
elif (v_1=200) then
Else
end if;
2)oracle
if (v_1 =100) then
elsif (v_1=200) then
Else
end if;