漁人碼頭

Informix數據庫用戶一般都熟悉的使用SQL語句進行簡單的查詢和統計，而在Informix-Online的數據庫服務器中提供了用結構化查詢語言SQL語句和流程控制存儲語言SPL創建存儲例程，以減少Informix的處理。存儲例程是SQL語句和SPL的集合。它們存放在數據庫中，SQL語句會被分析.優化,在例程的執行中,高速緩存中會有一執行規劃,使后續操作的執行速度很快。而單獨的SQL語句只有在執行時才會被優化，并且存儲例程可以被不同的開發工具調用（4GL?、ESQL/C、POWERBUILDER.DELPHI），在SELECT語句中也調用過程。例程調試簡單，不必重新編譯應有軟件包。在例程創建時Informix查詢處理器會分析它，并產生一執行規劃，永久存放于SPROCEDURES.SYSPROBODY.和SYSPROPLAN中,其后例程按此規劃執行,由于大部分查詢處理已經完成,存儲例程可以在瞬間執行完畢。由于存儲例程所具有的優越性，它已成為進行Informix數據庫核心開發的有力工具，掌握它對Informix的開發人員有積極意義，現將我在實際工作中使用的語法和例子詳解于下,疏漏和錯誤請來信指教。?

1.創建和執行例程?

??①.創建例程語法：?

??????CRAETE?PROCEDURE?[OWNER.]PROCEDURE_NAME?(參數1??參數類型=[DEFAULT],?參數2??參數類型=[DEFAULT],參數n??參數類型=[DEFAULT])?

????????????????????RETURNING?值1類型,值2類型,值n類型；?

??????<......>;語句體；??????END?RPOCEDURE?

例程大小不可超過64K,這包括所有的SQL.SPL.空格.跳格符,例程名最多18個字符,并在數據庫中唯一存在,語句間用";"分隔,例程只能在當前數據庫中創建,例程創建后是一標準?

執行模板塊，可在不同的應有中對其調用,這對開發不同版本的應用將更為便利。例：?

create?procedure?"test".upwage()?

define?rev_rev_item_code?varchar(2,0);?

define?rev_p_rev_date?date;?

define?acc_rec_prem_no?decimal(8,2);define?rev_p_rev_amt??decimal(10,2);define?rev_I_info_branch?varchar(6,0);define?rev_I_info_appl_no?decimal(8,0);define?rev_I_info_date?date;define?rev_o_rev_date?date;?

define?rev_o_rev_amt?decimal(10,2);?

define?acc_ac_rev_amnt?decimal(10,2);?

define?acc_rec_prem_date?date;?

begin?work;?

foreach?cur_rev?for?select?I_info_appl_branch,I_info_appl_no,I_info_date,?

o_rev_date,o_rev_amt?into?rev_I_info_branch,?

rev_I_info_appl_no,rev_I_info_date,rev_o_rev_date,?????????????????????????????rev_o_rev_amt?from?rev_rec_tbl?where?

(rev_item_code="PS"?)?and?p_rev_date?is?null?and?

p_rev_amt=0?and?(I_info_appl_no?is?not?null?or?

I_info_date?is?not?null);?

select?max(rec_prem_acc_no)?into?acc_rec_prem_no?from?rec_prem_acc?where???????????????????????????????????I_info_appl_branch=rev_I_info_branch?

and?I_info_appl_no=rev_I_info_appl_no?

and?I_info_date=rev_I_info_date?

and?o_rev_date=rev_o_rev_date?

and?rev_item_code="PS"?and?

ac_rev_amnt=rev_o_rev_amt;?

select?date(rec_prem_date)?into?acc_rec_prem_date?

from?rec_prem_acc?where?I_info_appl_branch=rev_I_info_branch?

and?I_info_appl_no=rev_I_info_appl_no?

and?I_info_date=rev_I_info_date?

and?o_rev_date=rev_o_rev_date?

and?rev_item_code="PS"?and?

ac_rev_amnt=rev_o_rev_amt?and?

rec_prem_acc_no=acc_rec_prem_no?;?

select?ac_rev_amnt?into?acc_ac_rev_amnt?

??from?rec_prem_acc?

?where?I_info_appl_branch=rev_I_info_branch?

??and?I_info_appl_no=rev_I_info_appl_no?

??and?I_info_date=rev_I_info_date?

??and?o_rev_date=rev_o_rev_date?

??and?rev_item_code="PS"?

??and?rec_prem_acc_no=acc_rec_prem_no?

