存儲(chǔ)過(guò)程
存儲(chǔ)過(guò)程是存儲(chǔ)在服務(wù)器上的一組預(yù)編譯的Transact-SQL語(yǔ)句，是一種封裝重復(fù)任務(wù)操作的方法，支持用戶提供的變量，具有強(qiáng)大的編程功能。它類(lèi)似于DOS系統(tǒng)中的BAT文件。在BAT文件中，可以包含一組經(jīng)常執(zhí)行的命令，這組命令通過(guò)BAT文件的執(zhí)行而被執(zhí)行。同樣的道理，可以把要完成某項(xiàng)任務(wù)的許多Transact-SQL語(yǔ)句寫(xiě)在一起，組織成存儲(chǔ)過(guò)程的形式，通過(guò)執(zhí)行該存儲(chǔ)過(guò)程就可以完成這項(xiàng)任務(wù)。存儲(chǔ)過(guò)程與BAT文件又有差別，即存儲(chǔ)過(guò)程已經(jīng)進(jìn)行了預(yù)編譯。
1、創(chuàng)建存儲(chǔ)過(guò)程的方法
在Transact-SQL語(yǔ)言中，創(chuàng)建存儲(chǔ)過(guò)程可以使用CREATE PROCEDURE語(yǔ)句，其語(yǔ)法形式如下：
CREATE PROC[EDURE] procedure_name[;number]
[{@parameter data_type}[VARYING][=default][OUTPUT]
]],…n]
[WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
[FOR REPLICATION]
AS sql_statement[…n]
在上面的CREATE PROCEDURE語(yǔ)句中，方括號(hào)"[ ]"中的內(nèi)容是可選的，花括號(hào)"{}"中的內(nèi)容是必須出現(xiàn)的，不能省略，[，…n]表示前面的參數(shù)樣式，可以重復(fù)出現(xiàn)。豎線"|"表示兩邊的選項(xiàng)可以任選一個(gè)。
下面分析該語(yǔ)句中各種選項(xiàng)的含義。
CREATE PROCEDURE是關(guān)鍵字，也可以寫(xiě)成CREATE PROC。
procedure_name是該存儲(chǔ)過(guò)程的名稱(chēng)，名稱(chēng)可以是任何符合命名規(guī)則的標(biāo)示符。名稱(chēng)后的[；number]參數(shù)表示可以定義一系列的存儲(chǔ)過(guò)程名稱(chēng)，這些存儲(chǔ)過(guò)程的數(shù)量由number指定。
參數(shù)名稱(chēng)可以使用@parameter data_type來(lái)指定。在Transact-SQL語(yǔ)言中，用戶定義的參數(shù)名稱(chēng)前面加"@"符號(hào)，這些數(shù)據(jù)類(lèi)型是Transact-SQL語(yǔ)言允許的各種數(shù)據(jù)類(lèi)型，包括系統(tǒng)提供的數(shù)據(jù)類(lèi)型和用戶定義的數(shù)據(jù)類(lèi)型。
當(dāng)參數(shù)類(lèi)型為cursor時(shí)，必須使用關(guān)鍵字VARYING和OUTPUT。VARYING表示結(jié)果集可以是一個(gè)輸出參數(shù)，其內(nèi)容是動(dòng)態(tài)的。該關(guān)鍵字只能在使用游標(biāo)作為數(shù)據(jù)類(lèi)型時(shí)使用。關(guān)鍵字OUTPUT表示這是一個(gè)輸出參數(shù)，可以把存儲(chǔ)過(guò)程執(zhí)行的結(jié)果信息返回應(yīng)用程序。
default用于指定參數(shù)的默認(rèn)值。
RECOMPILE選項(xiàng)表示重新編譯該存儲(chǔ)過(guò)程。該選項(xiàng)只是在需要的時(shí)候才使用，例如經(jīng)常需要改變數(shù)據(jù)庫(kù)模式時(shí)。
ENCRYPTION選項(xiàng)用來(lái)加密創(chuàng)建存儲(chǔ)過(guò)程的文本，防止他人查看。
選項(xiàng)FOR REPLICATION主要用于復(fù)制過(guò)程中。注意，該選項(xiàng)不能和選項(xiàng)RECOMPILE同時(shí)使用。
AS是一個(gè)關(guān)鍵字，表示其后的內(nèi)容是存儲(chǔ)過(guò)程的語(yǔ)句。參數(shù)sql-statement[…n]表示在一個(gè)存儲(chǔ)過(guò)程中可以包含多個(gè)Transact-SQL語(yǔ)句。
2、存儲(chǔ)過(guò)程的優(yōu)點(diǎn)
在頻繁訪問(wèn)數(shù)據(jù)庫(kù)的系統(tǒng)中，開(kāi)發(fā)者都樂(lè)于使用存儲(chǔ)過(guò)程，這與存儲(chǔ)過(guò)程的下列優(yōu)點(diǎn)是分不開(kāi)的。
⑴ 存儲(chǔ)過(guò)程可以與其他應(yīng)用程序共享應(yīng)用程序的邏輯，從而確保一致的數(shù)據(jù)訪問(wèn)和操縱。
⑵ 存儲(chǔ)過(guò)程提供了一種安全機(jī)制。如果用戶被授予執(zhí)行存儲(chǔ)過(guò)程權(quán)限，那么即使該用戶沒(méi)有訪問(wèn)在執(zhí)行該存儲(chǔ)過(guò)程中所參考的表或視圖的權(quán)限，該用戶也可以完全執(zhí)行該存儲(chǔ)過(guò)程而不受到影響。因此，可以創(chuàng)建存儲(chǔ)過(guò)程來(lái)完成所有的增加、刪除等操作，并且可以通過(guò)編程控制上述操作中對(duì)信息的訪問(wèn)權(quán)限。
⑶ 存儲(chǔ)過(guò)程執(zhí)行速度快，便于提高系統(tǒng)的性能。由于存儲(chǔ)過(guò)程在第一次執(zhí)行之后，其執(zhí)行規(guī)劃就駐存在過(guò)程高速緩沖存儲(chǔ)區(qū)中，在以后的操作中，只需從過(guò)程高速緩沖存儲(chǔ)區(qū)中調(diào)用編譯好的二進(jìn)制形式存儲(chǔ)過(guò)程來(lái)執(zhí)行。
⑷ 使用存儲(chǔ)過(guò)程可以減少網(wǎng)絡(luò)傳輸時(shí)間。如果有一千條Transact-SQL語(yǔ)句的命令，一條一條地通過(guò)網(wǎng)絡(luò)在客戶機(jī)和服務(wù)器之間傳送，那么這種傳輸所耗費(fèi)的時(shí)間將很長(zhǎng)。但是，如果把這一千條Transact-SQL語(yǔ)句的命令寫(xiě)成一條較為復(fù)雜的存儲(chǔ)過(guò)程命令，這時(shí)在客戶機(jī)和服務(wù)器之間網(wǎng)絡(luò)傳輸所需的時(shí)間就會(huì)大大減少。
SQL Server 2000數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程的調(diào)用
VB作為當(dāng)今應(yīng)用極為普遍的數(shù)據(jù)庫(kù)客戶端開(kāi)發(fā)工具之一，對(duì)客戶端應(yīng)用程序調(diào)用服務(wù)器端存儲(chǔ)過(guò)程提供了強(qiáng)大的支持。特別是隨著VB6.0的推出，VB客戶端應(yīng)用程序可以方便地利用ADO的對(duì)象和集合來(lái)實(shí)現(xiàn)對(duì)數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程的調(diào)用。
在筆者編寫(xiě)的科技檔案管理系統(tǒng)中，就是采用VB作為開(kāi)發(fā)平臺(tái)，采用SQL Server2000數(shù)據(jù)庫(kù)管理數(shù)據(jù)，在這個(gè)科技檔案管理系統(tǒng)中有海量的數(shù)據(jù)，并且對(duì)數(shù)據(jù)庫(kù)有頻繁的訪問(wèn)，利用存儲(chǔ)過(guò)程訪問(wèn)數(shù)據(jù)庫(kù)節(jié)省了執(zhí)行時(shí)間，大大提高了系統(tǒng)的性能。
存儲(chǔ)過(guò)程是存儲(chǔ)在服務(wù)器上的一組預(yù)編譯的Transact-SQL語(yǔ)句，是一種封裝重復(fù)任務(wù)操作的方法，支持用戶提供的變量，具有強(qiáng)大的編程功能。它類(lèi)似于DOS系統(tǒng)中的BAT文件。在BAT文件中，可以包含一組經(jīng)常執(zhí)行的命令，這組命令通過(guò)BAT文件的執(zhí)行而被執(zhí)行。同樣的道理，可以把要完成某項(xiàng)任務(wù)的許多Transact-SQL語(yǔ)句寫(xiě)在一起，組織成存儲(chǔ)過(guò)程的形式，通過(guò)執(zhí)行該存儲(chǔ)過(guò)程就可以完成這項(xiàng)任務(wù)。存儲(chǔ)過(guò)程與BAT文件又有差別，即存儲(chǔ)過(guò)程已經(jīng)進(jìn)行了預(yù)編譯。
1、創(chuàng)建存儲(chǔ)過(guò)程的方法
在Transact-SQL語(yǔ)言中，創(chuàng)建存儲(chǔ)過(guò)程可以使用CREATE PROCEDURE語(yǔ)句，其語(yǔ)法形式如下：
CREATE PROC[EDURE] procedure_name[;number]
[{@parameter data_type}[VARYING][=default][OUTPUT]
]],…n]
[WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
[FOR REPLICATION]
AS sql_statement[…n]
在上面的CREATE PROCEDURE語(yǔ)句中，方括號(hào)"[ ]"中的內(nèi)容是可選的，花括號(hào)"{}"中的內(nèi)容是必須出現(xiàn)的，不能省略，[，…n]表示前面的參數(shù)樣式，可以重復(fù)出現(xiàn)。豎線"|"表示兩邊的選項(xiàng)可以任選一個(gè)。
下面分析該語(yǔ)句中各種選項(xiàng)的含義。
CREATE PROCEDURE是關(guān)鍵字，也可以寫(xiě)成CREATE PROC。
procedure_name是該存儲(chǔ)過(guò)程的名稱(chēng)，名稱(chēng)可以是任何符合命名規(guī)則的標(biāo)示符。名稱(chēng)后的[；number]參數(shù)表示可以定義一系列的存儲(chǔ)過(guò)程名稱(chēng)，這些存儲(chǔ)過(guò)程的數(shù)量由number指定。
參數(shù)名稱(chēng)可以使用@parameter data_type來(lái)指定。在Transact-SQL語(yǔ)言中，用戶定義的參數(shù)名稱(chēng)前面加"@"符號(hào)，這些數(shù)據(jù)類(lèi)型是Transact-SQL語(yǔ)言允許的各種數(shù)據(jù)類(lèi)型，包括系統(tǒng)提供的數(shù)據(jù)類(lèi)型和用戶定義的數(shù)據(jù)類(lèi)型。
當(dāng)參數(shù)類(lèi)型為cursor時(shí)，必須使用關(guān)鍵字VARYING和OUTPUT。VARYING表示結(jié)果集可以是一個(gè)輸出參數(shù)，其內(nèi)容是動(dòng)態(tài)的。該關(guān)鍵字只能在使用游標(biāo)作為數(shù)據(jù)類(lèi)型時(shí)使用。關(guān)鍵字OUTPUT表示這是一個(gè)輸出參數(shù)，可以把存儲(chǔ)過(guò)程執(zhí)行的結(jié)果信息返回應(yīng)用程序。
default用于指定參數(shù)的默認(rèn)值。
RECOMPILE選項(xiàng)表示重新編譯該存儲(chǔ)過(guò)程。該選項(xiàng)只是在需要的時(shí)候才使用，例如經(jīng)常需要改變數(shù)據(jù)庫(kù)模式時(shí)。
ENCRYPTION選項(xiàng)用來(lái)加密創(chuàng)建存儲(chǔ)過(guò)程的文本，防止他人查看。
選項(xiàng)FOR REPLICATION主要用于復(fù)制過(guò)程中。注意，該選項(xiàng)不能和選項(xiàng)RECOMPILE同時(shí)使用。
AS是一個(gè)關(guān)鍵字，表示其后的內(nèi)容是存儲(chǔ)過(guò)程的語(yǔ)句。參數(shù)sql-statement[…n]表示在一個(gè)存儲(chǔ)過(guò)程中可以包含多個(gè)Transact-SQL語(yǔ)句。
2、存儲(chǔ)過(guò)程的優(yōu)點(diǎn)
在頻繁訪問(wèn)數(shù)據(jù)庫(kù)的系統(tǒng)中，開(kāi)發(fā)者都樂(lè)于使用存儲(chǔ)過(guò)程，這與存儲(chǔ)過(guò)程的下列優(yōu)點(diǎn)是分不開(kāi)的。
⑴ 存儲(chǔ)過(guò)程可以與其他應(yīng)用程序共享應(yīng)用程序的邏輯，從而確保一致的數(shù)據(jù)訪問(wèn)和操縱。
⑵ 存儲(chǔ)過(guò)程提供了一種安全機(jī)制。如果用戶被授予執(zhí)行存儲(chǔ)過(guò)程權(quán)限，那么即使該用戶沒(méi)有訪問(wèn)在執(zhí)行該存儲(chǔ)過(guò)程中所參考的表或視圖的權(quán)限，該用戶也可以完全執(zhí)行該存儲(chǔ)過(guò)程而不受到影響。因此，可以創(chuàng)建存儲(chǔ)過(guò)程來(lái)完成所有的增加、刪除等操作，并且可以通過(guò)編程控制上述操作中對(duì)信息的訪問(wèn)權(quán)限。
⑶ 存儲(chǔ)過(guò)程執(zhí)行速度快，便于提高系統(tǒng)的性能。由于存儲(chǔ)過(guò)程在第一次執(zhí)行之后，其執(zhí)行規(guī)劃就駐存在過(guò)程高速緩沖存儲(chǔ)區(qū)中，在以后的操作中，只需從過(guò)程高速緩沖存儲(chǔ)區(qū)中調(diào)用編譯好的二進(jìn)制形式存儲(chǔ)過(guò)程來(lái)執(zhí)行。
⑷ 使用存儲(chǔ)過(guò)程可以減少網(wǎng)絡(luò)傳輸時(shí)間。如果有一千條Transact-SQL語(yǔ)句的命令，一條一條地通過(guò)網(wǎng)絡(luò)在客戶機(jī)和服務(wù)器之間傳送，那么這種傳輸所耗費(fèi)的時(shí)間將很長(zhǎng)。但是，如果把這一千條Transact-SQL語(yǔ)句的命令寫(xiě)成一條較為復(fù)雜的存儲(chǔ)過(guò)程命令，這時(shí)在客戶機(jī)和服務(wù)器之間網(wǎng)絡(luò)傳輸所需的時(shí)間就會(huì)大大減少。
SQL Server 2000數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程的調(diào)用
VB作為當(dāng)今應(yīng)用極為普遍的數(shù)據(jù)庫(kù)客戶端開(kāi)發(fā)工具之一，對(duì)客戶端應(yīng)用程序調(diào)用服務(wù)器端存儲(chǔ)過(guò)程提供了強(qiáng)大的支持。特別是隨著VB6.0的推出，VB客戶端應(yīng)用程序可以方便地利用ADO的對(duì)象和集合來(lái)實(shí)現(xiàn)對(duì)數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程的調(diào)用。
在筆者編寫(xiě)的科技檔案管理系統(tǒng)中，就是采用VB作為開(kāi)發(fā)平臺(tái)，采用SQL Server2000數(shù)據(jù)庫(kù)管理數(shù)據(jù)，在這個(gè)科技檔案管理系統(tǒng)中有海量的數(shù)據(jù)，并且對(duì)數(shù)據(jù)庫(kù)有頻繁的訪問(wèn)，利用存儲(chǔ)過(guò)程訪問(wèn)數(shù)據(jù)庫(kù)節(jié)省了執(zhí)行時(shí)間，大大提高了系統(tǒng)的性能。