儲(chǔ)存過(guò)程的基礎(chǔ)的創(chuàng)建和簡(jiǎn)單應(yīng)用

1、儲(chǔ)存過(guò)程的基礎(chǔ)的創(chuàng)建和簡(jiǎn)單應(yīng)用CREATE PROCEDURE創(chuàng)建存儲(chǔ)過(guò)程，存儲(chǔ)過(guò)程是保存起來(lái)的可以接受和返回用戶提供的參數(shù)的 Transact-SQL 語(yǔ)句的集合。可以創(chuàng)建一個(gè)過(guò)程供永久使用，或在一個(gè)會(huì)話中臨時(shí)使用（局部臨時(shí)過(guò)程），或在所有會(huì)話中臨時(shí)使用（全局臨時(shí)過(guò)程）。也可以創(chuàng)建在 Microsoft® SQL Server 啟動(dòng)時(shí)自動(dòng)運(yùn)行的存儲(chǔ)過(guò)程。語(yǔ)法CREATE PROC EDURE procedure_name ; number parameter data_type VARYING = default OUTPUT ,.n WITH RECOMPILE | ENCRYP

2、TION | RECOMPILE , ENCRYPTION FOR REPLICATION AS sql_statement .n 參數(shù)procedure_name新存儲(chǔ)過(guò)程的名稱。過(guò)程名必須符合標(biāo)識(shí)符規(guī)則，且對(duì)于數(shù)據(jù)庫(kù)及其所有者必須唯一。有關(guān)更多信息，請(qǐng)參見(jiàn)使用標(biāo)識(shí)符。要?jiǎng)?chuàng)建局部臨時(shí)過(guò)程，可以在 procedure_name 前面加一個(gè)編號(hào)符 (#procedure_name)，要?jiǎng)?chuàng)建全局臨時(shí)過(guò)程，可以在 procedure_name 前面加兩個(gè)編號(hào)符 (#procedure_name)。完整的名稱（包括 # 或 #）不能超過(guò) 128 個(gè)字符。指定過(guò)程所有者的名稱是可選的。;number是可

3、選的整數(shù)，用來(lái)對(duì)同名的過(guò)程分組，以便用一條 DROP PROCEDURE 語(yǔ)句即可將同組的過(guò)程一起除去。例如，名為 orders 的應(yīng)用程序使用的過(guò)程可以命名為 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 語(yǔ)句將除去整個(gè)組。如果名稱中包含定界標(biāo)識(shí)符，則數(shù)字不應(yīng)包含在標(biāo)識(shí)符中，只應(yīng)在 procedure_name 前后使用適當(dāng)?shù)亩ń绶?。parameter過(guò)程中的參數(shù)。在 CREATE PROCEDURE 語(yǔ)句中可以聲明一個(gè)或多個(gè)參數(shù)。用戶必須在執(zhí)行過(guò)程時(shí)提供每個(gè)所聲明參數(shù)的值（除非定義了該參數(shù)的默認(rèn)值）。存儲(chǔ)過(guò)程最多可以有 2.100 個(gè)

4、參數(shù)。使用 符號(hào)作為第一個(gè)字符來(lái)指定參數(shù)名稱。參數(shù)名稱必須符合標(biāo)識(shí)符的規(guī)則。每個(gè)過(guò)程的參數(shù)僅用于該過(guò)程本身；相同的參數(shù)名稱可以用在其它過(guò)程中。默認(rèn)情況下，參數(shù)只能代替常量，而不能用于代替表名、列名或其它數(shù)據(jù)庫(kù)對(duì)象的名稱。有關(guān)更多信息，請(qǐng)參見(jiàn) EXECUTE。 data_type參數(shù)的數(shù)據(jù)類型。所有數(shù)據(jù)類型（包括 text、ntext 和 image）均可以用作存儲(chǔ)過(guò)程的參數(shù)。不過(guò)，cursor 數(shù)據(jù)類型只能用于 OUTPUT 參數(shù)。如果指定的數(shù)據(jù)類型為 cursor，也必須同時(shí)指定 VARYING 和 OUTPUT 關(guān)鍵字。有關(guān) SQL Server 提供的數(shù)據(jù)類型及其語(yǔ)法的更多信息，請(qǐng)參見(jiàn)數(shù)

5、據(jù)類型。 說(shuō)明 對(duì)于可以是 cursor 數(shù)據(jù)類型的輸出參數(shù)，沒(méi)有最大數(shù)目的限制。VARYING指定作為輸出參數(shù)支持的結(jié)果集（由存儲(chǔ)過(guò)程動(dòng)態(tài)構(gòu)造，內(nèi)容可以變化）。僅適用于游標(biāo)參數(shù)。default參數(shù)的默認(rèn)值。如果定義了默認(rèn)值，不必指定該參數(shù)的值即可執(zhí)行過(guò)程。默認(rèn)值必須是常量或 NULL。如果過(guò)程將對(duì)該參數(shù)使用 LIKE 關(guān)鍵字，那么默認(rèn)值中可以包含通配符（*、_、 和 ）。OUTPUT表明參數(shù)是返回參數(shù)。該選項(xiàng)的值可以返回給 EXECUTE。使用 OUTPUT 參數(shù)可將信息返回給調(diào)用過(guò)程。Text、ntext 和 image 參數(shù)可用作 OUTPUT 參數(shù)。使用 OUTPUT 關(guān)鍵字的輸出參數(shù)

