《SQLServer数据库应用第八章.ppt》由会员分享，可在线阅读，更多相关《SQLServer数据库应用第八章.ppt（43页珍藏版）》请在金锄头文库上搜索。
1、第八章第八章存储过程及其应用存储过程及其应用 18.1 8.1 存储过程概述存储过程概述 存储过程存储过程(stored (stored procedure)procedure)是存放在服务器上的是存放在服务器上的预先编译好的预先编译好的SQLSQL语句语句, ,用于完成用于完成某项任务，它可以接受参数、返某项任务，它可以接受参数、返回状态值和参数值，并且可以嵌回状态值和参数值，并且可以嵌套调用套调用 2存储过程类型系统存储过程系统存储过程本地存储过程本地存储过程临时存储过程临时存储过程远程存储过程远程存储过程扩展存储过程扩展存储过程本章主要本章主要内容内容3系统存储过程：系统存储过程：存储在
2、存储在mastermaster数据库中，由前缀数据库中，由前缀spsp标识标识作用：作用：从系统表中获取信息，从系统表中获取信息，允许系统管理员在没有直接更允许系统管理员在没有直接更新底层表的许可权下执行更新新底层表的许可权下执行更新数据库中系统表的数据库管理数据库中系统表的数据库管理工作。工作。绝大部分的系统存储过绝大部分的系统存储过程可以在任何数据库中执行程可以在任何数据库中执行 4本地存储过程：本地存储过程：这是用户在独立的这是用户在独立的用户数据库中为了完成某一特定功用户数据库中为了完成某一特定功能而编写的存储过程能而编写的存储过程临时存储过程：临时存储过程：它它与临时表类似，与临时表
3、类似，通常又分为通常又分为本地本地和和全局全局临时存储过临时存储过程程两种两种，当，当临时临时存储过程为本地时，存储过程为本地时，其名字以符号其名字以符号#开始，为开始，为全局全局时，时，以符号以符号#开始开始5远程存储过程：远程存储过程：远程存储过程在远程存储过程在分布式分布式查询中使用查询中使用扩展存储过程：扩展存储过程：使使SQLSQLServerServer可可动态装载并执行动态装载并执行DLL。这样用这样用户可使用象户可使用象C这样的编程语言这样的编程语言创建自己的外部例程创建自己的外部例程扩展存储过程由前缀扩展存储过程由前缀xp标识标识6存储过程的优点存储过程的优点提供了安全机制提
4、供了安全机制改进了执行性能改进了执行性能减少了网络流量减少了网络流量允许模块化程序设计允许模块化程序设计7 存储过程存储过程提供提供的安全的安全机制机制可以让用户通过存储可以让用户通过存储过程操作数据库中的数据，过程操作数据库中的数据，而不让用户直接操作于存而不让用户直接操作于存储过程相关的表，从而保储过程相关的表，从而保证数据库中数据的安全性证数据库中数据的安全性 8存储过程存储过程在第二次执行时，无需预在第二次执行时，无需预编译，从而改进系统的编译，从而改进系统的执行性能执行性能存储过程存储过程是存放在服务器上的预先是存放在服务器上的预先编译好的编译好的单条或多条单条或多条SQL语句语句并
5、并在在服务器上运行服务器上运行，用户无需在网络上，用户无需在网络上发送上百个发送上百个SQL语句，或是将众多语句，或是将众多数据从服务器下载至客户端后再进数据从服务器下载至客户端后再进行处理，从而行处理，从而大大减少了网络负载大大减少了网络负载9增强代码的可重用性，提高开增强代码的可重用性，提高开发效率发效率。存储过程可以视为为。存储过程可以视为为完成某特定功能而编写的功能完成某特定功能而编写的功能模块模块，将来可以在其他的存储，将来可以在其他的存储过程中引用该存储过程，从而过程中引用该存储过程，从而实现代码的重用性，加快应用实现代码的重用性，加快应用的开发速度，提高开发的质量的开发速度，提高
6、开发的质量和效率和效率10存储过程中包含存储过程中包含的输入的输入/出参数出参数8.2创建存储过程创建存储过程8.2.18.2.1创建简单的存储过程创建简单的存储过程CREATE PROCEDURE CREATE PROCEDURE owner.procedure_name;numberowner.procedure_name;numberparameter data_typeparameter data_typeVARYING=defaultOUTPUTVARYING=defaultOUTPUT，nnWITHRECOMPILE|ENCRYPTION|WITHRECOMPILE|ENCRYPT
7、ION|RECOMPILE, ENCRYPTIONRECOMPILE, ENCRYPTIONFOR REPLICATIONFOR REPLICATIONAS AS sql_statementssql_statements 重重新新编编译译加加密密在存储过在存储过程中需执程中需执行的操作行的操作11例：在My_DB1上新建my_procedure1存储过程，以返回所有diagrm_id=100和principal=200的信息。USEMy_DB1goCREATEPROCdbo.my_procedure1ASSELECT*FROMdbo.sysdiagrmsWHEREdiagrm_iddiagrm
8、_id=100=100 ANDprincipal=200principal=200Go12创建存储过程的步骤及注意事项创建存储过程的步骤及注意事项不能将CREATEPROCEDURE语句与其它SQL语句组合到单个批处理中创建存储过程是有权限的，其默认权限为dbo，其他用户若要获得创建存储过程的权限，要由dbo授权13存储过程是存储过程是数据库对象数据库对象，在命，在命名用户自定义的存储过程时应名用户自定义的存储过程时应避免使用避免使用sp前缀，以免和系统前缀，以免和系统存储过程混淆存储过程混淆尽量不要使用临时存储过程，尽量不要使用临时存储过程，以避免以避免tempdb上造成的对系上造成的对系统
9、表资源的争夺，从而影响系统表资源的争夺，从而影响系统的执行性能统的执行性能14存储过程最大尺寸被存储过程最大尺寸被限制为限制为128MB，存储过存储过程最多允许嵌套至程最多允许嵌套至32级级15例：例：编写指令执行编写指令执行my_procedure1存储过程存储过程USENorthwindEXECmy_procedure1GO16查看存查看存储过程信息储过程信息sysobjectssyscommentssysdependssp_stored_procedures可以使用如下命令：可以使用如下命令：17例：编写例：编写SQL指令查看创建存储过指令查看创建存储过程程my_procedure1的相
10、关代码信息的相关代码信息USENorthwindEXECsp_helptextmy_procedure1GO188.2.2创建和执行含参数存储过程创建和执行含参数存储过程通过使用参数，可以多通过使用参数，可以多次使用同一存储过程并次使用同一存储过程并按指定要求查找数据库按指定要求查找数据库191.创建创建带带输入参数输入参数的存储过程的存储过程输入参数输入参数是指由调用程序向存储过是指由调用程序向存储过程传递的参数，为了定义接受输入程传递的参数，为了定义接受输入参数的存储过程，需要在参数的存储过程，需要在CREATEPROCEDURE语句中声明一个或语句中声明一个或多个变量作为参数。多个变量作
11、为参数。如：如：parameter_namedatatype=default必须是常量必须是常量或或NULL20例：例：在在Northwind库上创建存储库上创建存储过程过程my_procedure2的，其中定的，其中定义了两个时间类型的输入参数和义了两个时间类型的输入参数和一个字符型输入参数，一个字符型输入参数，返回所有返回所有日期在两输入时间之间、目的地日期在两输入时间之间、目的地与输入的字符型参数相同的订单与输入的字符型参数相同的订单，其中字符型输入参数指定的默认其中字符型输入参数指定的默认值为值为USA。21USENorthwindgoCREATECREATEPROCPROCdbo.m
12、y_procedure2StartDateDateTime,EndDateDateTime,Countryvarchar(20)=USAAS22IF(StartDateISNULLorEndDateISNULLorCountryISNULL)BEGINRAISERRORRAISERROR(NULLvalueareinvalid,5,5)RETURNEND返回用户定义返回用户定义的错误信息并的错误信息并设系统标志设系统标志23SELECT*FROMdbo.ordersWHEREOrderDateBETWEENStartDateANDEndDateANDshipCountry=Countrygo2
13、42.执行执行带带输入参数输入参数的存储过程的存储过程使用参数名传送参数值使用参数名传送参数值按位置传送参数值按位置传送参数值两种方法由如下语句给出传递值由如下语句给出传递值parameter_name=value不参照被传递的不参照被传递的参数而直接给出参数而直接给出参数的传递值参数的传递值25例：例：使用使用参数名参数名传送参数值的方法，传送参数值的方法，通过通过my_procedure2存储过程，返存储过程，返回所有日期在回所有日期在1997.7.1与与1998.2.1日，日，目的地为目的地为USA的订单记录。的订单记录。USENorthwindGOEXECmy_procedure2En
14、dDate=2/1/1998,StartDate=7/1/199726例：例：使用按位置传送参数值的方法，使用按位置传送参数值的方法，通过通过my_procedure2存储过程，返存储过程，返回所有日期在回所有日期在1997.7.1与与1998.2.1日，日，目的地为目的地为USA的订单记录。的订单记录。USENorthwindGOEXECmy_procedure27/1/1997，2/1/1998,USA273.创建创建带带输出参数输出参数的存储过程的存储过程在创建存储过程的语句中定义输出在创建存储过程的语句中定义输出参数可以实现从存储过程中返回值参数可以实现从存储过程中返回值为了使用输出参
15、数。为了使用输出参数。在在CREATECREATEPROCEDUREPROCEDURE中指定中指定OUTPUTOUTPUTparameter_namedatatype=DefaultOUTPUT28例：在Northwind上创建my_procedure3存储过程，该存储过程在my_procedure2的基础上，使用输出参数返回符合要求的订单的条数。USENorthwindGOCREATEPROCdbo.my_procedure3StartDateDateTime,EndDateDateTime,Countryvarchar(20)=USA,recordcountintOUTPUTAS29IF
16、(StartDateISNULLorEndDateISNULLorCountryISNULL)BEGINRAISERROR(NULvalueareinvalid,5,5)RETURNEND30SELECT*FROMdbo.ordersWHEREWHEREOrderDateBETWEENBETWEENStartDateANDANDEndDateANDShipCountry=CountrySELECTrecordcount=ROWCOUNT用来返回受上一语句影用来返回受上一语句影响的行数的系统变量，响的行数的系统变量，在这里我们用它来返回在这里我们用它来返回符合条件订单的条数符合条件订单的条数31
17、4.执行执行带带输出参数输出参数的存储过程的存储过程为了接收存储过程的返回值，为了接收存储过程的返回值，必须声明作为输出的传递参数必须声明作为输出的传递参数在在EXECUTEEXECUTE语句中指定语句中指定OUTPUTOUTPUT32例：例：执行执行my_procedure3存储过程，存储过程，返回在返回在1997.7.1与与1998.2.1之间、目之间、目的地为的地为Germany的记录的条数的记录的条数USENorthwindGODECLAREDECLARErecordnumberintEXECEXECmy_procedure37/1/1997,2/1/1998,Germany,reco
18、rdnumberOUTPUTPRINTPRINTTheordercountis:+str(recordnumber)335.5.存储过程的重编译处理存储过程的重编译处理存储过程的处理存储过程的处理SQLSQLServer在创建存储过程时，在创建存储过程时，需进行语法检查，若存在语法需进行语法检查，若存在语法错误，将返回错误，并不创建错误，将返回错误，并不创建该存储过程；若语法正确，则该存储过程；若语法正确，则存储过程的文本将存储在存储过程的文本将存储在syscommentssyscomments系统表中系统表中34存储过程的重编译处理存储过程的重编译处理三三种种方方法法建立存储过程时设定重编译
19、选项建立存储过程时设定重编译选项CREATE PROCEDURE WITH CREATE PROCEDURE WITH RECOMPILE RECOMPILE 在执行存储过程时设定重编译选项在执行存储过程时设定重编译选项EXECUTEEXECUTE PROCEDURE_NAMEPROCEDURE_NAME PARAMETER WITH RECOMPILE PARAMETER WITH RECOMPILE 通过系统存储过程设定重编译选项通过系统存储过程设定重编译选项EXEC sp_recompile OBJECTEXEC sp_recompile OBJECT 358.3修改和删除存储过程修改和
20、删除存储过程8.3.1修改存储过程修改存储过程ALTER PROC procedure_name;numberALTER PROC procedure_name;number parameter data_typeparameter data_typeVARYING=defaultOUTPUT,nVARYING=defaultOUTPUT,nWITH RECOMPILE|ENCRYPTION|WITH RECOMPILE|ENCRYPTION| RECOMPILE,ENCRYTION RECOMPILE,ENCRYTIONFOR REPLICATIONFOR REPLICATIONAS AS
21、Sql_statement,.nSql_statement,.n 36例：例：修改存储过程修改存储过程my_procedure1,my_procedure1,返回返回1998.1.1(含含)以后的、目的地为美国的以后的、目的地为美国的,CustomerIDCustomerID,OrderDateOrderDate,ShipCountryShipCountry,OrderIDOrderID字段字段,并指定并指定重编译重编译和和加密加密选项选项USENorthwindgoALTERALTERPROCPROCdbo.my_procedure1WITHWITHRECOMPILERECOMPILE,EN
22、CRYPTIONENCRYPTIONASSELECTSELECTOrderIDOrderID，CustomerIDCustomerID，OrderDateOrderDate，ShipCountryShipCountry FROM FROMdbo.ordersWHEREWHEREOrderDate=1/1/1998ANDShipCountry=USA378.3.2 8.3.2 删除存储过程删除存储过程DROP PROCEDURE procedure_nameDROP PROCEDURE procedure_name388.4系统存储过程和扩展存储过程系统存储过程和扩展存储过程8.4.18.4.1
23、系统存储过程系统存储过程 sp_helpsql：显示关于显示关于SQL语句、存语句、存储过程和其他主题的信息储过程和其他主题的信息sp_help：提供关于存储过程或其他提供关于存储过程或其他数据库对象的报告数据库对象的报告sp_helptext：显示存储过程和其他对显示存储过程和其他对象的文本象的文本sp_depends：列举引用或依赖指定对列举引用或依赖指定对象的所有存储过程象的所有存储过程39角色定义了一组角色定义了一组Windows Windows 2003 2003 用户帐户和组，是用用户帐户和组，是用来实现最终用户安全性的来实现最终用户安全性的例：例：利用利用sp_addgroup命
24、令在当前命令在当前数据库中建立一个角色数据库中建立一个角色my_groupusemastergoEXECsp_addgroupmy_group408.4.2扩展存储过程扩展存储过程扩展存储过程提供一种方法，扩展存储过程提供一种方法，以类似于存储过程的方式，动态以类似于存储过程的方式，动态装入和执行装入和执行DDLDDL内的函数，扩展内的函数，扩展了了SQLSQLServerServer功能功能利用扩展存储过利用扩展存储过xp_enumgroups提供基于本地提供基于本地Windows2003的组的的组的列表，或是列表，或是Windows 2003域中定义的组域中定义的组的列表的列表 EXECxp_enumgroups41提供基于本地提供基于本地NTNT组的列表，或在组的列表，或在指定的指定的NTNT域中定域中定义的组的列表义的组的列表42 报告服务器的报告服务器的登录安全性配置登录安全性配置 43
