SQL第08章 存储过程和触发器.ppt

MS SQL Server 程序设计 授课教师：黄丽萍 Email:98hlp@163.com 教研室：软件与理论教研室 第08章 存储过程和触发器 内容提要: 存储过程（Stored Procedure）是一组完成特定功能的Transact- SQL语句的集合。存储过程是通过用户、其他过程或触发器来调用执行。 利用存储过程可以保证数据的完整性，提高执行重复任务的性能和数据的一致性。 存储过程主要应用于控制访问权限、为数据库表中的活动创建审计追踪、将关系到数据库及其所有相关应用程序的数据定义语句和数据操作语句分隔开。 触发器（Trigger）是一种特殊的存储过程。触发器通常在特定的表上定义，当该表的相应事件发生时自动执行，用于实现强制业务规则和数据完整性等。 第08章 存储过程和触发器 本章内容: 8.1 存储过程概述 8.2 创建和管理存储过程 8.3 触发器概述 8.4 创建和管理触发器 8.5小结 8.1 存储过程概述 存储过程的主要用途： 提高了处理复杂任务的能力。 增强了代码的复用率和共享性。存储过程一旦创建后即可在程序中调用任意多次。 减少了网络中数据的流量。存储过程在服务器注册，加快了过程的运行速度。 加强了系统的安全性。存储过程具有安全特性（例如权限）和所有权链接，用户可以被授予权限来执行存储过程而不必直接对存储过程中引用的对象具有权限。 8.1 存储过程概述 8.1.1 存储过程的类型 SQL Server 2005支持的存储过程的类型主要有如下4类。 （1）系统存储过程。SQL Server 2005 中的许多管理活动都是存储过程执行的。 （2）用户定义的存储过程。 （3）临时存储过程。以“#”或“##”为前缀，表示局部临时存储过程和全局临时存储过程。 （4）扩展存储过程。以xp_为前缀，是SQL Server 2005的实例可以动态加载和运行的 DLL。 8.1 存储过程概述 8.1.2 存储过程的设计原则 用户创建存储过程时，应注意遵循以下几点原则。 存储过程最大不能超过128MB。 用户定义的存储过程只能在当前数据库中创建。 存储过程是为了处理那些需要被多次运行的Transact-SQL语句集。 SQL Server允许在存储过程创建时引用一个不存在的对象，系统只检查创建存储过程的语法。执行时，存储过程引用了一个不存在的对象，则这次执行操作将会失败。 存储过程可以嵌套使用。嵌套的最大层次可以用 @NESTLEVEL函数来查看。 8.1 存储过程概述 8.1.3 常用系统存储过程的使用 SQL Server 2005提供了许多系统存储过程,下面介绍几种常用的系统存储过程。 （1）sp_helpdb 用于查看数据库名称及大小。 （2）sp_helptext 用于显示规则、默认值、未加密的存储过程、用户定义函数、触发器或视图的文本。 （3）sp_renamedb 用于重命名数据库。 （4）sp_rename 用于更改当前数据库中用户创建对象（如表、列或用户定义数据类型）的名称。 （5）sp_helplogins查看所有数据库用户登录信息。 （6）sp_helpsrvrolemember 用于以查看所有数据库用户所属的角色信息。 8.2 创建和管理存储过程 8.2.1 创建存储过程 2．使用CREATE PROCEDURE语句创建存储过程 CREATE PROCEDURE语句的语法格式如下： CREATE PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter_data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [ ,…n ] [ WITH { RECOMPILE | ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statament [ ,…n ] 8.2 创建和管理存储过程 使用输入参数 输入参数允许传递信息到存储过程内 在 CREATE PROCEDURE 中指定 @参数名 数据类型 [=默认值] 8.2 创建和管理存储过程 使用输出参数：如果需要从存储过程返回一个或多个值，可以通过在存储过程中指定输出参数来实现（以 OUTPUT 关键字指定的变量）。 在 CREATE PROCEDURE 中指定 @参数名 数据类型 OUTPUT 通过输出参数向调用它的存储过程或客户端返回信息 mathtutor.sql 8.2 创建和管理存储过程 8.2.1 创建存储过程 例8.2创建一个存储过程，输出指定学生的姓名及课程名称、期末成绩信息。 程序代码如下: CREATE PROCEDURE stud