6、可以是游標(biāo)占位符。n表示最多可以指定 2.100 個(gè)參數(shù)的占位符。RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTIONRECOMPILE 表明 SQL Server 不會(huì)緩存該過(guò)程的計(jì)劃，該過(guò)程將在運(yùn)行時(shí)重新編譯。在使用非典型值或臨時(shí)值而不希望覆蓋緩存在內(nèi)存中的執(zhí)行計(jì)劃時(shí)，請(qǐng)使用 RECOMPILE 選項(xiàng)。ENCRYPTION 表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 語(yǔ)句文本的條目。使用 ENCRYPTION 可防止將過(guò)程作為 SQL Server 復(fù)制的一部分發(fā)布。說(shuō)明 在升級(jí)過(guò)程中，SQL

7、 Server 利用存儲(chǔ)在 syscomments 中的加密注釋來(lái)重新創(chuàng)建加密過(guò)程。FOR REPLICATION指定不能在訂閱服務(wù)器上執(zhí)行為復(fù)制創(chuàng)建的存儲(chǔ)過(guò)程。.使用 FOR REPLICATION 選項(xiàng)創(chuàng)建的存儲(chǔ)過(guò)程可用作存儲(chǔ)過(guò)程篩選，且只能在復(fù)制過(guò)程中執(zhí)行。本選項(xiàng)不能和 WITH RECOMPILE 選項(xiàng)一起使用。AS指定過(guò)程要執(zhí)行的操作。sql_statement過(guò)程中要包含的任意數(shù)目和類型的 Transact-SQL 語(yǔ)句。但有一些限制。n是表示此過(guò)程可以包含多條 Transact-SQL 語(yǔ)句的占位符。注釋存儲(chǔ)過(guò)程的最大大小為 128 MB。用戶定義的存儲(chǔ)過(guò)程只能在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建

8、（臨時(shí)過(guò)程除外，臨時(shí)過(guò)程總是在 tempdb 中創(chuàng)建）。在單個(gè)批處理中，CREATE PROCEDURE 語(yǔ)句不能與其它 Transact-SQL 語(yǔ)句組合使用。 默認(rèn)情況下，參數(shù)可為空。如果傳遞 NULL 參數(shù)值并且該參數(shù)在 CREATE 或 ALTER TABLE 語(yǔ)句中使用，而該語(yǔ)句中引用的列又不允許使用 NULL，則 SQL Server 會(huì)產(chǎn)生一條錯(cuò)誤信息。為了防止向不允許使用 NULL 的列傳遞 NULL 參數(shù)值，應(yīng)向過(guò)程中添加編程邏輯或?yàn)樵摿惺褂媚J(rèn)值（使用 CREATE 或 ALTER TABLE 的 DEFAULT 關(guān)鍵字)。建議在存儲(chǔ)過(guò)程的任何 CREATE TABLE 或

9、 ALTER TABLE 語(yǔ)句中都為每列顯式指定 NULL 或 NOT NULL，例如在創(chuàng)建臨時(shí)表時(shí)。ANSI_DFLT_ON 和 ANSI_DFLT_OFF 選項(xiàng)控制 SQL Server 為列指派 NULL 或 NOT NULL 特性的方式（如果在 CREATE TABLE 或 ALTER TABLE 語(yǔ)句中沒(méi)有指定的話）。如果某個(gè)連接執(zhí)行的存儲(chǔ)過(guò)程對(duì)這些選項(xiàng)的設(shè)置與創(chuàng)建該過(guò)程的連接的設(shè)置不同，則為第二個(gè)連接創(chuàng)建的表列可能會(huì)有不同的為空性，并且表現(xiàn)出不同的行為方式。如果為每個(gè)列顯式聲明了 NULL 或 NOT NULL，那么將對(duì)所有執(zhí)行該存儲(chǔ)過(guò)程的連接使用相同的為空性創(chuàng)建臨時(shí)表。在創(chuàng)建或更

10、改存儲(chǔ)過(guò)程時(shí)，SQL Server 將保存 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 的設(shè)置。執(zhí)行存儲(chǔ)過(guò)程時(shí)，將使用這些原始設(shè)置。因此，所有客戶端會(huì)話的 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 設(shè)置在執(zhí)行存儲(chǔ)過(guò)程時(shí)都將被忽略。在存儲(chǔ)過(guò)程中出現(xiàn)的 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 語(yǔ)句不影響存儲(chǔ)過(guò)程的功能。其它 SET 選項(xiàng)（例如 SET ARITHABORT、SET ANSI_WARNINGS 或 SET ANSI_PADDINGS）在創(chuàng)建或更改存儲(chǔ)過(guò)程時(shí)不保存。如果存儲(chǔ)過(guò)程的

11、邏輯取決于特定的設(shè)置，應(yīng)在過(guò)程開(kāi)頭添加一條 SET 語(yǔ)句，以確保設(shè)置正確。從存儲(chǔ)過(guò)程中執(zhí)行 SET 語(yǔ)句時(shí)，該設(shè)置只在存儲(chǔ)過(guò)程完成之前有效。之后，設(shè)置將恢復(fù)為調(diào)用存儲(chǔ)過(guò)程時(shí)的值。這使個(gè)別的客戶端可以設(shè)置所需的選項(xiàng)，而不會(huì)影響存儲(chǔ)過(guò)程的邏輯。說(shuō)明 SQL Server 是將空字符串解釋為單個(gè)空格還是解釋為真正的空字符串，由兼容級(jí)別設(shè)置控制。如果兼容級(jí)別小于或等于 65，SQL Server 就將空字符串解釋為單個(gè)空格。如果兼容級(jí)別等于 70，則 SQL Server 將空字符串解釋為空字符串。有關(guān)更多信息，請(qǐng)參見(jiàn) sp_dbcmptlevel。 獲得有關(guān)存儲(chǔ)過(guò)程的信息若要顯示用來(lái)創(chuàng)建過(guò)程的文本，

