第9章存儲過程

1、南昌工程學(xué)院計算機系第第9章章 存儲過程存儲過程報告報告 人：譚德坤人：譚德坤Email:南昌工程學(xué)院計算機系學(xué)習要點：存儲過程的基本概念創(chuàng)建存儲過程 管理存儲過程等內(nèi)容 9.1 存儲過程的基本概念 9.2 創(chuàng)建存儲過程 9.3 執(zhí)行存儲過程 9.4 存儲過程的參數(shù) 9.5 存儲過程的返回值9.6 存儲過程的查看、修改和刪除9.1 存儲過程概述存儲過程概述 9.1.1 存儲過程的基本概念 存儲過程是獨立存在于表之外的數(shù)據(jù)庫對象，由被編譯在一起的一組Transact-SQL語句組成。它可以被客戶調(diào)用，也可以被另一個存儲過程或觸發(fā)器調(diào)用，它的參數(shù)可以被傳遞，它的出錯代碼也可以被檢驗。 在SQL S

2、erver中，使用存儲過程的優(yōu)點如下： 存儲過程在服務(wù)器端運行，執(zhí)行速度快。 存儲過程執(zhí)行一次后，其執(zhí)行規(guī)劃就駐留在高速緩沖存儲器，在以后的操作中，只需從高速緩沖存儲器中調(diào)用已編譯好的二進制代碼執(zhí)行，提高了系統(tǒng)性能。 確保數(shù)據(jù)庫的安全。使用存儲過程可以完成所有的數(shù)據(jù)庫操作，并可通過編程方式控制上述操作對數(shù)據(jù)庫信息訪問的權(quán)限。 自動完成需要預(yù)先執(zhí)行的任務(wù)。存儲過程可以在系統(tǒng)啟動時自動執(zhí)行，而不必在系統(tǒng)啟動后再進行手工操作，大大方便了用戶的使用，可以自動完成一些需要預(yù)先執(zhí)行的任務(wù)。9.1.2 存儲過程的類型存儲過程的類型 SQL Server支持五種類型的存儲過程：系統(tǒng)存儲過程、本地存儲過程、臨時

3、存儲過程、遠程存儲過程和擴展存儲過程。在不同情況下需要執(zhí)行不同的存儲過程。1系統(tǒng)存儲過程系統(tǒng)存儲過程 系統(tǒng)存儲過程是由系統(tǒng)提供的存儲過程，可以作為命令執(zhí)行各種操作。系統(tǒng)存儲過程定義在系統(tǒng)數(shù)據(jù)庫master中，其前綴是sp_ 。2本地存儲過程本地存儲過程 本地存儲過程是指在用戶數(shù)據(jù)庫中創(chuàng)建的存儲過程，這種存儲過程完成特定數(shù)據(jù)庫操作任務(wù)，其名稱不能以sp_為前綴。3臨時存儲過程臨時存儲過程 臨時存儲過程屬于本地存儲過程。如果本地存儲過程的名稱前面有一個“#”，該存儲過程就稱為局部臨時存儲過程，這種存儲過程只能在一個用戶會話中使用；如果本地存儲過程的名稱前有兩個“#”，該過程就是全局臨時存儲過程，這

4、種存儲過程可以在所有用戶會話中使用。4遠程存儲過程遠程存儲過程 遠程存儲過程指從遠程服務(wù)器上調(diào)用的存儲過程。5擴展存儲過程擴展存儲過程 在SQL Server環(huán)境之外執(zhí)行的動態(tài)鏈接庫稱為擴展存儲過程，其前綴是sp_。使用時需要先加載到SQL Server系統(tǒng)中，并且按照使用存儲過程的方法執(zhí)行。9.2 存儲過程的創(chuàng)建存儲過程的創(chuàng)建 默認情況下，用戶創(chuàng)建的存儲過程歸數(shù)據(jù)庫所有者擁有，數(shù)據(jù)庫的所有者可以把許可授權(quán)給其他用戶。 存儲過程由CREATE PROCEDURE語句創(chuàng)建，存儲過程的定義包括：過程名和參數(shù)的說明以及過程體，即包含執(zhí)行存儲過程操作的Transact-SQL語句。要使用存儲過程，首先

