第7章存储过程和触发器

1、第第7章章 存储过程和触发器存储过程和触发器 7.1 存储过程存储过程 7.2 触发器触发器 7.1 存储过程存储过程 存储过程存储过程Procedure是一组为了完成特定功能的是一组为了完成特定功能的SQL 语句集合，经编译后存储在数据库中，用户通过指定存语句集合，经编译后存储在数据库中，用户通过指定存 储过程的名称并给出参数来执行。存储过程中可以包含储过程的名称并给出参数来执行。存储过程中可以包含 逻辑控制语句和数据操纵语句，它可以接受参数、输出逻辑控制语句和数据操纵语句，它可以接受参数、输出 参数、返回单个或多个结果集以及返回值。参数、返回单个或多个结果集以及返回值。 存储过程优点 1.

2、 存储过程创建后可以在程序中被多次调用执行，而不必重新编写该 存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改， 但对应用程序源代码却毫无影响，从而极大的提高了程序的可移植性。 2. 存储过程能够实现较快的执行速度,因为存储过程是预编译的，在首 次运行一个存储过程时，查询优化器对其进行分析、优化并给出最终被存 在系统表中的存储计划。 3. 存储过程可被作为一种安全机制来充分利用系统管理员可以对执行 的某一个存储过程进行权限限制，从而能够实现对某些数据访问的限制， 避免非授权用户对数据的访问，保证数据的安全。 7.1.1 存储过程的类型存储过程的类型 （1）系统存储过程。系统存

3、储过程是由SQL Server提供的存储过程， 可以作为命令执行。定义在master数据库中，前缀sp_。 （2）扩展存储过程。扩展存储过程是指在SQL Server 2008环境之外， 使用编程语言（如C+语言）创建的外部例程形成的动态链接库（DLL）。 （3）用户存储过程。在SQL Server 2008中，用户存储过程可以使用T- SQL语言编写，在本书中，T-SQL存储过程就称为存储过程。 7.1.2 存储过程的创建与执行存储过程的创建与执行 1使用使用T-SQL命令创建存储过程命令创建存储过程 CREATE PROC | PROCEDURE schema_name. procedur

4、e_name ; number parameter type_schema_name. data_type /*定义参数的类型*/ VARYING = default OUT | OUTPUT READONLY /*定义参数的属性*/ ,.n WITH ,.n /*定义存储过程的处理方式*/ AS ; .n /*执行的操作*/ ; 其中， := ENCRYPTION RECOMPILE 7.1.2 存储过程的创建与执行存储过程的创建与执行 2存储过程的执行存储过程的执行 通过EXECUTE或EXEC命令可以执行一个已定义的存储过程，EXEC是 EXECUTE的简写。语法格式： EXEC | E

5、XECUTE return_status = module_name ;number | module_name_var parameter = value | variable OUTPUT | DEFAULT ,.n ; 7.1.2 存储过程的创建与执行存储过程的创建与执行 3举例举例 （1）设计简单的存储过程。 【例例7.1】 返回081101号学生的成绩情况。该存储过程不使用任何参数。 CREATE PROCEDURE student_info AS SELECT * FROM CJB WHERE 学号= 081101 存储过程定义后，执行存储过程student_info： EXECU

6、TE student_info 如果该存储过程是批处理中的第一条语句，则可使用 student_info 7.1.2 存储过程的创建与执行存储过程的创建与执行 （2）使用带参数的存储过程。 【例例7.2】 创建存储过程，使之能够返回总学分在指定范围内的学生 信息。 CREATE PROCEDURE proc1 min int, max int AS SELECT * FROM XSB WHERE 总学分 between min and max 执行方法： proc1 45,55 EXECUTE proc1 45,55 EXECUTE proc1 min =45, max =55 EXECUTE

7、 proc1 max =55, min =45 7.1.2 存储过程的创建与执行存储过程的创建与执行 【例例7.3】 从PXSCJ数据库的三个表中查询某人指定课程的成绩和学分。 CREATE PROCEDURE student_info1 name char (8), cname char(16) AS SELECT a.学号, 姓名, 课程名, 成绩, t.学分 FROM XSB a INNER JOIN CJB b ON a.学号 = b.学号 INNER JOIN KCB t ON b.课程号= t.课程号 WHERE a.姓名=name and t.课程名=cname 7.1.2 存储

8、过程的创建与执行存储过程的创建与执行 执行存储过程student_info1： EXECUTE student_info1 王林, 计算机基础 执行结果如下： 以下命令的执行结果与上面的相同： EXECUTE student_info1 name=王林, cname=计算机基础 或者 DECLARE proc char(20) SET proc= student_info1 EXECUTE proc name=王林, cname=计算机基础 7.1.2 存储过程的创建与执行存储过程的创建与执行 （3）使用带OUPUT参数的存储过程。 【例例7.4】 创建一个存储过程，使之能够求出所有学生的平均