12、請(qǐng)?jiān)谶^(guò)程所在的數(shù)據(jù)庫(kù)中執(zhí)行 sp_helptext，并使用過(guò)程名作為參數(shù)。 說(shuō)明 使用 ENCRYPTION 選項(xiàng)創(chuàng)建的存儲(chǔ)過(guò)程不能使用 sp_helptext 查看。若要顯示有關(guān)過(guò)程引用的對(duì)象的報(bào)表，請(qǐng)使用 sp_depends。 若要為過(guò)程重命名，請(qǐng)使用 sp_rename。 引用對(duì)象SQL Server 允許創(chuàng)建的存儲(chǔ)過(guò)程引用尚不存在的對(duì)象。在創(chuàng)建時(shí)，只進(jìn)行語(yǔ)法檢查。執(zhí)行時(shí)，如果高速緩存中尚無(wú)有效的計(jì)劃，則編譯存儲(chǔ)過(guò)程以生成執(zhí)行計(jì)劃。只有在編譯過(guò)程中才解析存儲(chǔ)過(guò)程中引用的所有對(duì)象。因此，如果語(yǔ)法正確的存儲(chǔ)過(guò)程引用了不存在的對(duì)象，則仍可以成功創(chuàng)建，但在運(yùn)行時(shí)將失敗，因?yàn)樗玫膶?duì)象不存在。

13、有關(guān)更多信息，請(qǐng)參見(jiàn)延遲名稱解析和編譯。 延遲名稱解析和兼容級(jí)別SQL Server 允許 Transact-SQL 存儲(chǔ)過(guò)程在創(chuàng)建時(shí)引用不存在的表。這種能力稱為延遲名稱解析。不過(guò)，如果 Transact-SQL 存儲(chǔ)過(guò)程引用了該存儲(chǔ)過(guò)程中定義的表，而兼容級(jí)別設(shè)置（通過(guò)執(zhí)行 sp_dbcmptlevel 來(lái)設(shè)置）為 65，則在創(chuàng)建時(shí)會(huì)發(fā)出警告信息。而如果在運(yùn)行時(shí)所引用的表不存在，將返回錯(cuò)誤信息。有關(guān)更多信息，請(qǐng)參見(jiàn) sp_dbcmptlevel 和延遲名稱解析和編譯。 執(zhí)行存儲(chǔ)過(guò)程成功執(zhí)行 CREATE PROCEDURE 語(yǔ)句后，過(guò)程名稱將存儲(chǔ)在 sysobjects 系統(tǒng)表中，而 CREA

14、TE PROCEDURE 語(yǔ)句的文本將存儲(chǔ)在 syscomments 中。第一次執(zhí)行時(shí)，將編譯該過(guò)程以確定檢索數(shù)據(jù)的最佳訪問(wèn)計(jì)劃。使用 cursor 數(shù)據(jù)類型的參數(shù)存儲(chǔ)過(guò)程只能將 cursor 數(shù)據(jù)類型用于 OUTPUT 參數(shù)。如果為某個(gè)參數(shù)指定了 cursor 數(shù)據(jù)類型，也必須指定 VARYING 和 OUTPUT 參數(shù)。如果為某個(gè)參數(shù)指定了 VARYING 關(guān)鍵字，則數(shù)據(jù)類型必須是 cursor，并且必須指定 OUTPUT 關(guān)鍵字。說(shuō)明 cursor 數(shù)據(jù)類型不能通過(guò)數(shù)據(jù)庫(kù) API（例如 OLE DB、ODBC、ADO 和 DB-Library）綁定到應(yīng)用程序變量上。因?yàn)楸仨毾冉壎?OUT

15、PUT 參數(shù)，應(yīng)用程序才可以執(zhí)行存儲(chǔ)過(guò)程，所以帶有 cursor OUTPUT 參數(shù)的存儲(chǔ)過(guò)程不能通過(guò)數(shù)據(jù)庫(kù) API 調(diào)用。只有將 cursor OUTPUT 變量賦值給 Transact-SQL 局部 cursor 變量時(shí)，才可以通過(guò) Transact-SQL 批處理、存儲(chǔ)過(guò)程或觸發(fā)器調(diào)用這些過(guò)程。Cursor 輸出參數(shù)在執(zhí)行過(guò)程時(shí)，以下規(guī)則適用于 cursor 輸出參數(shù)： 對(duì)于只進(jìn)游標(biāo)，游標(biāo)的結(jié)果集中返回的行只是那些存儲(chǔ)過(guò)程執(zhí)行結(jié)束時(shí)處于或超出游標(biāo)位置的行，例如： 在過(guò)程中的名為 RS 的 100 行結(jié)果集上打開(kāi)一個(gè)非滾動(dòng)游標(biāo)。 過(guò)程提取結(jié)果集 RS 的頭 5 行。過(guò)程返回到其調(diào)用者。返回