5、要創(chuàng)建一個存儲過程。9.2.1 使用使用CREATE PROCEDURE語句創(chuàng)建語句創(chuàng)建 1語法格式 CREATE PROC EDURE procedure_ name ;number parameter data_type VARYING =default OUTPUT n WITH RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION FOR REPLICATION AS sql_statement n2參數(shù)說明參數(shù)說明procedure_name：新存儲過程的名稱。過程名必須符合標識符規(guī)則，且對于數(shù)據(jù)庫及其所有者必須惟一。 如果要創(chuàng)建局部臨時過程，

6、可以在procedure_name前面加一個編號符#，要創(chuàng)建全局臨時過程，可以在procedure_name前面加兩個編號符#procedure_name。完整的名稱包括（#或#）不能超過128個字符。指定過程所有者的名稱是可選的。number：是可選的整數(shù)，用來對同名的過程分組，以便用一條DROP PROCEDURE語句即可將同組的過程一起除去。parameter：過程中的參數(shù)。在CREATE PROCEDURE語句中可以聲明一個或多個參數(shù)。用戶必須在執(zhí)行過程時提供每個所聲明參數(shù)的值（除非定義了該參數(shù)的默認值）。存儲過程最多可以有2100個參數(shù)。使用符號作為第一個字符來指定參數(shù)名稱。參數(shù)名稱

7、必須符合標識符的規(guī)則。每個過程的參數(shù)僅用于該過程本身；相同的參數(shù)名稱可以用在其他過程中。默認情況下，參數(shù)只能代替常量，而不能用于代替表名、列名或其他數(shù)據(jù)庫對象的名稱。data_type：參數(shù)的數(shù)據(jù)類型。所有數(shù)據(jù)類型（包括text、ntext和image）均可以用作存儲過程的參數(shù)。不過，cursor數(shù)據(jù)類型只能用于OUTPUT語句的參數(shù)。如果指定的數(shù)據(jù)類型為cursor，也必須同時指定VARYING和OUTPUT關(guān)鍵字。 說明：對于可以是cursor數(shù)據(jù)類型的輸出參數(shù)，沒有最大數(shù)目的限制。VARYING：指定作為輸出參數(shù)支持的結(jié)果集（由存儲過程動態(tài)構(gòu)造，內(nèi)容可以變化）。僅適用于游標參數(shù)。defa

8、ult：參數(shù)的默認值。如果定義了默認值，不必指定該參數(shù)的值即可執(zhí)行過程。默認值必須是常量或NULL。如果過程將對該參數(shù)使用LIKE關(guān)鍵字，那么默認值中可以包含通配符（、_、 和）。OUTPUT：表明參數(shù)是返回參數(shù)。該選項的值可以返回給EXECUTE。使用OUTPUT參數(shù)可將信息返回給調(diào)用過程。text、ntext和image類型數(shù)據(jù)可用作OUTPUT參數(shù)。使用OUTPUT關(guān)鍵字的輸出參數(shù)可以走游標占位符。n：表示最多可以指定2100個參數(shù)的占位符。RECOMPILE | ENCRYPTION |（RECOMPILE，ENCRYPTION）： RECOMPILE表明SQL Server不會緩存該

9、過程的計劃，該過程將在運行時重新編譯。在使用非典型值或臨時值而不希望覆蓋緩存在內(nèi)存中的執(zhí)行計劃時，應(yīng)該使用RECOMPILE選項。ENCRYPTION表示SQL Server加密syscomments表中包含CREATE PROCEDURE語句文本的條目。使用ENCRYPTION可防止將過程作為SQL Server復(fù)制的一部分發(fā)布。 說明：在升級過程中，SQL Server利用存儲在syscomments中的加密注釋來重新創(chuàng)建加密過程。 FOR REPLICATION：指定不能在訂閱服務(wù)器上執(zhí)行為復(fù)制創(chuàng)建的存儲過程。使用FOR REPLICATION選項創(chuàng)建的存儲過程可用作存儲過程篩選，且只能

