《数据库原理与应用 第7章 存储过程和触发器》由会员分享，可在线阅读，更多相关《数据库原理与应用 第7章 存储过程和触发器（56页珍藏版）》请在金锄头文库上搜索。
1、第7章 存储过程和触发器,【本章导读】,存储过程和触发器是数据库应用中两个重要的数据库对象。利用存储过程和触发器不仅可以简化用户的工作，而且在保障数据库的完整性方面也发挥着重要的作用。本章主要讲解存储过程与触发器的概念、类型、建立和使用。,【本章要点】,存储过程与触发器的功能、类型和特点 存储过程与触发器的建立、管理和使用 存储过程和触发器在维护数据库完整性中的作用,第7章 存储过程和触发器,7.1 存储过程 7.2 触发器 本章小结 习题七,7.1 存储过程,7.1.1 存储过程概述 7.1.2 存储过程的类型 7.1.3 创建存储过程 7.1.4 查看存储过程信息 7.1.5 修改存储过程
2、 7.1.6 删除存储过程,7.1.1 存储过程概述,存储过程（Stored Procedure）是一组事先编译好的Transact-SQL代码。 优点： （1）执行速度快。 （2）模块化的程序设计。 （3）减少网络通信量。 （4）保证系统的安全性。,7.1.2 存储过程的类型,用户定义的存储过程； 扩展存储过程； 系统存储过程。,7.1.3 创建存储过程,注意事项： （1）CREATE PROCEDURE 语句不能与其他 SQL 语句在单个批处理中组合使用。 （2）要创建过程，必须具有数据库的 CREATE PROCEDURE 权限，还必须具有对该数据库的 ALTER 权限。 （3）存储过程
3、是数据库作用域内的对象，它们的名称必须遵守标识符命名规则。 （4）只能在当前数据库中创建存储过程。 （5）存储过程最大为 128 MB。,7.1.3 创建存储过程,在创建存储过程时，应指定： （1）所有输入参数和向调用过程或批处理返回的输出参数。 （2）执行数据库操作（包括调用其他过程）的编程语句。 （3）返回至调用过程或批处理以表明成功或失败（以及失败原因）的状态值。,7.1.3 创建存储过程,使用SQL Server Management Studio创建存储过程 ：参见录像 使用T-SQL语句创建存储过程,使用T-SQL语句创建存储过程,CREATE PROC EDURE 存储过程名 ；
4、number parameter data_type VARYING = default OUTPUT , n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION FOR REPLICATION AS SQL语句,（1）parameter：存储过程中使用的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明的输入参数的值（除非定义了该参数的默认值）。存储过程最多可以有 2100 个参数。 （2）Default：参数的默认值。如果定义了默认值，不必指定该参数的值即可执行过程。默认值必须是
5、常量或 NULL。如果过程对该参数使用 LIKE 关键字，那么默认值中还可以包含通配符（如%、_、 和 ）。,（3）OUTPUT：表明参数是输出参数（返回参数）。该选项的值可以返回给EXECUTE。使用 OUTPUT 参数可将信息返回给调用过程。 （4）RECOMPILE： 表明 SQL Server 不会缓存该过程的计划，该过程将在运行时重新编译。 （5）ENCRYPTION ：表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 语句文本的条目，以对其文本进行加密。使用 ENCRYPTION 可防止将过程作为 SQL Server 复制的一部
6、分发布。 （6）FOR REPLICATION：指定不能在订阅服务器上执行为复制创建的存储过程。本选项不能和 WITH RECOMPILE 选项一起使用。,【例7-1】创建带有复杂SELECT语句的存储过程。 建立存储过程usp_s_c，实现查看学生选课的信息。该存储过程不使用参数。 use Study go create procedure usp_s_c as select s.sno, sname, o, cname, degree from Student s, Score sc, Course c where s.sno=sc.sno and o=o go,可以使用EXECUTE命令
7、在查询编辑器中执行存储过程。 exec usp_s_c,use Study go if exists ( select name from sysobjects where name = usp_s_c and type = p) drop procedure usp_s_c go create procedure usp_s_c as select s.sno, sname, o, cname, degree from Student s, Score sc, Course c where s.sno=sc.sno and o=o go,【例7-2】创建带有参数的存储过程。 建立存储过程us
8、p_s_cBysno，通过提供学生的学号来查询此学号学生的选课信息。 create procedure usp_s_cBysno sno char(10) as select s.sno, sname, o, cname, degree from Student s, Score sc, Course c where s.sno=sc.sno and o=o and s.sno=sno go 执行该存储过程： exec usp_s_cBysno 06081201,【例7-3】创建带有通配符参数的存储过程。 建立存储过程usp_s_cBysname，通过提供学生的姓名来查询此学生的选课信息。该存
9、储过程对传递的参数进行模式匹配。如果没有提供参数，则显示所有学生的选课信息。 create procedure usp_s_cBysname sname varchar(20)= % as select s.sno, sname, o, cname, degree from Student s, Score sc, Course c where s.sno=sc.sno and o=o and s.sname like sname go 执行该存储过程： exec usp_s_cBysname 李%,【例7-4】创建带有OUTPUT参数的存储过程。 创建存储过程usp_GetAvg，它返回某学
10、生的平均分信息。该例使用 OUTPUT 参数，外部过程、批处理或多个Transact-SQL 语句可以访问在过程执行期间设置的值。 create procedure usp_GetAvg sno char(10), avgdegree tinyint out as set avgdegree=(select avg(degree) from score where sno=sno) go,执行该存储过程，并使用其返回值。 declare avg tinyint exec usp_GetAvg 06081201,avg out print 学号为06081201的学生的平均分是：+str(avg
11、),7.1.4 查看存储过程信息,7.1.5 修改存储过程,语法格式为： ALTER PROCEDURE 存储过程名 ; number parameter data_type VARYING = default OUTPUT ,n WITH RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION FOR REPLICATION AS SQL语句,【例7-9】修改usp_s_c存储过程，对其进行加密。 use Study go alter procedure usp_s_c with encryption as select s.sno, sname, o,
12、 cname, degree from student s, score sc, course c where s.sno=sc.sno and o=o go,7.1.6 删除存储过程,DROP PROCEDURE 存储过程名 , n 【例7-10】 删除存储过程usp_s_c。 use Study go drop procedure usp_s_c go,7.2 触发器,7.2.1 触发器概述 7.2.2 创建触发器 7.2.3 管理触发器 7.2.4 修改触发器 7.2.5 删除触发器 7.2.6 存储过程和触发器在维护数据 完整性中的作用,7.2.1 触发器概述,Microsoft SQ
13、L Server 2005 提供了两种主要机制来强制执行业务规则和数据完整性：约束和触发器。 触发器是一种特殊的存储过程，它在执行语句事件时自动生效。 SQL Server 包括两大类触发器：DML 触发器和 DDL 触发器。,7.2.1 触发器概述,类型,用途,触发事件,DML触发器,DML触发器有三种类型： （1）AFTER触发器 （2）INSTEAD OF 触发器 （3）CLR 触发器,AFTER触发器,在执行了INSERT、UPDATE或DELETE语句操作之后执行AFTER触发器。 指定 AFTER 与指定 FOR 相同。 AFTER 触发器只能在表上指定。 一个表中可以具有多个给定
14、类型的 AFTER 触发器，只要它们的名称不相同即可。 每个触发器只能应用于一个表。 一个触发器可以应用于三个用户操作（UPDATE、INSERT 和 DELETE）的任何子集。,INSTEAD OF 触发器,执行 INSTEAD OF 触发器代替通常的触发动作。如可以为视图定义 INSTEAD OF 触发器，则这些触发器能够扩展视图可支持的更新类型。 INSTEAD OF触发器的主要优点是可以使不能更新的视图支持更新。基于多个表的视图必须使用 INSTEAD OF 触发器来支持引用多个表中数据的插入、更新和删除操作。 一个表只能具有一个给定类型的 INSTEAD OF 触发器。,CLR 触发
15、器,CLR 触发器可以是 AFTER 触发器或 INSTEAD OF 触发器。CLR 触发器还可以是 DDL 触发器。 CLR 触发器将执行在托管代码（在 .NET Framework 中创建并在 SQL Server 中上载的程序集的成员）中编写的方法，而不用执行 Transact-SQL 存储过程。,7.2.2 创建触发器,创建 DML 触发器前应考虑下列问题： （1）CREATE TRIGGER 语句必须是批处理中的第一个语句，该语句后面的所有其他语句将被解释为 CREATE TRIGGER 语句定义的一部分。 （2）创建 DML 触发器的权限默认分配给表的所有者，且不能将此权限转给其他
16、用户。 （3）DML 触发器为数据库对象，其名称必须遵循标识符的命名规则。 （4）虽然 DML 触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建 DML 触发器。,7.2.2 创建触发器,（5）虽然 DML 触发器可以引用临时表，但不能对临时表或系统表创建 DML 触发器。不应引用系统表，而应使用信息架构视图。 （6）对于含有用 DELETE 或 UPDATE 操作定义的外键的表，不能定义 INSTEAD OF DELETE 和 INSTEAD OF UPDATE 触发器。 （7）虽然 TRUNCATE TABLE 语句类似于不带 WHERE 子句的 DELETE 语句（用于删除所有行），但它
