第10章存儲(chǔ)過(guò)程10.1存儲(chǔ)過(guò)程簡(jiǎn)介10.2界面中操作存儲(chǔ)過(guò)程10.3用代碼操作存儲(chǔ)過(guò)程

10.1存儲(chǔ)過(guò)程簡(jiǎn)介10.1.1存儲(chǔ)過(guò)程的概念 在數(shù)據(jù)庫(kù)系統(tǒng)中，存儲(chǔ)過(guò)程（storedProcedure，簡(jiǎn)寫(xiě)為sproc）的作用很重要。 存儲(chǔ)過(guò)程包含一組T-SQL語(yǔ)句，這組語(yǔ)句包含一些固定的操作，可以實(shí)現(xiàn)某個(gè)具體任務(wù)。它被SQLServer服務(wù)器集中編譯處理后，存儲(chǔ)在數(shù)據(jù)庫(kù)服務(wù)器中，可以接受輸入?yún)?shù)、輸出參數(shù)、返回狀態(tài)值和嵌套調(diào)用。10.1存儲(chǔ)過(guò)程簡(jiǎn)介10.1.2存儲(chǔ)過(guò)程分類(lèi)存儲(chǔ)過(guò)程可以分為： 系統(tǒng)存儲(chǔ)過(guò)程、擴(kuò)展存儲(chǔ)過(guò)程、用戶(hù)自定義存儲(chǔ)過(guò)程3種類(lèi)型。10.1.2存儲(chǔ)過(guò)程分類(lèi)（1）系統(tǒng)存儲(chǔ)過(guò)程： 以sp_開(kāi)頭，由SQLServer內(nèi)建，存儲(chǔ)在master數(shù)據(jù)庫(kù)中。系統(tǒng)存儲(chǔ)過(guò)程主要從系統(tǒng)表中查詢(xún)信息或完成與更新數(shù)據(jù)庫(kù)表相關(guān)的管理任務(wù)或其他的系統(tǒng)管理任務(wù)。系統(tǒng)存儲(chǔ)過(guò)程可以在任何一個(gè)數(shù)據(jù)庫(kù)中執(zhí)行。下面列出一些常見(jiàn)的系統(tǒng)存儲(chǔ)過(guò)程：sp_helptext 用于顯示存儲(chǔ)過(guò)程的定義文本sp_rename 用于修改當(dāng)前數(shù)據(jù)庫(kù)中用戶(hù)對(duì)象的名稱(chēng)sp_renamedb用于修改指定數(shù)據(jù)庫(kù)的名稱(chēng)10.1.2存儲(chǔ)過(guò)程分類(lèi)（2）擴(kuò)展存儲(chǔ)過(guò)程 擴(kuò)展存儲(chǔ)過(guò)程是在SQLServer環(huán)境之外執(zhí)行的動(dòng)態(tài)鏈接庫(kù)，可以擴(kuò)展SQLServer的功能，通常以xp_開(kāi)頭。使用擴(kuò)展存儲(chǔ)過(guò)程時(shí)，需要先加載到SQLServer系統(tǒng)中，并按照存儲(chǔ)過(guò)程的方式執(zhí)行。（3）用戶(hù)自定義存儲(chǔ)過(guò)程 用戶(hù)自定義存儲(chǔ)過(guò)程是指在用戶(hù)數(shù)據(jù)庫(kù)中創(chuàng)建的存儲(chǔ)過(guò)程，可以完成特定的數(shù)據(jù)庫(kù)操作任務(wù)，其前綴不能是sp_。

本章后面講的主要是用戶(hù)自定義的存儲(chǔ)過(guò)程。10.2界面中操作存儲(chǔ)過(guò)程1.簡(jiǎn)單的存儲(chǔ)過(guò)程簡(jiǎn)單存儲(chǔ)過(guò)程直接完成一個(gè)程序段的運(yùn)行，不含任何輸入輸出參數(shù)和返回值?！纠?0.1】使用SQLServerManagementStudio圖形化工具創(chuàng)建一個(gè)簡(jiǎn)單的沒(méi)有參數(shù)的存儲(chǔ)過(guò)程proc_AllBooks，在數(shù)據(jù)庫(kù)中查詢(xún)BookInfo表(表4-7)中所有圖書(shū)信息。10.2界面中操作存儲(chǔ)過(guò)程2.含輸入?yún)?shù)的存儲(chǔ)過(guò)程【例10.2】使用SQLServerManagementStudio圖形化工具創(chuàng)建一個(gè)含輸入?yún)?shù)的存儲(chǔ)過(guò)程proc_CheapBooks，要求查詢(xún)BookInfo表(表4-7)中價(jià)格低于30元的所有圖書(shū)信息。

10.2.2執(zhí)行存儲(chǔ)過(guò)程【例10.3】使用SQLServerManagementStudio圖形化工具執(zhí)行簡(jiǎn)單的存儲(chǔ)過(guò)程proc_AllBooks。