10、在復(fù)制過程中執(zhí)行。該選項不能和WITH RECOMPILE選項一起使用。 AS：指定過程要執(zhí)行的操作。sql_statement：過程中要包含的任意數(shù)目和類型的Transact-SQL語句，但有一些限制。AS sql_statement n：其中的n是表示此過程可以包含多條Transact-SQL語句的占 位符。3注意事項注意事項 用戶定義的存儲過程只能在當前數(shù)據(jù)庫中創(chuàng)建（臨時過程除外，臨時過程總是在tempdb中創(chuàng)建）。 成功執(zhí)行CREATE PROCEDURE語句后，過程名稱存儲在sysobjects系統(tǒng)表中，而CREATEPROCEDURE語句的文本存儲在syscomments中。 自動

11、執(zhí)行存儲過程。SQL Server啟動時可以自動執(zhí)行一個或多個存儲過程。這些存儲過程必須由系統(tǒng)管理員在master數(shù)據(jù)庫中創(chuàng)建，并在sysadmin固定服務(wù)器角色下作為后臺過程執(zhí)行。這些過程不能有任何輸入?yún)?shù)。 sql_statement的限制。除了SET SHOWPLAN TEXT和SET SHOWPLAN ALL外，其他SET語句均可在存儲過程內(nèi)使用。 必須使用對象所有者名對數(shù)據(jù)庫對象進行限定的語句有：CREATE TABLE、ALTERTABLE、DROP TABLE、TRUNCATE TABLE、CREATE INDEX、DROP INDEX、UPDATESTATISTICS及DBCC

12、語句。 權(quán)限。CREATE PROCEDURE的權(quán)限默認授予sysadmin固定服務(wù)器角色成員、db_owner和db_ddladmin固定數(shù)據(jù)庫角色成員。sysadmin固定服務(wù)器角色成員和dlowner固定數(shù)據(jù)庫角色成員可以將CREATE PROCEDURE權(quán)限轉(zhuǎn)讓給其他用戶。9.2.2 使用企業(yè)管理器創(chuàng)建使用企業(yè)管理器創(chuàng)建 （1）打開企業(yè)管理器，展開服務(wù)器組，并展開相應(yīng)的服務(wù)器。 （2）打開“數(shù)據(jù)庫”文件夾，并打開要創(chuàng)建存儲過程的數(shù)據(jù)庫。 （3）選擇“存儲過程”選項，右擊鼠標，執(zhí)行“新建存儲過程”命令，打開創(chuàng)建存儲過程對話框如圖9-2所示。圖圖9-2 9-2 創(chuàng)建存儲過程對話框創(chuàng)建存儲過

13、程對話框 （4）在“文本”列表框中顯示了CREATE PROCEDURE語句的框架，可以修改要創(chuàng)建的存儲過程的名稱，然后加入存儲過程所包含的SQL語句。 （5）單擊“檢查語法”按鈕，可以檢查創(chuàng)建存儲過程的 SQL語句的語法是否正確。 （6）如果要將其設(shè)置為下次創(chuàng)建存儲過程的模板，可單擊“另存為模板”按鈕。 （7）完成后，單擊“確定”按鈕即可創(chuàng)建一個存儲過程。9.2.3 使用向?qū)?chuàng)建使用向?qū)?chuàng)建 （1）在企業(yè)管理器中，執(zhí)行“工具”下拉菜單中的“向?qū)А泵睿蜷_“選擇向?qū)А睂υ捒颉?（2）在“數(shù)據(jù)庫”文件夾選擇“創(chuàng)建存儲過程”向?qū)?，單擊“確定”按鈕，出現(xiàn)“創(chuàng)建存儲過程向?qū)А睔g迎對話框。 （3）單擊

