創(chuàng)建存儲(chǔ)過(guò)程

2024-07-21 02:12:00

字體：大中小

供稿：網(wǎng)友

在ms sql server 2000 中，創(chuàng)建一個(gè)存儲(chǔ)過(guò)程有兩種方法：一種是使用transaction-sql 命令create procedure，另一種是使用圖形化管理工具enterprise manager。用transaction- sql 創(chuàng)建存儲(chǔ)過(guò)程是一種較為快速的方法，但對(duì)于初學(xué)者，使用enterprise manager 更易理解，更為簡(jiǎn)單。
當(dāng)創(chuàng)建存儲(chǔ)過(guò)程時(shí)，需要確定存儲(chǔ)過(guò)程的三個(gè)組成部分；

所有的輸入?yún)?shù)以及傳給調(diào)用者的輸出參數(shù)。
被執(zhí)行的針對(duì)數(shù)據(jù)庫(kù)的操作語(yǔ)句，包括調(diào)用其它存儲(chǔ)過(guò)程的語(yǔ)句；
返回給調(diào)用者的狀態(tài)值，以指明調(diào)用是曬故鞘о堋?/li>

12.2.1 使用enterprise manager 創(chuàng)建存儲(chǔ)過(guò)程
按照下述步驟用enterprise manager 創(chuàng)建一個(gè)存儲(chǔ)過(guò)程：

啟動(dòng)enterprise manager，登錄到要使用的服務(wù)器。
選擇要?jiǎng)?chuàng)建存儲(chǔ)過(guò)程的數(shù)據(jù)庫(kù)，在左窗格中單擊stored procedure 文件夾，此時(shí)在右窗格中顯示該數(shù)據(jù)庫(kù)的所有存儲(chǔ)過(guò)程，如圖12-1 所示。
右擊stored procedure 文件夾，在彈出菜單中選擇new stored procedure, 此時(shí)打開(kāi)創(chuàng)建存儲(chǔ)過(guò)程對(duì)話框，如圖12-2 所示。
輸入存儲(chǔ)過(guò)程正文。
單擊check syntax，檢查語(yǔ)法是否正確。
單擊ok，保存。
在右窗格中，右擊該存儲(chǔ)過(guò)程，在彈出菜單中選擇all task, 選擇managepermissions，設(shè)置權(quán)限，如圖12-3 所示。

12.2.2 用create procedure 命令創(chuàng)建存儲(chǔ)過(guò)程
通過(guò)運(yùn)用create procedure 命令能夠創(chuàng)建存儲(chǔ)過(guò)程，在創(chuàng)建存儲(chǔ)過(guò)程之前，應(yīng)該考慮到以下幾個(gè)方面：

在一個(gè)批處理中，create procedure 語(yǔ)句不能與其它sql 語(yǔ)句合并在一起；
數(shù)據(jù)庫(kù)所有者具有默認(rèn)的創(chuàng)建存儲(chǔ)過(guò)程的權(quán)限，它可把該權(quán)限傳遞給其它的用戶；
存儲(chǔ)過(guò)程作為數(shù)據(jù)庫(kù)對(duì)象其命名必須符合命名規(guī)則；
只能在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建屬于當(dāng)前數(shù)據(jù)庫(kù)的存儲(chǔ)過(guò)程。

用create procedure 創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)法規(guī)則如下：
create proc [ edure ] procedure_name [ ; number ]
[ { @parameter data_type }
[ varying ] [ = default ] [ output ]
] [ ,...n ]
[ with
{ recompile | encryption | recompile , encryption } ]
[ for replication ]
as sql_statement [ ...n ]

各參數(shù)的含義如下：