??and?ac_rev_amnt=rev_o_rev_amt;?

if?acc_ac_rev_amnt?is?null?or?acc_rec_prem_date?is?null?then?

???continue?foreach;?

end?if;?

update?rev_rec_tbl?

???set?p_rev_date=acc_rec_prem_date,?

???????p_rev_amt?=acc_ac_rev_amnt?

?where?I_info_appl_branch=rev_I_info_branch?

???and?I_info_date=rev_I_info_date?

???and?I_info_appl_no=rev_I_info_appl_no?

???and?o_rev_date=rev_o_rev_date;?

end?foreach;?

commit?work;?

end?procedure;?

??②.執行例程語法：?

????A):在dbaccess中?

???????EXECUTE?PROCEDURE?DBNAME@SERVER_NAME:例程名(參數1,參數2,.....)用這種方法可對例程進行調試。?

????B):在Informix-4GL中?

???????PREPARE?PREP?STATTEMENT?FROM?"EXECUTE?PROCEDURE?DBNAME@SERVER_NAME:例程名（?,?,?,...）?

???????DECLARE?P_CURS?SURSOR?FOR?PREP?STMT?

???????OPEN?P_CURS?USING?參數1,參數2,....?

???????FETCH?P_CURS?INTO?返回值1，返回值2，...?

???????CLOSE?P_CURS?

???????當應用程序不支持EXECUT?PROCEDURE語法,則需使用PREPARE命令,如INFORMIX-4GL中,而使用INFORMIX-NEWEAR則無此限制,需注意的是PREPARE語句中變量用?號代替,其個數要與例程的參數個數和類型一致,返回值也是一樣。?

????C):在Informix-ESQL/C中?

???????EXEC?SQL?EXECUTE?PROCEDURE?例程名(參數1,參數2,...)?INTO?（返回值1，返回值2,...）在EC5.0或更高版本可使用EXECUTE?PROCEDURE?語法,在ESQL/C中宿主變量用于想存儲例程傳遞值,同時也接收返回值.?

????D):在POWER?BUILDER中?

???????DECLARE?邏輯名?PROCEDURE?FOR?例程名(:參數1,:參數2:...)?INTO?:返回值1,:返回值2,..USING?事物名?

???????EXEC?例程名(:參數1,:參數2,.....)?

???????PB要求為例程制定邏輯名,以后的SQL語句將以邏輯名為準指向后臺數據庫例程,當例程即便沒有參數也必須有小擴號.?

2.流程控制語言：?

在過程中也提供了其他語言具備的流程控制語言,完成循環判斷和分類處理的能力,主要有:①.IF?....ELIF.....ELSE.....END?IF例：?

CRAETE?PROCEDURE?STR_COM(STR1?CHAR(20),STR2?CHAR(20))?

RETURNING?INT;?

DEFINE?REL?INT;?

IF?STR1>;STR2?THEN??--當STR1>;STR2?REL=1?

LET?REL=1;?

ELIF?STR2>;STR1?THEN?--當STR2>;STR1?REL=-1?

LET?REL=-1;?

ELSE?

LET?REL=0;???--當STR1=STR2?REL=0?

END?IF?

RETUEN?REL;?

END?PROCEDURE?

當IF的條件為一個SQL語句如SELECT時需用擴號，并且返回值為單值。?

②.FOR?.....END?FOR?

例:FOR?INDEX?IN?(20?TO?30?STEP?2,100?TO?200?STEP?10)?

???--執行代碼?

???END?FOR?

FOR的條件可以是變量，常量或一個SQL語句的返回值?

③.WHILE.......END?WHILE?

當WHILE的條件為TRUE時執行WHILE后的語句,為FALSE退出循環。?

例:WHILE?I<10?

????INSERT?INTO?TBB_1?VALUES(I);?

????LET?I=I+1;?

????END?WHILE;?

④.FOREACH........END?FOREACH?

該語句較為特別FOREACH循環能夠聲明并打開游標,讀取記錄行,并關閉游標.其完整語法:?

?FOREACH?游標名?[WITH?HOLD]?SELECT?字段名?INTO?變量?FROM?

???TABLE?WHERE?條件?；?

?END?FOREACH?

?FOREACH?EXECUTE?PROCEDURE?例程名（參數1，參數2，..）?INTO?

??變量?

?END?FOREACH?

該循環中語句的執行次數與SELECT?和?EXECUTE?PROCEDURE語句返回的行數一樣多。如果FOREACH語句中包含一條EXECUTE?PROCEDURE，則循環停止的條件為：?

