漁人碼頭

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

1.創(chuàng)建和執(zhí)行例程?

??①.創(chuàng)建例程語法：?

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

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

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

例程大小不可超過64K,這包括所有的SQL.SPL.空格.跳格符,例程名最多18個字符,并在數(shù)據(jù)庫中唯一存在,語句間用";"分隔,例程只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建,例程創(chuàng)建后是一標(biāo)準(zhǔn)?

執(zhí)行模板塊，可在不同的應(yīng)有中對其調(diào)用,這對開發(fā)不同版本的應(yīng)用將更為便利。例：?

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;?

??②.執(zhí)行例程語法：?

????A):在dbaccess中?

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

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

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

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

???????OPEN?P_CURS?USING?參數(shù)1,參數(shù)2,....?

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

???????CLOSE?P_CURS?

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

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

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

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

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

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

???????PB要求為例程制定邏輯名,以后的SQL語句將以邏輯名為準(zhǔn)指向后臺數(shù)據(jù)庫例程,當(dāng)例程即便沒有參數(shù)也必須有小擴號.?

2.流程控制語言：?

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

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

RETURNING?INT;?

DEFINE?REL?INT;?

IF?STR1>;STR2?THEN??--當(dāng)STR1>;STR2?REL=1?

LET?REL=1;?

ELIF?STR2>;STR1?THEN?--當(dāng)STR2>;STR1?REL=-1?

LET?REL=-1;?

ELSE?

LET?REL=0;???--當(dāng)STR1=STR2?REL=0?

END?IF?

RETUEN?REL;?

END?PROCEDURE?

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

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

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

???--執(zhí)行代碼?

???END?FOR?

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

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

當(dāng)WHILE的條件為TRUE時執(zhí)行WHILE后的語句,為FALSE退出循環(huán)。?

例:WHILE?I<10?

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

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

????END?WHILE;?

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

該語句較為特別FOREACH循環(huán)能夠聲明并打開游標(biāo),讀取記錄行,并關(guān)閉游標(biāo).其完整語法:?

?FOREACH?游標(biāo)名?[WITH?HOLD]?SELECT?字段名?INTO?變量?FROM?

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

?END?FOREACH?

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

??變量?

?END?FOREACH?

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

.執(zhí)行了不帶任何參數(shù)的RETURN語句?

.執(zhí)行了END?PROCEDURE?

如果沒有返回行數(shù)據(jù),則不再執(zhí)行循環(huán)中的語句。存儲例程中不允許使用滾動游標(biāo)。?

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

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

例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)???將執(zhí)行傳遞給下一次循環(huán)⑥.EXIT?

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

???從循環(huán)中退出?

???例：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語句定義，變量的數(shù)據(jù)類型為除SERIAL以外的任意類型，如果定義一個TEXT或BYTE類型的變量，則該變量為指向數(shù)據(jù)的指針。傳遞給程序的變量必須在CRAETE?PROCEDURE語句中定義。DEFINE也可使用LIKE語句。變量類型缺省為局部變量，也可引用GLOBAL定義全局變量，全局變量在例程間保持它的值，直至?xí)捊Y(jié)束。用戶必須為每一個定義的全局變量賦缺省值，缺省值僅在例程第一次引用該全局變量時使用，以后的例程將會忽略其缺省值。?

??例：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?

?????當(dāng)執(zhí)行順序為SP1，SP2?返回值3，當(dāng)執(zhí)行順序為SP2，SP1返回值為6。?

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

??例：DEFINE?LEN?PROCEDURE?

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

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

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

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

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

?????.在語句塊內(nèi)以不同方式處理異常情況.?

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

??????例?:?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.在例程中執(zhí)行其他例程和系統(tǒng)命令:?

???應(yīng)用CALL命令可在例程中調(diào)用其他的例程。?

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

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

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

???????END?PROCEDURE?

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

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

????用RETURN命令可以將例程運行的結(jié)果返回給調(diào)用它的應(yīng)用。當(dāng)需要多次調(diào)用同一例程可執(zhí)行RETURN?WITH?RESUME命令，它可以保證下一次調(diào)用該例程時，所有變量保持原值，而且從RETURN?WITH?RESUME后的第一條語句執(zhí)行。?

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

5.調(diào)試跟蹤語句:?

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

????ON?EXECEPTION可在例程中設(shè)定斷點，向一個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設(shè)置的陷阱代碼。WITH?RESUME為可選項,當(dāng)用WITH?RESUME語句時,INFORMIX會執(zhí)行完ON?EXCEPTION語句塊的命令后會返回到出錯命令行的下一行接著執(zhí)行,或RAISE?EXCEPTION的下一行執(zhí)行。?

????RAISE?EXCEPTION人為設(shè)定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中類型轉(zhuǎn)換錯誤“；?

???????END?IF?

?????END?EXCEPTION?

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

END?PROCEDURE?

??

６.過程實例:?

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

RETURNING?INT;返回E_DATE與B_DATE之間的整年數(shù)(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;