14、“下一步”按鈕，出現(xiàn)選擇數(shù)據(jù)庫對話框，如圖9-3所示。 （4）在“數(shù)據(jù)庫名稱”下拉列表中選擇數(shù)據(jù)庫后，單擊“下一步”按鈕，出現(xiàn)選擇存儲過程對話框，如圖9-4所示。圖圖9-3 9-3 選擇數(shù)據(jù)庫對話框選擇數(shù)據(jù)庫對話框圖圖9-4 9-4 選擇存儲過程對話框選擇存儲過程對話框 （5）單擊“下一步”按鈕，出現(xiàn)完成創(chuàng)建存儲過程對話框。若單擊“完成”按鈕，即可完成存儲過程的創(chuàng)建。 （6）單擊“編輯”按鈕，可編輯存儲過程，如圖9-6所示。圖圖9-5 9-5 完成創(chuàng)建存儲過程對話框完成創(chuàng)建存儲過程對話框圖圖9-6 9-6 編輯存儲過程對話框編輯存儲過程對話框 （7）單擊“編輯SQL”按鈕，即可打開“編輯存儲過

15、程SQL”對話框，其中的列表框顯示了創(chuàng)建該存儲過程的Transact-SQL語句?？梢栽谝延械腡ransact-SQL語句的基礎(chǔ)上進行編輯，也可以單擊“分析”按鈕來執(zhí)行語法檢查。如圖9-7所示。圖圖9-7 “9-7 “編輯存儲過程編輯存儲過程SQL”SQL”對話框?qū)υ捒?（8）單擊“確定”按鈕，返回到圖9-5所示的對話框，再單擊“確定”按鈕即可。9.2 存儲過程的創(chuàng)建存儲過程的創(chuàng)建 創(chuàng)建步驟：創(chuàng)建步驟： 一般來說，創(chuàng)建一個存儲過程應(yīng)按照以下步驟進行： 編寫SQL語句。 測試SQL語句是否正確，并能實現(xiàn)功能要求。 若得到的結(jié)果數(shù)據(jù)符合預(yù)期要求，則按照存儲過程的語法，創(chuàng)建該建存儲過程。 執(zhí)行該存儲

16、過程，驗證其正確性。9.2 存儲過程的創(chuàng)建存儲過程的創(chuàng)建示例：示例：1、創(chuàng)建不帶參數(shù)的存儲過程存儲過程不使用任何參數(shù)，執(zhí)行時不要求傳遞參數(shù)。只是返回一個結(jié)果集合?！纠?1】創(chuàng)建一個存儲過程，將學(xué)生的姓名、學(xué)號、專業(yè)、課程名和相應(yīng)成績顯示出來。 見9.2.sql9.2 存儲過程的創(chuàng)建存儲過程的創(chuàng)建2、創(chuàng)建帶有參數(shù)的存儲過程可以給存儲過程傳遞參數(shù)，讓它根據(jù)參數(shù)的內(nèi)容進行相應(yīng)的操作，返回結(jié)果集?！纠?2】根據(jù)學(xué)生的學(xué)號和課程，求出他的成績，用存儲過程實現(xiàn)。 見9.3.sql 9.2 存儲過程的創(chuàng)建存儲過程的創(chuàng)建 說明：說明： 為防止別的用戶看到自己所編寫的存儲過程的腳本，創(chuàng)建存儲過程時可以使用參