.執行了不帶任何參數的RETURN語句?

.執行了END?PROCEDURE?

如果沒有返回行數據,則不再執行循環中的語句。存儲例程中不允許使用滾動游標。?

當使用WITH?HOLD時，更新游標將放置更新鎖，使其他過程無法更新該行，直至事物完成，當在FOREACH循環的語句塊中的UPDATE或DELETE有?

WHERE?CURRENT?OF短語，則存儲例程會自動使用更新游標?

例1：BEGIN?WORK；?

?????FOREACH?CUR_1?FOR?SELECT?DATE?INTO?V_DATE?FROM?TABLE?

?????IF?V_DATE?IS?NULL?THEN?

?????DELETE?FROM?TABLE?WHERE?CURRENT?OF?CUR_1;?

?????END?IF;?

?????END?FOREACH;?

?????COMMIT?WORK;?

例2：FOREACH?EXECUTE?PROCEDURE?BAR(10,20)?INTO?I?

?????INSERT?INTO?TABLE1?VALUES(I)?

?????END?PROCEDURE?

⑤.CONTINUE????適用語句(?FOR?WHILE?FOREACH)???將執行傳遞給下一次循環⑥.EXIT?

???適用語句(?FOR?WHILE?FOREACH)?

???從循環中退出?

???例：FOR?J=1?TO?20?

???????IF?J>;10?THEN?

??????????CONTINUE?FOR;?

???????END?IF?

???????LET?I,S=J,0;?

?????????WHILE?I>;0?

?????????????LET?I=I-1;?

?????????????IF?I=5?THEN?

???????????????EXIT?FOR;?

?????????????END?IF?

?????????END?WHILE?

???????END?FOR?

3.變量的定義與賦值:?

???存儲例程中使用的變量必須在例程開始處用DEFINE語句定義，變量的數據類型為除SERIAL以外的任意類型，如果定義一個TEXT或BYTE類型的變量，則該變量為指向數據的指針。傳遞給程序的變量必須在CRAETE?PROCEDURE語句中定義。DEFINE也可使用LIKE語句。變量類型缺省為局部變量，也可引用GLOBAL定義全局變量，全局變量在例程間保持它的值，直至會話結束。用戶必須為每一個定義的全局變量賦缺省值，缺省值僅在例程第一次引用該全局變量時使用，以后的例程將會忽略其缺省值。?

??例：CREATE?PROCEDURE?SP1（）?

????????RETURNING?INT；?

????????DEFINE?GLOBAL?I?INT?DEFAULT?1；?

????????LET?I=I+1；?

????????RETURN?I；?

??????END?PROCEDURE?

??????CRAETE?PROCEDURE?SP2?（）?

????????RETURNING?INT；?

????????DEFINE?GLOBAL?I?INT?DEFAULT?4；?

????????LET?I=I+1；?

????????RETURN?I;?

??????END?PROCEDURE?

?????當執行順序為SP1，SP2?返回值3，當執行順序為SP2，SP1返回值為6。?

?????例程也可被聲名為變量?

??例：DEFINE?LEN?PROCEDURE?

??????LET?X=LEN（A，B，C）?

????變量賦值必須用LET關鍵字，如果不給變量賦值，變量會有一個不確定值，任何對該變量的使用都會產生錯誤。?

??????BEGIN......END?

?利用BEGIN....END可以封裝語句,它允許用戶完成以下功能:?

?????.定義僅用于該語句塊的變量?

?????.在語句塊內以不同方式處理異常情況.?

?????.在某語句塊中定義的變量,在該語句塊以及它所包含的語句塊都有效,除非又將變量定義一次。?

??????例?:?CREATE?PROCEDURE?SP?（）?

???????????RETURNING?INT；?

???????????DEFINE?V1?INT；?

???????????LET?V1=1；?

???????????BEGIN?

?????????????DEFINE?V1?INT；?

?????????????LET?V1=2；?

???????????END?

??????????RETURN?V1；?

????????END?PROCEDURE?

???返回值為1。?

4.在例程中執行其他例程和系統命令:?

???應用CALL命令可在例程中調用其他的例程。?

???例：CREATE?PROCEDURE?SP（）?????

???????DEFINE?I，J，K，L?INT；?

?????????CALL?SP1（10，20）?RETURNING?I，J，K；?

???????END?PROCEDURE?

