实验7 存储过程和触发器

一、目的和要求

（1） 了解触发器的定义方法，及其目的，主要是实现业务规则的约束。 （2） 了解INSERTed逻辑表和deleted逻辑表的使用。

（3） 了解使用Transact-SQL语句定义存储过程的方法，或使用企业管理器或者存储过程创建向导完成存储过

程的定义方法。

（4）了解存储过程的调用方法，及其与调用函数的区别。存储过程调用时，其实参不需要加括号。

二、背景知识

（1）存储过程概述：

存储过程是SQL语句和可选控制流程语句的预编译集合，它以一个名称存储并作为一个单元处理。存储过程存储在数据库内，可由应用程序通过一个调用执行。存储过程可以接受参数，输出参数，返回单个或多个结果集，也可以返回一个值。

存储过程具有以下优点：

●可以在一个存储过程中，执行一系列SQL语句。 ●存储过程可以嵌套调用，以简化语句的描述

●存储过程在创建时即在服务器上进行编译，所以执行起来比单个SQL语句快，并且能减少网络通信的负担。

●存储过程中的参数可以具有默认值，默认值必须为常量或者NULL。 （2）触发器概述：

触发器是一种特殊类型的存储过程，常用作数据完整性约束。一个系统有三类触发器，分别为：INSERT触发器、UPDATE触发器和DELETE触发器。通常INSERT、UPDATE触发器被用来检查插入或者修改后的数据是否满足要求。DELETE触发器一般用作级联删除或记录外键的删除操作。当对指定表执行INSERT、UPDATE、DELETE时，相应的触发器会自动执行。触发器可以包含复杂的T-SQL语句。一个表可以有多个触发器。

在触发器执行的时候，会产生两个临时表：INSERTed表和deleted表。它们的结构和触发器所在的表的结构相同，SQLSERVER自动创建和管理这些表。

在对触发器的表进行操作时，系统执行过程如下：

●执行INSERT操作，插入到触发器表中的新行同时被插入到INSERTed表中。 ●执行DELETE操作，从触发器表中删除的行同时被插入到deleted表中。

●执行UPDATE操作，先从触发器表中删除旧行，然后再插入新行。其中，被删除的旧行，同时被插入到deleted表中；插入的新行，同时被插入到INSERTed表中。

触发器可以使用这两个临时表测试数据修改的效果及设置触发器操作的条件。 触发器具有以下优点：

●触发器可通过数据库中的相关表实现级联更改。但是，通过在表之间建立参考完整性约束，可以更有效地执行这些更改。

●触发器可以执行比CHECK约束更为复杂的约束定义。触发器可以使用其他表中的数据，而CHECK约束不行。

●一个表中的多个同类触发器，允许采用多个不同的对策，以响应同一个修改语句。

三、实验内容

通过查询分析器，设置XSGL数据库为当前数据库，调试实验步骤中提供的相关语句，熟悉存储过程和触发器的定义及使用方法。

四、实验步骤

1．创建一个按名字模糊查询学生基本信息的存储过程。

CREATE PROCEDURE chaxun_student_name(@sname varchar(20)) AS

(

SELECT sno AS 学号,sname AS 姓名 ,age AS 年龄,dname AS 学院名称 FROM student,dept WHERE student.dno=dept.dno AND sname LIKE @sname )

说明：存储过程必须先定义，然后在客户端进行调用。在查询分析器中，执行方法为：EXEC 存储过程名 参数 例如：本例调用存储过程查询名字中含有丽的学生信息方法为，在查询分析器中，执行如下语句：

EXEC chaxun_student_name '%丽%'

2．创建一个依据学生学号返回其所有课程平均分的存储过程student_average。

CREATE PROCEDURE student_average (@c1 varchar(8)) AS

RETURN (SELECT AVG(GRADE) FROM sc WHERE SNO=@c1)

说明：请同学们使用下面的SQL语句调用该存储过程，查询学号为'20002059'的学生的平均分：

DECLARE @no char(8) ,@avg float SET @no='20002059'

EXEC @avg=student_average @no PRINT CONVERT(CHAR(10),@avg)

SELECT SNAME ,@avg AS '平均分' FROM student WHERE SNO=@no

3．创建一个执行插入功能的存储过程，可以向chengjiao表中插入一条学生记录，该存储过程包含三个参数，分别表示学生学号、学生姓名、学生出生日期，其中学生出生日期参数默认值为'1990-1-1'。

1

CREATE PROCEDURE INSERT_chengjiao (@c1 varchar(8),@c2 varchar(10),@c3 datetime='1990-1-1') AS

INSERT INTO chengjiao(SNO,SNAME,BIRTHDAY) values(@c1,@c2,@c3)

说明：存储过程的参数可以具有缺省值，使用方法同c++相似。请同学们使用下面的SQL语句调用该存储过程，向chengjiao表中插入'20081001'和'20081002'两个学生：

EXEC INSERT_chengjiao '20081001', 'jiang'

EXEC INSERT_chengjiao '20081002','jiang','1991-12-1'

4．创建一个存储过程，可以根据指定的学生学号，通过参数返回该学生的姓名和所选课程的平均分。

CREATE PROCEDURE student_nameaverage(

@c1 varchar(8),@c2 varchar(10) OUTPUT ,@c3 float OUTPUT) AS

SELECT @c2=SNAME , @c3=AVG(GRADE) FROM student,sc

WHERE student.SNO=sc.SNO AND student.SNO=@c1 GROUP BY student.SNO,student.SNAME

说明：存储过程可以通过实参返回数据。请同学们使用下面的SQL语句调用该存储过程，查询学号为'20002059'的学生的姓名和平均分：

DECLARE @s_name char(10) DECLARE @s_avg float

EXEC student_nameaverage '20002059',@s_name OUTPUT, @s_AVG OUTPUT PRINT @s_name PRINT @s_avg

5．存储过程在执行后都会返回一个整型值。如果成功执行，返回0；否则，返回-1----99之间的数值。下例根据例3创建的存储过程，判断该存储过程是否执行成功。

DECLARE @x Int

exec @x=INSERT_chengjiao '20082001', 'jiang' PRINT @x 说明：

语句执行结果为：

违反了 UNIQUE KEY 约束 'UQ__chengjiao__3D5E1FD2'。不能在对象 'chengjiao' 中插入重复键。 语句已终止。

-4（-4表示存储过程没有成功执行）

2