17、數(shù)WITH ENCRYPTION。 如果用戶不給出傳遞給該存儲過程所需參數(shù)中的任何一個，將會產(chǎn)生錯誤。解決這種問題的一種方法是建立使用默認值的參數(shù)。要做到這一點，用戶必須在參數(shù)的定義之后加上等號，并在等號后面寫出默認值。 對于帶有默認值的存儲過程，如果執(zhí)行該存儲過程時不提供任何參數(shù)，則執(zhí)行返回的結(jié)果集將是空集，而不會產(chǎn)生錯誤。9.2 存儲過程的創(chuàng)建存儲過程的創(chuàng)建3、創(chuàng)建帶有輸出參數(shù)的存儲過程通過在創(chuàng)建存儲過程的語句中定義輸出參數(shù)定義輸出參數(shù)，可以創(chuàng)建帶輸出參數(shù)的存儲過程。執(zhí)行該存儲過程，可以返回一個或多個值。 【例例93】根據(jù)專業(yè)班級名統(tǒng)計某個班級不及格的學(xué)生的人數(shù)，用存儲過程實現(xiàn)。 見9.6

18、.sql 9.3 執(zhí)行存儲過程執(zhí)行存儲過程 1語法格式 EXECUTE return_status= procedure_name ;number | procedure_name_var parameter= value | variable OUTPUT | DEFAULT ,n WITH RECOMPILE 2參數(shù)說明參數(shù)說明 return_status：一個可選的整型變量，保存存儲過程的返回狀態(tài)。此變量在用于EXECUTE語句前，必須在批處理、存儲過程或函數(shù)中已聲明。procedure_name：調(diào)用的存儲過程名稱。 number：可選的整數(shù)，用于將相同名稱的過程進行組合，使得它們可以

19、用一句DROP PROCEDURE語句除去。該參數(shù)不能用于擴展存儲過程。在同一應(yīng)用程序中使用的過程一般都以該方式組合。procedure_name_var：局部定義變量名，代表存儲過程名稱。 parameter：過程參數(shù)，在CREATE PROCEDURE語句中定義。參數(shù)名稱前必須加上符號。在以“parameter_name=value”格式使用時，參數(shù)名稱和常量不一定按照CREATEPROCEDURE語句中定義的順序出現(xiàn)。但是，如果有一個參數(shù)使用“parameter_name=value”格式，則其他所有參數(shù)都必須使用這種格式。 默認情況下，參數(shù)可為空。如果傳遞NULL參數(shù)值，且該參數(shù)用于C

20、REATE或ALTER TABLE語句中不允許為NULL的列（例如，插入至不允許為NULL的列），SQL Server就會報錯。為避免將NULL參數(shù)值傳遞給不允許為NULL的列，可以在過程中添加程序設(shè)計邏輯或采用默認值（使用CREATE或ALTER TABLE語句中的DEFAULT關(guān)鍵字）。 value：過程中參數(shù)的值。如果參數(shù)名稱沒有指定，參數(shù)值必須以CREATE PROCEDURE語句中定義的順序給出。variable：用來保存參數(shù)或者返回參數(shù)的變量。 OUTPUT：指定存儲過程必須返回一個參數(shù)。該存儲過程的匹配參數(shù)也必須由關(guān)鍵字OUTPUT創(chuàng)建。使用游標變量作參數(shù)時使用該關(guān)鍵字。 DEF

21、AULT：根據(jù)過程的定義提供參數(shù)的默認值。當過程需要的參數(shù)值沒有事先定義好默認值或缺少參數(shù)或指定了DEFAULT關(guān)鍵字，就會出錯。 n：占位符，表示在它前面的項目可以多次重復(fù)執(zhí)行。例如，EXCUTE語句可以指定一個或者多個parameter、value或variable。WITH RECOMPILE：強制編譯新的計劃。如果所提供的參數(shù)為非典型參數(shù)或者數(shù)據(jù)有很大的改變，使用該選項，在以后的程序執(zhí)行中使用更改過的計劃。該選項不能用于擴展存儲過程。建議盡量少使用該選項，因為它消耗較多的系統(tǒng)資源。3注意事項注意事項 如果存儲過程名的前三個字符為sp_，SQL Server會在Master數(shù)據(jù)庫中尋找該