16、到調(diào)用者的結(jié)果集 RS 由 RS 的第 6 到 100 行組成，調(diào)用者中的游標(biāo)處于 RS 的第一行之前。 對(duì)于只進(jìn)游標(biāo)，如果存儲(chǔ)過(guò)程完成后，游標(biāo)位于第一行的前面，則整個(gè)結(jié)果集將返回給調(diào)用批處理、存儲(chǔ)過(guò)程或觸發(fā)器。返回時(shí)，游標(biāo)將位于第一行的前面。對(duì)于只進(jìn)游標(biāo)，如果存儲(chǔ)過(guò)程完成后，游標(biāo)的位置超出最后一行的結(jié)尾，則為調(diào)用批處理、存儲(chǔ)過(guò)程或觸發(fā)器返回空結(jié)果集。 說(shuō)明 空結(jié)果集與空值不同。對(duì)于可滾動(dòng)游標(biāo)，在存儲(chǔ)過(guò)程執(zhí)行結(jié)束時(shí)，結(jié)果集中的所有行均會(huì)返回給調(diào)用批處理、存儲(chǔ)過(guò)程或觸發(fā)器。返回時(shí)，游標(biāo)保留在過(guò)程中最后一次執(zhí)行提取時(shí)的位置。對(duì)于任意類型的游標(biāo)，如果游標(biāo)關(guān)閉，則將空值傳遞回調(diào)用批處理、存儲(chǔ)過(guò)程或觸發(fā)

17、器。如果將游標(biāo)指派給一個(gè)參數(shù)，但該游標(biāo)從未打開(kāi)過(guò)，也會(huì)出現(xiàn)這種情況。 說(shuō)明 關(guān)閉狀態(tài)只有在返回時(shí)才有影響。例如，可以在過(guò)程中關(guān)閉游標(biāo)，稍后再打開(kāi)游標(biāo)，然后將該游標(biāo)的結(jié)果集返回給調(diào)用批處理、存儲(chǔ)過(guò)程或觸發(fā)器。臨時(shí)存儲(chǔ)過(guò)程SQL Server 支持兩種臨時(shí)過(guò)程：局部臨時(shí)過(guò)程和全局臨時(shí)過(guò)程。局部臨時(shí)過(guò)程只能由創(chuàng)建該過(guò)程的連接使用。全局臨時(shí)過(guò)程則可由所有連接使用。局部臨時(shí)過(guò)程在當(dāng)前會(huì)話結(jié)束時(shí)自動(dòng)除去。全局臨時(shí)過(guò)程在使用該過(guò)程的最后一個(gè)會(huì)話結(jié)束時(shí)除去。通常是在創(chuàng)建該過(guò)程的會(huì)話結(jié)束時(shí)。臨時(shí)過(guò)程用 # 和 # 命名，可以由任何用戶創(chuàng)建。創(chuàng)建過(guò)程后，局部過(guò)程的所有者是唯一可以使用該過(guò)程的用戶。執(zhí)行局部臨時(shí)過(guò)程

18、的權(quán)限不能授予其他用戶。如果創(chuàng)建了全局臨時(shí)過(guò)程，則所有用戶均可以訪問(wèn)該過(guò)程，權(quán)限不能顯式廢除。只有在 tempdb 數(shù)據(jù)庫(kù)中具有顯式 CREATE PROCEDURE 權(quán)限的用戶，才可以在該數(shù)據(jù)庫(kù)中顯式創(chuàng)建臨時(shí)過(guò)程（不使用編號(hào)符命名）?？梢允谟杌驈U除這些過(guò)程中的權(quán)限。 說(shuō)明 頻繁使用臨時(shí)存儲(chǔ)過(guò)程會(huì)在 tempdb 中的系統(tǒng)表上產(chǎn)生爭(zhēng)用，從而對(duì)性能產(chǎn)生負(fù)面影響。建議使用 sp_executesql 代替。sp_executesql 不在系統(tǒng)表中存儲(chǔ)數(shù)據(jù)，因此可以避免這一問(wèn)題。自動(dòng)執(zhí)行存儲(chǔ)過(guò)程SQL Server 啟動(dòng)時(shí)可以自動(dòng)執(zhí)行一個(gè)或多個(gè)存儲(chǔ)過(guò)程。這些存儲(chǔ)過(guò)程必須由系統(tǒng)管理員創(chuàng)建，并在 sys

19、admin 固定服務(wù)器角色下作為后臺(tái)過(guò)程執(zhí)行。這些過(guò)程不能有任何輸入?yún)?shù)。 對(duì)啟動(dòng)過(guò)程的數(shù)目沒(méi)有限制，但是要注意，每個(gè)啟動(dòng)過(guò)程在執(zhí)行時(shí)都會(huì)占用一個(gè)連接。如果必須在啟動(dòng)時(shí)執(zhí)行多個(gè)過(guò)程，但不需要并行執(zhí)行，則可以指定一個(gè)過(guò)程作為啟動(dòng)過(guò)程，讓該過(guò)程調(diào)用其它過(guò)程。這樣就只占用一個(gè)連接。在啟動(dòng)時(shí)恢復(fù)了最后一個(gè)數(shù)據(jù)庫(kù)后，即開(kāi)始執(zhí)行存儲(chǔ)過(guò)程。若要跳過(guò)這些存儲(chǔ)過(guò)程的執(zhí)行，請(qǐng)將啟動(dòng)參數(shù)指定為跟蹤標(biāo)記 4022。如果以最低配置啟動(dòng) SQL Server（使用 -f 標(biāo)記），則啟動(dòng)存儲(chǔ)過(guò)程也不會(huì)執(zhí)行。有關(guān)更多信息，請(qǐng)參見(jiàn)跟蹤標(biāo)記。 若要?jiǎng)?chuàng)建啟動(dòng)存儲(chǔ)過(guò)程，必須作為 sysadmin 固定服務(wù)器角色的成員登錄，并在 ma