procedure_name
是要?jiǎng)?chuàng)建的存儲(chǔ)過(guò)程的名字，它后面跟一個(gè)可選項(xiàng)number，它是一個(gè)整數(shù)，用來(lái)區(qū)別一組同名的存儲(chǔ)過(guò)程。存儲(chǔ)過(guò)程的命名必須符合命名規(guī)則，在一個(gè)數(shù)據(jù)庫(kù)中或?qū)ζ渌姓叨裕鎯?chǔ)過(guò)程的名字必須惟一。
@parameter
是存儲(chǔ)過(guò)程的參數(shù)。在create procedure 語(yǔ)句中，可以聲明一個(gè)或多個(gè)參數(shù)。當(dāng)調(diào)用該存儲(chǔ)過(guò)程時(shí)，用戶必須給出所有的參數(shù)值，除非定義了參數(shù)的缺省值。若參數(shù)的形式以 @parameter=value 出現(xiàn)，則參數(shù)的次序可以不同，否則用戶給出的參數(shù)值必須與參數(shù)列表中參數(shù)的順序保持一致。若某一參數(shù)以@parameter=value 形式給出，那么其它參數(shù)也必須以該形式給出。一個(gè)存儲(chǔ)過(guò)程至多有1024 個(gè)參數(shù)。
data_type
是參數(shù)的數(shù)據(jù)類型。在存儲(chǔ)過(guò)程中，所有的數(shù)據(jù)類型包括text 和image 都可被用作參數(shù)。但是，游標(biāo)cursor 數(shù)據(jù)類型只能被用作output 參數(shù)。當(dāng)定義游標(biāo)數(shù)據(jù)類型時(shí)，也必須對(duì)varing 和output 關(guān)鍵字進(jìn)行定義。對(duì)可能是游標(biāo)型數(shù)據(jù)類型的output 參數(shù)而言，參數(shù)的最大數(shù)目沒(méi)有限制。
varying
指定由output 參數(shù)支持的結(jié)果集，僅應(yīng)用于游標(biāo)型參數(shù)。
default
是指參數(shù)的缺省值。如果定義了缺省值，那么即使不給出參數(shù)值，則該存儲(chǔ)過(guò)程仍能被調(diào)用。缺省值必須是常數(shù)，或者是空值。
output
表明該參數(shù)是一個(gè)返回參數(shù)。用output 參數(shù)可以向調(diào)用者返回信息。text 類型參數(shù)不能用作output 參數(shù)。
recompile
指明sql server 并不保存該存儲(chǔ)過(guò)程的執(zhí)行計(jì)劃，該存儲(chǔ)過(guò)程每執(zhí)行一次都又要重新編譯。
encryption
表明sql server 加密了syscomments 表，該表的text 字段是包含有create procedure語(yǔ)句的存儲(chǔ)過(guò)程文本，使用該關(guān)鍵字無(wú)法通過(guò)查看syscomments 表來(lái)查看存儲(chǔ)過(guò)程內(nèi)容。
for replication
選項(xiàng)指明了為復(fù)制創(chuàng)建的存儲(chǔ)過(guò)程不能在訂購(gòu)服務(wù)器上執(zhí)行，只有在創(chuàng)建過(guò)濾存儲(chǔ)過(guò)程時(shí)（僅當(dāng)進(jìn)行數(shù)據(jù)復(fù)制時(shí)過(guò)濾存儲(chǔ)過(guò)程才被執(zhí)行），才使用該選項(xiàng)。for replication與with recompile 選項(xiàng)是互不兼容的。
as
指明該存儲(chǔ)過(guò)程將要執(zhí)行的動(dòng)作。
sql_statement
是任何數(shù)量和類型的包含在存儲(chǔ)過(guò)程中的sql 語(yǔ)句。

另外應(yīng)該指出，一個(gè)存儲(chǔ)過(guò)程的最大尺寸為128m，用戶定義的存儲(chǔ)過(guò)程必須創(chuàng)建在當(dāng)前數(shù)據(jù)庫(kù)中。
下面將給出幾個(gè)例子，用來(lái)詳細(xì)介紹如何創(chuàng)建包含有各種保留字的存儲(chǔ)過(guò)程。