存储过程-《SQLServer2005程序设计语言》

SQLServer2005数据库程序设计第10章存储过程存储过程概述设计存储过程创建存储过程修改存储过程执行存储过程删除存储过程常用系统存储过程建立CLR存储过程第10章存储过程在SQLServer中,存储过程既子程序就是将常用的或复杂的工作，预先用SQL语句写好并用指定的名称存储起来。用EXECUTE执行。存储过程是存储在服务器上的T-SQL语句的命名集合是封装重复性任务的方法支持用户声明变量、条件执行以及其他强有力的编程特性10.1存储过程概述SQLServer中的存储过程可以包含执行数据库操作（包括调用其他过程）的编程语句接受输入参数向调用过程或批处理返回状态值，以表明成功或失败（以及失败原因）以输出参数的形式将多个值返回至调用过程或批处理10.1存储过程概述存储过程------------------------单个SELECT语句SELECT语句块SELECT语句与逻辑控制语句可以包含存储过程可以包含数据操纵语句、变量、逻辑控制语句等10.1存储过程概述10.1存储过程概述在SQLServer2005中，按编写的语言，存储过程有两种类型：T-SQL或CLR。T-SQL（SQLServer的存储过程）：T-SQL存储过程是指保存的T-SQL语句集合。CLR：是指对.NET公共语言运行时(CLR)方法的引用。SQLServer支持的T-SQL存储过程系统存储过程（sp_）：存储在master数据库内，以“sp_”前缀标识本地存储过程：在单独的用户数据库内创建临时存储过程：可能是局部的，名称以“#”开头；也可能是全局的，名称以“##”开头远程存储过程：是指从远程服务器上调用的存储过程，分布式查询支持这项功能扩展存储过程（sp_或xp_）：在SQLServer环境外执行10.1存储过程概述关于系统存储过程的前缀sp_所有的系统存储过程的名字都以sp_开头任何创建在master数据库中的以“sp_”开头的存储过程都可以在其他数据库中被直接调用而不需要用数据库名完全引用，不必使用完全合法名称10.1存储过程概述执行扩展存储过程常用的扩展存储过程例：execxp_cmdshell'dird:\'EXECsp_configure'showadvancedoption','1';RECONFIGURE;EXECsp_configure'xp_cmdshell','1';RECONFIGURE;使用存储过程的优点：只在创造时进行编译，执行速度快提高系统性能确保数据库的安全自动完成需要预先执行的任务可重复使用,可减少数据库开发人员的工作量10.1存储过程概述创建存储过程只能在当前数据库内创建存储过程，除了临时存储过程。临时存储过程总是创建在tempdb数据库中存储过程可以引用表、视图、用户定义函数、其他存储过程以及临时表若存储过程创建了局部临时表，则当存储过程执行结束后临时表消失权限10.1存储过程概述存储过程概述设计存储过程创建存储过程修改存储过程执行存储过程删除存储过程常用系统存储过程建立CLR存储过程第10章存储过程10.2设计存储过程用户可以设计自己的存储过程，以提高业务逻辑使用的通用性。设计存储过程通常应该遵循以下原则：存储过程名称避免使用sp的前缀尽量不使用临时存储过程。CREATE/ALTERPROCEDURE必须是批处理的第一个语句。使用CREATEPROCEDURE定义SQL语句集合（不能使用下表中的语句）：10.2设计存储过程存储过程概述设计存储过程创建存储过程修改存储过程执行存储过程删除存储过程常用系统存储过程建立CLR存储过程第10章存储过程10.3.1在SQLServerManagementStudio中建立存储过程用户可以使用SQLServerManagementStudio工具建立存储过程10.3.2用CREATEPROCEDURE语句创建存储过程创建存储过程只能在当前数据库内创建存储过程，除了临时存储过程。临时存储过程总是创建在tempdb数据库中存储过程可以引用表、视图、用户定义函数、其他存储过程以及临时表若存储过程创建了局部临时表，则当存储过程执行结束后临时表消失USENorthwindGOCREATEPROCdbo.OverdueOrdersASSELECT*FROMdbo.OrdersWHERERequiredDate<GETDATE()ANDShippedDateISNull语法CREATEPROC[EDURE][schema_name.]proceduce_name[;number]/*定义存储过程名，number对同名的过程分组*/[{@parameterdata_type}/*定义参数类型[VARYING][=default][OUT[PUT]]/*定义参数属性[,…n][WITH{RECOMPILE|ENCRYPTION}]/*定义存储过程的处理方式ASsql_statement[…n]/*执行的操作10.3.2用CREATEPROCEDURE语句创建存储过程注释：存储过程名必须符合标识符规则。Number为可选参数，用于区分同名的存储过程@parameter为存储过程的形参，data_type指定数据类型Default指定存储过程输入参数的默认值，OUTPUT指定参数从存储过程的返回信息。RECOMPILE表明每次运行该过程，将对其重新编译，ENCRYPTION对包含创建存储过程的文本加密。sql_statement代表过程体包含的T-SQL语句10.3.2用CREATEPROCEDURE语句创建存储过程--示例：使用带有复杂SELECT语句的简单过程--下面的存储过程从四个表的联接中返回所有作者、出版的书籍以及出版社。该存储过程不使用任何参数。USEpubsifobject_id('au_info_all','P')isnotnull DROPPROCEDUREau_info_allGOCREATEPROCEDUREau_info_allASSELECTau_lname,au_fname,title,pub_nameFROMauthorsaINNERJOINtitleauthortaONa.au_id=ta.au_idINNERJOINtitlestONt.title_id=ta.title_idINNERJOINpublisherspONt.pub_id=p.pub_id查看存储过程的信息查看所有类型存储过程的额外信息系统存储过程sp_help、sp_helptext、sp_depends显示数据库中的存储过程以及拥有者名字的列表系统存储过程sp_stored_procedures得到存储过程的信息查询系统视图sys.sysobjects、sys.syscomments、sys.sysdepends10.3.2用CREATEPROCEDURE语句创建存储过程嵌套存储过程：一个存储过程调用另一个存储过程可以嵌套32层。若试图超过32层嵌套，则整个存储过程调用链失败当前的嵌套层数存储在系统函数@@nestlevel中若一个存储过程调用了第二个存储过程，那么第二个存储过程可以访问第一个存储过程创建的所有对象，包括临时表嵌套的存储过程可以递归调用。10.3.2用CREATEPROCEDURE语句创建存储过程CREATEPROCEDUREinnerprocasselect@@NESTLEVELAS'InnerLevel'示例1CREATEPROCEDUREouterprocasselect@@NESTLEVELAS'OuterLevel'EXECinnerproc示例210.3.2用CREATEPROCEDURE语句创建存储过程重命名存储过程重命名存储过程sp_renameoldname,newname限定存储过程所引用的对象名称创建存储过程的准则ü每个任务创建一个存储过程ü创建，测试存储过程，并对其进行故障诊断ü存储过程名称避免使用sp_前缀

