1創(chuàng)建和執(zhí)行存儲過程

數(shù)據(jù)庫設(shè)計與實現(xiàn)創(chuàng)建和執(zhí)行存儲過程楊陽存儲過程學習目標：理論目標：理解存儲過程的運行過程實踐目標：掌握存儲過程的創(chuàng)建和執(zhí)行的操作存儲過程存儲過程的概念存儲過程(StoredProcedure)類似于C語言中的函數(shù)，它是一組為了完成特定功能的T-SQL語句集合。存儲過程存儲在數(shù)據(jù)庫內(nèi)，可以有用戶的應(yīng)用程序，通過指定存儲過程名稱及相關(guān)參數(shù)來執(zhí)行。存儲過程存儲過程的優(yōu)點。1.模塊化的程序設(shè)計，實現(xiàn)代碼多次調(diào)用。存儲過程只需創(chuàng)建一次，并存儲在數(shù)據(jù)庫中，在以后的使用中，便可重復(fù)調(diào)用，不需要每次重新編寫。2.加快執(zhí)行速度。如果某一操作包含大量的或者需多次執(zhí)行的代碼，存儲過程要比T-SQL代碼執(zhí)行速度要快。因為創(chuàng)建存儲過程時，已經(jīng)被分析和優(yōu)化，但是對于T-SQL代碼，每次執(zhí)行時都要進行編譯和優(yōu)化。3.減少網(wǎng)絡(luò)流量。使用存儲過程，可以調(diào)用需要若干行T-SQL代碼的操作，而不需要通過網(wǎng)絡(luò)傳送這些代碼。4.可以作為安全機制。對用戶只授予執(zhí)行存儲過程的權(quán)限，而不授予用戶直接訪問相應(yīng)表的權(quán)限，這樣既保證了用戶操作數(shù)據(jù)庫中的數(shù)據(jù)，又能保證用戶不能訪問相應(yīng)的表，保證數(shù)據(jù)的安全。存儲過程存儲過程的分類1.系統(tǒng)存儲過程。系統(tǒng)存儲過程是一組預(yù)編的T-SQL語句，提供了管理數(shù)據(jù)庫和更新表的功能，系統(tǒng)存儲過程位于master數(shù)據(jù)庫和msdb數(shù)據(jù)庫，并且所有的存儲過程的名稱均為sp_*形式。例如sp_databases，代表列出服務(wù)器所有的數(shù)據(jù)庫，2.用戶定義的存儲過程。由用戶創(chuàng)建并能完成某種特定功能的存儲過程，分為：T-SQL存儲過程：指保存的T-SQL語句集合，可以接受和返回用戶提出的參數(shù)。CLR存儲過程：指對Microsoft.NETFramework公共語言運行時方法的引用，可以接受和返回用戶提供的參數(shù)。在.NETFramework程序集中作為類的公共靜態(tài)方法實現(xiàn)的。3.擴展存儲過程。擴展存儲過程是SQLServer實例可以動態(tài)加載和運行的DLL。擴展存儲過程是使用SQLServer擴展存儲過程API編寫的，可直接在SQLServer實例的地址空間中運行。創(chuàng)建與執(zhí)行存儲過程【例8-1】為數(shù)據(jù)庫“Librarymanage”創(chuàng)建一個存儲過程，存儲過程名稱為Booksearch，該存儲過程在“Bookinfo”中查詢書籍“數(shù)據(jù)庫”的具體情況。1．打開[對象資源管理]，左鍵單擊展開“[數(shù)據(jù)庫]-[Librarymanage]-[可編程性]”節(jié)點，選擇存儲過程節(jié)點，單擊右鍵彈出菜單，左鍵點擊“新建存儲過程”命令。2.在[查詢編輯器]中出現(xiàn)存儲過程的編程模板，在模板上通過編寫T-SQL代碼，創(chuàng)建存儲過程。3.單擊“執(zhí)行”按鈕，運行成功后，在[對象資源管理]窗口，首先，右鍵單擊“[存儲過程]”節(jié)點，點擊“刷新”按鈕，然后左鍵單擊展開“[存儲過程]”節(jié)點，可以看到新建的存儲過程Booksearch。4.創(chuàng)建完新的存儲過程，在[對象資源管理]窗口，首先，右鍵單擊已創(chuàng)建的存儲過程dbo.Booksearch彈出菜單，左鍵點擊“執(zhí)行存儲過程”命令。創(chuàng)建與執(zhí)行存儲過程使用T-SQL創(chuàng)建存儲過程，其語法如下：CREATEPROC[EDURE]<存儲過程名>{@參數(shù)1數(shù)據(jù)類型}[=默認值][OUTPUT]，…{@參數(shù)n數(shù)據(jù)類型}[=默認值][OUTPUT]AS[BEGIN]T-SQL語句[END]