(1)在SSMS中，展開(kāi)“數(shù)據(jù)庫(kù)”節(jié)點(diǎn)，展開(kāi)“圖書(shū)借閱信息管理系統(tǒng)”—“可編程性”—“存儲(chǔ)過(guò)程”，找到proc_AllBooks，右鍵選擇“執(zhí)行存儲(chǔ)過(guò)程”。(2)在彈出的“執(zhí)行過(guò)程”對(duì)話(huà)框中，選擇“確定”，即執(zhí)行了存儲(chǔ)過(guò)程。需要說(shuō)明的是，因?yàn)閜roc_AllBooks存儲(chǔ)過(guò)程，不含任何參數(shù)，所以在彈出的“執(zhí)行過(guò)程”對(duì)話(huà)框中沒(méi)有需要填寫(xiě)的參數(shù)信息（圖10-9）。執(zhí)行后的結(jié)果如圖10.10。10.2.2執(zhí)行存儲(chǔ)過(guò)程【例10.4】使用SQLServerManagementStudio圖形化工具執(zhí)行含參數(shù)的存儲(chǔ)過(guò)程proc_CheapBooks。

(1)在SSMS中，展開(kāi)“數(shù)據(jù)庫(kù)”節(jié)點(diǎn)，展開(kāi)“圖書(shū)借閱信息管理系統(tǒng)”—“可編程性”—“存儲(chǔ)過(guò)程”，找到proc_CheapBooks，右鍵選擇“執(zhí)行存儲(chǔ)過(guò)程”。(2)10.2.2執(zhí)行存儲(chǔ)過(guò)程【例10.4】使用SQLServerManagementStudio圖形化工具執(zhí)行含參數(shù)的存儲(chǔ)過(guò)程proc_CheapBooks。

(1)在SSMS中，展開(kāi)“數(shù)據(jù)庫(kù)”節(jié)點(diǎn)，展開(kāi)“圖書(shū)借閱信息管理系統(tǒng)”—“可編程性”—“存儲(chǔ)過(guò)程”，找到proc_CheapBooks，右鍵選擇“執(zhí)行存儲(chǔ)過(guò)程”。(2)10.3用代碼操作存儲(chǔ)過(guò)程使用T-SQL語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程是最常見(jiàn)的方法，可以使用CREATEPROC關(guān)鍵詞來(lái)實(shí)現(xiàn)。其語(yǔ)法格式如下：createproc[edure]

存儲(chǔ)過(guò)程名數(shù)據(jù)類(lèi)型}[=默認(rèn)值][output][,...n][withencryption]as{sql語(yǔ)句}10.3用代碼操作存儲(chǔ)過(guò)程1.簡(jiǎn)單的存儲(chǔ)過(guò)程【例10.7】使用T-SQL語(yǔ)句創(chuàng)建一個(gè)簡(jiǎn)單的存儲(chǔ)過(guò)程proc_AllReaders，要求查詢(xún)ReaderCard表(表4-9)中所有讀者信息。use圖書(shū)借閱信息管理系統(tǒng)gocreateprocproc_AllReadersasselect*fromReaderCard10.3用代碼操作存儲(chǔ)過(guò)程2.含輸入?yún)?shù)的存儲(chǔ)過(guò)程 含參數(shù)的存儲(chǔ)過(guò)程，目的是通過(guò)使用參數(shù)向存儲(chǔ)過(guò)程輸入和輸出信息，從而擴(kuò)展存儲(chǔ)過(guò)程的功能?！纠?0.8】使用T-SQL語(yǔ)句創(chuàng)建一個(gè)含輸入?yún)?shù)的存儲(chǔ)過(guò)程proc_MorFReaders，要求查詢(xún)ReaderCard表(表4-9)中指定性別的讀者信息。createprocproc_MorFReaders@sexnchar(1) --輸入?yún)?shù)asselect*fromReaderCardwhere --使用輸入?yún)?shù)10.3用代碼操作存儲(chǔ)過(guò)程3.含輸出參數(shù)的存儲(chǔ)過(guò)程【例10.9】使用T-SQL語(yǔ)句創(chuàng)建一個(gè)含輸出參數(shù)的存儲(chǔ)過(guò)程proc_ReadersNum，要求查詢(xún)ReaderCard表(表4-9)中指定性別的讀者數(shù)量，并返回?cái)?shù)量的值。createprocproc_ReadersNum@sexnchar(1)='男',--輸入?yún)?shù)，并指定了默認(rèn)值@numintoutput --輸出參數(shù)asselect@num=count(*)fromReaderCard --輸出參數(shù)被賦值where --使用輸入?yún)?shù)10.3用代碼操作存儲(chǔ)過(guò)程4.含有參數(shù)和返回值的存儲(chǔ)過(guò)程【例10.10】使用T-SQL語(yǔ)句創(chuàng)建一個(gè)含返回值的存儲(chǔ)過(guò)程proc_ReadersNumInfo，要求查詢(xún)ReaderCard表(表4-9)中指定性別的讀者數(shù)量，并返回讀者人數(shù)信息。createprocproc_ReadersNumInfo@famalenchar(1)='女', @malenchar(1)='男', --輸入?yún)?shù)@f_numintoutput ,@m_numintoutput --輸出參數(shù)10.3用代碼操作存儲(chǔ)過(guò)程4.含有參數(shù)和返回值的存儲(chǔ)過(guò)程【例10.10】使用T-SQL語(yǔ)句創(chuàng)建一個(gè)含返回值的存儲(chǔ)過(guò)程proc_ReadersNumInfo，要求查詢(xún)ReaderCard表(表4-9)中指定性別的讀者數(shù)量，并返回讀者人數(shù)信息。asdeclare@nint

