第14讲存储过程

1SQLSERVER主讲人：孟宪颖SQLSERVER2第14讲存储过程14.1存储过程综述14.2创建、执行、修改、删除简单存储过程14.3存储过程的重新编译14.4系统存储过程与扩展存储过程314.1存储过程综述1.存储过程概念存储过程是一组预先写好的能实现某种功能的T-SQL程序，指定一个程序名并由SQLServer编译后将其存在SQLServer中，以后要实现该功能，则可以调用这个程序来完成用户可以通过存储过程的名字并给出参数来执行它。2.存储过程类型系统存储过程扩展存储过程用户自定义存储过程系统存储过程存储以SP_为前缀,是由SQLServer2005自己创建、管理和使用的一种特殊的存储过程，不要对其进行修改或删除。如Sp_helpdb、Sp_renamedb等。由用户自行创建的存储过程，可以输入参数、向客户端返回表格或结果、消息等，也可以返回输出函数。414.2创建、执行、修改、删除简单存储过程14.2.1无参存储过程的创建14.2.2无参存储过程的执行14.2.3带参存储过程的创建14.2.4带参存储过程的执行14.2.5查看存储过程14.2.6修改存储过程14.2.7删除存储过程514.2.1无参存储过程的创建使用SQL语句创建存储过程1）语法格式如下：CREATEPROC[EDURE]procedure_nameASsql_statement[...n]2）语法注释：procedure_name：新建存储过程的名称，其名称必须符合标识符命名规则，且对于数据库及其所有者必须唯一。sql_statement：指存储过程中的任意数目和类型的Transact-SQL语句。614.2.1无参存储过程的创建例使用SQL语句在PUBS数据库中，创建一个名称为pr_searchorddate的存储过程，该存储过程将查询出sales表中订购日期ord_date在1994年以后的记录信息。其程序清单如下：USEpubsGOCREATEPROCpr_searchorddateASSELECT*FROMsalesWHEREord_date=‘1994-1-1'GO714.2.2无参存储过程的执行1.对存储在服务器上的存储过程，可以使用EXECUTE命令或其名称执行它，其语法格式如下：EXEC[UTE]procedure_name对上例的存储过程pr_searchorddate的执行语句如下：EXECUTEpr_searchorddate814.2.3带参存储过程的创建1）语法格式如下：CREATEPROC[EDURE]procedure_name[{@parameterdata_type}[OUTPUT]][,...n]ASsql_statement[...n]2）语法注释：parameter：存储过程中的输入和输出参数。data_type：参数的数据类型。OUTPUT：表明参数是返回参数。该选项的值可以返回给EXEC[UTE]。914.2.3带参存储过程的创建例在PUBS数据库中创建一个存储过程pr_searchempl,查询出authors表中state字段为某个州且姓中包含某字符串的所有的员工信息。USEPUBSGOCREATEPROCpr_searchempl@statechar(2),@strvarchar(40)AsSelect*FromauthorsWherestate=@stateandau_lnamelike‘%’+@str+’%’1014.2.4带参存储过程的执行语法格式如下：[[EXEC[UTE]]{[@return_status=]{procedure_name}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]][,...n][WITHRECOMPILE]对上例的存储过程pr_searchempl的执行语句如下：EXECUTEpr_searchempl‘CA’,’hi’1114.2.3带参存储过程的创建例在PUBS数据库中创建一个存储过程pr_titleprice,统计出titles表中pub_id字段为某编号的书籍总价格。CREATEPROCpr_titleprice@pub_idchar(4),@spricemoneyoutputAsSelect@sprice=sum(price)FromtitlesWherepub_id@pub_id存储过程的执行:declare@ssmoneyexecpr_titleprice'0877',@ssoutputselect@ssas总价格1214.2.3带参存储过程的创建参数传递的方式存储过程里可以包含参数，在执行存储过程时，如果不指明参数名称，则按存储过程所定义的参数次序传送。如果在存储过程里定义了参数的默认值，并且放在最后，则可以不指定该参数。1314.2.3带参存储过程的创建例在northwind数据库中创建一个存储过程，输入类别名称、单价、库存量和订购量后，可以查看该类型中大于该单价、库存量和订购量的产品。1414.2.3带参存储过程的创建createprocpr_liyi@categorynamevarchar(15),@unitpricemoney=$10,@unitsinstocksmallint,@unitsonordersmallint=5asbeginselect*fromproductsjoincategoriesonproducts.categoryid=categories.categoryidwhere(categories.categoryname=@categoryname)and(products.unitprice@unitprice)and(products.unitsinstock@unitsinstock)and(products.unitsonorder@unitsonorder)endgo1514.2.3带参存储过程的创建执行该存储过程的方法如下，•在这种执行方法中，是将存储过程中所需的参数依次传递给存储过程，即类别名称为“beverages”饮料、单价大于1、库存量大于10，订购量大于20的所有产品。execpr_liyi'beverages',1,10,201614.2.3带参存储过程的创建•如果不按参数顺序传递参数，则要指定参数名，如以下代码：execpr_liyi@unitprice=1,@unitsonorder=20,@unitsinstock=10,@categoryname='beverages'1714.2.3带参存储过程的创建•在传递参数时，也可以用default来代表默认值，如以下代码是可以正确执行的：execpr_liyi'beverages',default,10,default•由于pr_liyi存储过程的最后一个参数定义了默认值，所以可以将其省略，例如：execpr_liyi'beverages',default,101814.2.3带参存储过程的创建•由于pr_liyi存储过程中有一个参数定义了默认值，但并不是排在最后的参数，如果要省略这两个有默认值的参数，就必须要指定参数名，如：execpr_liyi@unitsinstock=10,@categoryname='beverages'1914.2.3带参存储过程的创建存储过程的返回值存储过程可以接受输入参数并以输出参数的形式向调用它的过程返回多个值，也可以向调用它的过程返回状态值，以说明该存储过程运行成功或失败。在执行存储过程时，可以有3种不同的返回值：•以“returnn”的形式返回一个整数值。•指定一个output的返回参数以返回值。•执行T-SQL语句返回数据集，如select语句。2014.2.3带参存储过程的创建例在northwind数据库创建一个存储过程，返回产品表中的所有产品的库存量。createprocpr_lierasbegindeclare@fanhuizhiintselect@fanhuizhi=sum(unitsinstock)fromproductsreturn@fanhuizhiend2114.2.3带参存储过程的创建接受这个返回值必须要用变量来接收，如：declare@jieshouzhiintexec@jieshouzhi=pr_lierprint@jieshouzhi2214.2.3带参存储过程的创建上例中返回的值也可以用output参数来返回，可以将上例的存储过程改写为：createprocpr_lier_2@fanhuizhiintoutputasbeginselect@fanhuizhi=sum(unitsinstock)fromproductsend2314.2.3带参存储过程的创建接收output的返回值也必须要用变量，如：declare@jieshouzhiintexecpr_lier_2@jieshouzhioutputprint@jieshouzhi注意：return返回的是整数，output可以返回任何数据。如果让return返回非整数值，在创建存储过程时不会出错，但是运行存储过程时将会出错。2414.2.3带参存储过程的创建以上是在批程序中接收return返回值和output参数的方法，而第三种返回值，是直接返回到执行存储过程的应用程序中，无法在批程序中使用，只能在批程序的结果中将其显示出来。例在northwind数据库中建立一个存储过程，用于查看某个类别的所有产品，其代码如下:2514.2.3带参存储过程的创建createprocpr_lisan@categorynamevarchar(15)asbeginselectproductid,productnamefromproductsinnerjoincategoriesonproducts.categoryid=categories.categoryidwherecategories.categoryname=@categorynameEndgoexecpr_lisan'beverages'2614.2.3带参存储过程的创建不显示影响行数的通知在查询编辑器里执行查询或修改的T-SQL语句或执行的存储过程里含有查询或修改的T-SQL语句时，都会返回影响了多少行记录的语句。然而有些时候，并不希望返回这些信息，以免干扰应用程序的运行，也可以减少带宽，此时可以将影响的行数信息关闭，关闭方法为：Setnocounton如果要再次显示影响记录的行数，将on改为off即可。27存储过程练习1、在PUBS数据库中查询titles表中书名包含某字符的书的详细信息，如书名、作者名、作者次序、价格、出版社等。2、在NORTHWIND数据库中统计orderdetails表中某一产品的订购总数量，并将总数量的值返回存储过程。28[综合案例]1、在PUBS数据库中建立一张用户表TUSER，其中包含用户名、密码、电话、地址、邮政编码、E-MAIL地址等字段。2、在PUBS数据库中创建存储过程，使得每执行一次就能够向TUSER表中插入一条记录。29创建存储过程的注意事项只能在当前数据库中创建存储过程。数据库的所有者可以创建存储过程，也可以授权其他用户创建存储过程。存储过程是数据库对象，其名称必须遵守标识符命名规则。不能将CREATEPROCEDURE语句与其它SQL语句组合到单个批处理中。创建存储过程时，应指定所有输入参数和向调用过程或批处理返回的输出参数、执行数据库操作的编程语句和返回至调用过程或批处理以表明成功或失败的状态值。3014.2.5查看存储过程1.使用SSMS查看存储过程2.使用系统存储过程查看存储过程信息在SQLServer中，可以使用sp_helptext、sp_depends、sp_help等系统存储过程来查看存储过程的不同信息。1）使用sp_helptext查看存储过程的文本信息.其语法格式为：sp_helptext存储过程名2）使用sp_depen