2.SQL存儲(chǔ)過(guò)程創(chuàng)建
創(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 | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
參數(shù)
procedure_name
新存儲(chǔ)過(guò)程的名稱。過(guò)程名必須符合標(biāo)識(shí)符規(guī)則，且對(duì)于數(shù)據(jù)庫(kù)及其所有者必須唯一。
要?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
是可選的整數(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ù)亩ń绶?br />
@parameter
過(guò)程中的參數(shù)。在 CREATE PROCEDURE 語(yǔ)句中可以聲明一個(gè)或多個(gè)參數(shù)。用戶必須在執(zhí)行過(guò)程時(shí)提供每個(gè)所聲明參數(shù)的值（除非定義了該參數(shù)的默認(rèn)值）。存儲(chǔ)過(guò)程最多可以有 2.100 個(gè)參數(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ì)象的名稱。
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)鍵字。
說(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)的值可以返回給 EXEC[UTE]。使用 OUTPUT 參數(shù)可將信息返回給調(diào)用過(guò)程。Text、ntext 和 image 參數(shù)可用作 OUTPUT 參數(shù)。使用 OUTPUT 關(guān)鍵字的輸出參數(shù)可以是游標(biāo)占位符。
n
表示最多可以指定 2.100 個(gè)參數(shù)的占位符。
{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}
RECOMPILE 表明 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 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)建（臨時(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 或 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)建或更改存儲(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ò)程的邏輯取決于特定的設(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ò)程的邏輯。
天涯風(fēng)云??19:30:43
說(shuō)明 SQL Server 是將空字符串解釋為單個(gè)空格還是解釋為真正的空字符串，由兼容級(jí)別設(shè)置控制。如果兼容級(jí)別小于或等于 65，SQL Server 就將空字符串解釋為單個(gè)空格。如果兼容級(jí)別等于 70，則 SQL Server 將空字符串解釋為空字符串。
獲得有關(guān)存儲(chǔ)過(guò)程的信息
若要顯示用來(lái)創(chuàng)建過(guò)程的文本，請(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ì)象不存在。
延遲名稱解析和兼容級(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ò)誤信息。
執(zhí)行存儲(chǔ)過(guò)程
成功執(zhí)行 CREATE PROCEDURE 語(yǔ)句后，過(guò)程名稱將存儲(chǔ)在 sysobjects 系統(tǒng)表中，而 CREATE 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)楸仨毾冉壎?OUTPUT 參數(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)用者。
返回到調(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ā)器。如果將游標(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ò)程的權(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)符命名）。可以授予或廢除這些過(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)建，并在 sysadmin 固定服務(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í)行。
若要?jiǎng)?chuàng)建啟動(dòng)存儲(chǔ)過(guò)程，必須作為 sysadmin 固定服務(wù)器角色的成員登錄，并在 master 數(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ù)器。
??
* 各種分類的高速緩存對(duì)象均可以使用這些計(jì)數(shù)器，包括特殊 sql、準(zhǔn)備 sql、過(guò)程、觸發(fā)器等。
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è)置。
如果其他用戶要使用某個(gè)存儲(chǔ)過(guò)程，那么在該存儲(chǔ)過(guò)程內(nèi)部，一些語(yǔ)句使用的對(duì)象名必須使用對(duì)象所有者的名稱限定。這些語(yǔ)句包括：
ALTER TABLE
CREATE INDEX
CREATE TABLE
所有 DBCC 語(yǔ)句
DROP TABLE
DROP INDEX
TRUNCATE TABLE
UPDATE 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)讓給其他用戶。執(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 pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = \'au_info_all\' AND type = \'P\')
DROP PROCEDURE au_info_all
GO
CREATE PROCEDURE au_info_all
AS
SELECT au_lname, 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_id
GO
au_info_all 存儲(chǔ)過(guò)程可以通過(guò)以下方法執(zhí)行：
EXECUTE au_info_all
-- Or
EXEC au_info_all
如果該過(guò)程是批處理中的第一條語(yǔ)句，則可使用：
au_info_all
天涯風(fēng)云??19:31:29
B. 使用帶有參數(shù)的簡(jiǎn)單過(guò)程
下面的存儲(chǔ)過(guò)程從四個(gè)表的聯(lián)接中只返回指定的作者（提供了姓名）、出版的書(shū)籍以及出版社。該存儲(chǔ)過(guò)程接受與傳遞的參數(shù)精確匹配的值。
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = \'au_info\' AND type = \'P\')
DROP PROCEDURE au_info
GO
USE pubs
GO
CREATE PROCEDURE au_info
@lastname varchar(40),
@firstname varchar(20)
AS
SELECT au_lname, 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_id
WHERE au_fname = @firstname
AND au_lname = @lastname
GO
au_info 存儲(chǔ)過(guò)程可以通過(guò)以下方法執(zhí)行：
EXECUTE au_info \'Dull\', \'Ann\'
-- Or
EXECUTE au_info @lastname = \'Dull\', @firstname = \'Ann\'
-- Or
EXECUTE au_info @firstname = \'Ann\', @lastname = \'Dull\'
-- Or
EXEC au_info \'Dull\', \'Ann\'
-- Or
EXEC au_info @lastname = \'Dull\', @firstname = \'Ann\'
-- Or
EXEC au_info @firstname = \'Ann\', @lastname = \'Dull\'
如果該過(guò)程是批處理中的第一條語(yǔ)句，則可使用：
au_info \'Dull\', \'Ann\'
-- Or
au_info @lastname = \'Dull\', @firstname = \'Ann\'
-- Or
au_info @firstname = \'Ann\', @lastname = \'Dull\'
天涯風(fēng)云??19:31:41
C. 使用帶有通配符參數(shù)的簡(jiǎn)單過(guò)程
下面的存儲(chǔ)過(guò)程從四個(gè)表的聯(lián)接中只返回指定的作者（提供了姓名）、出版的書(shū)籍以及出版社。該存儲(chǔ)過(guò)程對(duì)傳遞的參數(shù)進(jìn)行模式匹配，如果沒(méi)有提供參數(shù)，則使用預(yù)設(shè)的默認(rèn)值。
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = \'au_info2\' AND type = \'P\')
DROP PROCEDURE au_info2
GO
USE pubs
GO
CREATE PROCEDURE au_info2
@lastname varchar(30) = \'D%\',
@firstname varchar(18) = \'%\'
AS
SELECT au_lname, 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_id
WHERE au_fname LIKE @firstname
AND au_lname LIKE @lastname
GO
au_info2 存儲(chǔ)過(guò)程可以用多種組合執(zhí)行。下面只列出了部分組合：
EXECUTE au_info2
-- Or
EXECUTE au_info2 \'Wh%\'
-- Or
EXECUTE au_info2 @firstname = \'A%\'
-- Or
EXECUTE au_info2 \'[CK]ars[OE]n\'
-- Or
EXECUTE au_info2 \'Hunter\', \'Sheryl\'
-- Or
EXECUTE au_info2 \'H%\', \'S%\'
--------------------------------------------------------------------------------
天涯風(fēng)云??19:32:22
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 pubs
GO
IF EXISTS(SELECT name FROM sysobjects
WHERE name = \'titles_sum\' AND type = \'P\')
DROP PROCEDURE titles_sum
GO
USE pubs
GO
CREATE PROCEDURE titles_sum @@TITLE varchar(40) = \'%\', @@SUM money OUTPUT
AS
SELECT \'Title Name\' = title
FROM titles
WHERE title LIKE @@TITLE
SELECT @@SUM = SUM(price)
FROM titles
WHERE title LIKE @@TITLE
GO
接下來(lái)，將該 OUTPUT 參數(shù)用于控制流語(yǔ)言。
說(shuō)明 OUTPUT 變量必須在創(chuàng)建表和使用該變量時(shí)都進(jìn)行定義。
參數(shù)名和變量名不一定要匹配，不過(guò)數(shù)據(jù)類型和參數(shù)位置必須匹配（除非使用 @@SUM = variable 形式）。
DECLARE @@TOTALCOST money
EXECUTE titles_sum \'The%\', @@TOTALCOST OUTPUT
IF @@TOTALCOST < 200
BEGIN
PRINT \' \'
PRINT \'All of these titles can be purchased for less than $200.\'
END
ELSE
SELECT \'The total cost of these titles is $\'
+ RTRIM(CAST(@@TOTALCOST AS varchar(20)))
下面是結(jié)果集：
Title Name
------------------------------------------------------------------------
The Busy Executive\'s Database Guide
The Gourmet Microwave
The Psychology of Computer Cooking
(3 row(s) affected)
Warning, null value eliminated from aggregate.
All of these titles can be purchased for less than $200.
天涯風(fēng)云??19:32:33
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 pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = \'titles_cursor\' and type = \'P\')
DROP PROCEDURE titles_cursor
GO
CREATE PROCEDURE titles_cursor @titles_cursor CURSOR VARYING OUTPUT
AS
SET @titles_cursor = CURSOR
FORWARD_ONLY STATIC FOR
SELECT *
FROM titles
OPEN @titles_cursor
GO
接下來(lái)，執(zhí)行一個(gè)批處理，聲明一個(gè)局部游標(biāo)變量，執(zhí)行上述過(guò)程以將游標(biāo)賦值給局部變量，然后從該游標(biāo)提取行。
USE pubs
GO
DECLARE @MyCursor CURSOR
EXEC titles_cursor @titles_cursor = @MyCursor OUTPUT
WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM @MyCursor
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
GO
天涯風(fēng)云??19:32:43
F. 使用 WITH RECOMPILE 選項(xiàng)
如果為過(guò)程提供的參數(shù)不是典型的參數(shù)，并且新的執(zhí)行計(jì)劃不應(yīng)高速緩存或存儲(chǔ)在內(nèi)存中，WITH RECOMPILE 子句會(huì)很有幫助。
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = \'titles_by_author\' AND type = \'P\')
DROP PROCEDURE titles_by_author
GO
CREATE PROCEDURE titles_by_author @@LNAME_PATTERN varchar(30) = \'%\'
WITH RECOMPILE
AS
SELECT RTRIM(au_fname) + \' \' + RTRIM(au_lname) AS \'Authors full name\',
title AS Title
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON ta.title_id = t.title_id
WHERE au_lname LIKE @@LNAME_PATTERN
GO
天涯風(fēng)云??19:32:59
G. 使用 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 encrypt_this
GO
USE pubs
GO
CREATE PROCEDURE encrypt_this
WITH ENCRYPTION
AS
SELECT *
FROM authors
GO
EXEC 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.id
WHERE o.name = \'encrypt_this\'
下面是結(jié)果集：
說(shuō)明 text 列的輸出顯示在單獨(dú)一行中。執(zhí)行時(shí)，該信息將與 id 列信息出現(xiàn)在同一行中。
id text
---------- ------------------------------------------------------------
1413580074 ?????????????????????????????????e??????????????????????????????????????????????????????????????????????????
(1 row(s) affected)
天涯風(fēng)云??19:33:10
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_showindexes\' AND type = \'P\')
DROP PROCEDURE sp_showindexes
GO
USE master
GO
CREATE PROCEDURE sp_showindexes
@@TABLE varchar(30) = \'sys%\'
AS
SELECT o.name AS TABLE_NAME,
i.name AS INDEX_NAME,
indid AS INDEX_ID
FROM sysindexes i INNER JOIN sysobjects o
ON o.id = i.id
WHERE o.name LIKE @@TABLE
GO
USE pubs
EXEC sp_showindexes \'emp%\'
GO
下面是結(jié)果集：
TABLE_NAME INDEX_NAME INDEX_ID
---------------- ---------------- ----------------
employee employee_ind 1
employee PK_emp_id 2
(2 row(s) affected)
天涯風(fēng)云??19:33:24
I. 使用延遲名稱解析
下面的示例顯示四個(gè)過(guò)程以及延遲名稱解析的各種可能使用方式。盡管引用的表或列在編譯時(shí)不存在，但每個(gè)存儲(chǔ)過(guò)程都可創(chuàng)建。
IF EXISTS (SELECT name FROM sysobjects
WHERE name = \'proc1\' AND type = \'P\')
DROP PROCEDURE proc1
GO
-- Creating a procedure on a nonexistent table.
USE pubs
GO
CREATE PROCEDURE proc1
AS
SELECT *
FROM does_not_exist
GO
-- Here is the statement to actually see the text of the procedure.
SELECT o.id, c.text
FROM sysobjects o INNER JOIN syscomments c
ON o.id = c.id
WHERE o.type = \'P\' AND o.name = \'proc1\'
GO
USE master
GO
IF EXISTS (SELECT name FROM sysobjects
WHERE name = \'proc2\' AND type = \'P\')
DROP PROCEDURE proc2
GO
-- Creating a procedure that attempts to retrieve information from a
-- nonexistent column in an existing table.
USE pubs
GO
CREATE PROCEDURE proc2
AS
DECLARE @middle_init char(1)
SET @middle_init = NULL
SELECT au_id, middle_initial = @middle_init
FROM authors
GO
-- Here is the statement to actually see the text of the procedure.
SELECT o.id, c.text
FROM sysobjects o INNER JOIN syscomments c
ON o.id = c.id
WHERE o.type = \'P\' and o.name = \'proc2\'
3.sql存儲(chǔ)過(guò)程及應(yīng)用
一、簡(jiǎn)介：
? ?
存儲(chǔ)過(guò)程（Stored Procedure）， 是一組為了完成特定功能的SQL 語(yǔ)句，集經(jīng)編譯后
? ? 存儲(chǔ)在數(shù)據(jù)庫(kù)中，用戶通過(guò)指定存儲(chǔ)過(guò)程的名字并給出參數(shù)，如果該存儲(chǔ)過(guò)程帶有參數(shù)來(lái)執(zhí)行
它，
? ? 在SQL Server 的系列版本中，存儲(chǔ)過(guò)程分為兩類：系統(tǒng)提供的存儲(chǔ)過(guò)程和用戶自定義存儲(chǔ)過(guò)程
。
? ? 系統(tǒng)SP，主要存儲(chǔ)master 數(shù)據(jù)庫(kù)中，并以sp_為前綴并且系統(tǒng)存儲(chǔ)過(guò)程主要是從系統(tǒng)表中獲取
? ? 信息，從而為系統(tǒng)管理員管理SQL Server。 用戶自定義存儲(chǔ)過(guò)程是由用戶創(chuàng)建，并能完成
? ? 某一特定功能，如：查詢用戶所需數(shù)據(jù)信息的存儲(chǔ)過(guò)程。
? ?
? ? 存儲(chǔ)過(guò)程具有以下優(yōu)點(diǎn)
? ? 1.存儲(chǔ)過(guò)程允許標(biāo)準(zhǔn)組件式編程(模塊化設(shè)計(jì))
? ? 存儲(chǔ)過(guò)程在被創(chuàng)建以后，可以在程序中被多次調(diào)用，而不必重新編寫該存儲(chǔ)過(guò)程的SQL語(yǔ)句，而
且數(shù)
? ? 據(jù)庫(kù)專業(yè)人員可隨時(shí)對(duì)存儲(chǔ)過(guò)程進(jìn)行修改，但對(duì)應(yīng)用程序源代碼毫無(wú)影響。因?yàn)閼?yīng)用程序源代
碼只包含存
? ? 儲(chǔ)過(guò)程的調(diào)用語(yǔ)句，從而極大地提高了程序的可移植性。
? ?? ?? ?? ?
? ? 2.存儲(chǔ)過(guò)程能夠?qū)崿F(xiàn)快速的執(zhí)行速度
如果某一操作包含大量的Transaction-SQL 代碼,，或分別被多次執(zhí)行，那么存儲(chǔ)過(guò)程要比批處理
的
? ? 執(zhí)行速度快很多，因?yàn)榇鎯?chǔ)過(guò)程是預(yù)編譯的，在首次運(yùn)行一個(gè)存儲(chǔ)過(guò)程時(shí)，查詢優(yōu)化器對(duì)其進(jìn)
行分析優(yōu)
? ? 化，并給出最終被存在系統(tǒng)表中的執(zhí)行計(jì)劃，而批處理的Transaction-SQL 語(yǔ)句在每次運(yùn)行時(shí)
都要進(jìn)行
? ? 編譯和優(yōu)化，因此速度相對(duì)要慢一些。
? ?? ?? ?? ?? ?
? ? 3.存儲(chǔ)過(guò)程能夠減少網(wǎng)絡(luò)流量
對(duì)于同一個(gè)針對(duì)數(shù)據(jù)數(shù)據(jù)庫(kù)對(duì)象的操作，如查詢修改，如果這一操作所涉及到的Transaction-SQL
? ? 語(yǔ)句被組織成一存儲(chǔ)過(guò)程，那么當(dāng)在客戶計(jì)算機(jī)上調(diào)用該存儲(chǔ)過(guò)程時(shí)，網(wǎng)絡(luò)中傳送的只是該調(diào)
用語(yǔ)句，否
? ? 則將是多條SQL 語(yǔ)句從而大大增加了網(wǎng)絡(luò)流量降低網(wǎng)絡(luò)負(fù)載。
? ?? ?? ?? ?
? ? 4.存儲(chǔ)過(guò)程可被作為一種安全機(jī)制來(lái)充分利用
系統(tǒng)管理員通過(guò)，對(duì)執(zhí)行某一存儲(chǔ)過(guò)程的權(quán)限進(jìn)行限制，從而能夠?qū)崿F(xiàn)對(duì)相應(yīng)的數(shù)據(jù)訪問(wèn)權(quán)限的
限
? ? 制。
? ? 二、變量
? ? @I
? ? 三、流程控制語(yǔ)句(if else | select case | while )
? ? Select ... CASE 實(shí)例
? ? DECLARE @iRet INT, @PKDisp VARCHAR(20)
? ? SET @iRet = '1'
? ? Select @iRet =
? ? CASE
? ?? ???WHEN @PKDisp = '一' THEN 1
? ?? ???WHEN @PKDisp = '二' THEN 2
? ?? ???WHEN @PKDisp = '三' THEN 3
? ?? ???WHEN @PKDisp = '四' THEN 4
? ?? ???WHEN @PKDisp = '五' THEN 5
? ?? ???ELSE 100
? ? END
? ? 四、存儲(chǔ)過(guò)程格式
? ?? ???
? ? 創(chuàng)建存儲(chǔ)過(guò)程
? ? Create Proc dbo.存儲(chǔ)過(guò)程名
? ? 存儲(chǔ)過(guò)程參數(shù)
? ? AS
? ? 執(zhí)行語(yǔ)句
? ? RETURN
? ? 執(zhí)行存儲(chǔ)過(guò)程
? ? GO
*********************************************************/
-- 變量的聲明,sql里面聲明變量時(shí)必須在變量前加@符號(hào)
? ? DECLARE @I INT
-- 變量的賦值，變量賦值時(shí)變量前必須加set
? ? SET @I = 30
-- 聲明多個(gè)變量
? ? DECLARE @s varchar(10),@a INT
-- Sql 里if語(yǔ)句
? ? IF 條件 BEGIN
? ?? ???執(zhí)行語(yǔ)句
? ? END
? ? ELSE BEGIN
? ?? ???執(zhí)行語(yǔ)句
? ? END
? ?? ?? ?? ?
? ? DECLARE @d INT
? ? set @d = 1
? ? IF @d = 1 BEGIN
? ? -- 打印
? ?? ???PRINT '正確'
? ? END
? ? ELSE BEGIN
? ?? ???PRINT '錯(cuò)誤'
? ? END
-- Sql 里的多條件選擇語(yǔ)句.
? ? DECLARE @iRet INT, @PKDisp VARCHAR(20)
? ? SET @iRet = 1
? ? Select @iRet =
? ? CASE
? ?? ???WHEN @PKDisp = '一' THEN 1
? ?? ???WHEN @PKDisp = '二' THEN 2
? ?? ???WHEN @PKDisp = '三' THEN 3
? ?? ???WHEN @PKDisp = '四' THEN 4
? ?? ???WHEN @PKDisp = '五' THEN 5
? ?? ???ELSE 100
? ? END
-- 循環(huán)語(yǔ)句
? ? WHILE 條件 BEGIN? ?
? ?? ???執(zhí)行語(yǔ)句
? ? END
? ? DECLARE @i INT
? ? SET @i = 1
? ? WHILE @i<1000000 BEGIN
? ?? ???set @i=@i+1
? ? END
? ? -- 打印
? ? PRINT @i
-- TRUNCATE 刪除表中的所有行，而不記錄單個(gè)行刪除操作，不能帶條件
? ? /*
? ? TRUNCATE TABLE 在功能上與不帶 Where 子句的 Delete 語(yǔ)句相同：二者均刪除表中的全部行
。但 TRUNCATE TABLE 比 Delete 速度快，且使用的系統(tǒng)和事務(wù)日志資源少。
? ? Delete 語(yǔ)句每次刪除一行，并在事務(wù)日志中為所刪除的每行記錄一項(xiàng)。TRUNCATE TABLE 通過(guò)
釋放存儲(chǔ)表數(shù)據(jù)所用的數(shù)據(jù)頁(yè)來(lái)刪除數(shù)據(jù)，并且只在事務(wù)日志中記錄頁(yè)的釋放。
? ? TRUNCATE TABLE 刪除表中的所有行，但表結(jié)構(gòu)及其列、約束、索引等保持不變。新行標(biāo)識(shí)所用
的計(jì)數(shù)值重置為該列的種子。如果想保留標(biāo)識(shí)計(jì)數(shù)值，請(qǐng)改用 Delete。如果要?jiǎng)h除表定義及其數(shù)據(jù)，請(qǐng)
使用 Drop TABLE 語(yǔ)句。
? ? 對(duì)于由 FOREIGN KEY 約束引用的表，不能使用 TRUNCATE TABLE，而應(yīng)使用不帶 Where 子句的
Delete 語(yǔ)句。由于 TRUNCATE TABLE 不記錄在日志中，所以它不能激活觸發(fā)器。
? ? TRUNCATE TABLE 不能用于參與了索引視圖的表。
? ? 示例
? ?? ???下例刪除 authors 表中的所有數(shù)據(jù)。*/
? ?? ???
? ?? ???TRUNCATE TABLE authors
? ?? ?? ?? ?? ?
-- Select INTO 從一個(gè)查詢的計(jì)算結(jié)果中創(chuàng)建一個(gè)新表。 數(shù)據(jù)并不返回給客戶端，這一點(diǎn)和普通的
-- Select 不同。 新表的字段具有和 Select 的輸出字段相關(guān)聯(lián)（相同）的名字和數(shù)據(jù)類型。
? ?? ???
? ?? ???select * into NewTable
? ?? ?? ?? ?from Uname
-- Insert INTO Select
? ?? ???-- 表ABC必須存在
? ?? ???-- 把表Uname里面的字段Username復(fù)制到表ABC
? ?? ???Insert INTO ABC Select Username FROM Uname
-- 創(chuàng)建臨時(shí)表
? ?? ???Create TABLE #temp(
? ?? ?? ?? ?UID int identity(1, 1) PRIMARY KEY,
? ?? ?? ?? ?UserName varchar(16),
? ?? ?? ?? ?Pwd varchar(50),
? ?? ?? ?? ?Age smallint,
? ?? ?? ?? ?Sex varchar(6)
? ?? ???)
? ?? ???-- 打開(kāi)臨時(shí)表
? ?? ???Select * from #temp
-- 存儲(chǔ)過(guò)程
? ?? ???-- 要?jiǎng)?chuàng)建存儲(chǔ)過(guò)程的數(shù)據(jù)庫(kù)
? ?? ???Use Test
? ?? ???-- 判斷要?jiǎng)?chuàng)建的存儲(chǔ)過(guò)程名是否存在
? ?? ?? ?? ?if Exists(Select name From sysobjects Where name='csp_AddInfo' And
type='P')
? ?? ?? ?? ?-- 刪除存儲(chǔ)過(guò)程
? ?? ?? ?? ?Drop Procedure dbo.csp_AddInfo
? ?? ???Go
? ?? ?? ?? ?? ?
? ?? ?? ?? ?? ?
? ?? ???-- 創(chuàng)建存儲(chǔ)過(guò)程
? ?? ???Create Proc dbo.csp_AddInfo
? ?? ???-- 存儲(chǔ)過(guò)程參數(shù)
? ?? ???@UserName varchar(16),
? ?? ???@Pwd varchar(50),
? ?? ???@Age smallint,
? ?? ???@Sex varchar(6)
? ?? ???AS
? ?? ???-- 存儲(chǔ)過(guò)程語(yǔ)句體
? ?? ???insert into Uname (UserName,Pwd,Age,Sex)
? ?? ?? ?? ?values (@UserName,@Pwd,@Age,@Sex)
? ?? ???RETURN
? ?? ???-- 執(zhí)行
? ?? ???GO
? ?? ?? ?? ?? ?
? ?? ???-- 執(zhí)行存儲(chǔ)過(guò)程
? ?? ???EXEC csp_AddInfo 'Junn.A','123456',20,'男'
4.各種存儲(chǔ)過(guò)程使用指南
<%@ Language=VBScript %>
<%
'---開(kāi)始鏈接數(shù)據(jù)庫(kù)
Dim strConnString
strConnString = "driver={SQL Server};server=songhp;uid=sa;pwd=;database=XVZDemo"
set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open strConnstring
'---結(jié)束鏈接數(shù)據(jù)庫(kù)
'---開(kāi)始為輸入?yún)?shù)賦值
Dim SelectSql , SelectRs
Dim SelectID , SelectName , SelectReturn
SelectSql = "Select Max(CompanyID) From Dim_Company"
Set SelectRs = Conn.Execute(SelectSql)
SelectID = SelectRs(0)
'---結(jié)束為輸入?yún)?shù)賦值
Dim TiggerType
TiggerType = 3
Set Cmd = Server.CreateObject("ADODB.Command")
Set Cmd.ActiveConnection = Conn
Cmd.CommandType = 4? ?'---聲明此過(guò)程為存儲(chǔ)過(guò)程
If TiggerType = 1 then
'---開(kāi)始一個(gè)輸入?yún)?shù)的存儲(chǔ)過(guò)程調(diào)用
Cmd.CommandText = "TransCompany1"
Set CmdParam = Cmd.CreateParameter("@TransID",3,1)
Cmd.Parameters.Append CmdParam
Cmd("@TransID") = SelectID
Cmd.Execute
'---結(jié)束一個(gè)輸入?yún)?shù)的存儲(chǔ)過(guò)程調(diào)用
Elseif TiggerType = 2 then
'---開(kāi)始一個(gè)輸入?yún)?shù)，一個(gè)輸出參數(shù)的存儲(chǔ)過(guò)程調(diào)用
Cmd.CommandText = "TransCompany2"
Set CmdParamID = Cmd.CreateParameter("@TransID",3,1)
Cmd.Parameters.Append CmdParamID
Cmd("@TransID") = SelectID
Set CmdParamName = Cmd.CreateParameter("@TransName",202,2,50)
Cmd.Parameters.Append CmdParamName
Cmd.Execute
SelectName = Cmd("@TransName")
'---結(jié)束一個(gè)輸入?yún)?shù)，一個(gè)輸出參數(shù)的存儲(chǔ)過(guò)程調(diào)用
Elseif TiggerType = 3 then
'---開(kāi)始一個(gè)輸入?yún)?shù)，一個(gè)輸出參數(shù)，一個(gè)返回值的存儲(chǔ)過(guò)程調(diào)用
Cmd.CommandText = "TransCompany3"
Set CmdParamReturn = Cmd.CreateParameter("Return_Value",3,4)
Cmd.Parameters.Append CmdParamReturn
Set CmdParamID = Cmd.CreateParameter("@TransID",3,1)
Cmd.Parameters.Append CmdParamID
Cmd("@TransID") = SelectID
Set CmdParamName = Cmd.CreateParameter("@TransName",202,2,50)
Cmd.Parameters.Append CmdParamName
Cmd.Execute
SelectName = Cmd("@TransName")
SelectReturn = Cmd("Return_Value")
'---結(jié)束一個(gè)輸入?yún)?shù)，一個(gè)輸出參數(shù)，一個(gè)返回值的存儲(chǔ)過(guò)程調(diào)用
End if
Conn.Close
Set Conn = Nothing
Set Cmd = Nothing
Set CmdParamID = Nothing
Set CmdParamname = Nothing
Set CmdParamReturn = Nothing
%>
5.ASP中存儲(chǔ)過(guò)程調(diào)用的兩種方式及比較
本人用sql server 和asp寫了一個(gè)簡(jiǎn)單的留言本，在不斷的嘗試中發(fā)現(xiàn)，分頁(yè)顯示留言的時(shí)候，不同的執(zhí)行方式，時(shí)間上的一些差別。
下面通過(guò)對(duì)比來(lái)看看幾種方式的用時(shí)對(duì)比。
一，使用存儲(chǔ)過(guò)程分頁(yè)，這種情況又分為兩種方式：
第一種，使用command對(duì)象，如下：
Set Cmd=server.CreateObject("Adodb.Command")
Cmd.ActiveConnection=conn
Cmd.CommandText="ycuu_gb_getmsg"
Cmd.CommandType=4'adCmdStoredProc
cmd.prepared=true'
set??param=Cmd.CreateParameter("@iPageNo",adInteger,1,2,Page)
Cmd.Parameters.Append??param
set??param=Cmd.CreateParameter("@iPageSize",adInteger,1,2,PageSizeConst)
Cmd.Parameters.Append??param
set rs=Cmd.execute
第二種，使用connection對(duì)象的執(zhí)行方法直接執(zhí)行，具體如下：
set rs=conn.execute ("execute ycuu_gb_getmsg "&page&", "&pagesizeConst)
二，不使用存儲(chǔ)過(guò)程，直接使用ADODB.RecordSet的功能來(lái)分頁(yè)，具體代碼如下：
Set rs = Server.CreateObject("ADODB.Recordset")
sql = "Select * FROM Guestbook Order By dateandtime Desc"
rs.open sql,conn,1,1
rs.pagesize = 150'每頁(yè)顯示的留言數(shù)量，
total = rs.RecordCount
mypagesize = rs.pagesize
rs.absolutepage = page
為了更加明顯地顯示出速度，我把每頁(yè)顯示的留言數(shù)量加大到150(事實(shí)上當(dāng)然不會(huì)設(shè)置這么大的數(shù)值啦)。至于我機(jī)器的配置，就省略不說(shuō)了，因?yàn)橹饕撬俣葘?duì)比。
發(fā)現(xiàn)，執(zhí)行的時(shí)候時(shí)間分別如下：
第一種：穩(wěn)定于0.1953125 秒到0.2109375 秒之間，平均值大概是：0.20秒
第二種：穩(wěn)定于0.1716875 秒到0.1857秒之間，平均值大概是：0.177秒
第三種：穩(wěn)定于0.4375 秒到0.4632秒之間，平均值大概是：0.45秒
但是，當(dāng)讀取的記錄條數(shù)為20的時(shí)候，結(jié)果如下：
發(fā)現(xiàn)，執(zhí)行的時(shí)候時(shí)間分別如下：
第一種：穩(wěn)定于.0390625??秒到.0546875??秒之間，平均值大概是：0.045秒
第二種：穩(wěn)定于0.046875??秒到.0546875 秒之間，平均值大概是：0.050秒
第三種：穩(wěn)定于.09375 秒到0.1015625 秒之間，平均值大概是：0.97秒
在這樣看來(lái)，似乎conn.execute和command.execute這兩種方式似乎差別并不大，
而前者的調(diào)用方式好像更加簡(jiǎn)單一點(diǎn)。
同時(shí)，在這里可以看出分頁(yè)的存儲(chǔ)過(guò)程速度確實(shí)比recordset的分頁(yè)速度要快很多。
PS：小弟第一次發(fā)文，嗚嗚嗚，發(fā)現(xiàn)寫一篇好的真難，我以后會(huì)努力的了。希望大家包涵我這次寫得不好。對(duì)了，我還想問(wèn)問(wèn)各位大俠conn.execute和command.execute這兩種方式中那種更加好的，呵呵，因?yàn)槲以诰W(wǎng)上找到的都是后者這種方式執(zhí)行存儲(chǔ)過(guò)程的。不知道為什么不用前面那種那么簡(jiǎn)單的。 |
|
6.SQL存儲(chǔ)過(guò)程在.NET數(shù)據(jù)庫(kù)中的應(yīng)用
一．前言：
存儲(chǔ)過(guò)程（Stored Procedure）是一組為了完成特定功能的SQL語(yǔ)句集，經(jīng)編譯后存儲(chǔ)在數(shù)據(jù)庫(kù)中。用戶通過(guò)指定存儲(chǔ)過(guò)程的名字并給出參數(shù)（如果該存儲(chǔ)過(guò)程帶有參數(shù)）來(lái)執(zhí)行它。存儲(chǔ)過(guò)程是數(shù)據(jù)庫(kù)中的一個(gè)重要對(duì)象，任何一個(gè)設(shè)計(jì)良好的數(shù)據(jù)庫(kù)應(yīng)用程序都應(yīng)該用到存儲(chǔ)過(guò)程。總的來(lái)說(shuō)，存儲(chǔ)過(guò)程具有以下一些優(yōu)點(diǎn)：
◆存儲(chǔ)過(guò)程允許標(biāo)準(zhǔn)組件式編程
◆存儲(chǔ)過(guò)程能夠?qū)崿F(xiàn)較快的執(zhí)行速度
◆存儲(chǔ)過(guò)程能夠減少網(wǎng)絡(luò)流量
◆存儲(chǔ)過(guò)程可被作為一種安全機(jī)制來(lái)充分利用
本文作者將向大家介紹.NET數(shù)據(jù)庫(kù)應(yīng)用程序中存儲(chǔ)過(guò)程的應(yīng)用，以及如何將它與ADO.NET中的SqlDataAdapter對(duì)象、DataSet對(duì)象等結(jié)合使用以提高.NET數(shù)據(jù)庫(kù)應(yīng)用程序的總體性能。
二．系統(tǒng)要求：
開(kāi)發(fā)工具：Visual Studio.NET
數(shù)據(jù)庫(kù)管理系統(tǒng)：SQL Server 2000（其中包含了示例程序所用到的Pubs數(shù)據(jù)庫(kù)）
三．創(chuàng)建一個(gè)簡(jiǎn)單的存儲(chǔ)過(guò)程：
這里我將向大家介紹如何運(yùn)用Visual Studio.NET IDE來(lái)創(chuàng)建存儲(chǔ)過(guò)程。運(yùn)用Visual Studio.NET IDE創(chuàng)建存儲(chǔ)過(guò)程是非常容易和直觀的，你只要在服務(wù)器資源管理器中導(dǎo)向到Pubs數(shù)據(jù)庫(kù)并展開(kāi)節(jié)點(diǎn)，就會(huì)發(fā)現(xiàn)包括存儲(chǔ)過(guò)程在內(nèi)的各種數(shù)據(jù)庫(kù)對(duì)象，如圖1所示。
在存儲(chǔ)過(guò)程節(jié)點(diǎn)上點(diǎn)擊右鍵便可彈出一個(gè)菜單，其中包含了“新建存儲(chǔ)過(guò)程”的命令。新建一個(gè)存儲(chǔ)過(guò)程后，IDE中的代碼編輯窗口便出現(xiàn)如下所示的代碼模板：
CREATE PROCEDURE dbo.StoredProcedure1
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT )
*/
AS
/* SET NOCOUNT ON */
RETURN
上面的代碼模板符合簡(jiǎn)化的創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)法規(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 ]
限于篇幅，各個(gè)參數(shù)的含義在此就不多作介紹了，有興趣的讀者可以參考有關(guān)SQL Server 2000數(shù)據(jù)庫(kù)管理系統(tǒng)的資料。
下面我對(duì)該代碼模板中的各個(gè)語(yǔ)法成分略作介紹。CREATE PROCEDURE聲明創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，后面跟著該存儲(chǔ)過(guò)程的名稱。“/*……*/”中的成分是該存儲(chǔ)過(guò)程的參數(shù)，可包括輸入?yún)?shù)和輸出參數(shù)。AS關(guān)鍵字后面的內(nèi)容是該存儲(chǔ)過(guò)程的主體部分，其中是任何數(shù)量和類型的包含在存儲(chǔ)過(guò)程中的SQL語(yǔ)句。RETURN關(guān)鍵字表明存儲(chǔ)過(guò)程結(jié)束并能返回整型狀態(tài)值給調(diào)用者。下面我們就來(lái)創(chuàng)建一個(gè)簡(jiǎn)單的不帶參數(shù)的存儲(chǔ)過(guò)程并運(yùn)用之：
CREATE PROCEDURE dbo.up_GetPublisherInfo
AS
SELECT pub_id, pub_name, city, state, country
FROM publishers
RETURN
創(chuàng)建以上存儲(chǔ)過(guò)程后，保存之。保存完畢，與該存儲(chǔ)過(guò)程相對(duì)應(yīng)的節(jié)點(diǎn)就會(huì)出現(xiàn)在服務(wù)器資源管理器中。同時(shí)請(qǐng)注意代碼編輯窗口中的CREATE關(guān)鍵字變?yōu)锳LTER關(guān)鍵字了，該關(guān)鍵字是用于更改任何現(xiàn)有的存儲(chǔ)過(guò)程的。要運(yùn)行上述存儲(chǔ)過(guò)程，只要點(diǎn)擊其節(jié)點(diǎn)并在右鍵彈出菜單中選擇“運(yùn)行存儲(chǔ)過(guò)程”，運(yùn)行的結(jié)果圖示如下：
四．創(chuàng)建一個(gè)帶參數(shù)的存儲(chǔ)過(guò)程：
以上我們創(chuàng)建了一個(gè)簡(jiǎn)單的不帶參數(shù)的存儲(chǔ)過(guò)程，而在實(shí)際的應(yīng)用中往往會(huì)用到很多帶有參數(shù)的存儲(chǔ)過(guò)程。帶有參數(shù)的存儲(chǔ)過(guò)程一般是用于更新數(shù)據(jù)或是插入數(shù)據(jù)的。下面我們可以運(yùn)用同樣的操作方法創(chuàng)建一個(gè)帶參數(shù)的存儲(chǔ)過(guò)程：
CREATE PROCEDURE dbo.up_UpdatePublisherInfo
(
@pub_id char (4),
@pub_name varchar (40),
@city varchar (20),
@state char (2),
@country varchar (30)
)
AS
UPDATE publishers
SET pub_name = @pub_name, city = @city, state = @state,
country = @country
WHERE ( pub_id = @pub_id )
RETURN
在上面的創(chuàng)建存儲(chǔ)過(guò)程的代碼中，我們通過(guò)在名稱前添加一個(gè)“@”標(biāo)志來(lái)聲明存儲(chǔ)過(guò)程的局部變量－參數(shù)，同時(shí)還聲明了各個(gè)參數(shù)的類型，確定了各個(gè)參數(shù)的方向值，也即表明該參數(shù)是輸入型的還是輸出型的或者是輸入輸出型的或者是返回值型的。用戶通過(guò)相應(yīng)的存儲(chǔ)過(guò)程名稱以及正確有效的參數(shù)便可調(diào)用該存儲(chǔ)過(guò)程了。還有，你可以通過(guò)運(yùn)用OUTPUT關(guān)鍵字在參數(shù)中添加輸出型的參數(shù)，具體方法請(qǐng)參考上面的語(yǔ)法規(guī)則。輸出型的參數(shù)能返回給調(diào)用者相關(guān)的信息。
上面的存儲(chǔ)過(guò)程能更新publishers表中相應(yīng)出版商的信息。你可以通過(guò)點(diǎn)擊該存儲(chǔ)過(guò)程的節(jié)點(diǎn)，在右鍵彈出菜單中選擇“運(yùn)行存儲(chǔ)過(guò)程”來(lái)執(zhí)行它。一旦執(zhí)行，IDE中便彈出一個(gè)輸入出版商信息的對(duì)話框（如圖3所示）。在該對(duì)話框中填入正確有效的更新信息，注意pub_id的值在原來(lái)的表中必須存在，然后點(diǎn)擊“確定”按鈕便可更新數(shù)據(jù)了。
7.使用SQL存儲(chǔ)過(guò)程要特別注意的問(wèn)題
存儲(chǔ)過(guò)程是一個(gè)運(yùn)行于SQL數(shù)據(jù)庫(kù)之中最核心的事務(wù),它通過(guò)長(zhǎng)駐內(nèi)存的形式,進(jìn)行讀取\處理\寫入最為頻繁處理的數(shù)據(jù).
? ? ASP雖然在微軟的ASPX的侵襲下，仍舊是中小企業(yè)繼續(xù)在用的一種網(wǎng)頁(yè)語(yǔ)言，但是當(dāng)要讀取海量數(shù)據(jù)的時(shí)候,如果仍舊使用普通的SQL進(jìn)行讀取運(yùn)行與寫入,將導(dǎo)致系統(tǒng)資源的嚴(yán)重浪費(fèi)，所以我們?cè)贏SP中使用存儲(chǔ)過(guò)程,以提高數(shù)據(jù)的存取速度,同時(shí)通過(guò)SQL核心的獲取數(shù)據(jù)的方法,可以有效的減少垃圾數(shù)據(jù)(不被立即使用,而且也不備較短的時(shí)間里被使用的數(shù)據(jù))操作.
? ? 在ASP中使用存儲(chǔ)過(guò)程也是相當(dāng)?shù)娜菀?例如以下一個(gè)通過(guò)SQL存儲(chǔ)過(guò)程進(jìn)行分頁(yè)的方法:
存儲(chǔ)過(guò)程:
CREATE procedure p_splitpage? ?
@sql nvarchar(4000), --要執(zhí)行的sql語(yǔ)句
@page int=1,? ? --要顯示的頁(yè)碼
@pageSize int,??--每頁(yè)的大小
@pageCount int=0 out, --總頁(yè)數(shù)
@recordCount int=0 out --總記錄數(shù)
as
set nocount on
declare @p1 int
exec sp_cursoropen @p1 output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@pagecount output
set @recordCount = @pageCount
select @pagecount=ceiling(1.0*@pagecount/@pagesize)
,@page=(@page-1)*@pagesize+1
exec sp_cursorfetch @p1,16,@page,@pagesize??
exec sp_cursorclose @p1
GO
ASP頁(yè)面的內(nèi)容
sql = "Select id, c_s_name from tabNews where deleted<>1 Order By id Desc"
page = cint(page_get)
if page_post<>""then
page = cint(page_post)
end if
if not page > 0 then??
page = 1
end if
pagesize=20’每頁(yè)的條數(shù)
set cmd = server.CreateObject("adodb.command")
cmd.ActiveConnection = conn
cmd.CommandType = 4
cmd.CommandText = "p_SplitPage"
cmd.Parameters.Append cmd.CreateParameter("@sql",8,1, 4000, sql)
cmd.Parameters.Append cmd.CreateParameter("@page",4,1, 4, page)
cmd.Parameters.Append cmd.CreateParameter("@pageSize",4,1, 4, pageSize)
cmd.Parameters.Append cmd.CreateParameter("@pageCount",4,2, 4, pageCount)
cmd.Parameters.Append cmd.CreateParameter("@recordCount",4,2, 4, recordCount)
set rs = cmd.Execute
set rs = rs.NextRecordSet
pageCount = cmd.Parameters("@pageCount").value
recordCount = cmd.Parameters("@recordCount").value
if pageCount = 0 then pageCount = 1
if page>pageCount then??
response.Redirect("?page="&pageCount)??
end if
set rs = cmd.Execute
? ? 我們?nèi)绱司涂梢詫?shí)現(xiàn)對(duì)數(shù)據(jù)的讀取并可以進(jìn)行有效的分頁(yè),但是我們往往會(huì)發(fā)現(xiàn)一個(gè)問(wèn)題,如果我們構(gòu)造的SQL語(yǔ)句如果使用的是select * from tab ...的話，就經(jīng)常出現(xiàn)無(wú)法讀取數(shù)據(jù)的錯(cuò)誤,或者是讀取出來(lái),但是有的數(shù)據(jù)無(wú)法顯示的錯(cuò)誤，經(jīng)過(guò)仔細(xì)的檢查發(fā)現(xiàn),如果是排列在SQL語(yǔ)句的前列的數(shù)據(jù)可以被讀取,而如果不按照SQL讀取出來(lái)的字段進(jìn)行順序讀取,就會(huì)出現(xiàn)數(shù)據(jù)丟失的情況,所以唯一的途徑就是進(jìn)行順序讀取.如:
? ? 對(duì)于select id, newsTitle, newsContent from tabNews where ...的SQL語(yǔ)句,就應(yīng)當(dāng)將所有的數(shù)據(jù)讀取到變量上來(lái)，并且要求是按照SQL語(yǔ)句的順序進(jìn)行讀取，然后這些數(shù)據(jù)就可以自由的使用了.
? ? id = rs("id")
? ? newsTitle = rs("newsTitle")
? ? ...
? ? 分析出現(xiàn)這個(gè)的原因是:SQL數(shù)據(jù)庫(kù)在構(gòu)造虛擬表的時(shí)候就是以一種先進(jìn)先出的原則,把所有的數(shù)據(jù)排列在一個(gè)內(nèi)存段之中,通過(guò)順序的讀取,將數(shù)據(jù)逐一的讀取,而如果跳過(guò)某個(gè)具體的字段獲取下一個(gè)字段的信息,系統(tǒng)就會(huì)將原來(lái)的那個(gè)字段的信息丟失,以釋放內(nèi)存,這是出于系統(tǒng)構(gòu)造的簡(jiǎn)單性和系統(tǒng)的內(nèi)存最低化的要求,所以這樣也保證了有限的內(nèi)存資源得到最充分的發(fā)揮,這也是為什么存儲(chǔ)過(guò)程比普通的SQL要快的原因.
|