20、ster 數(shù)據(jù)庫(kù)中創(chuàng)建存儲(chǔ)過(guò)程。使用 sp_procoption 可以： 將現(xiàn)有存儲(chǔ)過(guò)程指定為啟動(dòng)過(guò)程。停止在 SQL Server 啟動(dòng)時(shí)執(zhí)行過(guò)程。查看 SQL Server 啟動(dòng)時(shí)執(zhí)行的所有過(guò)程的列表。 存儲(chǔ)過(guò)程嵌套存儲(chǔ)過(guò)程可以嵌套，即一個(gè)存儲(chǔ)過(guò)程可以調(diào)用另一個(gè)存儲(chǔ)過(guò)程。在被調(diào)用過(guò)程開(kāi)始執(zhí)行時(shí)，嵌套級(jí)將增加，在被調(diào)用過(guò)程執(zhí)行結(jié)束后，嵌套級(jí)將減少。如果超出最大的嵌套級(jí)，會(huì)使整個(gè)調(diào)用過(guò)程鏈?zhǔn)?。可?NESTLEVEL 函數(shù)返回當(dāng)前的嵌套級(jí)。若要估計(jì)編譯后的存儲(chǔ)過(guò)程大小，請(qǐng)使用下列性能監(jiān)視計(jì)數(shù)器。 性能監(jiān)視器對(duì)象名 性能監(jiān)視計(jì)數(shù)器名稱 SQLServer：緩沖區(qū)管理器 高速緩存大?。?yè)面數(shù)）

21、SQLServer：高速緩存管理器 高速緩存命中率 高速緩存頁(yè) 高速緩存對(duì)象計(jì)數(shù)* * 各種分類的高速緩存對(duì)象均可以使用這些計(jì)數(shù)器，包括特殊 sql、準(zhǔn)備 sql、過(guò)程、觸發(fā)器等。有關(guān)更多信息，請(qǐng)參見(jiàn) SQL Server:Buffer Manager 對(duì)象和 SQL Server:Cache Manager 對(duì)象。 sql_statement 限制除了 SET SHOWPLAN_TEXT 和 SET SHOWPLAN_ALL 之外（這兩個(gè)語(yǔ)句必須是批處理中僅有的語(yǔ)句），任何 SET 語(yǔ)句均可以在存儲(chǔ)過(guò)程內(nèi)部指定。所選擇的 SET 選項(xiàng)在存儲(chǔ)過(guò)程執(zhí)行過(guò)程中有效，之后恢復(fù)為原來(lái)的設(shè)置。 如果其他

22、用戶要使用某個(gè)存儲(chǔ)過(guò)程，那么在該存儲(chǔ)過(guò)程內(nèi)部，一些語(yǔ)句使用的對(duì)象名必須使用對(duì)象所有者的名稱限定。這些語(yǔ)句包括： ALTER TABLECREATE INDEXCREATE TABLE所有 DBCC 語(yǔ)句DROP TABLEDROP INDEXTRUNCATE TABLEUPDATE STATISTICS 權(quán)限CREATE PROCEDURE 的權(quán)限默認(rèn)授予 sysadmin 固定服務(wù)器角色成員和 db_owner 和 db_ddladmin 固定數(shù)據(jù)庫(kù)角色成員。sysadmin 固定服務(wù)器角色成員和 db_owner 固定數(shù)據(jù)庫(kù)角色成員可以將 CREATE PROCEDURE 權(quán)限轉(zhuǎn)讓給其他用

23、戶。執(zhí)行存儲(chǔ)過(guò)程的權(quán)限授予過(guò)程的所有者，該所有者可以為其它數(shù)據(jù)庫(kù)用戶設(shè)置執(zhí)行權(quán)限。示例A. 使用帶有復(fù)雜 SELECT 語(yǔ)句的簡(jiǎn)單過(guò)程下面的存儲(chǔ)過(guò)程從四個(gè)表的聯(lián)接中返回所有作者（提供了姓名）、出版的書(shū)籍以及出版社。該存儲(chǔ)過(guò)程不使用任何參數(shù)。USE pubsIF EXISTS (SELECT name FROM sysobjects WHERE name = 'au_info_all' AND type = 'P') DROP PROCEDURE au_info_allGOCREATE PROCEDURE au_info_allASSELECT au_lname,

24、 au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_idGOau_info_all 存儲(chǔ)過(guò)程可以通過(guò)以下方法執(zhí)行：EXECUTE au_info_all- OrEXEC au_info_all如果該過(guò)程是批處理中的第一條語(yǔ)句，則可使用：au_info_allB. 使用帶有參數(shù)的簡(jiǎn)單過(guò)程下

25、面的存儲(chǔ)過(guò)程從四個(gè)表的聯(lián)接中只返回指定的作者（提供了姓名）、出版的書(shū)籍以及出版社。該存儲(chǔ)過(guò)程接受與傳遞的參數(shù)精確匹配的值。USE pubsIF EXISTS (SELECT name FROM sysobjects WHERE name = 'au_info' AND type = 'P') DROP PROCEDURE au_infoGOUSE pubsGOCREATE PROCEDURE au_info lastname varchar(40), firstname varchar(20) AS SELECT au_lname, au_fname, titl

26、e, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id WHERE au_fname = firstname AND au_lname = lastnameGOau_info 存儲(chǔ)過(guò)程可以通過(guò)以下方法執(zhí)行：EXECUTE au_info 'Dull', 'Ann'- OrEXECUT

27、E au_info lastname = 'Dull', firstname = 'Ann'- OrEXECUTE au_info firstname = 'Ann', lastname = 'Dull'- OrEXEC au_info 'Dull', 'Ann'- OrEXEC au_info lastname = 'Dull', firstname = 'Ann'- OrEXEC au_info firstname = 'Ann', lastnam

