SQLServer数据库应用第八章.ppt

1,第八章 存储过程及其应用,2,8.1 存储过程概述,存储过程(stored procedure)是存放在服务器上的预先编译好的SQL语句,用于完成某项任务，它可以接受参数、返回状态值和参数值，并且可以嵌套调用,3,存储过程类型,系统存储过程 本地存储过程 临时存储过程 远程存储过程 扩展存储过程,本章主要内容,4,系统存储过程：存储在master数据库中，由前缀sp标识 作用：从系统表中获取信息，允许系统管理员在没有直接更新底层表的许可权下执行更新数据库中系统表的数据库管理工作。绝大部分的系统存储过程可以在任何数据库中执行,5,本地存储过程：这是用户在独立的用户数据库中为了完成某一特定功能而编写的存储过程,临时存储过程：它与临时表类似，通常又分为本地和全局临时存储过程两种，当临时存储过程为本地时，其名字以符号#开始，为全局时，以符号#开始,6,远程存储过程：远程存储过程在分布式查询中使用,扩展存储过程：使SQL Server可动态装载并执行 DLL。这样用户可使用象 C 这样的编程语言创建自己的外部例程 扩展存储过程由前缀xp标识,7,存储过程的优点,提供了安全机制 改进了执行性能 减少了网络流量 允许模块化程序设计,8,存储过程提供的安全机制可以让用户通过存储过程操作数据库中的数据，而不让用户直接操作于存储过程相关的表，从而保证数据库中数据的安全性,9,存储过程在第二次执行时，无需预编译，从而改进系统的执行性能,存储过程是存放在服务器上的预先编译好的单条或多条SQL语句并在服务器上运行，用户无需在网络上发送上百个SQL语句，或是将众多数据从服务器下载至客户端后再进行处理，从而大大减少了网络负载,10,增强代码的可重用性，提高开发效率。存储过程可以视为为完成某特定功能而编写的功能模块，将来可以在其他的存储过程中引用该存储过程，从而实现代码的重用性，加快应用的开发速度，提高开发的质量和效率,11,存储过程中包含的输入/出参数,8.2 创建存储过程,8.2.1创建简单的存储过程,CREATE PROCEDURE owner.procedure_name;number parameter data_type VARYING=defaultOUTPUT，n WITHRECOMPILE|ENCRYPTION| RECOMPILE, ENCRYPTION FOR REPLICATION AS sql_statements,重新编译,加密,在存储过程中需执行的操作,12,例：在My_DB1上新建my_procedure1存储过程，以返回所有diagrm_id=100和principal=200的信息。 USE My_DB1 go CREATE PROC dbo.my_procedure1 AS SELECT * FROM dbo.sysdiagrms WHERE diagrm_id=100 AND principal=200 Go,13,创建存储过程的步骤及注意事项,不能将CREATE PROCEDURE语句与其它SQL语句组合到单个批处理中,创建存储过程是有权限的，其默认权限为dbo，其他用户若要获得创建存储过程的权限，要由dbo授权,14,存储过程是数据库对象，在命名用户自定义的存储过程时应避免使用sp前缀，以免和系统存储过程混淆,尽量不要使用临时存储过程，以避免tempdb上造成的对系统表资源的争夺，从而影响系统的执行性能,15,存储过程最大尺寸被限制为128 MB，存储过程最多允许嵌套至32级,16,例：编写指令执行my_procedure1存储过程 USE Northwind EXEC my_procedure1 GO,17,查看存储过程信息,sysobjects syscomments sysdepends sp_stored_procedures,可以使用如下命令：,18,例：编写SQL指令查看创建存储过程my_procedure1的相关代码信息 USE Northwind EXEC sp_helptext my_procedure1 GO,19,8.2.2 创建和执行含参数存储过程,通过使用参数，可以多次使用同一存储过程并按指定要求查找数据库,20,1. 创建带输入参数的存储过程,输入参数是指由调用程序向存储过程传递的参数，为了定义接受输入参数的存储过程，需要在CREATE PROCEDURE语句中声明一个或多个变量作为参数。 如： parameter_name datatype=default,必须是常量或NULL,21,例：在Northwind库上创建存储过程my_procedure2的，其中定义了两个时间类型的输入参数和一个字符型输入参数，返回所有日期在两输入时间之间、目的地与输入的字符型参数相同的订单，其中字符型输入参数指定的默认值为USA。,22,USE Northwind go CREATE PROC dbo.my_procedure2 StartDate DateTime, EndDate DateTime, Country varchar(20)=USA AS,23,IF (StartDate IS NULL or EndDate IS NULL or Country IS NULL) BEGIN RAISERROR (NULL value are invalid,5,5) RETURN END,返回用户定义的错误信息并设系统标志,24,SELECT * FROM dbo.orders WHERE OrderDate BETWEEN StartDate AND EndDate AND shipCountry=Country go,25,2. 执行带输入参数的存储过程,使用参数名传送参数值 按位置传送参数值,两种方法,由如下语句给出传递值 parameter_name=value,不参照被传递的参数而直接给出参数的传递值,26,例：使用参数名传送参数值的方法，通过my_procedure2存储过程，返回所有日期在1997.7.1与1998.2.1日，目的地为USA的订单记录。 USE Northwind GO EXEC my_procedure2 EndDate=2/1/1998, StartDate=7/1/1997,27,例：使用按位置传送参数值的方法，通过my_procedure2存储过程，返回所有日期在1997.7.1与1998.2.1日，目的地为USA的订单记录。 USE Northwind GO EXEC my_procedure2 7/1/1997，2/1/1998,USA,28,3. 创建带输出参数的存储过程,在创建存储过程的语句中定义输出参数可以实现从存储过程中返回值为了使用输出参数。 在CREATE PROCEDURE中指定OUTPUT parameter_name datatype=Default OUTPUT,29,例：在Northwind上创建my_procedure3存储过程，该存储过程在my_procedure2的基础上，使用输出参数返回符合要求的订单的条数。 USE Northwind GO CREATE PROC dbo.my_procedure3 StartDate DateTime, EndDate DateTime, Country varchar(20)=USA, recordcount int OUTPUT AS,30,IF (StartDate IS NULL or EndDate IS NULL or Country IS NULL) BEGIN RAISERROR(NUL value are invalid,5,5) RETURN END,31,SELECT * FROM dbo.orders WHERE OrderDate BETWEEN StartDate AND EndDate AND ShipCountry=Country SELECT recordcount=ROWCOUNT,用来返回受上一语句影响的行数的系统变量，在这里我们用它来返回符合条件订单的条数,32,4. 执行带输出参数的存储过程,为了接收存储过程的返回值，必须声明作为输出的传递参数 在EXECUTE语句中指定OUTPUT,33,例：执行my_procedure3存储过程，返回在1997.7.1与1998.2.1之间、目的地为Germany的记录的条数 USE Northwind GO DECLARE recordnumber int EXEC my_procedure3 7/1/1997,2/1/1998,Germany, recordnumber OUTPUT PRINT The order count is:+ str(recordnumber),34,5.存储过程的重编译处理,存储过程的处理,SQL Server在创建存储过程时，需进行语法检查，若存在语法错误，将返回错误，并不创建该存储过程；若语法正确，则存储过程的文本将存储在syscomments系统表中,35,存储过程的重编译处理,三种方法,建立存储过程时设定重编译选项 CREATE PROCEDURE WITH RECOMPILE,在执行存储过程时设定重编译选项 EXECUTE PROCEDURE_NAME PARAMETER WITH RECOMPILE,通过系统存储过程设定重编译选项 EXEC sp_recompile OBJECT,36,8.3修改和删除存储过程,8.3.1 修改存储过程,ALTER PROC procedure_name;number parameter data_type VARYING=defaultOUTPUT,n WITH RECOMPILE|ENCRYPTION| RECOMPILE,ENCRYTION FOR REPLICATION AS Sql_statement,n,37,例：修改存储过程my_procedure1,返回1998.1.1(含)以后的、目的地为美国的, CustomerID, OrderDate, ShipCountry, OrderID字段, 并指定重编译和加密选项 USE Northwind go ALTER PROC dbo.my_procedure1 WITH RECOMPILE, ENCRYPTION AS SELECT OrderID，CustomerID，OrderDate，ShipCountry FROM dbo.orders WHERE OrderDate=1/1/1998 AND ShipCountry=USA,38,8.3.2 删除存储过程,DROP PROCEDURE procedure_name,39,8.4 系统存储过程和扩展存储过程,8.4.1系统存储过程,sp_helpsql：显示关于SQL语句、存 储过程和其他主题的信息 sp_help：提供关于存储过程或其他 数据库对象的报告 sp_helptext：显示存储过程和其他对 象的文本 sp_depends：列举引用或依赖指定对 象的所有存储过程,40,角色定义了一组Windows 2003 用户帐户和组，是用来实现最终用户安全性的,例：利用sp_addgroup命令在当前数据库中建立一个角色my_group use master go EXEC sp_addgroup my_group,41,8.4.2 扩展存储过程,扩展存储过程提供一种方法，以类似于存储过程的方式，动态装入和执行DDL内的函数，扩展了SQL Server功能,利用扩展存储过xp_enumgroups 提供基于本地Windows 2003的组的列表，或是Windows 2003域中定义的组的列表 EXEC xp_enumgroups,42,提供基于本地NT组的列表，或在指定的NT域中定义的组的列表,43,报告服务器的登录安全性配置,