使用T-SQL語句進行調(diào)用，語法如下：EXEC<存儲過程名稱>[參數(shù)表]

創(chuàng)建與執(zhí)行存儲過程【例8-2】為數(shù)據(jù)庫“Librarymanage”創(chuàng)建一個存儲過程“sp_bookquantity”，該存儲過程在“Bookinfo”表中查詢剩余數(shù)量少于5本的書籍的編號、書名、作者、出版社和剩余數(shù)量。單擊“新建查詢”按鈕，在[查詢編輯器]中輸入以下代碼。USELibrarymanageGOCREATEPROCEDUREsp_bookquantityASBEGINSELECTBook_ID,Book_name,Book_author,Book_press,Book_price,Book_quantityFROMBookinfoWHEREBook_quantity<5END單擊“執(zhí)行”按鈕，即創(chuàng)建了存儲過程“sp_bookquantity”。

創(chuàng)建與執(zhí)行存儲過程調(diào)用此存儲過程：EXECsp_bookquantity結(jié)果如圖

創(chuàng)建與執(zhí)行存儲過程【例8-3】為數(shù)據(jù)庫“Librarymanage”創(chuàng)建一個存儲過程“proc_bookname”，該存儲過程根據(jù)使用者輸入的書名在“Bookinfo”表中查詢書籍的書名、作者、出版社和價錢。單擊“新建查詢”按鈕，在[查詢編輯器]中輸入以下代碼。USELibrarymanageGOCREATEPROCEDUREproc_bookname@booknameNVARCHAR(50)ASBEGINSELECTBook_name,Book_author,Book_press,Book_priceFROMBookinfoWHEREBook_name=@booknameEND單擊“執(zhí)行”按鈕，即創(chuàng)建了存儲過程“proc_bookname”。

創(chuàng)建與執(zhí)行存儲過程調(diào)用存儲過程，執(zhí)行以下代碼，查詢書名為“數(shù)據(jù)庫”的書籍的情況：EXECproc_bookname'數(shù)據(jù)庫‘結(jié)果如圖

創(chuàng)建與執(zhí)行存儲過程【例8-4】為數(shù)據(jù)庫“Librarymanage”創(chuàng)建一個存儲過程“proc_bookpress”，該存儲過程根據(jù)使用者輸入的出版社名在“Bookinfo”表中查詢書籍的編號、書名、類型、作者、出版社和價錢等信息，不輸入書名時查詢“清華大學出版社”出版書籍的信息。單擊“新建查詢”按鈕，在[查詢編輯器]中輸入以下代碼。USELibrarymanageGOCREATEPROCEDUREproc_bookpress@PRESSNVARCHAR(50)='清華大學出版社'ASBEGINSELECTBook_ID,Book_name,Book_type,Book_author,Book_press,Book_priceFROMBookinfoWHEREBook_press=@PRESSEND

單擊“執(zhí)行”按鈕，即創(chuàng)建了存儲過程“proc_bookpress”。創(chuàng)建與執(zhí)行存儲過程調(diào)用存儲過程，在不輸入書名時候，執(zhí)行以下代碼，：EXECproc_bookpress結(jié)果如圖

創(chuàng)建與執(zhí)行存儲過程調(diào)用存儲過程，查詢科學出版社出版的書籍，執(zhí)行以下代碼，：EXECproc_bookpress‘科學出版社’

結(jié)果如圖

創(chuàng)建與執(zhí)行存儲過程【例8-5】為數(shù)據(jù)庫“Librarymanage”創(chuàng)建一個存儲過程“proc_readerdepartment”，該存儲過程根據(jù)使用者輸入的讀者號在即可顯示讀者單位和姓名。單擊“新建查詢”按鈕，在[查詢編輯器]中輸入以下代碼。USELibrarymanageGOCREATEPROCEDUREproc_readerdepartment@ReaderIDNVARCHAR(8),@ReadernameNVARCHAR(30)OUTPUT,@ReaderdepartmentNVARCHAR(50)OUTPUTASSELECT@Readername=Reader_name,@Readerdepartment=Reader_departmentFROMReaderinfoWHEREReader_ID=@ReaderID

單擊“執(zhí)行”按鈕，即創(chuàng)建了存儲過程“proc_bookname”。

創(chuàng)建與執(zhí)行存儲過程調(diào)用存儲過程，執(zhí)行以下代碼，查詢書名為“數(shù)據(jù)庫”的書籍的情況：declare@RIDNVARCHAR(8),@ReadernameNVARCHAR(30),@ReaderdepartmentNVARCHAR(50)set@RID=12010101ex