28、e = 'Dull'如果該過(guò)程是批處理中的第一條語(yǔ)句，則可使用：au_info 'Dull', 'Ann'- Orau_info lastname = 'Dull', firstname = 'Ann'- Orau_info firstname = 'Ann', lastname = 'Dull'C. 使用帶有通配符參數(shù)的簡(jiǎn)單過(guò)程下面的存儲(chǔ)過(guò)程從四個(gè)表的聯(lián)接中只返回指定的作者（提供了姓名）、出版的書(shū)籍以及出版社。該存儲(chǔ)過(guò)程對(duì)傳遞的參數(shù)進(jìn)行模式匹配，如果沒(méi)有提供參數(shù)，則使用預(yù)設(shè)的默認(rèn)值

29、。USE pubsIF EXISTS (SELECT name FROM sysobjects WHERE name = 'au_info2' AND type = 'P') DROP PROCEDURE au_info2GOUSE pubsGOCREATE PROCEDURE au_info2 lastname varchar(30) = 'D*', firstname varchar(18) = '*'AS SELECT au_lname, au_fname, title, pub_nameFROM authors a INN

30、ER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_idWHERE au_fname LIKE firstname AND au_lname LIKE lastnameGOau_info2 存儲(chǔ)過(guò)程可以用多種組合執(zhí)行。下面只列出了部分組合：EXECUTE au_info2- OrEXECUTE au_info2 'Wh*'- OrEXECUTE au_info

31、2 firstname = 'A*'- OrEXECUTE au_info2 'CKarsOEn'- OrEXECUTE au_info2 'Hunter', 'Sheryl'- OrEXECUTE au_info2 'H*', 'S*'D. 使用 OUTPUT 參數(shù)OUTPUT 參數(shù)允許外部過(guò)程、批處理或多條 Transact-SQL 語(yǔ)句訪問(wèn)在過(guò)程執(zhí)行期間設(shè)置的某個(gè)值。下面的示例創(chuàng)建一個(gè)存儲(chǔ)過(guò)程 (titles_sum)，并使用一個(gè)可選的輸入?yún)?shù)和一個(gè)輸出參數(shù)。首先，創(chuàng)建過(guò)程：USE pubsG

32、OIF EXISTS(SELECT name FROM sysobjects WHERE name = 'titles_sum' AND type = 'P') DROP PROCEDURE titles_sumGOUSE pubsGOCREATE PROCEDURE titles_sum TITLE varchar(40) = '*', SUM money OUTPUTASSELECT 'Title Name' = titleFROM titles WHERE title LIKE TITLE SELECT SUM = SUM(

33、price)FROM titlesWHERE title LIKE TITLEGO接下來(lái)，將該 OUTPUT 參數(shù)用于控制流語(yǔ)言。 說(shuō)明 OUTPUT 變量必須在創(chuàng)建表和使用該變量時(shí)都進(jìn)行定義。參數(shù)名和變量名不一定要匹配，不過(guò)數(shù)據(jù)類型和參數(shù)位置必須匹配（除非使用 SUM = variable 形式）。 DECLARE TOTALCOST moneyEXECUTE titles_sum 'The*', TOTALCOST OUTPUTIF TOTALCOST < 200 BEGIN PRINT ' ' PRINT 'All of these titl

34、es can be purchased for less than $200.'ENDELSE SELECT 'The total cost of these titles is $' + RTRIM(CAST(TOTALCOST AS varchar(20)下面是結(jié)果集：Title Name - The Busy Executive's Database GuideThe Gourmet MicrowaveThe Psychology of Computer Cooking(3 row(s) affected)Warning, null value elimi

35、nated from aggregate.All of these titles can be purchased for less than $200.E. 使用 OUTPUT 游標(biāo)參數(shù)OUTPUT 游標(biāo)參數(shù)用來(lái)將存儲(chǔ)過(guò)程的局部游標(biāo)傳遞回調(diào)用批處理、存儲(chǔ)過(guò)程或觸發(fā)器。首先，創(chuàng)建以下過(guò)程，在 titles 表上聲明并打開(kāi)一個(gè)游標(biāo)：USE pubsIF EXISTS (SELECT name FROM sysobjects WHERE name = 'titles_cursor' and type = 'P')DROP PROCEDURE titles_curso

36、rGOCREATE PROCEDURE titles_cursor titles_cursor CURSOR VARYING OUTPUTASSET titles_cursor = CURSORFORWARD_ONLY STATIC FORSELECT *FROM titlesOPEN titles_cursorGO接下來(lái)，執(zhí)行一個(gè)批處理，聲明一個(gè)局部游標(biāo)變量，執(zhí)行上述過(guò)程以將游標(biāo)賦值給局部變量，然后從該游標(biāo)提取行。USE pubsGODECLARE MyCursor CURSOREXEC titles_cursor titles_cursor = MyCursor OUTPUTWHILE (

37、FETCH_STATUS = 0)BEGIN FETCH NEXT FROM MyCursorENDCLOSE MyCursorDEALLOCATE MyCursorGOF. 使用 WITH RECOMPILE 選項(xiàng)如果為過(guò)程提供的參數(shù)不是典型的參數(shù)，并且新的執(zhí)行計(jì)劃不應(yīng)高速緩存或存儲(chǔ)在內(nèi)存中，WITH RECOMPILE 子句會(huì)很有幫助。USE pubsIF EXISTS (SELECT name FROM sysobjects WHERE name = 'titles_by_author' AND type = 'P') DROP PROCEDURE tit

38、les_by_authorGOCREATE PROCEDURE titles_by_author LNAME_PATTERN varchar(30) = '*'WITH RECOMPILEASSELECT RTRIM(au_fname) + ' ' + RTRIM(au_lname) AS 'Authors full name', title AS TitleFROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON ta.title_

