SQL Server 数据库开发经典案例教程 习题解答等-吕玉桂 第13章存储过程新

第13章存储过程本章目标了解存储过程的优点掌握常用的系统存储过程掌握如何创立存储过程掌握如何调用存储过程存储过程介绍存储过程是在数据库管理系统中保存的，预先编译的并能实现某种功能的SQL程序。存储过程相当于编程语言(如JAVA等)中的方法，就是由SQL语句和控制语句组成的能够完成特定某个功能的预编译语句的集合。存储过程是保存在数据库效劳器中的，可以直接在SQLServer客户端中调用也可以通过程序语言调用.存储过程的优点存储过程的优点:允许模块化程序设计只需创立过程一次并将其存储在数据库中，以后即可在程序中调用该过程任意次允许更快执行存储过程将比Transact-SQL批代码的执行要快减少网络流量存储过程存储在后端数据库中不需要通过网络传输可作为平安机制使用即使对于没有直接执行存储过程中语句权限的用户，也可授予他执行该存储过程的权限存储过程中的语句存储过程----------------单个SELECT语句SELECT语句块可以包含SELECT语句与逻辑控制语句存储过程中的语句SQLServer中的存储过程与其他语言中的过程或函数类似，它们的共同特征是:它们都接收输入参数，并向调用过程或语句返回值。它们都包含在数据库中执行操作或调用其他存储过程的编程语句。它们都向调用过程返回状态值，指示执行过程是否成功常用的系统存储过程SQLServer提供系统存储过程，它们是一组预编译的T-SQL语句所有系统存储过程的名称都以“_sp〞开头。系统存储过程位于master数据库中系统存储过程

说明sp_databases列出服务器上的所有数据库sp_helpdb报告有关指定数据库或所有数据库的信息sp_renamedb更改数据库的名称sp_tables返回当前环境下可查询的对象的列表sp_columns返回某个表列的信息sp_help查看某个表的所有信息sp_helpconstraint查看某个表的约束sp_helpindex查看某个表的索引sp_stored_procedures列出当前环境中的所有存储过程sp_helptext显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本常用的系统存储过程的使用EXECsp_server_info--返回效劳器信息EXECsp_databases --返回效劳器数据库信息EXECsp_who --返回当前登录用户信息和进程信息EXECsp_tables --返回表信息EXECsp_helpdb --返回特定数据库信息例如:其他系统存储过程的使用一些系统存储过程必须在特定的数据库中使用,大多数在所有数据库中可用创立存储过程创立存储过程SSMS:可视化的方式T-SQL:代码使用CREATEPROCEDURE语句创立存储过程。所有的存储过程都创立在当前数据库中语法:CREATEPROC[EDURE]存储过程名

[{@参数1数据类型}[=默认值][OUTPUT],.......,{@参数n数据类型}[=默认值][OUTPUT]]ASSQL语句其中,参数局部为可选创立简单的存储过程--使用存储过程返回所有的书籍信息USEBookShopGO--判断存储过程select_books是否存在IFOBJECT_ID('SELECT_books','P')ISNOTNULLDROPPROCEDURESELECT_books;GO--创立存储过程CREATEPROCEDURESELECT_booksASSELECTId,Title,Author,PublishDate,UnitPrice,ISBNFROMbooksGO调用存储过程语法:

调用例如存储过程EXEC过程名[参数]EXECUTESELECT_books创立带参数的存储过程CREATEPROC[EDURE]存储过程名

[{@参数1数据类型}[=默认值][OUTPUT],.......,{@参数n数据类型}[=默认值][OUTPUT]]ASSQL语句

存储过程中的参数可分为2种:输入参数:可以在调用时向存储过程传递参数,此参数可用来在存储过程中传入值输出参数:如果希望返回值，那么可以使用输出参数，输出参数后有“OUTPUT〞标记，执行存储过程后，将把返回值存放在输出参数中，可供其他T-SQL语句读取访问CREATEPROCEDURE语句中声明一个或多个变量作为参数@参数数据类型[=默认值]创立存储过程的语法创立带输入参数的存储过程例如-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--执行存储过程EXECSELECT_WITH_pub'人民邮电出版社'GO创立带输入参数的存储过程例如-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--执行存储过程EXECSELECT_book_parm'C','北京'GO创立带默认参数的存储过程例如：按照指定价格和出版日期查找图书，价格参数提供默认值为100，出版日期参数默认值为系统日期CREATEPROCEDURESELECT_pubdate@priceDECIMAL=100,@pubdateDATETIME=NULLASIF(@pubdateISNULL)SET@pubdate=GETDATE() SELECTId,Title,Author,PublishDate,UnitPrice,ISBNFROMbooksWHEREunitprice<@priceAND publishdate<=@pubdateGO--测试使用默认参数，即:@price=100,@pubdate=getdateEXECSELECT_pubdateGO

--测试用户给定的参数值，不使用默认参数EXECSELECT_pubdate10,'2021-1-1'GO创立带输出参数的存储过程例如-1CREATEPROCSUM_sales@booknameVARCHAR(20)OUTPUT,@moneyDECIMALOUTPUTAS--统计书籍销售信息SELECTa.BookName书名,isnull(SUM(b.quantity),0)销售数量,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)问题：统计每本书的销售情况,显示书的名称,销售数量,销售总金额,并返回已销售的书籍中销售金额最低的书的书名和金额执行带输出参数的存储过程DECLARE@nameVARCHAR(20)DECLARE@moneyDECIMALEXECUTESUM_sales@nameOUTPUT,@moneyOUTPUTPRINT'销售金额最少的书:'+@name+',金额为：'+CAST(@moneyASVARCHAR(4))处理存储过程中的错误RAISERROR({消息id|消息文本|@局部变量}{,错误严重级别,状态}消息id:使用sp_addmessage存储在sys.messages目录视图中的用户定义错误消息号。用户定义错误消息的错误号应当大于50000。如果未指定，那么RAISERROR引发一个错误号为50000的错误消息消息文本：用户定义的消息错误严重级别：用户定义的与该消息关联的严重级别，任何用户都可以指定0到18之间的严重级别、状态：0-255的整数RAISERROR例如编写一个存储过程，查询某出版社在指定的日期之后出版的书籍信息，并能返回该出版社销售的最好的书籍名称和销售数量。如果用户输入的日期超过系统日期那么提示错误，不能执行查询。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

--返回销售最号的书籍名称和销售数量SELECTTOP1@BookName=a.BookName,@count=SUM(b.Quantity)FROMBookInfoa,SalesDetailsbWHEREa.BookID=b.BookIDANDa.PublisherID=@pubIdANDa.PublishDate>@pubdateGROUPBYa.BookNameORDERBYSUM(b.Quantity)DESCENDELSEBEGINRAISERROR('出版日期不能大于系统日期',16,1)return--返回END

RAISERROR例如执行存储过程DECLARE@pubnameVARCHAR(50)DEC