存儲(chǔ)過(guò)程的一點(diǎn)相關(guān)資料
1 存儲(chǔ)過(guò)程的概念
通常情況下，在客戶-服務(wù)器體系結(jié)構(gòu)中，運(yùn)行在客戶端的應(yīng)用程序通過(guò)SQL對(duì)服務(wù)器端的數(shù)據(jù)庫(kù)進(jìn)行訪問(wèn)時(shí)，每一條SQL語(yǔ)句是分別地、一句一句地從客戶端向服務(wù)器發(fā)出請(qǐng)求，然后數(shù)據(jù)庫(kù)服務(wù)器再將結(jié)果一個(gè)一個(gè)地返回給應(yīng)用程序。但是，有一些應(yīng)用程序，涉及的服務(wù)器端數(shù)據(jù)庫(kù)處理活動(dòng)很多，而與用戶的交互活動(dòng)較少，在這種情況下，將有關(guān)數(shù)據(jù)庫(kù)的處理活動(dòng)以儲(chǔ)存過(guò)程的形式放在數(shù)據(jù)庫(kù)服務(wù)器上完成，則可以大大減輕網(wǎng)上傳輸流量，提高應(yīng)用程序性能。儲(chǔ)存過(guò)程的概念如下圖所示。
使用儲(chǔ)存過(guò)程的應(yīng)用程序具有以下優(yōu)點(diǎn)：
A:減少網(wǎng)上傳輸流量；
B:改善服務(wù)器處理密集型應(yīng)用的性能；
C:以訪問(wèn)數(shù)據(jù)庫(kù)服務(wù)器特有的功能特性；
D:易于維護(hù)；
2 存儲(chǔ)過(guò)程的程序結(jié)構(gòu)
一個(gè)完整的使用儲(chǔ)存過(guò)程的應(yīng)用程序由兩部分組成：一是儲(chǔ)存過(guò)程本身，它被存放在數(shù)據(jù)庫(kù)服務(wù)器端并數(shù)據(jù)庫(kù)服務(wù)器上運(yùn)行；二是對(duì)存儲(chǔ)過(guò)程進(jìn)行調(diào)用的客戶端應(yīng)用程序，它在客戶端上運(yùn)行?？蛻舳藨?yīng)用程序與服務(wù)器端儲(chǔ)存過(guò)程分別運(yùn)行在兩個(gè)不同的進(jìn)程空間中，并且有不同的功能劃分。
客戶端應(yīng)用程序的主要功能是：
A:關(guān)數(shù)據(jù)結(jié)構(gòu)和主變量定義、分配并初始化存儲(chǔ)空間；
B:與數(shù)據(jù)庫(kù)進(jìn)行連接；
C:通過(guò)SQL CALL語(yǔ)句調(diào)用存儲(chǔ)過(guò)程
D:完成事務(wù)的提交（COMMIT）和回滾（ROLLBACK）（注：除非采用分布式工作單元，服務(wù)器端的存儲(chǔ)過(guò)程也可以完成事務(wù)的提交和回滾）；
E:執(zhí)行CONNECT RESET語(yǔ)句；
服務(wù)器端儲(chǔ)存過(guò)程的主要功能是：
A:接收客戶端應(yīng)用程序傳送的SQLCA和SQLDA等數(shù)據(jù)庫(kù)結(jié)構(gòu)；
B:作為與客戶端應(yīng)用程序相同的事務(wù)在數(shù)據(jù)庫(kù)服務(wù)器上運(yùn)行；
C:向客戶端應(yīng)用程序返回SQLCA和其它輸出數(shù)據(jù)；
3 客戶端應(yīng)用程序
客戶端應(yīng)用程序在使用CALL語(yǔ)句調(diào)用存儲(chǔ)過(guò)程之前必須先執(zhí)行幾個(gè)步驟。首先，必須連接數(shù)據(jù)庫(kù)，聲明、分配并初始化SQLDA結(jié)構(gòu)或宿主變量。
提示：不要在數(shù)據(jù)庫(kù)服務(wù)器上分配這些變量結(jié)構(gòu)。數(shù)據(jù)庫(kù)管理系統(tǒng)將根據(jù)客戶端應(yīng)用程序中的結(jié)構(gòu)在服務(wù)器端自動(dòng)分配所需的存儲(chǔ)。
客戶端應(yīng)用程序必須保證在調(diào)用存儲(chǔ)過(guò)程之前已建立了數(shù)據(jù)庫(kù)連接，否則，將會(huì)產(chǎn)生錯(cuò)誤。
3.1 CALL 語(yǔ)句
客戶端應(yīng)用程序可通過(guò)CALL語(yǔ)句向服務(wù)器發(fā)出存儲(chǔ)過(guò)程調(diào)用請(qǐng)求。CALL的語(yǔ)法格式如下所示：
CALL {過(guò)程名|主變量名}{（主變量1，…，主變量N）|USING DESCRIPTION 描述符名}
CALL語(yǔ)句的作用是將調(diào)用參數(shù)通過(guò)一個(gè)SQLDA數(shù)據(jù)結(jié)構(gòu)從客戶端傳送給服務(wù)器端的存儲(chǔ)過(guò)程，存儲(chǔ)過(guò)程執(zhí)行后再將輸出結(jié)果通過(guò)同一個(gè)SQLDA返回給客戶端應(yīng)用程序。
1.有關(guān)被調(diào)用存儲(chǔ)過(guò)程的名稱的規(guī)定
1)在客戶端應(yīng)用程序中，被調(diào)用的存儲(chǔ)過(guò)程的名稱既可以直接給出，也可以通過(guò)一個(gè)主變量給出。
2)如果被調(diào)用的存儲(chǔ)過(guò)程的名稱是直接給出的，則它必須是一個(gè)通常的標(biāo)識(shí)符（identifier）并且長(zhǎng)度不能超過(guò)254個(gè)字節(jié)。由于通常的標(biāo)識(shí)符中不能含有空格或其它特殊字符，所以，如果被調(diào)用的存儲(chǔ)過(guò)程的名稱中必須包含有空格或其它特殊字符時(shí)，則只能使用主變量來(lái)給出其名稱。
3)如果是通過(guò)主變量給出被調(diào)用的存儲(chǔ)過(guò)程的名稱，則使用的主變量必須是一個(gè)具有長(zhǎng)度屬性的字符串型的變量，并且其長(zhǎng)度不能超過(guò)254個(gè)字節(jié)。
4)被調(diào)用的存儲(chǔ)過(guò)程的名稱可以采用下列幾種格式：
proname：表示要裝入名為proname 的存儲(chǔ)過(guò)程函數(shù)庫(kù)并執(zhí)行其中名為proname的函數(shù)；
proname!funcname：表示要裝入名為proname 的存儲(chǔ)過(guò)程函數(shù)庫(kù)并執(zhí)行其中名為funcname的函數(shù)；
注：按照缺省方式，在基于UNIX的平臺(tái)上，數(shù)據(jù)庫(kù)管理系統(tǒng)將在INSTHOME/sqllib/function目錄中查找被隔離的（fenced）存儲(chǔ)過(guò)程函數(shù)庫(kù)。而在Intel平臺(tái)上，存儲(chǔ)過(guò)程函數(shù)庫(kù)的搜索位置則由操作系統(tǒng)環(huán)境變量LIBPATH說(shuō)明。對(duì)于不加隔離的（unfenced）存儲(chǔ)過(guò)程函數(shù)庫(kù)，其搜索位置分別是INSTHOME/sqllib/unfenced（基于UNIX的系統(tǒng)）或sqllib\dll\unfenced（基于Intel的系統(tǒng)）。
絕對(duì)路徑：如/home/user1/procname!funcname即表示要裝入/home/user1目錄下的名為proname 的存儲(chǔ)過(guò)程函數(shù)庫(kù)并執(zhí)行其中名為funcname的函數(shù)；
2.通過(guò)宿主變量傳遞過(guò)程調(diào)用參數(shù)
以下是一段通過(guò)宿主變量傳遞過(guò)程調(diào)用參數(shù)的程序例子：
EXEC SQL BEGIN DECLARE SECTION;
char host_var1[15]；
float host_var2;
short ind_var2;
long host_var3;
short ind_var3;
char procname[254] = “myproc”;
EXEC SQL END DECALRE SECTION;
strcopy(host_var1,”new data”);
host_var2 = 17.6;
ind_var2 = 0;
ind_var3 = -1;
EXEC SQL CALL :procname
(:host_var1,:host_var2 :ind_var2,:host_var3 :ind_var3);
如上面的程序段所示，在調(diào)用存儲(chǔ)過(guò)程時(shí)，可以通過(guò)宿主變量向存儲(chǔ)過(guò)程傳遞調(diào)用參數(shù)。存儲(chǔ)過(guò)程的調(diào)用參數(shù)可分為只輸入的（inputonly）、只輸出的（output only）和既輸入又輸出的（both input andoutput）。如果調(diào)用參數(shù)沒(méi)有明確被說(shuō)明為只輸入的（input only）或只輸出的（outputonly），則CALL語(yǔ)句將其缺省處理為既輸入又輸出的（both input andoutput）的調(diào)用參數(shù)。調(diào)用參數(shù)的輸入輸出類型由相應(yīng)的指示符變量確定。對(duì)于只輸入的（inputonly）的調(diào)用參數(shù)其相應(yīng)指示符變量的值應(yīng)為0（例如，上面程序段中的host_var2）；對(duì)于只輸出的（outputonly）的調(diào)用參數(shù)其相應(yīng)指示符變量的值應(yīng)為-1（ 例如，上面程序段中的host_var3）；
3.通過(guò)SQLDA傳遞過(guò)程調(diào)用參數(shù)
以下是一段通過(guò)SQLDA傳遞過(guò)程調(diào)用參數(shù)的程序例子：
struct sqlda *inout_sqlda = (struct sqlda *)malloc(SQLDASIZE(3));
long host_var3;
short ind_var3 = -1;
short ind_var2 = 0;
inout_sqlda->sqln = 3;
inout_sqlda->sqld = 3;
inout_sqlda->sqlvar[0].sqltype = SQL_TYPE_CSTR;
inout_sqlda->sqlvar[0].sqllen = 16;
inout_sqlda->sqlvar[0].sqldata = (char *)malloc(inout_sqlda->sqlvar[0].sqllen);
strcpy(inout_sqlda->sqlvar[0].sqldata,”new data”);
inout_sqlda->sqlvar[1].sqltype = SQL_TYPE_NFLOAT;
inout_sqlda->sqlvar[1].sqllen = sizeof(float);
inout_sqlda->sqlvar[1].sqldata = (char *)malloc(inout_sqlda->sqlvar[1].sqllen);
*(float *) inout_sqlda->sqlvar[1].sqldata = 17.6;
inout_sqlda->sqlvar[1].sqlind = &ind_var2;
inout_sqlda->sqlvar[2].sqltype = SQL_TYPE_NINTEGER;
inout_sqlda->sqlvar[2].sqllen = sizeof(long);
inout_sqlda->sqlvar[2].sqldata = (char *)&host_var3;
inout_sqlda->sqlvar[1].sqlind = &ind_var3;
EXEC SQL CALL myproc USING DESCRIPTOR :*inout_sqlda;
如上面的程序段所示，在調(diào)用存儲(chǔ)過(guò)程時(shí)，也可以通過(guò)SQLDA向存儲(chǔ)過(guò)程傳遞調(diào)用參數(shù)。
3.2 創(chuàng)建存儲(chǔ)過(guò)程
以下是通過(guò)CREATE PROCEDURE語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程的例子：
CREATE PROCEDURE MYPROC(INOUT HOST1 CHAR(15),
IN HOST2 DOUBLE, OUT HOST3 INTEGER)
EXTERNAL NAME ‘/home/user1/myfn!fn1’
LANGUAGE C
PARAMETER STYLE DB2DARI
1)CREATE PROCEDURE語(yǔ)句的作用是向數(shù)據(jù)庫(kù)服務(wù)器注冊(cè)一個(gè)新的存儲(chǔ)過(guò)程；
2)MYPROC為存儲(chǔ)過(guò)程的指定名。客戶端應(yīng)用程序可以在CALL語(yǔ)句使用這一名字調(diào)用相應(yīng)的存儲(chǔ)過(guò)程；
3)INOUTHOST1CHAR（15）表示存儲(chǔ)過(guò)程中所需的一個(gè)調(diào)用參數(shù)。其中，參數(shù)輸入輸出類型INOUT表明該參數(shù)既可向存儲(chǔ)過(guò)程提供輸入信息，也可接收從存儲(chǔ)過(guò)程返回的信息。參數(shù)輸入輸出類型IN表示相應(yīng)的參數(shù)只用于輸入(input only)；參數(shù)輸入輸出類型OUT表示相應(yīng)的參數(shù)只用于輸出(outputonly)；
4)在同一個(gè)模式下，不允許定義名稱、參數(shù)個(gè)數(shù)和數(shù)據(jù)類型都完全相同的存儲(chǔ)過(guò)程；
5)EXTERNAL NAME（外部名）表示用戶為實(shí)現(xiàn)所定義的存儲(chǔ)過(guò)程而編寫的程序代碼段的名稱；
6)LANGUAGE C 在CREATE PROCEDURE語(yǔ)句中是必不可少的，其作用是指明存儲(chǔ)過(guò)程體的語(yǔ)言接口約定。該子句的另一個(gè)選項(xiàng)是LANGUAGE JAVA；
7)PARAMETERSTYLE的作用是說(shuō)明向存儲(chǔ)過(guò)程傳遞參數(shù)以及從存儲(chǔ)過(guò)程接收返回結(jié)果的有關(guān)約定。DB2DARI表明存儲(chǔ)過(guò)程使用的參數(shù)傳遞約定將與C語(yǔ)言的函數(shù)調(diào)用和連接約定相兼容；PARAMETER STYLE DB2DARI必須與LANGUAGEC一起使用。DB2GENERAL表明存儲(chǔ)過(guò)程使用的參數(shù)傳遞約定將與JAVA語(yǔ)言的函數(shù)調(diào)用和連接約定相兼容；PARAMETER STYLEDB2GENERAL必須與LANGUAGE JAVA一起使用；
4 服務(wù)器端存儲(chǔ)過(guò)程的實(shí)現(xiàn)
4.1實(shí)現(xiàn)存儲(chǔ)過(guò)程的函數(shù)定義
在UDB中，存儲(chǔ)過(guò)程的實(shí)現(xiàn)代碼一般可應(yīng)用某種編程語(yǔ)言（如C、C++或JAVA等）來(lái)編寫。下面以C語(yǔ)言為例，說(shuō)明在編寫存儲(chǔ)過(guò)程的實(shí)現(xiàn)代碼時(shí)應(yīng)注意的事項(xiàng)。
SQL_API_RC SQL_API_FN
myproc(void *reserved1,
void *reserved2,
struct sqlda * inout_sqlda,
struct sqlca *ca)
{
/* no connecttion related statements */
/* runs in background */
/* no command to terminate current process *./
/* (no exit, _exit, or at exit) */
/* if DUOW no COMMIT or ROLLBACK */
return(ret_value);
}
1)上面程序段開(kāi)頭的SQL_API_RC和SQL_API_FN為2個(gè)預(yù)定義的宏，目的是程序的可移植性；
2)在實(shí)現(xiàn)存儲(chǔ)過(guò)程的函數(shù)體中不能出現(xiàn)與數(shù)據(jù)庫(kù)連接相關(guān)的語(yǔ)句，即不能出現(xiàn)CONNECT，CONNECT RESET，CREATEDATABASE，DROP DATABASE，ALTER DATABASE，BACKUP，RESTORE，ROLLFORWORD等語(yǔ)句；
3)由于存儲(chǔ)過(guò)程只在后臺(tái)運(yùn)行，所以不允許有寫屏幕（如，printf）的動(dòng)作，但允許寫文件（fprintf）；
4)存儲(chǔ)過(guò)程的函數(shù)體只是一個(gè)被數(shù)據(jù)庫(kù)管理系統(tǒng)調(diào)用的程序例程，所以當(dāng)執(zhí)行結(jié)束時(shí)總是應(yīng)當(dāng)將控制交還該調(diào)用它的函數(shù)，而不應(yīng)終止當(dāng)前進(jìn)程，即在其函數(shù)體中不應(yīng)出現(xiàn)exit（）,_exit（）這樣的函數(shù)調(diào)用；
5)如果調(diào)用存儲(chǔ)過(guò)程的客戶端應(yīng)用程序的數(shù)據(jù)庫(kù)連接類型（CONNECT TYPE）為DUOW，則存儲(chǔ)過(guò)程中不能發(fā)出終止事務(wù)的語(yǔ)句，即無(wú)論動(dòng)態(tài)或靜態(tài)的COMMIT、ROLLBACK都不允許出現(xiàn)。
4.2 存儲(chǔ)過(guò)程的參數(shù)傳遞
SQL_API_RC SQL_API_FN
myproc(void *reserved1,void *reserved2,
struct sqlda *inout_sqlda, struct sqlca *ca)
{
struct sqlca sqlca; /*for local use */
/* use input data in SQLDA */
/* do not change SQLD,SQLTYPE, or SQLLEN */
/* do not change pointer for SQLDATA or SQLIND */
/* return data in SQLDATA(and SQLIND) */
memcpy(ca,&sqlca,sizeof(struct sqlca));
…
1)存儲(chǔ)過(guò)程將通過(guò)SQLDA中的輸入變量得到客戶端應(yīng)用程序傳來(lái)的輸入?yún)?shù)，然后再通過(guò)SQLDA中的輸出變量將輸出結(jié)果返回給客戶端應(yīng)用程序。由于SQLDA各個(gè)域的原始值都是由客戶端應(yīng)用程序在過(guò)程調(diào)用前設(shè)置的，因此，存儲(chǔ)過(guò)程的函數(shù)體不應(yīng)改變SQLDA中的SQLD，SQLTYPE，SQLLEN等域的值。此外，雖然存儲(chǔ)過(guò)程的函數(shù)體可以改變SQLDATA及SQLIND中所含指針指向的變量的值，但卻不應(yīng)改變SQLDATA及SQLIND中所含的指針。
注：SQLDA中的變量可以同時(shí)既是輸入變量又是輸出變量。
2) 在存儲(chǔ)過(guò)程的函數(shù)體返回之前，應(yīng)當(dāng)顯式地將本地SQLCA中的信息拷貝到存儲(chǔ)過(guò)程的SQLCA參數(shù)之中。
4.3存儲(chǔ)過(guò)程的返回值
SQL_API_RC SQL_API_FN
myproc(void *reserved1,void *reserved2,
struct sqlda *inout_sqlda, struct sqlca *ca)
{
/* processing */
/* in this application,the second SQLVAR field
is used to determine if the client intends to
call the server procedure again, A value of 0
means no further calls. */
if((*float *)inout_sqlda->sqlvar[1].sqlda != 0)
return(SQLZ_HOLD_PROC)
else
return(SQL_DISCONNECT_PROC);
}
1） 應(yīng)當(dāng)特別注意的是，存儲(chǔ)過(guò)程的返回值根本不會(huì)返回給客戶端應(yīng)用程序。這里所說(shuō)的返回值的作用是使數(shù)據(jù)庫(kù)管理系統(tǒng)能夠確定當(dāng)存儲(chǔ)過(guò)程執(zhí)行終止時(shí)是否將存儲(chǔ)過(guò)程從內(nèi)存中釋放掉。
2） 存儲(chǔ)過(guò)程可以向數(shù)據(jù)庫(kù)管理系統(tǒng)返回下列2個(gè)值：
—SQL_DISCONNECT_PROC：其含義是告訴數(shù)據(jù)庫(kù)管理系統(tǒng)當(dāng)所有信息都傳遞給客戶端后，即可釋放（或卸載）存儲(chǔ)過(guò)程及其數(shù)據(jù)存儲(chǔ)；
—SQL_HOLD_PROC：其含義是告訴數(shù)據(jù)庫(kù)管理系統(tǒng)將存儲(chǔ)過(guò)程庫(kù)函數(shù)仍然保持在內(nèi)存當(dāng)中，這樣將可以保證當(dāng)客戶端下一次發(fā)出對(duì)該存儲(chǔ)過(guò)程的DARI調(diào)用時(shí)被調(diào)用的庫(kù)函數(shù)已經(jīng)在內(nèi)存當(dāng)中了，從而提高系統(tǒng)性能。
3） 如果客戶端只對(duì)存儲(chǔ)過(guò)程調(diào)用一次，則應(yīng)返回SQL_DISCONNECT_PROC；
4） 如果客戶端需要對(duì)存儲(chǔ)過(guò)程調(diào)用多次，則應(yīng)返回SQL_HOLD_PROC，從而保證存儲(chǔ)過(guò)程不會(huì)被卸載；
5） 如果存儲(chǔ)過(guò)程以SQL_HOLD_PROC作為返回值，則當(dāng)其被最后一次調(diào)用時(shí)則應(yīng)以SQL_DISCONNECT_PROC作為返回值，從而保證最后一次調(diào)用后將存儲(chǔ)過(guò)程從內(nèi)存中釋放掉。否則該存儲(chǔ)過(guò)程將被一直保存在主存中，直到數(shù)據(jù)庫(kù)管理系統(tǒng)停止運(yùn)行；
客戶端應(yīng)用程序在對(duì)存儲(chǔ)過(guò)程進(jìn)行最后一次調(diào)用時(shí)，應(yīng)將這一信息通知被調(diào)用的存儲(chǔ)過(guò)程。
