SQL Server 數(shù)據(jù)庫開發(fā)經(jīng)典案例教程第13章存儲過程

第13章存儲過程本章目標了解存儲過程的優(yōu)點掌握常用的系統(tǒng)存儲過程掌握如何創(chuàng)建存儲過程掌握如何調(diào)用存儲過程存儲過程介紹存儲過程是在數(shù)據(jù)庫管理系統(tǒng)中保存的，預(yù)先編譯的并能實現(xiàn)某種功能的SQL程序。存儲過程相當(dāng)于編程語言(如JAVA等)中的方法，就是由SQL語句和控制語句組成的能夠完成特定某個功能的預(yù)編譯語句的集合。存儲過程是保存在數(shù)據(jù)庫服務(wù)器中的，可以直接在SQLServer客戶端中調(diào)用也可以通過程序語言調(diào)用.存儲過程的優(yōu)點存儲過程的優(yōu)點:允許模塊化程序設(shè)計只需創(chuàng)建過程一次并將其存儲在數(shù)據(jù)庫中，以后即可在程序中調(diào)用該過程任意次允許更快執(zhí)行存儲過程將比Transact-SQL批代碼的執(zhí)行要快減少網(wǎng)絡(luò)流量存儲過程存儲在后端數(shù)據(jù)庫中不需要通過網(wǎng)絡(luò)傳輸可作為安全機制使用即使對于沒有直接執(zhí)行存儲過程中語句權(quán)限的用戶，也可授予他執(zhí)行該存儲過程的權(quán)限存儲過程中的語句存儲過程----------------單個SELECT語句SELECT語句塊可以包含SELECT語句與邏輯控制語句存儲過程中的語句SQLServer中的存儲過程與其他語言中的過程或函數(shù)類似，它們的共同特征是:它們都接收輸入?yún)?shù)，并向調(diào)用過程或語句返回值。它們都包含在數(shù)據(jù)庫中執(zhí)行操作或調(diào)用其他存儲過程的編程語句。它們都向調(diào)用過程返回狀態(tài)值，指示執(zhí)行過程是否成功常用的系統(tǒng)存儲過程SQLServer提供系統(tǒng)存儲過程，它們是一組預(yù)編譯的T-SQL語句所有系統(tǒng)存儲過程的名稱都以“_sp”開頭。系統(tǒng)存儲過程位于master數(shù)據(jù)庫中系統(tǒng)存儲過程

說明sp_databases列出服務(wù)器上的所有數(shù)據(jù)庫sp_helpdb報告有關(guān)指定數(shù)據(jù)庫或所有數(shù)據(jù)庫的信息sp_renamedb更改數(shù)據(jù)庫的名稱sp_tables返回當(dāng)前環(huán)境下可查詢的對象的列表sp_columns返回某個表列的信息sp_help查看某個表的所有信息sp_helpconstraint查看某個表的約束sp_helpindex查看某個表的索引sp_stored_procedures列出當(dāng)前環(huán)境中的所有存儲過程sp_helptext顯示默認值、未加密的存儲過程、用戶定義的存儲過程、觸發(fā)器或視圖的實際文本常用的系統(tǒng)存儲過程的使用EXECsp_server_info--返回服務(wù)器信息EXECsp_databases --返回服務(wù)器數(shù)據(jù)庫信息EXECsp_who --返回當(dāng)前登錄用戶信息和進程信息EXECsp_tables --返回表信息EXECsp_helpdb --返回特定數(shù)據(jù)庫信息示例:其他系統(tǒng)存儲過程的使用一些系統(tǒng)存儲過程必須在特定的數(shù)據(jù)庫中使用,大多數(shù)在所有數(shù)據(jù)庫中可用創(chuàng)建存儲過程創(chuàng)建存儲過程SSMS:可視化的方式T-SQL:代碼

使用CREATEPROCEDURE語句創(chuàng)建存儲過程。所有的存儲過程都創(chuàng)建在當(dāng)前數(shù)據(jù)庫中語法:CREATEPROC[EDURE]存儲過程名

[{@參數(shù)1數(shù)據(jù)類型}[=默認值][OUTPUT],.......,{@參數(shù)n數(shù)據(jù)類型}[=默認值][OUTPUT]]ASSQL語句其中,參數(shù)部分為可選

創(chuàng)建簡單的存儲過程--使用存儲過程返回所有的書籍信息USEBookShopGO--判斷存儲過程select_books是否存在IFOBJECT_ID('SELECT_books','P')ISNOTNULLDROPPROCEDURESELECT_books;GO--創(chuàng)建存儲過程CREATEPROCEDURESELECT_booksASSELECTId,Title,Author,PublishDate,UnitPrice,ISBNFROMbooksGO調(diào)用存儲過程語法:

調(diào)用示例存儲過程EXEC過程名[參數(shù)]EXECUTESELECT_books創(chuàng)建帶參數(shù)的存儲過程CREATEPROC[EDURE]存儲過程名

[{@參數(shù)1數(shù)據(jù)類型}[=默認值][OUTPUT],.......,{@參數(shù)n數(shù)據(jù)類型}[=默認值][OUTPUT]]ASSQL語句

