第05章 存储过程和触发器讲义

《第05章 存储过程和触发器讲义》由会员分享，可在线阅读，更多相关《第05章 存储过程和触发器讲义（67页珍藏版）》请在金锄头文库上搜索。

1、第五章 存储过程、触发器和数据完整性 （ SQL的高级功能）,存储过程 触发器 数据完整性,任务,掌握存储过程，触发器的概念和使用方法； 掌握运用T-SQL编写基本的存储过程、触发器。（ ） 理解存储过程、触发器的用途； 进一步理解数据完整性的含义（） 了解数据完整性的规则、默认值等使用。,5.1 存储过程,5.1.1 存储过程的基本概念 5.1.2 存储过程的优点 5.1.3 存储过程的分类 5.1.4 存储过程的使用方法,5.1 存储过程,传统的数据库结构 管理数据等共享资源， 所有的应用程序都在用户端，都与用户实际运行的应用程序捆绑在一起； 客户/服务器数据库 管理数据等共享资源 承担一

2、些应用逻辑，完成来自客户端的一些处理请求，在数据库中还可以存放程序，即存储过程。,5.1.1 存储过程的基本概念,是什么：是事先编好的、存储在服务器端的数据库中的程序（预编译的SQL集合），这些程序用来完成对数据库的指定操作。 怎么使用：这些程序可以由应用程序的调用启动，或由数据完整性规则和触发器调用。 怎么理解：存储过程是用户可以简单地将其作为一个函数来调用，无须重复执行存储过程的SQL语句。它包含一组经常执行的、逻辑完整的SQL语句。,5.1.2 存储过程的优点,减轻程序编写的工作量：可以在各个程序中反复调用定义好的存储过程。 存储过程能够实现较快的执行速度 ：因为存储过程是预编译的，而批

3、处理的T- SQL 语句在每次运行时都要进行编译和优化，因此速度相对要慢一些。 存储过程能够减少网络流量 ： 对于同一个针对数据库对象的操作，所涉及到的 T-SQL 语句被组织成一存储过程，当在客户端调用该存储过程时，网络中传送的只是该调用语句 ，降低网络负载 。 存储过程增加安全机制：系统管理员通过对执行某一存储过程的权限进行限制，从而能够实现对相应的数据访问权限的限制，避免非授权用户对数据的访问，保证数据的安全。,不使用存储过程时，所有的数据处理都在客户端完成；而使用存储过程时，可以使数据处理在服务器端完成。,5.1.3 储存过程的分类,系统存储过程 SQL Server本身提供了一些存储

4、过程，用于管理SQL Server和显示有关数据库和用户的信息，我们称之为系统存储过程。系统存储过程都以“sp_” 开头，存储在master数据库中。 用户存储过程 用户也可以编写自己的存储过程，并把它存放在数据库中。这样安排的主要目的就是要充分发挥数据库服务器的功能，尽量减少网络上的堵塞。,5.1.4 存储过程的使用方法,创建存储过程 执行存储过程 修改存储过程 删除存储过程,创建存储过程,CREATE PROCedure procedure_name ; number parameter data_type = default , AS sql_statement,s procedure_

5、name：给出存储过程名； s number：对同名的存储过程指定一个序号； s parameter：给出参数名； s data_type：指出参数的数据类型； s = default：给出参数的默认值； s sql_statement：存储过程所要执行的SQL语句，它可以是一组SQL语句，可以包含流程控制语句等。,例：创建一个最简单的存储过程（无参数调用）：,CREATE PROCedure sp_getemp; 1 AS SELECT * FROM 职工,说明：创建存储过程sp_getemp;1，要求查询职工信息,例：带参数的存储过程：,CREATE PROCedure sp_getemp

6、; 2 (salary int) AS SELECT * FROM 职工 WHERE 工资 salary,说明：创建存储过程sp_getemp;2，要求查询工资值大于给定值的职工信息,说明：存储过程一般用来完成数据查询和数据处理操作，所以在存储过程中不可以使用创建数据库对象的语句，即在存储过程中一般不能含有以下语句： CREATE TABLE CREATE VIEW CREATE DEFAULT CREATE RULE CREATE TRIGGER CREATE PROCEDURE,执行存储过程,EXECute = =|,例：执行带参数的sp_getemp;2存储过程,Execute sp_g

7、etemp;2 1240,说明：执行存储过程sp_getemp;2，要求查询工资值大于1240元的职工信息,存储过程的返回值和状态信息,无论什么时候执行存储过程，总要返回一个结果码，用以指示存储过程的执行状态。如果存储过程执行成功，返回的结果码是0；如果存储过程执行失败，返回的结果码一般是一个负数，它和失败的类型有关。我们在创建存储过程时，也可以定义自己的状态码和错误信息。,常用的存储过程返回状态表,0 过程成功执行 1 对象丢失 2 发生数据类型错误 3 处理过程被死锁 4 发生权限错误 5 发生语法错误 6 发生恶意用户错误 7 发生资源错误 8 遭遇非致命的内部错误 9 遭遇系统限制 1

8、0 发生致命的内部不稳定性 12 表或索引被破坏 13 数据库被破坏 14 发生硬盘错误 通常用全局变量ERROR 返回最后执行的SQL 语句的错误代码。,CREATE PROCedure sp_getemp;3 (salary int =NULL) AS IF salary IS NULL BEGIN PRINT 必须提供一个数值作参数！ RETURN 13 END IF NOT EXISTS (SELECT * FROM 职工 WHERE 工资 salary) BEGIN PRINT 没有满足条件的记录！ RETURN -103 END SELECT * FROM 职工 WHERE 工资