???用SYSTEM可在例程中調用系統命令。?通過SYSTEM命令，用戶可以執行操作系統命令。系統命令放在括號內，用雙管道號（||），還可以為SYSTEM命令串連起多個表達式。但在存儲例程中不能使用該命令的返回值，如果系統調用失敗（返回非零值），返回值將和SQL錯誤代碼一起，放在ISAM代碼中。?

????例：SYSTEM?"/usr/exec/exec.sh"?

????用RETURN命令可以將例程運行的結果返回給調用它的應用。當需要多次調用同一例程可執行RETURN?WITH?RESUME命令，它可以保證下一次調用該例程時，所有變量保持原值，而且從RETURN?WITH?RESUME后的第一條語句執行。?

????例：RETURN?V_INT?WITH?RESUME;?

5.調試跟蹤語句:?

???我們在做應用的時候，當程序完成后都需要進行數據測試，以便驗證程序邏輯的嚴密性，在例程中，INFORMIX也提供了調試跟蹤語句，其主要有（TRACE，ON?EXCEPTION，RAISE?EXCEPTION）。TRACE?語句可以跟蹤語句塊中每一個活動語句的結果和過程，并且可用SET?DEBUG?FILE?TO?FILENAME?語法寫入指定文件中。?

????ON?EXECEPTION可在例程中設定斷點，向一個DEBUGLOG或ERRLOG文件寫入錯誤信息。完整語法為:?

????ON?EXCEPTION?IN?（error_number）?

???????set?sql錯誤代碼變量，isam錯誤代碼變量，錯誤信息變量?

???????處理語句?

????END?EXCEPTION?WITH?RESUME?

????注意SET后的3個變量必須在DEFINE中定義，其中sql錯誤代碼．Isam錯誤代碼變量類型為INT,錯誤信息變量為足夠長的CHAR變量。Error_number是一個SQL錯誤代碼或用RAISE?EXCEPTION設置的陷阱代碼。WITH?RESUME為可選項,當用WITH?RESUME語句時,INFORMIX會執行完ON?EXCEPTION語句塊的命令后會返回到出錯命令行的下一行接著執行,或RAISE?EXCEPTION的下一行執行。?

????RAISE?EXCEPTION人為設定SQL錯誤代碼，ISAM錯誤代碼，錯誤信息。?

????RAISE?EXCEPTION與ON?EXCEPTION語法連用很容易跟蹤例程的錯誤代碼?

?例：CREATE?PROCEDURE?TMP_PROCEDURE()?

???????DEFINE?SQLCODE?INT;?

???????DEFINE?ISAMCODE?INT;?

???????DEFINE?ERR_TXT?CHAR(255);?

??????ON?EXCEPTION?SET?SQLCODE,ISAMCODE,ERR_TXT?

????????IF?SQLCODE=?-284?THEN?

???????????RASIE?EXCEPTION?SQLCODE,ISAMCODE,"在TMP_PROCEDURE中查詢返回多條記錄";?

????????END?IF?

????????IF?SQLCODE=?-1218?THEN?

???????????RASIE?EXCEPTION?SQLCODE,ISAMCODE,"在TMP_RPOCEDURE中類型轉換錯誤“；?

???????END?IF?

?????END?EXCEPTION?

??????SELECT?.......?

END?PROCEDURE?

??

６.過程實例:?

CREATE?PROCEDURE?YEARS(E_DATE?DATE,B_DATE?DATE)?

RETURNING?INT;返回E_DATE與B_DATE之間的整年數(E_DATE大于B_DATE)?

????DEFINE?V_E??INT;?

????DEFINE?V_B??INT;?

????LET?V_E?=?MONTH(E_DATE);?

????LET?V_B?=?MONTH(B_DATE);?

????IF?V_E?<?V_B?THEN?

???????LET?V_E?=?YEAR(E_DATE)-YEAR(B_DATE)-1;?

???????RETURN?V_E;?

????ELIF?V_E?>;?V_B?THEN?

???????LET?V_E?=?YEAR(E_DATE)-YEAR(B_DATE);?

???????RETURN?V_E;?

????END?IF;?

????LET?V_E?=?DAY(E_DATE);?

????LET?V_B?=?DAY(B_DATE);?

????IF?V_E?<?V_B?THEN?

???????LET?V_E?=?YEAR(E_DATE)-YEAR(B_DATE)-1;?

???????RETURN?V_E;?

????ELSE?

???????LET?V_E?=?YEAR(E_DATE)-YEAR(B_DATE);?

???????RETURN?V_E;?

????END?IF;?

END?PROCEDURE;