ü尽可能减少临时存储过程的使用

ü存储过程的参数分两种：输入参数输出参数输入参数：用于向存储过程传入值，类似C语言的按值传递;输出参数：用于在调用存储过程后，返回结果，类似C语言的按引用传递;intsum(inta,intb){ints;s=a+b;returns;}c=sum（5,8）传入参数值返回结果存储过程的参数输入参数允许传递信息到存储过程内在CREATEPROCEDURE中指定@参数名数据类型[=默认值]指定参数的原则所有的输入参数值都应在存储过程开始时进行检查，以尽早捕获缺失值和非法值的情况应为参数提供合适的默认值。若定义了默认值，用户可以在未指定参数值的基础上执行存储过程参数对存储过程而言是局部的。输入参数输入参数示例：CREATEPROCProduction.LongLeadProducts@MinimumLengthintASIF(@MinimumLength<0)

BEGIN

RAISERROR('Invalidleadtime.',14,1)

RETURN

ENDSELECT Name,ProductNumber,DaysToManufactureFROM Production.ProductWHERE DaysToManufacture>=@MinimumLengthORDERBYDaysToManufactureDESC,NameEXECProduction.LongLeadProducts@MinimumLength=4CREATEPROCEDUREdbo.GetProducts2@namevarchar(50)ASSELECT*FROMProduction.ProductWHEREName=@name输入参数示例：以下执行语句均正确execGetProducts2'HexNut22'execGetProducts2@name='HexNut22'GetProducts2'HexNut22'输入参数通过参数名传递值在EXECUTE语句中以“@参数名=值”的格式指定参数称为通过参数名传递当通过参数名传递值时，可以以任何顺序指定参数值，并且可以省略允许空值或具有默认值的参数若定义了参数的默认值，当调用存储过程时，参数课未指定值或者参数的值指定为DEFAULT关键字输入参数输入参数提供合适的默认值，验证输入参数值，包括空值检查CREATEPROCProduction.LongLeadProducts@MinimumLengthint=1 --defaultvalueASIF(@MinimumLength<0) --validate