9、salary RETURN 0,例：带参数和返回状态值的存储过程。,利用全局变量修改刚才的例子,CREATE PROCedure sp_getemp;3 (salary int =NULL) AS SELECT * FROM 职工 WHERE 工资 salary IF ERROR = 0 RETURN 0 ELSE RETURN -1,例：执行以上存储过程。,DECLARE status int EXECUTE status=sp_getemp;3 salary 1200 print status,存储过程的修改和删除,修改存储过程的语句是（一般格式）： ALTER PROCedure pro

10、cedure_name ; number parameter data_type = default , AS sql_statement 删除存储过程的语句是： DROP PROCedure procedure_name 注意：删除存储过程的语句中不能指定序号。也就是说，该语句将同时删除同名的所有存储过程。,小结：要用好存储过程,存储过程是客户/服务器机制的一个重要组成部分，如果使用客户/服务器机制的数据库管理系统，但是不理解存储过程或没有充分利用存储过程，那将使客户/服务器机制的功能大打折扣，使系统的整体性能可能降低很多。,5.2 触发器,5.2.1 触发器的基本概念 5.2.2 触发器的

11、用途 5.2.3 触发器与存储过程的比较 5.2.4 触发器的使用方法,5.2.1 触发器的基本概念,触发器可以看作是一类特殊的存储过程，它在满足某个特定条件时自动触发执行。 触发器是为表上的更新、插入、删除操作定义的，也就是说当表上发生更新、插入或删除操作时触发器将执行。 因此，相应的，触发器根据触发类型分为insert，delete，update触发器。,5.2.2 触发器的用途,触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。 除此之外，触发器还有以下的功能： 触发器可以通过级联的方式对相关的表进行修改。比如，对父表的修改，可以引起对子孙表的一系列修

12、改，从而保证数据的一致性和完整性。 触发器可以禁止或撤消违反参照完整性的修改。 触发器可以强制比用CHECK约束定义更加复杂的限制。,5.2.3 触发器与存储过程的比较,联系： 1、触发器也是存储过程。 2、它们都是提高数据库服务器性能的工具。 区别： 1、执行方法不同。触发器主要是通过事件进行触发而被执行的，存储过程可以通过存储过程名字而被直接调用。 2、建立方法不同。,触发器是依附于表的数据库对象,CREATE TRIGGER 语句必须是批处理的第一个语句 表的所有者具有创建触发器的缺省权限，表的所有者不能把该权限传给其它用户。 触发器是数据库对象，所以其命名必须符合命名规则。 尽管在触发

13、器的SQL 语句中可以参照其它数据库中的对象，但是触发器只能创建在当前数据库中。 虽然触发器可以参照视图或临时表，但不能在视图或临时表上创建触发器，而只能在基表或在创建视图的表上创建触发器。 一个触发器只能对应一个表，这是由触发器的机制决定的。,触发器是依附于表的数据库对象,一个触发器和三部分内容有关： 定义触发器的表 激活触发器的数据操作语句 触发器要采取的动作,不能在触发器中使用的sql语句,Create database和create table 所有drop语句 数据库修改语句alter table,alter database 对象权限语句grant和revoke ,5.2.4 触发

14、器的使用方法,创建触发器的语句 触发器的使用原理-理解两个视图并掌握它们的使用 插入类触发器 删除类触发器 更新类触发器 触发器的相关操作 修改删除触发器,建立触发器的语句,CREATE TRIGGER trigger_name ON table FOR INSERT | UPDATE | DELETE AS IF UPDATE(column) AND | OR UPDATE(column) sql_statement,s trigger_name：给出了触发器的名称； s table：说明了定义触发器的表或视图； s FOR INSERT | UPDATE | DELETE ：说明了激活触发

15、器的数据操作语句； s IF UPDATE(column)：对应于UPDATE类触发器，说明如果更新某（些）列则做如何处理； s sql_statement：触发器所要执行的SQL语句，它可以是一组SQL语句，可以包含流程控制语句等。,例：建立一个简单的触发器。,CREATE TRIGGER wh_trigger ON 仓库 FOR INSERT AS PRINT 插入了一个仓库元组,说明：当在“仓库”表中插入一行元组，则输出“插入了一个仓库元组”。,触发器的原理 两个特殊的视图,每个触发器有两个特殊的视图：插入视图（inserted）和删除视图（deleted） 。 它们是逻辑表且是由系统管

16、理的，存储在内存中，不允许用户直接对其修改，结构与原表有相同的表结构。 当触发器工作完成，这两个视图也被删除。 它们主要保存因用户操作（存放刚插入的新记录和存放刚删除的旧记录）而被影响到的原数据值或新数据值。 它们是只读的，即用户不能向这两个表写入内容,但可以引用表中的数据。,触发器的原理 这两个视图与数据操作的关系,一旦对表执行了插入操作，插入视图就是用来存储向原表插入的内容。 一旦对表执行了删除操作，则将所有的删除行存放至删除视图中。 更新操作包括两个部分即先将更新的内容去掉然后将新值插入，因此对一个定义了更新类型触发器的表来讲，在删除视图中存放了旧值，然后在插入视图中存放新值。,插入类触发器,插入类触发