《存储过程和触发器 (2).ppt》由会员分享，可在线阅读，更多相关《存储过程和触发器 (2).ppt（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 存储过程的优点,减轻程序编写的工作量：可以在各个程序中反复调用定义好的存储过程。存储过程能够实现较快的执行速度：因为存储过程是预编译的，而批处理的T-SQL 语句在每次运行时都要进行编译和优
3、化，因此速度相对要慢一些。存储过程能够减少网络流量：对于同一个针对数据库对象的操作，所涉及到的 T-SQL 语句被组织成一存储过程，当在客户端调用该存储过程时，网络中传送的只是该调用语句，降低网络负载。存储过程增加安全机制：系统管理员通过对执行某一存储过程的权限进行限制，从而能够实现对相应的数据访问权限的限制，避免非授权用户对数据的访问，保证数据的安全。,不使用存储过程时，所有的数据处理都在客户端完成；而使用存储过程时，可以使数据处理在服务器端完成。,储存过程的分类,系统存储过程SQL Server本身提供了一些存储过程，用于管理SQL Server和显示有关数据库和用户的信息，我们称之为系统
4、存储过程。系统存储过程都以“sp_”开头，存储在master数据库中。用户存储过程用户也可以编写自己的存储过程，并把它存放在数据库中。这样安排的主要目的就是要充分发挥数据库服务器的功能，尽量减少网络上的堵塞。,5.1.4 存储过程的使用方法,创建存储过程执行存储过程修改存储过程删除存储过程,创建存储过程,CREATE PROCedure procedure_name;number parameter data_type=default,AS sql_statement,s procedure_name：给出存储过程名；s number：对同名的存储过程指定一个序号；s parameter：给出
5、参数名；s data_type：指出参数的数据类型；s=default：给出参数的默认值；s sql_statement：存储过程所要执行的SQL语句，它可以是一组SQL语句，可以包含流程控制语句等。,例：创建一个最简单的存储过程（无参数调用）：,CREATE PROCedure sp_getemp;1AS SELECT*FROM 职工,说明：创建存储过程sp_getemp;1，要求查询职工信息,例：带参数的存储过程：,CREATE PROCedure sp_getemp;2(salary int)AS SELECT*FROM 职工 WHERE 工资 salary,说明：创建存储过程sp_ge
6、temp;2，要求查询工资值大于给定值的职工信息,说明：存储过程一般用来完成数据查询和数据处理操作，所以在存储过程中不可以使用创建数据库对象的语句，即在存储过程中一般不能含有以下语句：CREATE TABLECREATE VIEWCREATE DEFAULTCREATE RULECREATE TRIGGERCREATE PROCEDURE,执行存储过程,EXECute=|,例：执行带参数的sp_getemp;2存储过程,Execute sp_getemp;2 1240,说明：执行存储过程sp_getemp;2，要求查询工资值大于1240元的职工信息,存储过程的返回值和状态信息,无论什么时候执行
7、存储过程，总要返回一个结果码，用以指示存储过程的执行状态。如果存储过程执行成功，返回的结果码是0；如果存储过程执行失败，返回的结果码一般是一个负数，它和失败的类型有关。我们在创建存储过程时，也可以定义自己的状态码和错误信息。,常用的存储过程返回状态表,0 过程成功执行1 对象丢失2 发生数据类型错误3 处理过程被死锁4 发生权限错误5 发生语法错误6 发生恶意用户错误7 发生资源错误8 遭遇非致命的内部错误9 遭遇系统限制10 发生致命的内部不稳定性12 表或索引被破坏13 数据库被破坏14 发生硬盘错误通常用全局变量ERROR 返回最后执行的SQL 语句的错误代码。,CREATE PROCe
8、dure sp_getemp;3(salary int=NULL)AS IF salary IS NULLBEGIN PRINT 必须提供一个数值作参数！RETURN 13ENDIF NOT EXISTS(SELECT*FROM 职工 WHERE 工资 salary)BEGIN PRINT 没有满足条件的记录！RETURN-103ENDSELECT*FROM 职工 WHERE 工资 salaryRETURN 0,例：带参数和返回状态值的存储过程。,利用全局变量修改刚才的例子,CREATE PROCedure sp_getemp;3(salary int=NULL)AS SELECT*FROM
9、职工 WHERE 工资 salaryIF ERROR=0 RETURN 0ELSERETURN-1,例：执行以上存储过程。,DECLARE status intEXECUTE status=sp_getemp;3 salary 1200print status,存储过程的修改和删除,修改存储过程的语句是（一般格式）：ALTER PROCedure procedure_name;number parameter data_type=default,AS sql_statement 删除存储过程的语句是：DROP PROCedure procedure_name注意：删除存储过程的语句中不能指定序
10、号。也就是说，该语句将同时删除同名的所有存储过程。,小结：要用好存储过程,存储过程是客户/服务器机制的一个重要组成部分，如果使用客户/服务器机制的数据库管理系统，但是不理解存储过程或没有充分利用存储过程，那将使客户/服务器机制的功能大打折扣，使系统的整体性能可能降低很多。,5.2 触发器,触发器的基本概念5.2.2 触发器的用途5.2.3 触发器与存储过程的比较5.2.4 触发器的使用方法,5.2.1 触发器的基本概念,触发器可以看作是一类特殊的存储过程，它在满足某个特定条件时自动触发执行。触发器是为表上的更新、插入、删除操作定义的，也就是说当表上发生更新、插入或删除操作时触发器将执行。因此，
11、相应的，触发器根据触发类型分为insert，delete，update触发器。,5.2.2 触发器的用途,触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。除此之外，触发器还有以下的功能：触发器可以通过级联的方式对相关的表进行修改。比如，对父表的修改，可以引起对子孙表的一系列修改，从而保证数据的一致性和完整性。触发器可以禁止或撤消违反参照完整性的修改。触发器可以强制比用CHECK约束定义更加复杂的限制。,5.2.3 触发器与存储过程的比较,联系：1、触发器也是存储过程。2、它们都是提高数据库服务器性能的工具。区别：1、执行方法不同。触发器主要是通过事件进行触
12、发而被执行的，存储过程可以通过存储过程名字而被直接调用。2、建立方法不同。,触发器是依附于表的数据库对象,CREATE TRIGGER 语句必须是批处理的第一个语句表的所有者具有创建触发器的缺省权限，表的所有者不能把该权限传给其它用户。触发器是数据库对象，所以其命名必须符合命名规则。尽管在触发器的SQL 语句中可以参照其它数据库中的对象，但是触发器只能创建在当前数据库中。虽然触发器可以参照视图或临时表，但不能在视图或临时表上创建触发器，而只能在基表或在创建视图的表上创建触发器。一个触发器只能对应一个表，这是由触发器的机制决定的。,触发器是依附于表的数据库对象,一个触发器和三部分内容有关：定义触
13、发器的表激活触发器的数据操作语句触发器要采取的动作,不能在触发器中使用的sql语句,Create database和create table所有drop语句数据库修改语句alter table,alter database对象权限语句grant和revoke,触发器的使用方法,创建触发器的语句触发器的使用原理-理解两个视图并掌握它们的使用插入类触发器删除类触发器更新类触发器触发器的相关操作修改删除触发器,建立触发器的语句,CREATE TRIGGER trigger_nameON tableFOR INSERT|UPDATE|DELETE AS IF UPDATE(column)AND|OR
14、UPDATE(column)sql_statement,strigger_name：给出了触发器的名称；stable：说明了定义触发器的表或视图；sFOR INSERT|UPDATE|DELETE：说明了激活触发器的数据操作语句；sIF UPDATE(column)：对应于UPDATE类触发器，说明如果更新某（些）列则做如何处理；ssql_statement：触发器所要执行的SQL语句，它可以是一组SQL语句，可以包含流程控制语句等。,例：建立一个简单的触发器。,CREATE TRIGGER wh_triggerON 仓库FOR INSERT AS PRINT 插入了一个仓库元组,说明：当在“
15、仓库”表中插入一行元组，则输出“插入了一个仓库元组”。,触发器的原理 两个特殊的视图,每个触发器有两个特殊的视图：插入视图（inserted）和删除视图（deleted）。它们是逻辑表且是由系统管理的，存储在内存中，不允许用户直接对其修改，结构与原表有相同的表结构。当触发器工作完成，这两个视图也被删除。它们主要保存因用户操作（存放刚插入的新记录和存放刚删除的旧记录）而被影响到的原数据值或新数据值。它们是只读的，即用户不能向这两个表写入内容,但可以引用表中的数据。,触发器的原理这两个视图与数据操作的关系,一旦对表执行了插入操作，插入视图就是用来存储向原表插入的内容。一旦对表执行了删除操作，则将所
16、有的删除行存放至删除视图中。更新操作包括两个部分即先将更新的内容去掉然后将新值插入，因此对一个定义了更新类型触发器的表来讲，在删除视图中存放了旧值，然后在插入视图中存放新值。,插入类触发器,插入类触发器就是当表上发生插入操作时所触发执行的程序。,例：对职工表的插入操作定义一个触发器，使得当插入职工记录时，检查相应的仓库元组是否存在，如果不存在则撤消所做的插入操作。,CREATE TRIGGER e_ins_triggerON 职工 FOR INSERTAS,如果 插入的职工元组的仓库号在仓库表中存在,那么插入成功，职工表增加一行元组。如果 插入的职工元组的仓库号在仓库表中不存在,则插入操作不成
17、功，给出相应的提示，并且事务回滚到插入操作之前。,IF(SELECT COUNT(*)FROM 仓库 w,inserted i WHERE w.仓库号=i.仓库号)=0BEGIN RAISERROR(非法仓库号！,1,1)ROLLBACK TRANSACTIONEND,思考：如果上题要求改为在职工表的插入操作定义一个触发器，使得当插入职工记录时，检查实体完整性，如果不满足实体完整性则撤消所做的插入操作。,CREATE TRIGGER e_ins_trigger2ON 职工 FOR INSERTAS,如果 插入的职工元组的职工号在原职工表中不存在,那么插入成功，职工表增加一行元组。如果 插入的职
18、工元组的职工号在原职工表中已存在,则插入操作不成功，给出相应的提示，并且事务回滚到插入操作之前。,IF(SELECT COUNT(*)FROM 职工 w,inserted i WHERE w.职工号=i.职工号)0BEGIN RAISERROR(重复的职工号！,1,1)ROLLBACK TRANSACTIONEND,删除类触发器,删除类触发器就是当表上发生删除操作时所触发执行的程序。,例：定义一个触发器，使得当删除仓库记录时，同时将所属所有职工记录的仓库号字段值置为空值NULL：,CREATE TRIGGER w_del_triggerON 仓库 FOR DELETEASUPDATE 职工SE
19、T 仓库号=NULLWHERE 仓库号=(SELECT 仓库号 FROM deleted),思考：若改为定义一个触发器，使得当删除仓库记录时，同时将所属所有职工记录删除：,CREATE TRIGGER w_del_trigger2ON 仓库 FOR DELETEASDELETE FROM 职工 FROM deletedWHERE 职工.仓库号=deleted.仓库号,更新类触发器,更新类触发器就是当表上发生更新操作时所触发执行的程序。,例：对职工表的更新操作定义一个触发器，使得当职工变换所属仓库时，检查相应的仓库元组是否存在，如果不存在则撤消所做的更新操作，如果新的仓库号是WH2则将工资提高1
20、0%。,定义语句（注意表名，哪一类的触发器）当改变职工所属的仓库号时（仓库号a-仓库号b，不确定，怎么写语句？）如果仓库号b在仓库表中不存在，事务回滚，撤销更新职工表的仓库号字段，维持原来的元组。如果仓库号b在仓库表中存在，则进行下面的工作如果更新的仓库号b为WH2，则给这个职工增加10%工资如果更新的仓库号b不是WH2，而是其它合法的仓库号，则只是修改该职工所属的仓库号（即用户的更新操作成功）,WH3,1331,WH5,CREATE TRIGGER e_upd_triggerON 职工 FOR UPDATEASDECLARE wh_no CHAR(4)IF UPDATE(仓库号)BEGIN
21、IF(SELECT COUNT(*)FROM 仓库 w,inserted i WHERE w.仓库号=i.仓库号)=0 BEGIN RAISERROR(非法仓库号！,16,1)ROLLBACK TRANSACTION END ELSE BEGIN SELECT wh_no=仓库号 FROM inserted IF wh_no=WH2 UPDATE 职工 SET 工资=工资*1.10 WHERE 职工号=(SELECT 职工号 FROM inserted)ENDEND,定义语句,当更新操作，生成两张视图,如果仓库号b在仓库表中不存在，不符合参照完整性，事务回滚。,如果仓库号b在仓库表中存在，且为
22、WH2则增加工资,使用系统存储过程查看触发器,系统存储过程sp_help，sp_helptext 和sp_depends 分别提供有关触发器的不同信息。sp_help，通过该系统过程可以了解触发器的一般信息如触发器的名字属性类型创建时间使用sp_help 系统过程的命令格式是sp_help 触发器名字,sp_helptext 通过sp_helptext 能够查看触发器的正文信息，其语法格式为 sp_helptext 触发器名sp_depends 通过sp_depends 能够查看指定触发器所引用的表或指定的表涉及到的所有触发器，其语法形式如下 sp_depends 触发器名字 sp_depen
23、ds 表名,修改删除触发器,可以修改触发器的名字和正文使用sp_rename 命令修改触发器的名字，其语法格式为 sp_rename oldname,newname用Alert trigger 命令修改触发器正文删除已创建的触发器有两种方法：用系统命令DROP TRIGGER 删除指定的触发器，其语法形式如下 DROP TRIGGER 触发器名字删除触发器所在的表时，将自动删除与该表相关的触发器。,5.3 数据完整性,在第3章已经介绍了在关系数据模型上数据完整性的概念和规则；在前一章介绍了CREATE TABLE语句中可以实现的一些完整性约束。这里介绍与数据完整性有关的其他一些内容。.,5.3
24、.1 规则,在CREATE TABLE语句中可以使用CHECK子句实现一些用户定义完整性或域完整性约束。另外还可以通过“规则”（RULE）来实现用户定义完整性或域完整性。CHECK约束固定在一个表的一个列上，它只在指定的列上起作用。如果在不同的列上有相同的约束条件，则可以使用规则，一个规则可以绑定在多个列上。规则是一种独立的数据库对象，它可以绑定到一个列上来约束该列的取值范围等。,规则的用法,定义规则绑定到相应的列上,建立规则的命令是：,CREATE RULE rule AS condition_expression rule：给出新建规则的名称；condition_expression：定义
25、规则的条件，可以是任何有效的表达式，并且可以包含诸如算术运算符、关系运算符和谓词（如IN、LIKE、BETWEEN）之类的元素。注意：规则不能引用列或其它数据库对象，规则可以包含不引用数据库对象的内置函数；condition_expression需要包含一个变量，变量的前面有一个前缀；该表达式引用通过 UPDATE或INSERT语句输入或传递的字段值。,例如，规定某类数值对象的取值范围是10003000，则可以定义规则：,CREATE RULE range_rule ASrange=1000 AND range=3000,规则的绑定,规则是独立的数据库对象，要通过系统存储过程sp_bindru
26、le把规则绑定到数据列上，该系统存储过程的格式是：sp_bindrule rulename,objname,futureonly rulename是用CREATE RULE命令建立的规则名；objname指出要绑定的表和列或用户定义的数据类型；futureonly，当绑定规则到用户定义的数据类型时可以选用此项，该选项是禁止已经存在的、用用户定义数据类型定义的列遵循新的规则。,例如，将规则range_rule绑定到职工表的工资列上,sp_bindrule range_rule,职工.工资 使用CREATE RULE命令创建的规则对象，可以绑定到多个数据列上，即一个规则可以反复使用。,绑定的消除和
27、规则的删除,绑定到数据列上的规则可以去除，相应的系统存储过程是sp_unbindrule。例如，取消绑定在职工表工资列上的规则可以使用如下语句：sp_unbindrule 职工.工资规则可以删除，删除规则的命令是DROP RULE，但是删除规则之前，必须首先解除所有的绑定。,5.3.2 默认值,在CREATE TABLE命令中可以使用DEFAULT约束为数据列定义默认值。这里介绍另外一种方法：使用CREATE DEFAULT命令创建默认值对象。,CREATE DEFAULT命令的格式,CREATE DEFAULT default AS constant_expressiondefault：是建
28、立的默认值对象名；constant_expression：定义默认值的常量表达式。,例如，定义一个值为“北京”的默认值对象val_bj,CREATE DEFAULT val_bjAS 北京,绑定默认值,默认值是独立的数据库对象，它要作用于某个数据对象，则也和绑定规则一样，需要用类似的系统存储过程把默认值绑定到列，绑定默认值的系统存储过程是sp_bindefault，具体格式是：sp_bindefault defname,objname,futureonly defname：是用CREATE DEFAULT命令建立的默认值对象名；objname：指出要绑定的表和列或用户定义的数据类型；futur
29、eonly，当绑定默认值到用户定义的数据类型时可以选用此项，该选项是禁止已经存在的、用用户定义数据类型定义的列遵循新的默认值约定。,例如，将定义的默认值对象val_bj绑定到仓库关系的城市列上和供应商关系的地址列上：,sp_bindefault val_bj,仓库.城市sp_bindefault val_bj,供应商.地址,绑定的去除和默认值的删除,绑定到数据列上的默认值可以去除，相应的系统存储过程是sp_unbindefault。例如，取消绑定在供应商表地址列上的默认值可以使用如下语句：sp_unbindefault 供应商.地址默认值也可以删除，删除默认值的命令是DROP DEFAULT，
30、但是删除默认值之前，必须首先解除所有的绑定。,5.3.3 用户定义数据完整性,除了实体完整性约束和参照完整性约束，其他与数据完整性的有关的内容都是用户定义数据完整性的范畴。而实现用户定义数据完整性，除了CREATE TABLE命令中的CHECK约束，以及本章介绍的规则（RULE）和默认值(DEFAULT)，更多的是使用触发器来实现灵活、复杂的数据完整性要求。,举例,假设有如下关系模式：管理(仓库号，设备号，职工号)它所包含的语义是：一个仓库可以有多个职工；一名职工仅在一个仓库工作；在每个仓库一种设备仅由一名职工保管（但每名职工可以保管多种设备）。显然该关系模式的关键字是(仓库号,设备号)。进一
31、步假设，该关系中已经有元组：(WH1,P1,E1)(WH1,P2,E1)现在接着要插入元组(WH2,P2,E1)，注意：这种操作显然违反了语义“一名职工仅在一个仓库工作”，但它不违反实体完整性约束，一般的域完整性约束也无济于事。这时则只能通过触发器来实现数据完整性约束。,建立触发器,CREATE TRIGGER wh_emp ON 管理FOR INSERT,UPDATEASDECLARE emp CHAR(6),wh CHAR(6)SELECT wh=仓库号,emp=职工号 FROM insertedIF(SELECT COUNT(*)FROM 管理 WHERE 职工号=emp AND 仓库号
32、wh)0BEGIN RAISERROR(该职工已经属于其他仓库！,16,1)ROLLBACK TRANSACTIONEND,用户定义的数据类型,用户定义完整性另一种最常见的形式就是创建用户定义的数据类型。用户定义的数据类型并不是一种新的数据类型，它只是用另外一种形式来描述已有的数据类型。如果数据库中存在很多复杂的企业规则，用户定义数据类型可以增加系统的透明度，这对维护数据完整性是有帮助的。在SQL Server中，使用系统存储过程sp_addtype定义用户数据类型。,【本章小节】,存储过程和触发器，它们都是独立的数据库对象和存储在数据库上的特殊的程序。存储过程由用户调用，完成指定的数据处理任务；触发器则由特定的操作触发，从而自动完成相关的处理任务。使用触发器可以实现更强的数据完整性方面的约束。规则、默认值。,P145，课后习题六题,习题,第六次实验的思考题（数据操作）,就实验内容举例说明在进行插入，更新和删除操作时分别是怎样实现相关的数据完整性的？,