BEGIN

RAISERROR('Invalidleadtime.',14,1)

RETURN

ENDSELECT Name,ProductNumber,DaysToManufactureFROM Production.ProductWHERE DaysToManufacture>=@MinimumLengthORDERBYDaysToManufactureDESC,NameEXECProduction.LongLeadProducts通过位置传递参数只传递值（而没有对被传值参数的引用）称为通过位置传递参数值必须以参数在CREATEPROCEDURE语句中的定义顺序列出可以忽略有默认值的参数，但不能中断次序输入参数CREATEPROCEDUREdbo.[sum1]@aint,@bintASDECLARE@sumintSET@sum=@a+@bprint@sum输入参数以下方式执行均可：exec[sum1]@a=1,@b=2exec[sum1]@b=2,@a=1exec[sum1]1,2CREATEPROCEDUREdbo.[yeartoyearsales]@beginningdatedatetime,@endingdatedatetimeASIF@beginningdateISNULLOR@endingdateISNULLBEGINRAISERROR(‘NULLvaluearenotallowed’,14,1)RETURNENDSELECTO.Shippeddate,O.Orderid,OS.Subtotal,DATENAME(yy,Shippeddate)ASyearFROMORDERSOINNERJOIN[OrderSubtotals]OSONO.Orderid=OS.OrderidWHEREO.ShippeddateBETWEEN@beginningdateAND@endingdate输入参数输出参数：以OUTPUT关键字指定的变量存储过程通过输出参数向调用它的存储过程或客户端返回信息通过输出参数，存储过程的运行结果可以得到保留，即使存储过程运行结束输出参数的特性调用语句必须包含一个变量名，以接受返回值。不能传递常数可以在随后的Transact-SQL语句中使用返回变量输出参数和返回值CREATEPROCEDUREdbo.[sum]@aint,@bint,@sumintOUTPUTASSET@sum=@a+@b示例DECLARE@answerintexec[sum]1,2,@answerOUTPUTselect@answer输出参数和返回值DECLARE@answerintexec[sum]@a=1,@b=2,@sum=@answerOUTPUTselect@answerCREATEPROCHumanResources.AddDepartment@Namenvarchar(50),@GroupNamenvarchar(50),@DeptIDsmallintOUTPUTASINSERTINTOHumanResources.Department(Name,GroupName)VALUES (@Name,@GroupName)SET@DeptID=SCOPE_IDENTITY()输出参数和返回值CREATEPROCHumanResources.AddDepartment@Namenvarchar(50),@GroupNamenvarchar(50),@DeptIDsmallintOUTPUTASIF((@Name='')OR(@GroupName=''))RETURN-1INSERTINTOHumanResources.Department(Name,GroupName)VALUES (@Name,@GroupName)SET@DeptID=SCOPE_IDENTITY()RETURN0DECLARE@deptintEXECAddDepartment'Refunds','',@deptOUTPUTSELECT@deptDECLARE@deptint,@resultintEXEC@result=AddDepartment'Refunds','',@deptOUTPUTIF(@result=0) SELECT@deptELSE SELECT'Errorduringinsert'显式地重新编译存储过程存储过程可以显式地重新编译。显式重新编译过程的方法CREATEPROCEDURE[WITHRECOMPILE]创建存储过程时在其定义中指定WITHRECOMPILE选项，表明SQLServer将不对该存储过程计划进行高速缓存，该存储过程将在每次执行时都重新编译EXECUTE[WITHRECOMPILE]在执行存储过程时指定WITHRECOMPILE选项，可强制对存储过程进行重新编译--示例：使用WITHRECOMPILE选项--如果为过程提供的参数不是典型的参数，并且新的执行计划不应高速缓存或存储在内存中，WITHRECOMPILE子句会很有帮助。USEpubsGOCREATEPROCEDUREtitles_by_author@@LNAME_PATTERNvarchar(30)='%'WITHRECOMPILEASSELECTRTRIM(au_fname)+''+RTRIM(au_lname)AS'Authorsfullname',titleASTitleFROMauthorsaINNERJOINtitleauthortaONa.au_id=ta.au_idINNERJOINtitlestONta.title_id=t.title_idWHEREau_lnameLIKE@@LNAME_PATTERN创建对用户隐藏存储过程文本的存储过程encrypt_this：检索authors的所有内容。（提示：WITHENCRYPTION选项）练习41精选PPTUSEpubsGOCREATEPROCEDUREencrypt_thisWITHENCRYPTIONASSELECT*FROMauthors练习——答案42精选PPT创建一个更新雇员电话号码的存储过程，电话号码保存在表Employees中，要求执行存储过程时，可将指定的雇员的电话号码改成新的号码。练习43精选PPT练习——答案CREATEPROCEDUREUpdateEmployeePhone (@EmployeeID_1int, @HomePhone_2 nvarchar(24))ASUPDATENorthwind.dbo.EmployeesSETHomePhone =@HomePhone_2WHEREEmployeeID=@EmployeeID_144精选PPT练习——答案创建后执行存储过程验证。EXECUpdateEmployeePhone@EmployeeID_1=6,@HomePhone_2=’(206)-555-7773’45精选PPT练习创建一个名为FindCustomer1的存储过程，可以用它来找出SQLSERVER中的northwind数据库的Customer表中，CustomerID为指定值（输入参数）的记录的ContactName字段的名称，另外指定一个输出参数LineNum做为输出参数，还有必须在存储过程中判断CustomerID不能为空串，是的话要打印出出错信息，并返回错误值-1,如果查询成功在输出变量LineNum中保留选出的行数，然后返回值0。写出相应的SQL语句.46精选PPT练习——答案CREATEPROCFindCustomer@LineNumintOUTPUT,@CustomerIDchar(5)ASIFLEN(@CustomerID)=0BEGINPRINT'YoumustsupplyavalidCustomerID'RETURN-1ENDSELECTcontactNameFromCustomersWHERECustomerID=@CustomerIDSET@LineNum=@@ROWCOUNTRETURN047精选PPT练习——答案执行过程一：declare@LineNumintexecFindCustomer@LineNumOUTPUT,'ALFKI'select@LineNum执行过程二：DECLARE@LineNumint,@resultintEXEC@result=FindCustome@LineNumOUTPUT,'ALFKIIF(@result=0) SELECT@LineNumELSE SELECT'ErrorCustomerID'48精选PPT存储过程概述设计存储过程创建存储过程修改存储过程执行存储过程删除存储过程常用系统存储过程建立CLR存储过程第10章存储过程修改存储过程USENorthwindGOALTERPROCdbo.OverdueOrdersASSELECTCONVERT(char(8),RequiredDate,1)RequiredDate,CONVERT(char(8),OrderDate,1)OrderDate,OrderID,CustomerID,EmployeeIDFROMOrdersWHERERequiredDate<GETDATE()ANDShippedDateISNullORDERBYRequiredDateGO10.4修改存储过程语法ALTERPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASsql_statement[...n]10.4修改存储过程修改存储过程的注意事项若想修改带选项创建的存储过程，例如WITHENCRYPTION选项，则必须在ALTERPROCEDURE语句中包括那些选项，以保留选项的功能ALTERPROCEDURE语句只更改单个表，不影响嵌套的存储过程ALTERPROCEDURE权限10.4修改存储过程存储过程概述设计存储过程创建存储过程修改存储过程执行存储过程删除存储过程常用系统存储过程建立CLR存储过程第10章存储过程存储过程的两种执行方法；1）单独执行存储过程（EXECUTE）2）作为INSERT语句的一部分执行存储过程10.5执行存储过程1）单独执行存储过程语法：[[EXEC[UTE]]{[@返回状态=]{存储过程名[;编号]|

@存储过程名称变量}[[@参数=]{值|@变量[OUTPUT]|[DEFAULT]][,...n][WITHRECOMPILE]例如：EXECOverdueOrders10.5执行存储过程2）在INSERT语句内执行存储过程语法：INSERTINTO表名EXEC[UTE]……将本地或远程存储过程返回的结果集插入本地表中在INSERT语句内执行的存储过程必须返回关系结果集10.5执行存储过程createprocinsert_procasselect*fromstudentwherestu_sex='女‘执行：insertintogirl_studentexecinsert_proc存储过程概述设计存储过程创建存储过程修改存储过程执行存储过程删除存储过程常用系统存储过程建立CLR存储过程第10章存储过程10.6删除存储过程删除存储过程语法：DROPPROCEDURE{存储过程名}[,...n]删除存储过程的注意事项在删除存储过程之前，执行sp_depends检查是否有对象依赖于此存储过程DROPPROCProduction.LongLeadProducts存储过程概述设计存储过程创建存储过程修改存储过程执行存储过程删除存储过程常用系统存储过程建立CLR存储过程第10章存储过程10.7常