Informix和Oracle預存程序的異同

建立預存程序的文法:

　　一、Informix

　　create procedure proc_name( [....in_parameter_list])

　　returning out_para_list / out_result_set;

　　二、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;

　　三、幾個簡單的例子

　　1、沒有參數也沒有傳回值

　　1）Informix

　　create procedure pNoParam()

　　begin

　　on exception

　　rollback work;

　　return;

　　end exception

　　begin work;

　　delete from t1;

　　delete from t2;

　　commit work;

　　end;

　　end procedure;

　　2）Oracle

　　create or replace procedure pNoParam

　　as

　　begin

　　delete from t1;

　　delete from t2;

　　commit;

　　exception

　　when others then

　　begin

　　rollback;

　　end;

　　end pNoParam;

　　2、有輸入輸出

　　往t1表中插入一條記錄，傳回值表示插入是否成功。

　　1）Informix

　　create procedure pNormalParam(f1 integer, f2 varchar(10))

　　returning integer;

　　begin

　　on exception

　　rollback work;

　　return -1;

　　end exception

　　begin work;

　　insert into t1 values(f1, f2);

　　commit work;

　　return 0;

　　2）Oracle

　　create or replace procedure pNormalParam(f1 number,

　　f2 varchar2, v_Result out number)

　　as

　　begin

　　insert into t1 values(f1,f2);

　　commit;

　　v_Result = 0;

　　return;

　　exception

　　when others then

　　begin

　　rollback;

　　v_Result := -1;

　　end;

　　end pNormalParam;

　　需要注意的是，在oracle預存程序中，參數是不能加上size的，比如f1，在t1表中該欄位是number(10,0)，而這裡只能寫number，而不能寫number(10,0)。

　　

　　

　　3、返回記錄集

　　1）Informix

　　create procedure pReturnSet() returning integer, varchar(10);

　　define i integer;

　　define j varchar(10);

　　foreach

　　select f1, f2 into i, j from t1

　　return i, j with resume;

　　end foreach;

　　end procedure;

　　2）Oracle

　　create or replace package TestRefCursorPkg as type TestRefCursorTyp is ref cursor; procedure pReturnSet(RefCursor out TestRefCursorTyp); end TestRefCursorPkg;

　　create or replace package body TestRefCursorPkg as

　　procedure pReturnSet (RefCursor out TestRefCursorTyp)

　　as

　　localCursor TestRefCursorTyp;

　　begin

　　open localCursor for select f1, f2 from t1;

　　RefCursor := localCursor;

　　end pReturnSet;

　　end TestRefCursorPkg;

　　/

　　四、其他差異說明

　　1、錯誤捕捉

　　1）Informix使用

 

　　on exception

　　end exception

　　2）Oracle

　　使用

　　exception

　　when others then

　　2、對遊標的處理

　　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

　　② Oracle

　　Currtime date;

　　Currtime := sysdate;

　　2）當前日期的增減

　　① Informix

　　let tmp_date = today + 3 UNITS day; -- 目前時間加三天

　　let tmp_datetime = current + 1 UNITS second; -- 目前時間加1秒種

　　② Oracle

　　Tmp_date := sysdate + 3; -- 目前時間加三天

　　Tmp_date := sysdate + 1/24/3600; --目前時間加1秒種

　　3）日期轉換成字串

　　① Informix

　　let v_PeriodEndTime = year(v_date)||extend(v_date,month to month)

　　||extend(v_date,day to day) ||extend(v_date,hour to hour)

　　||extend(v_date,minute to minute)|| extend(v_date,second to second);

　　② Oracle

　　v_PeriodEndTime := to_char(v_date, 'yyyymmddhh24miss');

　　　4）字串轉換成日期

　　假設字串的形式是yyyymmddhhmiss形式的

　　① Informix

　　-- 直接轉換成日期

　　let v_BeginDate = substr(v_BeginTime,1,4)||'-'||substr(v_BeginTime,5,2)

　　||'-'||substr(v_BeginTime,7,2)||' '||substr(v_BeginTime,9,2)

　　||':'||substr(v_BeginTime,11,2)||':'||substr(v_BeginTime,13,2);

　　-- 這個月的第一天

　　let v_date = substr(v_BeginTime,1,4)||'-'

　　||substr(v_BeginTime,5,2)||'-1 00:00:00';

　　-- 這個星期的第一天

　　let v_date = substr(v_BeginTime,1,4)||'-'||substr(v_BeginTime,5,2)

　　||'-'||substr(v_BeginTime,7,2)||' 00:00:00';

　　let v_week = weekday(v_date);

　　let v_date = v_date - v_week UNITS day;

　　② Oracle

　　-- 直接轉換成日期

　　v_BeginDate := to_date(v_BeginTime, 'yyyymmddhh24miss');

　　-- 這個月的第一天

　　v_BeginDate := trunc(to_date(v_BeginTime, 'yyyymmddhh24miss'), ‘mm’);

　　-- 這個星期的第一天

　　v_BeginDate := trunc(to_date(v_BeginTime, 'yyyymmddhh24miss'), ‘day’);

　　5）事務

　　在oracle中預設情況下，一個事務的結束就是下一個事務的開始，所以對於一個事務來說，我們只要寫commit；即可，不需要明確標出什麼時候開始一個事務，而informix需要。

　　6）列印調試資訊

　　7）Informix

　　--設定跟蹤模式

　　set debug file to "trace_check"; -- with append;

　　--說明“with append”表示以追加模式開啟跟蹤結果檔案

　　trace '開始執行預存程序'

　　trace 'v_date='||v_date;

　　trace ‘預存程序執行完畢’

　　trace off;

　　執行完以後開啟目前的目錄下的trace_check即可看到列印出來的資訊。

　　8）Oracle

　　DBMS_OUTPUT.PUT_LINE(‘開始執行預存程序’);

　　DBMS_OUTPUT.PUT_LINE('v_date='||v_date);

　　DBMS_OUTPUT.PUT_LINE(‘預存程序執行完畢’);

　　先設定一下緩衝區的大小

　　set serveroutput on size 100000; -- 如果不執行該語句，會看不到調試資訊

　　執行完畢以後，列印出來的資訊就會直接顯示在介面上。

　　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;