存儲(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ò)程。