39、id = t.title_idWHERE au_lname LIKE LNAME_PATTERNGOG. 使用 WITH ENCRYPTION 選項(xiàng)WITH ENCRYPTION 子句對(duì)用戶隱藏存儲(chǔ)過(guò)程的文本。下例創(chuàng)建加密過(guò)程，使用 sp_helptext 系統(tǒng)存儲(chǔ)過(guò)程獲取關(guān)于加密過(guò)程的信息，然后嘗試直接從 syscomments 表中獲取關(guān)于該過(guò)程的信息。IF EXISTS (SELECT name FROM sysobjects WHERE name = 'encrypt_this' AND type = 'P') DROP PROCEDURE encryp

40、t_thisGOUSE pubsGOCREATE PROCEDURE encrypt_thisWITH ENCRYPTIONASSELECT * FROM authorsGOEXEC sp_helptext encrypt_this下面是結(jié)果集：The object's comments have been encrypted.接下來(lái)，選擇加密存儲(chǔ)過(guò)程內(nèi)容的標(biāo)識(shí)號(hào)和文本。SELECT c.id, c.text FROM syscomments c INNER JOIN sysobjects o ON c.id = o.idWHERE = 'encrypt_this

41、'下面是結(jié)果集：說(shuō)明 text 列的輸出顯示在單獨(dú)一行中。執(zhí)行時(shí)，該信息將與 id 列信息出現(xiàn)在同一行中。id text - -(1 row(s) affected) - 1/F 2006-11-12 18:22:00 - answer0161 樂(lè)號(hào)：10000傳小字條 Re:sql存儲(chǔ)過(guò)程基礎(chǔ)語(yǔ)法H. 創(chuàng)建用戶定義的系統(tǒng)存儲(chǔ)過(guò)程下面的示例創(chuàng)建一個(gè)過(guò)程，顯示表名以 emp 開(kāi)頭的所有表及其對(duì)應(yīng)的索引。如果沒(méi)有指定參數(shù)，該過(guò)程將返回表名以 sys 開(kāi)頭的所有表（及索引）。IF EXISTS (SELECT name FROM sysobjects WHERE name = 'sp

42、_showindexes' AND type = 'P') DROP PROCEDURE sp_showindexesGOUSE masterGOCREATE PROCEDURE sp_showindexes TABLE varchar(30) = 'sys*'AS SELECT AS TABLE_NAME, AS INDEX_NAME, indid AS INDEX_IDFROM sysindexes i INNER JOIN sysobjects o ON o.id = i.id WHERE LIKE TAB

43、LEGO USE pubsEXEC sp_showindexes 'emp*'GO下面是結(jié)果集：TABLE_NAME INDEX_NAME INDEX_ID - - -employee employee_ind 1employee PK_emp_id 2(2 row(s) affected)I. 使用延遲名稱解析下面的示例顯示四個(gè)過(guò)程以及延遲名稱解析的各種可能使用方式。盡管引用的表或列在編譯時(shí)不存在，但每個(gè)存儲(chǔ)過(guò)程都可創(chuàng)建。IF EXISTS (SELECT name FROM sysobjects WHERE name = 'proc1' AND type =

44、 'P') DROP PROCEDURE proc1GO- Creating a procedure on a nonexistent table.USE pubsGOCREATE PROCEDURE proc1AS SELECT * FROM does_not_existGO - Here is the statement to actually see the text of the procedure.SELECT o.id, c.textFROM sysobjects o INNER JOIN syscomments c ON o.id = c.idWHERE o.ty

45、pe = 'P' AND = 'proc1'GOUSE masterGOIF EXISTS (SELECT name FROM sysobjects WHERE name = 'proc2' AND type = 'P') DROP PROCEDURE proc2GO- Creating a procedure that attempts to retrieve information from a- nonexistent column in an existing table.USE pubsGOCREATE P

46、ROCEDURE proc2AS DECLARE middle_init char(1) SET middle_init = NULL SELECT au_id, middle_initial = middle_init FROM authorsGO - Here is the statement to actually see the text of the procedure.SELECT o.id, c.textFROM sysobjects o INNER JOIN syscomments c ON o.id = c.idWHERE o.type = 'P' and o

47、.name = 'proc2'=結(jié)束=還有另外一個(gè)參考的儲(chǔ)存過(guò)程例子有關(guān)ASP與存儲(chǔ)過(guò)程的文章不少，這些資料旨在提供一點(diǎn)幫助，僅限于此，現(xiàn)在我基本上通過(guò)調(diào)用存儲(chǔ)過(guò)程訪問(wèn)SQL Server，以下的文字雖不敢保證絕對(duì)正確，但都是實(shí)踐的總結(jié)，希望對(duì)大家能有幫助。 存儲(chǔ)過(guò)程就是作為可執(zhí)行對(duì)象存放在數(shù)據(jù)庫(kù)中的一個(gè)或多個(gè)SQL命令。定義總是很抽象。存儲(chǔ)過(guò)程其實(shí)就是能完成一定操作的一組SQL語(yǔ)句，只不過(guò)這組語(yǔ)句是放在數(shù)據(jù)庫(kù)中的(這里我們只談SQL Server)。如果我們通過(guò)創(chuàng)建存儲(chǔ)過(guò)程以及在ASP中調(diào)用存儲(chǔ)過(guò)程，就可以避免將SQL語(yǔ)句同ASP代碼混雜在一起。這樣做的好處至少有三個(gè)：第一、