9、学分以 及男同学的平均学分。 CREATE PROCEDURE proc2 avg int output,avg1 int output AS SELECT avg=AVG(总学分) FROM XSB SELECT avg1=AVG(总学分) FROM XSB WHERE 性别=1 DECLARE avg2 int,avg3 int exec proc2 avg2 output,avg3 output select avg2,avg3 CREATE PROCEDURE proc2 sex bit,avg int output,avg1 int output AS SELECT avg=AVG(

10、总学分) FROM XSB SELECT avg1=AVG(总学分) FROM XSB WHERE 性别=sex DECLARE sex bit,avg int,avg1 int set sex=1 EXECUTE proc2 sex,avg output,avg1 output print 平均总学分:+Ltrim(str(avg) if sex=0 print 女同学平均总学分:+Ltrim(str(avg1) else print 男同学平均总学分:+Ltrim(str(avg1) 思考：上例中若想求出所有学生的平均学分以及指定性别的同学平 均学分如何解答？ 7.1.2 存储过程的创建与

11、执行存储过程的创建与执行 【例例7.5】 创建一个存储过程do_insert，作用是向XSB表中插入一行数 据。创建另外一个存储过程do_action，在其中调用第一个存储过程，并根 据条件处理该行数据，处理后输出相应的信息。 第一个存储过程： CREATE PROCEDURE dbo.do_insert AS INSERT INTO XSB VALUES(091201, 陶伟, 1, 1990-03-05, 软件工程 ,50, NULL); 7.1.2 存储过程的创建与执行存储过程的创建与执行 第二个存储过程： CREATE PROCEDURE do_action X bit, STR CH

12、AR(8) OUTPUT AS BEGIN EXEC do_insert IF X=0 BEGIN UPDATE XSB SET 姓名=刘英, 性别=0 WHERE 学号=091201 SET STR=修改成功 END ELSE IF X=1 BEGIN DELETE FROM XSB WHERE 学号=091201 SET STR=删除成功 END END 7.1.2 存储过程的创建与执行存储过程的创建与执行 接下来执行存储过程do_action来查看结果： DECLARE str char(8) EXEC dbo.do_action 0, str OUTPUT SELECT str; 执行

13、结果如下： 7.1.2 存储过程的创建与执行存储过程的创建与执行 （4）使用带有通配符参数的存储过程。 【例例7.6】 从三个表的连接中返回指定学生的学号、姓名、所选课程名 称及该课程的成绩。该存储过程在参数中使用了模式匹配，如果没有提供参 数，则使用预设的默认值。 CREATE PROCEDURE st_info name varchar(30) = 李% AS SELECT a.学号,a.姓名,c.课程名,b.成绩 FROM XSB a INNER JOIN CJB b ON a.学号 =b.学号 INNER JOIN KCB c ON c.课程号= b.课程号 WHERE 姓名 LIKE

14、 name 执行存储过程： EXECUTE st_info /*参数使用默认值*/ 或者 EXECUTE st_info 王% /*传递给name 的实参为王%*/ 7.1.3 存储过程的修改存储过程的修改 语法格式： ALTER PROC | PROCEDURE schema_name. procedure_name ; number parameter type_schema_name. data_type VARYING = default OUTPUT ,.n WITH ,.n FOR REPLICATION AS ; .n | EXTERNAL NAME asse MBly_name

15、.class_name.method_name ; 7.1.3 存储过程的修改存储过程的修改 【例例7.7】 对例7.3中创建的存储过程student_info1进行修改，将第一 个参数改成学生的学号。 USE PXSCJ GO ALTER PROCEDURE student_info1 number char(6),cname char(16) AS SELECT 学号, 课程名, 成绩 FROM CJB, KCB WHERE CJB.学号=number AND KCB.课程名=cname 7.1.3 存储过程的修改存储过程的修改 【例例7.8】 创建名为select_students的存储

16、过程，在默认情况下，该存储过程可 查询所有学生信息。当该存储过程需更改为能检索计算机专业的学生信息时，用 ALTER PROCEDURE重新定义该存储过程。 l创建select_students存储过程。 CREATE PROCEDURE select_students /*创建存储过程*/ AS SELECT * FROM XSB ORDER BY 学号 GO l修改存储过程select_students。 ALTER PROCEDURE select_students WITH ENCRYPTION AS SELECT * FROM XSB WHERE 专业= 计算机 ORDER BY 学

17、号 GO 7.1.4 存储过程的删除存储过程的删除 当不再使用一个存储过程时，就要把它从数据库中删除。使用DROP PROCEDURE语句可永久地删除存储过程。语法格式： DROP PROC | PROCEDURE schema_name. procedure ,.n 【例例7.9】 删除PXSCJ数据库中的student_info存储过程。 USE PXSCJ GO IF EXISTS(SELECT name FROM sysobjects WHERE name=student_info) DROP PROCEDURE student_info 7.1.5 界面操作存储过程界面操作存储过程

18、（1）创建存储过程。例如，如果要通过图形向导方式定义一个存储过程来查询 PXSCJ数据库中每个同学各门功课的成绩，那么其主要步骤如下： 7.1.5 界面操作存储过程界面操作存储过程 （2）执行存储过程。在PXSCJ数据库的“存储过程”目录下选择要执行的存储 过程，例如student_info1，右击鼠标，选择“执行存储过程”菜单项。 7.2 触发器触发器 触发器（触发器（TriggerTrigger）是用户定义在关系表）是用户定义在关系表 上的一类由事件驱动的特殊过程，是保证数据上的一类由事件驱动的特殊过程，是保证数据 完整性的一种方法。完整性的一种方法。 l由服务器自动激活由服务器自动激活

19、l可以进行更为复杂的检查和操作，具有更可以进行更为复杂的检查和操作，具有更 精细和更强大的数据控制能力精细和更强大的数据控制能力 7.2.1 触发器的类型触发器的类型 （1）DML触发器。当数据库中发生数据操纵语言（DML）事件时将调 用DML触发器。 根据触发器的执行与触发事件的先后关系， DML触发器分为AFTER 触 发器和 INSTEAD OF触发器。 AFTER 触发器只适用于表； INSTEAD OF触发器 适用于表和视图。 根据触发事件的类型划分，DML触发器也可分为三种类型：INSERT、 UPDATE和DELETE。 （2）DDL触发器。DDL触发器也是由相应的事件触发的，但

20、DDL触发 器触发的事件是数据定义语句（DDL）。这些语句主要是以CREATE、ALTER、 DROP等关键字开头的语句。 7.2.2 触发器的创建触发器的创建 1创建创建DML触发器触发器 语法格式： CREATE TRIGGER schema_name . trigger_name ON table | view /*指定操作对象*/ WITH ENCRYPTION /*说明是否采用加密方式*/ AFTER | INSTEAD OF INSERT , UPDATE , DELETE AS sql_statement ; .n 7.2.2 触发器的创建触发器的创建 （1）触发器中使用的特殊表

21、。执行触发器时，系统创建了两个特殊的临 时表inserted表和deleted表,触发器执行后被删除。 inserted表：当向表中插入数据时，新的记录副本也插入到inserted表中。 deleted表：当从表中删除数据时，被删除的记录副本存放到deleted表中。 对于数据更新操作，相当于插入新记录同时删除旧记录。也就是说更新 前的记录存到deleted表中，更新后的数据插入到inserted表中。 （2）创建DML触发器的说明。 CREATE TRIGGER 语句必须是批处理中的第一条语句，并且只能应用到 一个表中。 在同一CREATE TRIGGER语句中，可以为多种操作（如INSER

22、T和UPDATE） 定义相同的触发器操作。 不能对临时表或系统表创建DML触发器。 DML触发器最大的用途是返回行级数据的完整性，而不是返回结果， 所以应当尽量避免返回任何结果集。 TRUNCATE TABLE语句不会触发DELETE触发器。 7.2.2 触发器的创建触发器的创建 7.2.2 触发器的创建触发器的创建 （3）创建INSERT触发器。INSERT触发器是当对触发器表执行INSERT语 句时就会激活的触发器。 【例例7.10】 创建一个表table1，其中只有一列a。在表上创建一个触发 器，每次插入操作时，将变量str的值设为“TRIGGER IS WORKING”并显 示。 CR

23、EATE TABLE table1(a int) GO CREATE TRIGGER table1_insert ON table1 AFTER INSERT AS BEGIN DECLARE str char(50) SET str=TRIGGER IS WORKING PRINT str END 7.2.2 触发器的创建触发器的创建 【例例7.11】 创建触发器，当向CJB表中插入一个学生的成绩时，将XSB 表中该学生的总学分加上添加的课程的学分。 CREATE TRIGGER cjb_insert ON CJB AFTER INSERT AS BEGIN DECLARE num char

24、(6), kc_num char(3) DECLARE xf int SELECT num=学号, kc_num=课程号 from inserted SELECT xf=学分 FROM KCB WHERE 课程号=kc_num UPDATE XSB SET 总学分=总学分+xf WHERE 学号=num PRINT 修改成功 END 7.2.2 触发器的创建触发器的创建 （4）创建UPDATE触发器。UPDATE触发器在对触发器表执行UPDATE语 句后触发。在执行UPDATE触发器时，将触发器表的原记录保存到deleted临 时表中，将修改后的记录保存到inserted临时表中。 【例例7.

25、12】 创建触发器，当修改XSB表中的学号时，同时也要将CJB表 中的学号修改成相应的学号（假设XSB表和CJB表之间没有定义外键约束）。 CREATE TRIGGER xsb_update ON XSB AFTER UPDATE AS BEGIN DECLARE old_num char(6), new_num char(6) SELECT old_num=学号 FROM deleted SELECT new_num=学号 FROM inserted UPDATE CJB SET 学号=new_num WHERE 学号=old_num END 7.2.2 触发器的创建触发器的创建 接着修改X

26、SB表中的一行数据，并查看触发器执行结果： UPDATE XSB SET 学号=081120 WHERE 学号=081101 GO SELECT * FROM CJB WHERE 学号=081120 执行结果如下： 7.2.2 触发器的创建触发器的创建 （5）创建DELETE触发器。 【例例7.13】 在删除XSB表中的一条学生记录时将CJB表中该学生的相应 记录也删除。 CREATE TRIGGER xsb_delete ON XSB AFTER DELETE AS BEGIN DELETE FROM CJB WHERE 学号 =(SELECT 学号 FROM deleted) END 思考

27、：如果在XSB中删除多条记录时该触发器如何执行？ 7.2.2 触发器的创建触发器的创建 【例例7.14】 在KCB表中同时创建UPDATE和DELETE触发器，当修改或删 除KCB表中的“课程号”字段时，同时修改或删除CJB表中的该课程号。 CREATE TRIGGER kcb_trig ON KCB AFTER UPDATE, DELETE AS BEGIN IF (UPDATE(课程号) UPDATE CJB SET 课程号=(SELECT 课程号 FROM inserted) WHERE 课程号=(SELECT 课程号 FROM deleted) ELSE DELETE FROM CJB

28、 WHERE 课程号 IN(SELECT 课程号 FROM deleted) END 7.2.2 触发器的创建触发器的创建 （6）创建INSTEAD OF触发器。与AFTER触发器不同的是，INSTEAD OF 触发器触发时只执行触发器内部的SQL语句，而不执行激活该触发器的 SQL语句。一个表或视图中只能有一个INSTEAD OF触发器。 【例例7.15】 创建表table2，值包含一列a，在表中创建INSTEAD OF INSERT触发器，当向表中插入记录时显示相应消息。 CREATE TABLE table2(a int) GO CREATE TRIGGER table2_insert

29、ON table2 INSTEAD OF INSERT AS PRINT INSTEAD OF TRIGGER IS WORKING 7.2.2 触发器的创建触发器的创建 【例例7.16】 在PXSCJ数据库中创建视图stu_view，包含学生学号、专 业、课程号、成绩。该视图依赖于表XSB和CJB，是不可更新视图。可以 在视图上创建INSTEAD OF触发器，当向视图中插入数据时分别向表XSB 和CJB插入数据，从而实现向视图插入数据的功能。 首先创建视图： CREATE VIEW stu_view AS SELECT XSB.学号, 专业, 课程号, 成绩 FROM XSB, CJB WH

30、ERE XSB.学号=CJB.学号 CREATE TRIGGER INSTEADtrig ON stu_view INSTEAD OF INSERT AS BEGIN DECLARE XH CHAR(6),XM CHAR(8),ZY CHAR(12), KCH CHAR(3),CJ INT SELECT XH=学号,XM=无名,ZY=专业, KCH=课程号,CJ=成绩 FROM INSERTED INSERT INTO XSB(学号,姓名,专业) VALUES(XH,XM,ZY) INSERT INTO CJB VALUES(XH,KCH,CJ) END 然后创建INSTEAD OF 触发器：

31、 7.2.2 触发器的创建触发器的创建 7.2.2 触发器的创建触发器的创建 向视图插入一行数据： INSERT INTO stu_view VALUES(091102, 计算机, 101, 85 ) 查看数据是否插入： SELECT * FROM stu_view WHERE 学号= 091102 查看与视图关联的XSB表和CJB的情况： SELECT * FROM XSB WHERE 学号= 091102 SELECT * FROM CJB WHERE 学号= 091102 7.2.2 触发器的创建触发器的创建 2创建创建DDL触发器触发器 语法格式： CREATE TRIGGER tri

32、gger_name ON ALL SERVER | DATABASE WITH ENCRYPTION FOR | AFTER event_type | event_group ,.n AS sql_statement ; .n 7.2.2 触发器的创建触发器的创建 【例例7.17】 创建PXSCJ数据库作用域的DDL触发器，当删除一个表时， 提示禁止该操作，然后回滚删除表的操作。 USE PXSCJ GO CREATE TRIGGER safety ON DATABASE AFTER DROP_TABLE AS PRINT 不能删除该表 ROLLBACK TRANSACTION 尝试删除表table