--聲明返回值set@n=1--為返回值賦值select@f_num=count(*)fromReaderCard --輸出參數(shù)被賦值where --使用輸入?yún)?shù)select@m_num=count(*)fromReaderCard --輸出參數(shù)被賦值where --使用輸入?yún)?shù)ifset@n=0elseset@n=1return@n10.3.2執(zhí)行存儲(chǔ)過(guò)程【例10.12】使用T-SQL語(yǔ)句執(zhí)行簡(jiǎn)單的存儲(chǔ)過(guò)程proc_AllReaders，顯示所有讀者的信息。Execproc_AllReaders10.3.2執(zhí)行存儲(chǔ)過(guò)程【例10.13】使用T-SQL語(yǔ)句執(zhí)行含輸入?yún)?shù)的存儲(chǔ)過(guò)程proc_MorFReaders，顯示所有男讀者的信息。Execproc_AllReaders'男'

10.3.2執(zhí)行存儲(chǔ)過(guò)程【例10.14】使用T-SQL語(yǔ)句執(zhí)行含輸出參數(shù)的存儲(chǔ)過(guò)程proc_ReadersNum，顯示所有男讀者的數(shù)量。（如圖10.16）declare@numintExecproc_ReadersNumoutputprint'男讀者的數(shù)量:aschar(10))10.3.2執(zhí)行存儲(chǔ)過(guò)程【例10.15】使用T-SQL語(yǔ)句執(zhí)行含返回值及輸入輸出參數(shù)的存儲(chǔ)過(guò)程proc_ReadersNumInfo，顯示讀者的數(shù)量信息。declare@FnumintExec@Str=proc_ReadersNumInfooutputprint'男讀者的數(shù)量:aschar(10))print'女讀者的數(shù)量:aschar(10))if@str=0print'女讀者多'elseprint'女讀者不多于男讀者'10.3.2執(zhí)行存儲(chǔ)過(guò)程【例10.16】使用Exec語(yǔ)句執(zhí)行字符串實(shí)例。declare@strvarchar(40)set@str='select*fromReaderCard'Exec注意，Exec執(zhí)行字符串時(shí)，后面的括號(hào)不能省略。10.3.3查看存儲(chǔ)過(guò)程1.查看存儲(chǔ)過(guò)程的定義文本

EXECsp_helptext存儲(chǔ)過(guò)程名2.查看存儲(chǔ)過(guò)程的依賴(lài)關(guān)系

EXECsp_depends存儲(chǔ)過(guò)程名3.查看存儲(chǔ)過(guò)程的參數(shù)

EXECsp_help存儲(chǔ)過(guò)程名4.查看數(shù)據(jù)庫(kù)中全部存儲(chǔ)過(guò)程

EXECsp_stored_procedures10.3.3查看存儲(chǔ)過(guò)程【例10.17】使用相關(guān)的系統(tǒng)存儲(chǔ)過(guò)程查看存儲(chǔ)過(guò)程proc_ReadersNumInfo的定義、相關(guān)性和參數(shù)。EXECsp_helptextproc_ReadersNumInfo--查看存儲(chǔ)過(guò)程的定義EXECsp_dependsproc_ReadersNumInfo--查看存儲(chǔ)過(guò)程的相關(guān)性EXECsp_helpproc_ReadersNumInfo --查看存儲(chǔ)過(guò)程的參數(shù)EXECsp_stored_procedures --查看全部存儲(chǔ)過(guò)程10.3.4修改和刪除存儲(chǔ)過(guò)程修改存儲(chǔ)過(guò)程的語(yǔ)法如下：alterproc[edure]存儲(chǔ)過(guò)程名數(shù)據(jù)類(lèi)型}[=默認(rèn)值][output][,...n][withencryption]as{sql語(yǔ)句}10.3.4修改和刪除存儲(chǔ)過(guò)程【例10.18】使用T-SQL語(yǔ)句修改存儲(chǔ)過(guò)程proc_ReadersNum，能夠顯示不同類(lèi)型的讀者信息，并加密。執(zhí)行存儲(chǔ)過(guò)程，顯示學(xué)生讀者的信息。alterprocproc_ReadersNum@類(lèi)型varchar(50)=‘Student’, --輸入?yún)?shù)@numintoutput --輸出參數(shù)WITHENCRYPTION--加密asselect@num=count(*)fromReaderCard --輸出參數(shù)被賦值where

--使用輸入?yún)?shù)10.3.4修改和刪除存儲(chǔ)過(guò)程【例10.18】