22、過程。如果沒能找到合法的過程名，SQL Server會尋找所有者名稱為dbo的過程。 參數(shù)可以通過value或parameter name：value提供。 執(zhí)行存儲過程時，若語句是批處理中的第一個語句，則不一定要指定EXECUTE關(guān)鍵字。9.3 執(zhí)行存儲過程執(zhí)行存儲過程 3自動執(zhí)行存儲過程 SQL Server在啟動時可以自動運行一個或多個存儲過程。 如果需要SQL Server在每次啟動時自動運行某個存儲過程，可以使用系統(tǒng)存儲過程sp_procoption進行設(shè)置。 使用系統(tǒng)存儲過程sp_procoption可以將master數(shù)據(jù)庫中的現(xiàn)有存儲過程設(shè)置為自動執(zhí)行存儲過程或解除自動執(zhí)行。sp

23、_procoption的語法格式為： sp_procoption ProcName = procedure_name , OptionName = option , OptionValue = value 見9.8.sql 9.9.sql 9.4 存儲過程的參數(shù)存儲過程的參數(shù) 9.4.1 參數(shù)傳遞方式 1順序法 在傳遞參數(shù)時，使傳遞的參數(shù)和定義時的參數(shù)順序一致，對于使用默認值的參數(shù)可以用DEFAULT值代替。2提示法提示法 在傳遞參數(shù)時，采用提示的方法，如“class_id =200202”的形式，此時，各個參數(shù)的順序可以任意排列。 9.4.2 使用默認參數(shù)使用默認參數(shù) 創(chuàng)建存儲過程時，可以為

24、參數(shù)提供一個默認值，默認值必須為常量或者NULL。 9.4.3 使用帶使用帶OUTPUT的返回參的返回參數(shù)數(shù) 在創(chuàng)建存儲過程時，可以定義返回參數(shù)。在執(zhí)行存儲過程時，可以將結(jié)果返回給返回參數(shù)。9.5 存儲過程的返回值存儲過程的返回值 存儲過程在執(zhí)行后都會返回一個整型值。如果執(zhí)行成功，則返回0；否則返回199之間的數(shù)值。 9.5.1 RETURN語句語句 1語法 RETURN 整數(shù)表達式 2功能 從查詢或過程中無條件退出。RETURN語句可在任何時候從過程、批處理或語句塊中退出，不執(zhí)行位于RETURN之后的語句。3說明說明 整數(shù)表達式：返回的整型值。存儲過程可以給調(diào)用過程或應(yīng)用程序返回整型值。 在

25、建立存儲過程的時候，需要定義任意的出錯條件，并把它們與整型的出錯代碼聯(lián)系起來。 用于存儲過程時，RETURN不能返回空值。如果過程試圖返回空值，系統(tǒng)將生成警告信息并返回0值。 9.5.2 捕獲存儲過程的返回值捕獲存儲過程的返回值 若要正確接收存儲過程的返回值，可使用EXECUTE語句。 1語法 EXECUTE return_status=procedure_name2功能功能 執(zhí)行存儲過程，將RETURN語句返回的值送狀態(tài)變量return_status。3說明說明 在執(zhí)行EXECUTE語句之前，首先要聲名變量return_status。9.6 存儲過程的查看、修改和刪存儲過程的查看、修改和刪除

26、除 9.6.1 存儲過程的查看 1使用sp_helptext查看存儲過程 EXEC sp_helptext test_ret 2使用企業(yè)管理器 （1）打開企業(yè)管理器，展開服務(wù)器組，并展開相應(yīng)的服務(wù)器； （2）打開數(shù)據(jù)庫文件夾，然后選擇存儲過程所在的數(shù)據(jù)庫“student”； （3）打開“存儲過程”文件夾，在右側(cè)詳細信息窗格中右擊存儲過程add_class，執(zhí)行“屬性”命令，打開“存儲過程屬性”對話框，如圖9-13所示；圖圖9-13 9-13 存儲過程屬性對話框存儲過程屬性對話框 （4）可以在此對話框中，直接修改存儲過程的定義，也可以設(shè)置存儲過程的權(quán)限。完成后，單擊“確定”按鈕即可。9.6.2 存儲過程的修改存儲過程的