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

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

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

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

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

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

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

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

存儲(chǔ)過程中的參數(shù)可分為2種:輸入?yún)?shù):可以在調(diào)用時(shí)向存儲(chǔ)過程傳遞參數(shù),此參數(shù)可用來在存儲(chǔ)過程中傳入值輸出參數(shù):如果希望返回值，則可以使用輸出參數(shù)，輸出參數(shù)后有“OUTPUT”標(biāo)記，執(zhí)行存儲(chǔ)過程后，將把返回值存放在輸出參數(shù)中，可供其他T-SQL語句讀取訪問CREATEPROCEDURE語句中聲明一個(gè)或多個(gè)變量作為參數(shù)@參數(shù)數(shù)據(jù)類型[=默認(rèn)值]創(chuàng)建存儲(chǔ)過程的語法創(chuàng)建帶輸入?yún)?shù)的存儲(chǔ)過程示例-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í)行存儲(chǔ)過程EXECSELECT_WITH_pub'人民郵電出版社'GO創(chuàng)建帶輸入?yún)?shù)的存儲(chǔ)過程示例-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í)行存儲(chǔ)過程EXECSELECT_book_parm'C','北京'GO創(chuàng)建帶默認(rèn)參數(shù)的存儲(chǔ)過程示例：按照指定價(jià)格和出版日期查找圖書，價(jià)格參數(shù)提供默認(rèn)值為100，出版日期參數(shù)默認(rèn)值為系統(tǒng)日期CREATEPROCEDURESELECT_pubdate@priceDECIMAL=100,@pubdateDATETIME=NULLASIF(@pubdateISNULL)SET@pubdate=GETDATE() SELECTId,Title,Author,PublishDate,UnitPrice,ISBNFROMbooksWHEREunitprice<@priceAND publishdate<=@pubdateGO--測(cè)試使用默認(rèn)參數(shù)，即:@price=100,@pubdate=getdateEXECSELECT_pubdateGO

--測(cè)試用戶給定的參數(shù)值，不使用默認(rèn)參數(shù)EXECSELECT_pubdate10,'2009-1-1'GO創(chuàng)建帶輸出參數(shù)的存儲(chǔ)過程示例-1CREATEPROCSUM_sales@booknameVARCHAR(20)OUTPUT,@moneyDECIMALOUTPUTAS--統(tǒng)計(jì)書籍銷售信息SELECTa.BookName書名,isnull(SUM(b.quantity),0)銷售數(shù)量,ISNULL(SUM(b.quantity*b.price*b.discount),0)銷售總價(jià)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)計(jì)每本書的銷售情況,顯示書的名稱,銷售數(shù)量,銷售總金額,并返回已銷售的書籍中銷售金額最低的書的書名和金額執(zhí)行帶輸出參數(shù)的存儲(chǔ)過程DECLARE@nameVARCHAR(20)DECLARE@moneyDECIMALEXECUTESUM_sales@nameOUTPUT,@moneyOUTPUTPRINT'銷售金額最少的書:'+@name+',金額為：'+CAST(@moneyASVARCHAR(4))處理存儲(chǔ)過程中的錯(cuò)誤RAISERROR({消息id|消息文本|@局部變量}{,錯(cuò)誤嚴(yán)重級(jí)別,狀態(tài)}消息id:使用sp_addmessage存儲(chǔ)在sys.messages目錄視圖中的用戶定義錯(cuò)誤消息號(hào)。用戶定義錯(cuò)誤消息的錯(cuò)誤號(hào)應(yīng)當(dāng)大于50000。如果未指定，則RAISERROR引發(fā)一個(gè)錯(cuò)誤號(hào)為50000的錯(cuò)誤消息消息文本：用戶定義的消息錯(cuò)誤嚴(yán)重級(jí)別：用戶定義的與該消息關(guān)聯(lián)的嚴(yán)重級(jí)別，任何用戶都可以指定0到18之間的嚴(yán)重級(jí)別、狀態(tài)：0-255的整數(shù)RAISERROR示例編寫一個(gè)存儲(chǔ)過程，查詢某出版社在指定的日期之后出版的書籍信息，并能返回該出版社銷售的最好的書籍名稱和銷售數(shù)量。如果用戶輸入的日期超過系統(tǒng)日期則提示錯(cuò)誤，不能執(zhí)行查詢。CREATEPROCSELECT_err@publisherVARCHAR(50),@pubdateDATETIME,@countINTOUTPUT,@bookNameVARCHAR(50)OUTPUTASDECLARE@pubIdINT--保存出版社IdIF(@pubdate<=GETDATE())BEGINSELECT@pubId=PublisherIDFROMPublisherWHEREPublisherName=@publisher--查詢出版社出版的書籍信息SELECTa.BookName書名,a.PublishDate出版日期,a.Price價(jià)格

FROMBookInfoaWHEREa.PublisherID=@pubIdANDa.PublishDate>@pubdate

--返回銷售最號(hào)的書籍名稱和銷售數(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--返回