第10章 存儲(chǔ)過程

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

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

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

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

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

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

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

存儲(chǔ)過程名數(shù)據(jù)類型}[=默認(rèn)值][output][,...n][withencryption]as{sql語句}10.3用代碼操作存儲(chǔ)過程1.簡(jiǎn)單的存儲(chǔ)過程【例10.7】使用T-SQL語句創(chuàng)建一個(gè)簡(jiǎn)單的存儲(chǔ)過程proc_AllReaders，要求查詢ReaderCard表(表4-9)中所有讀者信息。use圖書借閱信息管理系統(tǒng)gocreateprocproc_AllReadersasselect*fromReaderCard10.3用代碼操作存儲(chǔ)過程2.含輸入?yún)?shù)的存儲(chǔ)過程 含參數(shù)的存儲(chǔ)過程，目的是通過使用參數(shù)向存儲(chǔ)過程輸入和輸出信息，從而擴(kuò)展存儲(chǔ)過程的功能。【例10.8】使用T-SQL語句創(chuàng)建一個(gè)含輸入?yún)?shù)的存儲(chǔ)過程proc_MorFReaders，要求查詢ReaderCard表(表4-9)中指定性別的讀者信息。createprocproc_MorFReaders@sexnchar(1) --輸入?yún)?shù)asselect*fromReaderCardwhere --使用輸入?yún)?shù)10.3用代碼操作存儲(chǔ)過程3.含輸出參數(shù)的存儲(chǔ)過程【例10.9】使用T-SQL語句創(chuàng)建一個(gè)含輸出參數(shù)的存儲(chǔ)過程proc_ReadersNum，要求查詢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ǔ)過程4.含有參數(shù)和返回值的存儲(chǔ)過程【例10.10】使用T-SQL語句創(chuàng)建一個(gè)含返回值的存儲(chǔ)過程proc_ReadersNumInfo，要求查詢ReaderCard表(表4-9)中指定性別的讀者數(shù)量，并返回讀者人數(shù)信息。createprocproc_ReadersNumInfo@famalenchar(1)='女', @malenchar(1)='男', --輸入?yún)?shù)@f_numintoutput ,@m_numintoutput --輸出參數(shù)10.3用代碼操作存儲(chǔ)過程4.含有參數(shù)和返回值的存儲(chǔ)過程【例10.10】使用T-SQL語句創(chuàng)建一個(gè)含返回值的存儲(chǔ)過程proc_ReadersNumInfo，要求查詢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ǔ)過程【例10.12】使用T-SQL語句執(zhí)行簡(jiǎn)單的存儲(chǔ)過程proc_AllReaders，顯示所有讀者的信息。Execproc_AllReaders10.3.2執(zhí)行存儲(chǔ)過程【例10.13】使用T-SQL語句執(zhí)行含輸入?yún)?shù)的存儲(chǔ)過程proc_MorFReaders，顯示所有男讀者的信息。Execproc_AllReaders'男'

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

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

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

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

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

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