存儲過程中的參數(shù)可分為2種:輸入?yún)?shù):可以在調(diào)用時向存儲過程傳遞參數(shù),此參數(shù)可用來在存儲過程中傳入值輸出參數(shù):如果希望返回值，則可以使用輸出參數(shù)，輸出參數(shù)后有“OUTPUT”標記，執(zhí)行存儲過程后，將把返回值存放在輸出參數(shù)中，可供其他T-SQL語句讀取訪問CREATEPROCEDURE語句中聲明一個或多個變量作為參數(shù)@參數(shù)數(shù)據(jù)類型[=默認值]創(chuàng)建存儲過程的語法創(chuàng)建帶輸入?yún)?shù)的存儲過程示例-1示例：查詢指定的出版社出版的書籍USEBookShopGOIFOBJECT_ID('SELECT_WITH_pub','P')ISNOTNULLDROPPROCEDURESELECT_WITH_pub;GOCREATEPROCEDURESELECT_WITH_pub@pubnameVARCHAR(20)AS SELECTtitle,author,publishdate isbn,nameASpublishername FROMbooksainnerjoinpublishersb ONa.publisherid=b.idAND=@pubnameGO--執(zhí)行存儲過程EXECSELECT_WITH_pub'人民郵電出版社'GO創(chuàng)建帶輸入?yún)?shù)的存儲過程示例-2

問題：按出版社和類別模糊查詢USEBookShopGO

IFOBJECT_ID('SELECT_book_parm','P')ISNOTNULLDROPPROCEDURESELECT_book_parm;GOCREATEPROCEDURESELECT_book_parm@cateVARCHAR(20),@pubnameVARCHAR(20)ASSELECTBookName,author,isbn,publishdate,Price,b.CategoryNameAScategoryname,c.PublisherNameASpublishernameFROMbookinfoa,categoryb,publishercWHEREa.categoryid=b.CategoryIDANDa.publisherid=c.PublisherIDANDb.CategoryNameLIKE'%'+@cate+'%'ANDc.PublisherNameLIKE'%'+@pubname+'%'GO--執(zhí)行存儲過程EXECSELECT_book_parm'C','北京'GO創(chuàng)建帶默認參數(shù)的存儲過程示例：按照指定價格和出版日期查找圖書，價格參數(shù)提供默認值為100，出版日期參數(shù)默認值為系統(tǒng)日期CREATEPROCEDURESELECT_pubdate@priceDECIMAL=100,@pubdateDATETIME=NULLASIF(@pubdateISNULL)SET@pubdate=GETDATE() SELECTId,Title,Author,PublishDate,UnitPrice,ISBNFROMbooksWHEREunitprice<@priceAND publishdate<=@pubdateGO--測試使用默認參數(shù)，即:@price=100,@pubdate=getdateEXECSELECT_pubdateGO

--測試用戶給定的參數(shù)值，不使用默認參數(shù)EXECSELECT_pubdate10,'2009-1-1'GO創(chuàng)建帶輸出參數(shù)的存儲過程示例-1CREATEPROCSUM_sales@booknameVARCHAR(20)OUTPUT,@moneyDECIMALOUTPUTAS--統(tǒng)計書籍銷售信息SELECTa.BookName書名,isnull(SUM(b.quantity),0)銷售數(shù)量,ISNULL(SUM(b.quantity*b.price*b.discount),0)銷售總價FROMBookInfoaleftjoinSalesDetailsbONa.BookID=b.BookIDGROUPBYa.BookNameORDERBYSUM(b.Quantity)DESC

--返回銷售金額最低的書籍SELECTTOP1@bookname=a.bookname,@money=SUM(b.Quantity*b.Price*b.Discount)FROMBookInfoainnerjoinSalesDetailsbONa.BookID=b.BookIDGROUPBYa.BookNameORDERBYSUM(b.Quantity*b.Price*b.Discount)問題：統(tǒng)計每本書的銷售情況,顯示書的名稱,銷售數(shù)量,銷售總金額,并返回已銷售的書籍中銷售金額最低的書的書名和金額執(zhí)行帶輸出參數(shù)的存儲過程DECLARE@nameVARCHAR(20)DECLARE@moneyDECIMALEXECUTESUM_sales@nameOUTPUT,@moneyOUTPUTPRINT'銷售金額最少的書:'+@name+',金額為：'+CAST(@moneyASVARCHAR(4))處理存儲過程中的錯誤RAISERROR({消息id|消息文本|@局部變量}{,錯誤嚴重級別,狀態(tài)}消息id:使用sp_addmessage存儲在sys.messages目錄視圖中的用戶定義錯誤消息號。用戶定義錯誤消息的錯誤號應(yīng)當(dāng)大于50000。如果未指定，則RAISERROR引發(fā)一個錯誤號為50000的錯誤消息消息文本：用戶定義的消息錯誤嚴重級別：用戶定義的與該消息關(guān)聯(lián)的嚴重級別，任何用戶都可以指定0到18之間的嚴重級別、狀態(tài)：0-255的整數(shù)RAISERROR示例編寫一個存儲過程，查詢某出版社在指定的日期之后出版的書籍信息，并能返回該出版社銷售的最好的書籍名稱和銷售數(shù)量。如果用戶輸入的日期超過系統(tǒng)日期則提示錯誤，不能執(zhí)行查詢。CREATEPROCSELECT_err@publisherVARCHAR(50),@pubdateDATETIME,@countINTOUTPUT,@bookNameVARCHAR(50)OUTPUTASDECLARE@pubIdINT--保存出版社IdIF(@pubdate<=GETDATE())BEGINSELECT@pubId=PublisherIDFROMPublisherWHEREPublisherName=@publisher--查詢出版社出版的書籍信息SELECTa.BookName書名,a.PublishDate出版日期,a.Price價格

FROMBookInfoaWHEREa.PublisherID=@pubIdANDa.PublishDate>@pubdate

--返回銷售最號的書籍名稱和銷售數(shù)量SELECTTOP1@BookName=a.BookName,@count=SUM(b.Quantity)FROMBookInfoa,SalesDetailsbWHEREa.BookID=b.BookIDANDa.PublisherID=@pubIdANDa.PublishDate>@pubdateGROUPBYa.BookNameORDERBYSUM(b.Quantity)DESCENDELSEBEGINRAISERROR('出版日期不能大于系統(tǒng)日期',16,1)return--返回