1、第9章 存储过程与触发器本章导读本章介绍了数据库中两个重要的可编程对象存储过程和触发器，并结合实例讲解了在SQL Server中如何创建、修改和管理存储过程和触发器。学习目的与要求（1）掌握在SQL Server2005设计和管理存储过程的方法 . （2）掌握在SQL Server2005设计和管理触发器的方法,9.1 设计和管理存储过程,存储过程是 SQL 语句和控制流语句的预编译集合，以一个名称存储并作为一个单元处理。存储过程可包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。1、存储过程的类型（1）系统存储过程（2）用户定义存储过程（3）临时
2、存储过程（4）扩展存储过程,9.1.1存储过程概述,2、存储过程的主要优点（1）模块化编程。 （2）快速执行 （3）减少网络通信量 （4）提供安全机制,9.1.2创建存储过程,1格式CREATE PROCEDURE 架构名称.存储过程名 parameter 数据类型=default -设置默认值。OUTPUT -说明parameter定义的存储过程参数为一返回值。,.nWITH encryption|recompile -对存储过程文本进行加密。FOR REPLICATIONAS ,2存储过程的各选项设置规则1） parameter是过程中的参数。在 CREATE PROCEDURE 语句中可
3、以声明一个或多个参数。2）如果定义了 default 值，则无需指定此参数的值即可执行过程。默认值必须是常量或 NULL3）OUTPUT选项指示参数是输出参数。4）如果创建存储过程时，使用 WITH encryption子句，过程定义将以不可读的形式存储。5） FOR REPLICATION 指定不能在订阅服务器上执行为复制创建的存储过程。6） 指定过程要执行的操作7）可以在存储过程内引用临时表。,9.1.2创建存储过程,【例9.1】创建一个存储过程proc_student1用于显示学号为“J0402”的学生基本信息（包括学生学号、姓名、性别、系）。CREATE PROCEDURE proc_
4、student1 AS SELECT 学号,姓名,性别,系 FROM s WHERE 学号=J0402GO,9.1.2创建存储过程,【例】带参数的存储过程CREATE PROCEDURE dbo.stNAMEE CHAR(8)ASBEGINSELECT * FROM S WHERE 姓名 LIKE NAMEEEND,1.格式 EXEC | EXECUTE 返回状态= schema_name. 存储过程名称 形参= value | 变量 OUTPUT | DEFAULT ,.n WITH RECOMPILE 2.功能。“返回状态”是保存存储过程的返回状态。 “形参” 是在定义存储过程时，定义的参
5、数。在采用“形参=value ”格式时，参数名称和常量不必按在存储过程中定义的顺序提供。但是，如果任何参数使用了“形参=value ”格式，则对后续的所有参数均必须使用该格式。“value”是传递给存储过程的参数值。如果参数名称没有指定，参数值必须以在存储过程中定义的顺序提供。,9.1.3执行存储过程,【例9.2】现在我们就来执行刚才创建好的存储过程方法一：在SQLServer Management Studio中执行存储过程（演示）（1）执行不带参数的 proc_student1（2）执行带参数的 st【可编程性】【存储过程】,右击proc_student1，在弹出的菜单中选择【执行存储过程
6、】没有参数，直接单击【确定】，得到执行结果。若是有参数，在【值】一栏输入值即可，比如执行 st时，参数输入 李丽,9.1.3执行存储过程,方法二：使用命令（1）不带参数的命令方式在新建的查询窗口输入命令： EXEC proc_student1（2）带参数的命令方式EXEC st 李丽更多见下节,1.参数存储过程的参数在创建时声明，SQL Server支持两种参数：输入参数和输出参数。1）输入参数 输入参数允许调用程序为存储过程传送数据值。要定义存储过程的输入参数，必须在CREATE PROCEDURE语句中声明一个或多个变量及类型。在执行存储过程时，可以为输入参数传递参数值，或使用默认值。2）
7、输出参数 输出参数允许存储过程将数据值返回给调用程序。OUTPUT关键字用来指出输出参数。,9.1.4 存储过程的参数和状态值,【例】创建一个EMP表，并向表中插入记录的存储过程CREATE table Emp( 编号 INT, 姓名 CHAR(10), 性别 char(20), 职位 char(20), 工资 int)GOCREATE PROC ADD_Emp( 编号 INT, 姓名 CHAR(10), 性别 char(20), 职位 char(20), 工资 int)ASINSERT INTO EmpVALUES(编号,姓名,性别, 职位, 工资),于是可以使用下面的SQL 语句调用该过程
8、EXEC ADD_Emp 8,唐国强,男,演员,5000另外一种传递参数的方法是采用“ 编号=9”的形式，各个参数的顺序可以任意排列。例如上例EXEC ADD_Emp 编号=9,工资=6000,性别=女,姓名=赵微,职位=演员,创建存储过程时，可以为参数提供一个默认值，默认值必须为常量或者NULL。如上例过程改为：CREATE PROC ADD_Emp1( 编号 INT, 姓名 CHAR(10), 性别 char(20), 职位 char(20)=工程师, /*默认值为工程师*/ 工资 int)ASINSERT INTO emp VALUES(编号,姓名,性别, 职位, 工资)执行：EXEC
9、ADD_Emp1 编号=9,工资=6000,性别=女,姓名=赵薇职位没有输入，采用了默认值。,在创建存储过程时，可以定义返回参数。在执行存储过程时，可以将结果返回给返回参数。OUTPUT关键字用来输出参数。【例】下面的存储过程返回一个参数工资。CREATE PROC OUT_Emp( 编号 INT, 工资 int OUTPUT)ASSELECT 工资=工资FROM EmpWHERE 编号=编号执行该过程，返回编号为8的人员的工资DECLARE Price intEXEC OUT_Emp 8,Price OUTPUTSELECT 工资=Price,【例 9.11】CREATE PROCEDURE
10、 dbo.proc_student3num char(6), save smallint outputASBEGIN SELECT save=AVG(成绩) FROM s JOIN sc ON s.学号=sc.学号 WHERE s.学号=numENDDECLARE save_value SmallintEXEC proc_student3 J0401,save_value OUTPUTSELECT save_value AS 平均成绩,2返回值 存储过程可以返回整型状态值，表示过程是否成功执行，或者过程失败的原因。如果存储过程没有显式设置返回代码的值，SQL Server默认返回代码为0，表示
11、成功执行；若返回-1到-99之间的整数，表示没有成功执行。也可以使用RETURN语句，用大于0或小于-99之间的整数来定义自己的返回状态值，以表示不同的执行结果。在执行存储过程时，要定义一个变量来接收返回的状态值。 ）RETURN语句格式RETURN 返回整型值的表达式 ）功能RETURN 语句将无条件地从过程、批处理或语句块中退出。返回整型值。,9.1.4 存储过程的参数和状态值,【例9.14】CREATE PROC checkstateparacno Varchar(3)ASIF(SELECT MAX(成绩) FROM SC WHERE 课程号=paracno)90RETURN 1ELSE
12、RETURN 0DECLARE Ret intEXEC Ret=checkstate paracno=C03SELECT 结果=Ret,9.1.5 修改存储过程,1.格式ALTER PROCEDURE 架构名称.存储过程名 parameter 数据类型=default -设置默认值。OUTPUT -说明parameter定义的存储过程参数为一返回值。,.nWITH encryption|recompile -对存储过程文本进行加密。FOR REPLICATIONAS 2.功能其语法和CREATE PROCEDURE很相似。,【例9.18】现在我们就来修改刚才在【例9.11】中创建好的存储过程p
13、roc_student3，用于显示指定学号的学生各门课程的最高成绩，执行该存储过程返回学号为J0401的学生的最高成绩。完整的ALTER PROCEDURE语句如下：ALTER PROCEDURE dbo.proc_student3num char(6), max smallint outputASBEGIN SELECT max=max(成绩) FROM s JOIN sc ON s.学号=sc.学号 WHERE s.学号=numEND,9.1.5 修改存储过程,1格式 DROP PROCEDURE 存储过程名 ,.n 2功能从当前数据库中删除一个或多个存储过程或过程组。【例9.19】现在我
14、们就来删除刚才在【例9.18】中创建的存储过程proc_student3。方法一：在SQLServer Management Studio中删除存储过程方法二：使用SQL命令新建一个查询窗口，在里面输入命令：DROP PROCEDURE proc_student3,9.1.6删除存储过程,1格式 SP_RENAME 原存储过程名，新存储过程名2功能将存储过程名更改为新存储过程名。【例9.25】将【例9.12】创建存储过程的sg ，更名为student_proc。方法一：使用SQL命令SP_RENAME sg,student_proc注意: 更改对象名的任一部分都可能破坏脚本和存储过程。方法二：
15、在Management Studio重命名存储过程,9.1.8 重命名存储过程,触发器是特殊的存储过程，它也定义了一组Transact-SQL语句，用于完成某项任务。当使用UPDATE、INSERT 或 DELETE在指定表中对数据进行修改时，触发器会生效。触发器可以查询其它表，而且可以包含复杂的 SQL 语句。它们主要用于强制复杂的业务规则或要求。 触发器的主要作用是能强制数据完整性，保证数据一致性，主要表现为：强化约束 保证参照完整性 级联运行 跟踪变化 创建触发器时需指定： 名称、在其上定义触发器的表、触发器将何时激发、激活触发器的数据修改语句。,9.2 设计和管理触发器,9.2.1触发
16、器概述,1格式CREATE TRRIGER 架构的名称.触发器名 ON 表名|视图WITH encryption -对文本进行加密。FOR| AFTER | INSTEAD OF delete,insert,updateAS SQL 语句2功能触发器可分为AFTER触发器和INSTEAD OF 触发器两种。（1）AFTER：指定触发器只有在触发 SQL 语句中指定的所有操作都已成功执行后才激发。即触发器在触发它们的语句完成后执行。如果该语句因错误（如违反约束或语法错误）而失败，触发器将不会执行。不能为视图指定 AFTER 触发器，只能为表指定该触发器。可以为每个触发操作（INSERT、UPDA
17、TE 或 DELETE）指定多个 AFTER 触发器。在 SQL Server 2000 中 AFTER 是默认触发器。,9.2.2创建触发器,（2）INSTEAD OF：指定执行触发器而不是执行“触发 SQL 语句”，从而替代“触发语句”的操作。该触发器代替触发操作执行。在处理约束之前激发，即在数据变动之前被激发，并取代变动的数据操作。对于表或视图，每个 INSERT、UPDATE 或 DELETE 语句最多可定义一个 INSTEAD OF 触发器。（3）SQL语句：指定触发器要执行的操作（4）在触发器执行的过程中，常用到两个临时表：deleted表和inserted表。当向表中插入数据时，
18、INSERT触发器执行，并将新记录插入到和inserted表中；当从表中删除一条记录时，被删除的记录存放在deleted表中。对于UPDATE操作，SQL先将更新前的旧记录存在deleted表中，然后再将更新后的记录存放在inserted表中。,【例9.26】在学生选课表sc上创建一个触发器trigger_student1，该触发器被INSERT操作触发，当用户向sc表插入一条新记录时，判断该记录的学号在学生基本信息表s中是否存在，如果存在插入成功，否则插入失败。CREATE TRIGGER trigger_student1ON sc AFTER INSERTAS BEGIN IF(SELEC
19、T count(*) FROM inserted JOIN s ON inserted.学号=s.学号)=0 BEGIN ROLLBACK TRAN PRINT 插入记录无效！ ENDEND插入值观看效果：INSERT INTO SC VALUES(A0401,C01,90),9.2.2创建触发器,【例9.29】IF EXISTS(SELECT name FROM sysobjects WHERE name=reminder AND TYPE=TR)DROP TRIGGER reminderGOCREATE TRIGGER reminder ON SC AFTER INSERT,UPDATE,
20、DELETEAS BEGIN RAISERROR(不得对数据表进行任何修改！,16,10)ENDGO尝试删除：delete sc where 学号=J0401 and 课程号=C05,1格式ALTER TRRIGER 架构的名称.触发器名 ON 表名|视图WITH encryption -对文本进行加密。FOR| AFTER | INSTEAD OF delete,insert,updateAS SQL 语句2功能修改触发器，各选项的功能与创建触发器的命令一样。,9.2.5 修改触发器,【例9.37】现在我们就来修改刚才在【例9.26】中创建好的触发器trigger_student1，该触发器
21、被INSERT操作触发，当用户向sc表插入一条新记录时，判断该记录的学号在学生基本信息表s中是否存在，如果存在插入成功，否则插入失败；同时判断该记录的课程号在课程表C中是否存在，如果存在插入成功，否则插入失败.,9.2.5 修改触发器,ALTER TRIGGER trigger_student1ON dbo.sc AFTER INSERTAS BEGIN IF(SELECT count(*) FROM inserted JOIN S ON inserted.学号=S.学号 JOIN C ON inserted.课程号=C.课程号 )=0) BEGIN ROLLBACK TRAN PRINT 插入记录无效！ ENDEND,9.2.5 修改触发器,1格式DROP TRIGGER 触发器名n2功能删除指定触发器。【例9.38】现在我们就来删除刚才在【例9.28】中创建好的存储过程trigger _student3。DROP TRIGGER trigger_student3,9.2.6删除触发器,1格式SP_rename oldname,newname 2功能将原触发器名更改为新的触发器名。【例9.39】将【例9.35】中创建的触发器delete_stu的名称修改为d_student。sp_rename delete_stu,d_student,9.2.7重命名触发器,
