《SQL数据库》PPT课件.ppt

在大型数据库系统中，存储过程和触发器具有很重要的作用。无论是存储过程还是触发器，都是SQL语句和流程控制语句的集合。就本质而言，触发器也是一种存储过程，它在特定语言事件发生时自动执行。存储过程在运算时生成执行代码，所以，以后对其再运行时其执行效率很高。SQL Server 2005不仅提供了用户自定义存储过程的功能，而且也提供了许多可作为工具使用的系统存储过程。 本章主要介绍存储过程和触发器的概念和分类以及它们的使用方法。,第10章 存储过程和触发器,10.1 存储过程 10.2 触发器,第10章 存储过程和触发器,10.1.1 存储过程概述 存储过程（Stored Procedure）是一组完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行存储过程。 利用SQL Server创建一个应用程序时，SQL是主要的编程语言。使用SQL进行编程，有两种方法。其一是，在本地存储SQL程序，并创建应用程序向SQL Server发送命令来对结果进行处理。其二是，可以把部分用SQL编写的程序作为存储过程存储在SQL Server中，然后创建应用程序来调用存储过程，对数据结果进行处理。,10.1 存储过程,存储过程具有以下优点： 1. 存储过程允许标准组件式编程 2. 存储过程能够实现较快的执行速度 3. 存储过程能够减少网络流量 4. 存储过程可被作为一种安全机制来充分利用,10.1 存储过程,10.1.2 存储过程的类型 在SQL Server 2005中有多种可用的存储过程。在SQL Server 2005中存储过程分为三类：系统提供的存储过程、用户自定义存储过程和扩展存储过程。 1. 系统存储过程 SQL Server 2005中的许多管理活动都是通过一种特殊的存储过程执行的，这种存储过程被称为系统存储过程。系统过程主要存储在master数据库中并以sp_为前缀，并且系统存储过程主要是从系统表中获取信息，从而为数据库系统管理员管理SQL Server提供支持。通过系统存储过程，SQL Server中的许多管理性或信息性的活动（如获取数据库和数据库对象的信息）都可以被顺利有效地完成。,10.1 存储过程,2. 用户自定义的存储过程 用户自定义存储过程是由用户创建并能完成某一特定功能（如查询用户所需数据信息）的存储过程，是封装了可重用代码的SQL语句模块。存储过程可以接受输入参数、向客户端返回表格或标量结果和消息、调用数据定义语言（DDL）和数据操作语言（DML）语句，以及返回输出参数。在SQL Server中，用户自定义的存储过程有两种类型：T-SQL存储过程或CLR存储过程。本书所提到的用户定义的存储过程主要指T-SQL存储过程。 3. 扩展存储过程 扩展存储过程允许使用高级编程语言（例如C）创建应用程序的外部例程，从而使得SQL Server的实例可以动态加载和运行DLL。扩展存储过程直接在SQL Server实例的地址空间中运行。,10.1 存储过程,10.1.3 存储过程的设计规则 使用SQL语句CREATE PROCEDURE可以创建存储过程。其语法格式如下： CREATE PROC | PROCEDURE schema_name. procedure_name ; number parameter type_schema_name. data_type VARYING = default OUT PUT ,.n WITH ,.n FOR REPLICATION AS ; .n | ;,10.1 存储过程,10.1.4 使用存储过程 当创建存储过程时，需要确定存储过程的三个组成部分： 1) 所有的输入参数以及传给调用者的输出参数。 2) 被执行的针对数据库的操作语句，包括调用其它存储过程的语句。 3) 返回给调用者的状态值，以指明调用是成功还是失败。 在SQL Server 2005中，创建存储过程有两种方法：一种是使用SQL Server Management Studio，另一种是使用T-SQL命令CREATE PROCEDURE。,10.1 存储过程,【例1】用SQL命令窗口创建存储过程 CREATE PROCEDURE GetStudent number varchar(40) AS SELECT * FROM student WHERE sno= number go EXECUTE GetStudent 95001,10.1 存储过程,【例2】创建带有通配符参数的存储过程 下面的存储过程只从学生表中返回指定的一些学生（提供名字和姓氏）的信息。该存储过程对传递的参数进行模式匹配，如果没有提供参数，则返回所有学生的信息。 CREATE PROCEDURE GetStudentByName name varchar(40) = % AS SELECT * FROM student WHERE Sname LIKE name; EXECUTE GetStudentByName; 或者 EXECUTE GetStudentByName 刘%;,10.1 存储过程,【例3】创建使用output参数的存储过程 下面的存储过程从SC表中返回指定系的学生的平均成绩。 CREATE PROCEDURE GetStudentAvgGradeByDept dept varchar(40),avggrade int out AS SET avggrade= (SELECT AVG(Grade) FROM SC WHERE sno in (SELECT SNO FROM student WHERE Sdept=dept); GO DECLARE avggrade int EXEC GetStudentAvgGradeByDept cs,avggrade OUT PRINT avggrade,10.1 存储过程,【例4】使用sp_helptext查看存储过程的源代码 sp_helptext GetStudentAvgGradeByDept 说明：如果存储过程使用WITH ENCRYPTION，其源代码无法查看。 【例5】使用WITH ENCRYPTION创建存储过程 CREATE PROCEDURE GetStudent1 number varchar(40) WITH ENCRYPTION AS SELECT * FROM student WHERE sno= number go,10.1 存储过程,10.1.5 重新编译存储过程 在执行诸如添加索引或更改索引列中的数据等操作更改了数据库时，应重新编译访问数据库表的原始查询计划以对其重新优化。在SQL Server 2005重新启动后，第一次运行存储过程时自动执行此优化。当存储过程使用的基础表发生变化时，也会自动执行此优化。但如果添加了存储过程可能从中受益的新索引，将不会自动执行优化，直到下一次SQL Server重新启动并再运行该存储过程时为止。在这种情况下，强制在下次执行存储过程时对其重新编译会很有用。,10.1 存储过程,SQL Server中，强制重新编译存储过程的方式有三种： 1) sp_recompile系统存储过程强制在下次执行存储过程时对其重新编译。 2) 创建存储过程时在其定义中指定WITH RECOMPILE选项，可以指明SQL Server将不为该存储过程缓存计划，在每次执行该存储过程时对其重新编译。当存储过程的参数值在各次执行间都有较大差异，导致每次均需创建不同的执行计划时，可使用WITH RECOMPILE选项。 3) 可以在执行存储过程时指定WITH RECOMPILE选项，强制对其重新编译。,10.1 存储过程,触发器是一种特殊的存储过程，它在特定语言事件发生时自动执行。 这一节主要介绍触发器的概念、作用以及其使用方法。,10.2 触发器,10.2.1 触发器概述 触发器是一种特殊类型的存储过程，它不同于前面介绍过的存储过程，主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时，SQL Server就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合由这些SQL语句所定义的规则。,10.2 触发器,触发器的主要作用就是实现由主键和外键所不能保证的复杂的参照完整性和数据一致性。除此之外，触发器还有其它许多不同的功能： 1. 强化约束（Enforce restriction） 2. 跟踪变化（Auditing changes） 3. 级联运行（Cascaded operation） 4. 存储过程的调用（Stored procedure invocation）,10.2 触发器,10.2.2 触发器的类型 SQL Server 包括两大类触发器：DML触发器和DDL触发器。 1. DML触发器 当数据库中发生数据操作语言（DML）事件时将调用DML触发器。DML事件包括在指定表或视图中修改数据的INSERT语句、UPDATE语句或DELETE语句。DML触发器可以查询其他表，还可以包含复杂的T-SQL语句。系统将触发器和触发它的语句作为可在触发器内回滚的单个事务对待，如果检测到错误（例如，磁盘空间不足），则整个事务即自动回滚。,10.2 触发器,SQL Server 2005的DML触发器分为两类:,After触发器 这类触发器是在记录已经改变完之后（after），才会被激活执行，它主要是用于记录变更后的处理或检查，一旦发现错误，可以用Rollback Transaction语句来回滚本次的操作。 只能在表上定义。 Instead Of触发器 INSTEAD OF 触发器用来代替通常的触发动作，即当对表进行INSERT、UPDATE 或 DELETE 操作时，系统不是直接对表执行这些操作，而是把操作内容交给触发器，让触发器检查所进行的操作是否正确。如正确才进行相应的操作。因此，INSTEAD OF 触发器的动作要早于表的约束处理。 可以在视图上定义。,2. DDL触发器 DDL触发器是SQL Server 2005的新增功能。像常规触发器一样，DDL触发器将激发存储过程以响应事件。但与DML触发器不同的是，它们不会为响应针对表或视图的UPDATE、INSERT或DELETE语句而激发，相反，它们会为响应多种数据定义语言 (DDL)语句而激发。这些语句主要是以CREATE、ALTER和DROP开头的语句。DDL触发器可用于管理任务，例如审核和控制数据库操作。,10.2 触发器,10.2.3 触发器的设计规则 使用T-SQL语句CREATE TRIGGER可以创建触发器。其语法格式如下： CREATE TRIGGER schema_name . trigger_name ON table | view WITH ,.n FOR | AFTER | INSTEAD OF INSERT , UPDATE , DELETE WITH APPEND NOT FOR REPLICATION AS sql_statement ; .n | EXTERNAL NAME ,10.2 触发器,在SQL Server 2005里，为每个DML触发器都定义了两个特殊的表，一个是插入表(inserted)，一个是删除表(deleted)。这两个表是建在数据库服务器的内存中的，是由系统管理的逻辑表，而不是真正存储在数据库中的物理表。对于这两个表，用户只有读取的权限，没有修改的权限。这两个表的结构与触发器所在数据表的结构是完全一致的，当触发器的工作完成之后，这两个表也将会从内存中删除。 插入表(inserted)里存放的是更新后的记录：对于插入记录操作来说，插入表里存放的是要插入的数据；对于更新记录操作来说，插入表里存放的是要更新的记录。 删除表(deleted)里存放的是更新前的记录：对于更新记录操作来说，删除表里存放的是更新前的记录（更新完后即被删除）；对于删除记录操作来说，删除表里存入的是被删除的旧记录。,10.2 触发器,DELETE触发器是当对表执行DELETE操作时删除元组，将删除的元组放入deleted表中。检查deleted表中的数据，确定该如何处理。 INSERT触发器在对执行插入数据操作时,将插入表中的数据拷贝并送入inserted表中，根据inserted表中的值决定如何处理。 UPDATE触发器仅在更新数据操作时将要被更新的原数据移入deleted表中，将更新后的数据备份送入inserted表中，对deleted和inserted表进行检查，并决定如何处理。,10.2 触发器,10.2.4 使用触发器 上面介绍了触发器的概念、作用和一些基本问题，下面将通过实例介绍在SQL Server 2005中如何使用触发器。 【例6】创建DDL触发器。 CREATE TRIGGER safety ON DATABASE FOR DROP_TABLE AS PRINT 不能修改表结构 ROLLBACK,10.2 触发器,【例7】创建DML触发器。定义触发器，要求学生已经选修3门后，不能再选修课程 create trigger 选修限制 on SC after insert,update as if (select count(*) from SC,inserted where inserted.sno=SC.sno)=4 begin rollback tra