48、大大提高效率。存儲(chǔ)過(guò)程本身的執(zhí)行速度非?？?，而且，調(diào)用存儲(chǔ)過(guò)程可以大大減少同數(shù)據(jù)庫(kù)的交互次數(shù)。第二、提高安全性。假如將SQL語(yǔ)句混合在ASP代碼中，一旦代碼失密，同時(shí)也就意味著庫(kù)結(jié)構(gòu)失密。第三、有利于SQL語(yǔ)句的重用。在ASP中，一般通過(guò)command對(duì)象調(diào)用存儲(chǔ)過(guò)程，根據(jù)不同情況，本文也介紹其它調(diào)用方法。為了方便說(shuō)明，根據(jù)存儲(chǔ)過(guò)程的輸入輸出，作以下簡(jiǎn)單分類：1. 只返回單一記錄集的存儲(chǔ)過(guò)程假設(shè)有以下存儲(chǔ)過(guò)程(本文的目的不在于講述T-SQL語(yǔ)法，所以存儲(chǔ)過(guò)程只給出代碼，不作說(shuō)明)： /*SP1*/CREATE PROCEDURE dbo.getUserListasset nocount onb

49、eginselect * from dbo.userinfoendgo 以上存儲(chǔ)過(guò)程取得userinfo表中的所有記錄，返回一個(gè)記錄集。通過(guò)command對(duì)象調(diào)用該存儲(chǔ)過(guò)程的ASP代碼如下:'*通過(guò)Command對(duì)象調(diào)用存儲(chǔ)過(guò)程*DIM MyComm,MyRstSet MyComm = Server.CreateObject("ADODB.Command")MyComm.ActiveConnection = MyConStr 'MyConStr是數(shù)據(jù)庫(kù)連接字串MyComm.CommandText = "getUserList" '

50、指定存儲(chǔ)過(guò)程名MyComm.CommandType = 4 '表明這是一個(gè)存儲(chǔ)過(guò)程MyComm.Prepared = true '要求將SQL命令先行編譯Set MyRst = MyComm.ExecuteSet MyComm = Nothing 存儲(chǔ)過(guò)程取得的記錄集賦給MyRst，接下來(lái)，可以對(duì)MyRst進(jìn)行操作。在以上代碼中，CommandType屬性表明請(qǐng)求的類型，取值及說(shuō)明如下：-1 表明CommandText參數(shù)的類型無(wú)法確定1 表明CommandText是一般的命令類型2 表明CommandText參數(shù)是一個(gè)存在的表名稱4 表明CommandText參數(shù)是一個(gè)存儲(chǔ)過(guò)

51、程的名稱還可以通過(guò)Connection對(duì)象或Recordset對(duì)象調(diào)用存儲(chǔ)過(guò)程，方法分別如下： '*通過(guò)Connection對(duì)象調(diào)用存儲(chǔ)過(guò)程*DIM MyConn,MyRstSet MyConn = Server.CreateObject("ADODB.Connection")MyConn.open MyConStr 'MyConStr是數(shù)據(jù)庫(kù)連接字串Set MyRst = MyConn.Execute("getUserList",0,4) '最后一個(gè)參斷含義同CommandTypeSet MyConn = Nothing 

52、9;*通過(guò)Recordset對(duì)象調(diào)用存儲(chǔ)過(guò)程*DIM MyRstSet MyRst = Server.CreateObject("ADODB.Recordset")MyRst.open "getUserList",MyConStr,0,1,4'MyConStr是數(shù)據(jù)庫(kù)連接字串,最后一個(gè)參斷含義與CommandType相同 2. 沒(méi)有輸入輸出的存儲(chǔ)過(guò)程請(qǐng)看以下存儲(chǔ)過(guò)程： /*SP2*/CREATE PROCEDURE dbo.delUserAllasset nocount onbegindelete from dbo.userinfoendgo 該

53、存儲(chǔ)過(guò)程刪去userinfo表中的所有記錄，沒(méi)有任何輸入及輸出，調(diào)用方法與上面講過(guò)的基本相同，只是不用取得記錄集： '*通過(guò)Command對(duì)象調(diào)用存儲(chǔ)過(guò)程*DIM MyCommSet MyComm = Server.CreateObject("ADODB.Command")MyComm.ActiveConnection = MyConStr 'MyConStr是數(shù)據(jù)庫(kù)連接字串MyComm.CommandText = "delUserAll" '指定存儲(chǔ)過(guò)程名MyComm.CommandType = 4 '表明這是一個(gè)存儲(chǔ)過(guò)

54、程MyComm.Prepared = true '要求將SQL命令先行編譯MyComm.Execute '此處不必再取得記錄集Set MyComm = Nothing 當(dāng)然也可通過(guò)Connection對(duì)象或Recordset對(duì)象調(diào)用此類存儲(chǔ)過(guò)程，不過(guò)建立Recordset對(duì)象是為了取得記錄集，在沒(méi)有返回記錄集的情況下，還是利用Command對(duì)象吧。3. 有返回值的存儲(chǔ)過(guò)程在進(jìn)行類似SP2的操作時(shí)，應(yīng)充分利用SQL Server強(qiáng)大的事務(wù)處理功能，以維護(hù)數(shù)據(jù)的一致性。并且，我們可能需要存儲(chǔ)過(guò)程返回執(zhí)行情況，為此，將SP2修改如下： /*SP3*/CREATE PROCEDURE dbo.delUserAllasset nocount onbeginBEGIN TRANSACTIONdelete from dbo.userinfoIF error=0 beginCOMMIT TRANSACTIONreturn 1endELSEbeginROLLBACK TRANSACTIONreturn 0end returnendgo 以上存儲(chǔ)過(guò)程，在delete順利執(zhí)行時(shí)，返回1，否則返回0，并進(jìn)行回滾操作。為了在