《SQL Server 實(shí)用教程》第9章 存儲(chǔ)過程的創(chuàng)建和使用

本章學(xué)習(xí)目標(biāo)了解存儲(chǔ)過程的概念了解使用存儲(chǔ)過程的優(yōu)點(diǎn)了解系統(tǒng)存儲(chǔ)過程的特點(diǎn)及用途掌握創(chuàng)建存儲(chǔ)過程的方法掌握?qǐng)?zhí)行存儲(chǔ)過程的方法掌握查看和修改存儲(chǔ)過程的方法掌握刪除存儲(chǔ)過程的方法9.1概述9.1.1存儲(chǔ)過程的概念在使用Transact-SQL語言編程的過程中，可以將某些需要多次調(diào)用的實(shí)現(xiàn)某個(gè)特定任務(wù)的代碼段編寫成一個(gè)過程，將其保存在數(shù)據(jù)庫中，并由SQLServer服務(wù)器通過過程名來調(diào)用它們，這些過程就叫做存儲(chǔ)過程。存儲(chǔ)過程在創(chuàng)建時(shí)就被編譯和優(yōu)化，調(diào)用一次以后，相關(guān)信息就保存在內(nèi)存中，下次調(diào)用時(shí)可以直接執(zhí)行。9.1.1存儲(chǔ)過程的概念存儲(chǔ)過程有以下特點(diǎn)：存儲(chǔ)過程中可以包含一條或多條Transact-SQL語句。存儲(chǔ)過程可以接受輸入?yún)?shù)并可以返回輸出值。在一個(gè)存儲(chǔ)過程中可以調(diào)用另一個(gè)存儲(chǔ)過程。存儲(chǔ)過程可以返回執(zhí)行情況的狀態(tài)代碼給調(diào)用它的程序。9.1.2存儲(chǔ)過程的優(yōu)點(diǎn)使用存儲(chǔ)過程有很多優(yōu)點(diǎn)，具體如下：實(shí)現(xiàn)了模塊化編程，一個(gè)存儲(chǔ)過程可以被多個(gè)用戶共享和重用。存儲(chǔ)過程具有對(duì)數(shù)據(jù)庫立即訪問的功能。使用存儲(chǔ)過程可以加快程序的運(yùn)行速度。使用存儲(chǔ)過程可以減少網(wǎng)絡(luò)流量。。使用存儲(chǔ)過程可以提高數(shù)據(jù)庫的安全性。9.1.3存儲(chǔ)過程的分類在SQLServer中的存儲(chǔ)過程分為兩類：即系統(tǒng)提供的存儲(chǔ)過程和用戶自定義的存儲(chǔ)過程。系統(tǒng)存儲(chǔ)過程：由系統(tǒng)自動(dòng)創(chuàng)建，系統(tǒng)存儲(chǔ)過程出現(xiàn)在每個(gè)系統(tǒng)定義數(shù)據(jù)庫和用戶定義數(shù)據(jù)庫的sys構(gòu)架中。在SQLServer2005中，可將GRANT、DENY和REVOKE權(quán)限應(yīng)用于系統(tǒng)存儲(chǔ)過程。用戶自定義存儲(chǔ)過程：是指封裝了可重用代碼的模塊或例程，由用戶創(chuàng)建，能完成某一特定的功能?？梢越邮茌斎?yún)?shù)，返回輸出參數(shù)。9.1.3存儲(chǔ)過程的分類在SQLServer2005中，用戶自定義存儲(chǔ)過程有兩種類型：Transact-SQL存儲(chǔ)過程和CLR存儲(chǔ)過程。Transact-SQL存儲(chǔ)過程：是指保存的Transact-SQL語句集合。CLR存儲(chǔ)過程：是指對(duì)Microsoft.NETFramework公共語言運(yùn)行時(shí)(CLR)方法的引用，可以接受和返回用戶提供的參數(shù)。9.2創(chuàng)建存儲(chǔ)過程在SQLServer中，可以使用兩種方法創(chuàng)建存儲(chǔ)過程：當(dāng)創(chuàng)建存儲(chǔ)過程時(shí)，需要確定存儲(chǔ)過程的三個(gè)組成部分：所有的輸入?yún)?shù)以及傳給調(diào)用者的輸出參數(shù)。被執(zhí)行的針對(duì)數(shù)據(jù)庫的操作語句，包括調(diào)用其他存儲(chǔ)過程的語句。返回給調(diào)用者的狀態(tài)值，以指明調(diào)用是成功還是失敗。9.2.1使用SQLServer管理控制臺(tái)創(chuàng)建存儲(chǔ)過程在SQLServer管理控制臺(tái)中，選擇指定的服務(wù)器和數(shù)據(jù)庫，展開數(shù)據(jù)庫中的“可編程性”文件夾，右擊其中的“存儲(chǔ)過程”，在彈出的快捷菜單中選擇“新建存儲(chǔ)過程…”選項(xiàng)。例9-2-1：創(chuàng)建一個(gè)名稱為“StuInfo”的存儲(chǔ)過程，要求完成以下功能：在T_STUDENT表中查詢05541班學(xué)生的學(xué)號(hào)、姓名、性別、出生日期和政治面貌五個(gè)字段的內(nèi)容。9.2.2使用Transact-SQL語句創(chuàng)建存儲(chǔ)過程可以使用CREATEPROCEDURE命令創(chuàng)建存儲(chǔ)過程，考慮下列幾個(gè)事項(xiàng)：CREATEPROCEDURE語句不能與其他SQL語句在單個(gè)批處理中組合使用。必須具有數(shù)據(jù)庫的CREATEPROCEDURE權(quán)限。只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建存儲(chǔ)過程。不要?jiǎng)?chuàng)建任何使用sp_作為前綴的存儲(chǔ)過程。9.2.2使用Transact-SQL語句創(chuàng)建存儲(chǔ)過程CREATEPROCEDURE的語法形式如下：CREATE{PROC|PROCEDURE}[schema_name.]procedure_name[{@parameter[type_schema_name.]data_type}[VARYING][=default][OUT|OUTPUT]][,...n][WITHENCRYPTION]AS{<sql_statement>[;][...n]}[;]<sql_statement>::={[BEGIN]statements[END]}9.2.2使用Transact-SQL語句創(chuàng)建存儲(chǔ)過程其中，各參數(shù)的意義如下：schema_name：過程所屬架構(gòu)的名稱。procedure_name：新存儲(chǔ)過程的名稱。@parameter：過程中的參數(shù)。[type_schema_name.]data_type：參數(shù)以及所屬架構(gòu)的數(shù)據(jù)類型。VARYING：指定作為輸出參數(shù)支持的結(jié)果集。僅適用于cursor參數(shù)。9.2.2使用Transact-SQL語句創(chuàng)建存儲(chǔ)過程default：參數(shù)的默認(rèn)值。OUTPUT：指示參數(shù)是輸出參數(shù)。ENCRYPTION：將CREATEPROCEDURE語句的原始文本加密。<sql_statement>：要包含在過程中的一個(gè)或多個(gè)Transact-SQL語句。9.2.2使用Transact-SQL語句創(chuàng)建存儲(chǔ)過程例9-2-2：創(chuàng)建一個(gè)存儲(chǔ)過程StuScoreInfo，完成的功能是在表T_STUDENT、表T_COURSE和表t_SCORE中查詢以下字段：班級(jí)、學(xué)號(hào)、姓名、性別、課程名稱、考試分?jǐn)?shù)。例9-2-2：創(chuàng)建一個(gè)帶有參數(shù)的存儲(chǔ)過程Stu_Info，該存儲(chǔ)過程根據(jù)傳入的學(xué)生編號(hào)，在T_STUDENT中查詢此學(xué)生的信息。9.2.2使用Transact-SQL語句創(chuàng)建存儲(chǔ)過程例9-2-3：創(chuàng)建一個(gè)帶有參數(shù)的存儲(chǔ)過程Stu_Age，該存儲(chǔ)過程根據(jù)傳入的學(xué)生編號(hào)，在T_STUDENT中計(jì)算此學(xué)生的年齡，并根據(jù)程序的執(zhí)行結(jié)果返回不同的值，程序執(zhí)行成功，返回整數(shù)0，如果執(zhí)行出錯(cuò)，則返回錯(cuò)誤號(hào)。9.3執(zhí)行存儲(chǔ)過程存儲(chǔ)過程創(chuàng)建成功后，保存在數(shù)據(jù)庫中。在SQLServer中可以使用EXECUTE命令來直接執(zhí)行存儲(chǔ)過程，語法形式如下：[[EXEC[UTE]]{[@return_status=]{procedure_name|@procedure_name_var}

[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}[,...n]]}]9.3執(zhí)行存儲(chǔ)過程其中，各選項(xiàng)的含義如下：EXECUTE：執(zhí)行存儲(chǔ)過程的命令關(guān)鍵字。@return_status：是一個(gè)可選的整型變量，保存存儲(chǔ)過程的返回狀態(tài)。procedure_name：指定執(zhí)行的存儲(chǔ)過程的名稱。@procedure_name_var：是局部定義變量名，代表存儲(chǔ)過程名稱。@parameter：是在創(chuàng)建存儲(chǔ)過程時(shí)定義的過程參數(shù)。9.3執(zhí)行存儲(chǔ)過程例9-3-1：執(zhí)行前面創(chuàng)建的StuInfo存儲(chǔ)過程，它是一個(gè)無參的存儲(chǔ)過程。注意：如果省略EXECUTE關(guān)鍵字，則存儲(chǔ)過程必須是批處理中的第一條語句，否則會(huì)出錯(cuò)。例9-3-2：執(zhí)行存儲(chǔ)過程StuScoreInfo。9.3執(zhí)行存儲(chǔ)過程例9-3-3：執(zhí)行存儲(chǔ)過程Stu_Info，該存儲(chǔ)過程有一個(gè)輸入?yún)?shù)“學(xué)號(hào)”，在執(zhí)行時(shí)需要傳入一個(gè)學(xué)號(hào)值。例9-3-4：執(zhí)行存儲(chǔ)過程Stu_Age，該存儲(chǔ)過程有一個(gè)輸入?yún)?shù)“學(xué)號(hào)”，另外，還有一個(gè)輸出參數(shù)@Age。存儲(chǔ)過程執(zhí)行完后，有一個(gè)返回的狀態(tài)值，這個(gè)值可以從變量@ErrorValue得到。9.4查看和修改存儲(chǔ)過程9.4.1查看存儲(chǔ)過程1．使用SQLServer管理控制臺(tái)查看用戶創(chuàng)建的存儲(chǔ)過程在SQLServer管理控制臺(tái)中，選擇指定的服務(wù)器和數(shù)據(jù)庫，展開數(shù)據(jù)庫中的“可編程性”文件夾，單擊其中的“存儲(chǔ)過程”，在右邊的窗口中就會(huì)顯示出當(dāng)前數(shù)據(jù)庫中的所有存儲(chǔ)過程。9.4.1查看存儲(chǔ)過程2．使用系統(tǒng)存儲(chǔ)過程查看用戶創(chuàng)建的存儲(chǔ)過程sp_help：用于顯示存儲(chǔ)過程的參數(shù)及其數(shù)據(jù)類型sp_help[[@objname=]name]參數(shù)name為要查看的存儲(chǔ)過程的名稱。9.4.1查看存儲(chǔ)過程sp_helptext：用于顯示存儲(chǔ)過程的源代碼sp_helptext[[@objname=]name]參數(shù)name為要查看的存儲(chǔ)過程的名稱。9.4.1查看存儲(chǔ)過程sp_depends：用于顯示和存儲(chǔ)過程相關(guān)的數(shù)據(jù)庫對(duì)象sp_depends[@objname=]’object’參數(shù)object為要查看依賴關(guān)系的存儲(chǔ)過程的名稱。9.4.1查看存儲(chǔ)過程sp_stored_procedures：用于返回當(dāng)前數(shù)據(jù)庫中的存儲(chǔ)過程列表sp_stored_procedures[[@sp_name=]'name'][,[@sp_owner=]'owner'][,[@sp_qualifier=]'qualifier']其中，[@sp_name=]'name'用于指定返回目錄信息的過程名；[@sp_owner=]'owner'用于指定過程所有者的名稱；[@qualifier=]'qualifier'用于指定過程限定符的名稱。9.4.1查看存儲(chǔ)過程例9-4-1：使用系統(tǒng)存儲(chǔ)過程查看Stu_Age存儲(chǔ)過程的參數(shù)及其數(shù)據(jù)類型。例9-4-2：使用系統(tǒng)存儲(chǔ)過程查看StuScoreInfo存儲(chǔ)過程的相關(guān)的數(shù)據(jù)庫對(duì)象。9.4.2修改存儲(chǔ)過程1．使用SQLServer管理控制臺(tái)修改存儲(chǔ)過程使用SQLServer管理控制臺(tái)可以很方便地修改存儲(chǔ)過程的定義。在SQLServer管理控制臺(tái)中，展開存儲(chǔ)過程，右擊要修改的存儲(chǔ)過程，從彈出的快捷菜單中選擇“修改”選項(xiàng)，則會(huì)出現(xiàn)與創(chuàng)建存儲(chǔ)過程時(shí)類似的窗口。在該窗口中，可以直接修改定義該存儲(chǔ)過程的Transact-SQL語句。9.4.2修改存儲(chǔ)過程2．使用Transact-SQL語句修改存儲(chǔ)過程使用ALTERPROCEDURE語句可以更改存儲(chǔ)過程，但不會(huì)更改權(quán)限，也不影響相關(guān)的存儲(chǔ)過程或觸發(fā)器。其語法形式如下：ALTER{PROC|PROCEDURE}[schema_name.]procedure_name[{@parameter[type_schema_name.]data_type}[VARYING][=default][[OUT[PUT]][,...n][WITHENCRYPTION]ASsql_statement[...n]9.4.2修改存儲(chǔ)過程修改存儲(chǔ)過程時(shí)，應(yīng)該注意以下幾點(diǎn)：如果原來的過程定義是使用WITHENCRYPTION創(chuàng)建的，那么只有在ALTERPROCEDURE中也包含這個(gè)選項(xiàng)時(shí)，這個(gè)選項(xiàng)才有效。每次只能修改一個(gè)存儲(chǔ)過程。用ALTERPROCEDURE更改的存儲(chǔ)過程的權(quán)限保持不變。9.4.2修改存儲(chǔ)過程例9-4-3：修改前面創(chuàng)建的Stu_Info存儲(chǔ)過程，使之完成以下功能：根據(jù)傳入的學(xué)號(hào)，在表T_STUDENT、表T_COURSE和表t_SCORE中查詢此學(xué)生的班級(jí)、學(xué)號(hào)、姓名、性別、考試課程名稱和考試分?jǐn)?shù)。9.5重命名和刪除存儲(chǔ)過程9.5.1重命名存儲(chǔ)過程1．使用SQLServer管理控制臺(tái)修改存儲(chǔ)過程名稱通過SQLServer管理控制臺(tái)可以修改存儲(chǔ)過程的名稱。方法是：在SQLServer管理控制臺(tái)中，右擊要操作的存儲(chǔ)過程名稱，從彈出的快捷菜單中選擇“重命名”選項(xiàng)，當(dāng)存儲(chǔ)過程名稱變成可輸入狀態(tài)時(shí)，就可以直接修改該存儲(chǔ)過程的名稱。9.5.1重命名存儲(chǔ)過程2．使用系統(tǒng)存儲(chǔ)過程修改存儲(chǔ)過程名稱修改存儲(chǔ)過程的名稱也可以使用系統(tǒng)存儲(chǔ)過程sp_rename，其語法形式如下：sp_rename原存儲(chǔ)過程名稱，新存儲(chǔ)過程名稱例9-5-1：使用系統(tǒng)存儲(chǔ)過程將Stu_Info存儲(chǔ)過程的名稱修改為Stu_Info_SCORE。9.5.2刪除存儲(chǔ)過程1．使用SQLServer管理控制臺(tái)刪除存儲(chǔ)過程在SQLServer管理控制臺(tái)中，右擊要?jiǎng)h除的存儲(chǔ)過程，從彈出的快捷菜單中選擇“刪除”選項(xiàng)，會(huì)彈出“刪除對(duì)象”對(duì)話框。在該對(duì)話框中，單擊“確定”按鈕，即可完成刪除操作。9.5.2刪除存儲(chǔ)過程2．使用Transact-SQL語句刪除存儲(chǔ)過程刪除存儲(chǔ)過程也可以使用Transact-SQL語言中的DROP命令，DROP命令可以將一個(gè)或者多個(gè)存儲(chǔ)過程或者存儲(chǔ)過程組從當(dāng)前數(shù)據(jù)庫中刪除，其語法形式如下：DROP{PROC|PROCEDURE}{[schema_name.]proced