数据库技术

第六章 存储过程与触发器 存储过程的基本概念 存储过程的特点与作用 触发器的基本概念 触发器的特点与作用 存储过程创建、执行以及参数应用的方法 触发器的创建及使用方法 存储过程的参数应用方法

？ 问题提出 为什么需要存储过程？存储过程是什么？ 为什么要触发器？触发器是什么？

6.1 存储过程概述 存储过程的特点和类型 存储过程的创建和执行 存储过程参数和执行状态 存储过程的查看和修改 存储过程的删除

6.1.1 存储过程的特点和类型 存储过程 是存储在服务器上的Transact-SQL语句的命名集合。 是封装重复任务的方法 存储过程的特点 封装复杂操作 加快系统运行速度 实现代码重用 增强安全性 减少网络流量 调用方便 查询通知的工作流 数据库监视 下面是一个查询通知的常规的工作流： 1. 一个服务员用程序提交一个SQL查询到数据库服务器中，并包含一个特别的注释，指明当数据查询改变时服务将请求通知。 2. SQL Server生成一个预约请求，并为该查询记录。然后它创建一个最优的查询计划并执行。 3. 查询结果返回给客户端，并发送给客户端管理的缓存。 4. 数据库监视任何可能对数据做出的改变。当它发现了这样的一个改变，它会生成一个改变通知并发送这个通知到一个队列中。 5. 通知被处理并发送到客户端服务应用程序。

6.1.1 存储过程的特点和类型 存储过程的类型 SQL Server 2008中常用的存储过程类型有3种： 系统存储过程（sp_）: 由数据库系统自身创建，存储在master数据库中，以“sp_” 前缀标识 用户定义存储过程（本地存储过程）: 在单独的用户数据库内由用户创建。 临时存储过程：可以是局部的，名称以“#”开头；也可以是全局的，名称以“##”开头。 扩展存储过程（xp_）: 以动态链接库（DLL）的形式实现。以“xp_”为前缀，只能添加到master数据库中，在SQL Server 环境外执行。 6

6.1.2 存储过程的创建和执行 创建存储过程的过程 存储过程名称 参数的说明 存储过程的主体 （包含执行过程操作的 T-SQL 语句）两部分。 可以使用3种方法创建存储过程： 使用图形工具 使用向导 使用Transact-SQL语言中的CREATE PROCEDURE语句 7

6.1.2 存储过程的创建和执行 使用图形工具创建存储过程 【例6-1】在School数据库中，创建带输入参数的存储过程proc_SearchStudent， 查询指定学生姓名的学生信息。 1）打开SSMS，展开School数据库文件夹，右单击“存储过程”，在弹出的快捷菜单上选择“新建存储过程”命令，打开新建存储过程对话框。 2）将“<Procedure_Name, sysname, ProcedureName>” 参数替换成存储过程存储过程的名称。 3）在“—Add Parameters”行下添加输入参数。 4）在BEGIN…END中输入查询语句。 5）执行存储过程：EXEC proc_SearchStudent '张三'

6.1.2 存储过程的创建和执行 使用CREATE PROCEDURE语句创建存储过程 常用存储过程的语法格式： CREATE PROC[DURE] 存储过程名 {@形式参数 数据类型}[VARYING][=默认值][OUTPUT] AS SQL语句1 … SQL语句n 注意事项： “形式参数”名称必须符合标识符规则；OUTPUT表示该参数是可以返回的，可将信息返回调用者；如果有多个参数，可以依次按以上参数定义规则列出，用逗号“,”隔开。 Schema 英 [‘skiːmə] （斯克母） 架构 “架构(schema)”的实体，但此实体实际上是数据库用户。 encryption 英 [ɪn‘krɪpʃən] （英克损）加密  复制（Replication）[replɪ‘keɪʃ(ə)n] [累捕拉克星]：复制可以将数据和数据库对象从 个数据库复制和分发到另一个数据库，然后在数据库间进行同步，以维持一致性。 9

6.1.2 存储过程的创建和执行 【例6-2】在School中创建一个的存储过程proc_Student,查询所有学生的信息。 CREATE PROC proc_Student AS SELECT * FROM Student 执行存储过程 EXEC proc_Student

6.1.2 存储过程的创建和执行 使用EXECUTE(或EXEC)命令执行存储过程 [[EXEC[UTE]] 注意事项： 语法格式如下： [[EXEC[UTE]] { [@return_status=] procedure_name [;number] |@procedure_name_var } [[@parameter=]{value|@variable [OUTPUT]|[DEFAULT]] [,...n] [WITH RECOMPILE] 注意事项： 执行存储过程必须具有执行该过程的权限许可 如果存储过程是批处理中的第一条语句，EXECUTE命令可以省略 存储过程的最大大小为128MB WITH RECOMPILE表示过程在运行时重新编译 11

6.1.2 存储过程的创建和执行 【例6-3】在School中创建存储过程proc_CountStudent，根据课程编号统计选修该课程的学生人数。 CREATE PROC proc_CountStudent @ccode varchar(20), -- 课程号，输入参数 @number int OUTPUT -- 选课人数，输出参数 AS SELECT @number=COUNT(Grade.StudentCode) FROM Grade WHERE Grade.CourseCode=@ccode 执行方法： 在SSMS中右击存储过程proc_CountStudent，在右键菜单中点击“执行存储过程” 输入@ccode的值后，点击“确定”

6.1.2 存储过程的执行 使用对象资源管理器中执行存储过程，操作方法如下： 13

6.1.3 存储过程参数和执行状态 存储过程参数 类型有：“输入”和“输出”参数 （1）输入参数 定义存储过程时，可指定输入参数，以@作为参数名称的前置字符，声明若干个参数变量及其数据类型，一个存储过程最多指定1024个参数。 （2）输出参数 如果要在存储过程中传回值给调用者，可在参数名称后使用OUTPUT 关键词。 同时，为了使用输出参数，必须在创建和执行存储过程时都使用OUTPUT关键词。

6.1.3 存储过程参数和执行状态 【例6.4】创建一个带两个参数的存储过程，从St_Info、C_Info、S_C_Info表的相关联接中返回输入参数的学生姓名和课程类别、该学生选课的课程名称和成绩。 CREATE PROCEDURE ScoreInfo @stname varchar(20), @ctype char(4) AS SELECT St_Name, C_Type, C_Name, Score FROM St_Info a, S_C_Info b, C_Info c WHERE a.St_ID = b.St_ID AND b.C_No = c.C_No AND St_Name = @stname AND C_Type = @ctype 在“新建查询”窗格中输入并运行如下命令： EXEC ScoreInfo '吴中华','必修' 15

6.1.3 存储过程参数和执行状态 两种传递参数的方式：位置标识和名字标识。 若在“新建查询”窗格中输入并运行如下命令： 位置标识传递参数 只按顺序提供值 参数值必须以参数的定义顺序列出 可以忽略有默认值的参数，但不能中断次序 例：EXEC ScoreInfo '吴中华','必修' 名字标识传递值 在调用语句中以“@参数名=值”的格式指定参数 当通过参数名传递值时，可以以任何顺序指定参数值，并且可省略允许空值或具有默认值 的参数 若在“新建查询”窗格中输入并运行如下命令： EXEC ScoreInfo @ctype='必修',@stname='吴中华' EXEC ScoreInfo @ctype='必修',@stname='杨平娟' 16

6.1.3 存储过程参数和执行状态-举例 【例6.5】创建带一个输入参数和一个输出参数的存储过程，通过输入参数在 St_Info表中查询指定学号的学生，以输出参数的形式返回学生所在的班级名称（Cl_Name字段）。 CREATE PROC StClass @stid char(10), @class_name char(20) OUTPUT AS SELECT @class_name = cl_name FROM St_Info WHERE St_Info.St_ID = @stid 执行该存储过程的语句如下： DECLARE @get_clname char(20) EXEC StClass '0603060109', @get_clname OUTPUT SELECT @get_clname 注意：变量被声明，其值会先被设为NULL。 DECLARE 英 [dɪ‘kleə] 迪克莱尔 17

6.1.3 存储过程参数和执行状态-举例 使用“执行过程”对话框操作 使用“执行过程”对话框可以在对象资源管理器中执行存储过程。 如果该过程具有参数，则这些参数将显示在网格中。可以在每个参数的“值”框中键入参数值完成参数输入。

6.1.3 存储过程参数和执行状态 返回存储过程状态 为了增强存储过程的效率，应使用错误信息向用户传达事务状态（成功或失败）。 RETURN语句 从查询或存储过程无条件返回，同时可以返回一个整数状态值（返回码） 返回码为0表示执行成功，返回-1～-99之间的整数，表示执行失败。

6.1.4 存储过程的查看和修改 使用对象资源管理器查看或修改存储过程

6.1.4 存储过程的查看和修改 使用系统存储过程查看存储过程 系统存储过程 作 用 使用语法 sp_helptext 作 用 使用语法 sp_helptext 查看存储过程的文本信息 sp_helptext [@objname=] 存储过程名 sp_depends 查看存储过程的相关性 sp_depends [@objname=] 存储过程名 sp_help 查看存储过程的一般信息 sp_help [@objname=] 存储过程名

6.1.4 存储过程的查看和修改 使用ALTER PROCEDURE语句修改存储过程 语法格式： ALTER PROC[EDURE] [schema_name.] procedure_name[;number] [{@parameter data_type} [VARYING][=default][OUTPUT]][,...n] [WITH {RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}] [FOR REPLICATION] AS sql_statement [,...n ] 参数和保留字的含义说明与CREATE PROCEDURE语句一致。

6.1.4 存储过程的查看和修改 重命名存储过程 可使用系统存储过程sp_rename，语法格式： sp_rename ' stored procedure object_name', ' stored procedure new_name ' 【例6.6】 将【例6.1】创建的存储过程p_Stu更名为Student_proc。 完成操作的语句： sp_rename 'p_Stu', ' Student_proc' 注意：通过对象资源管理器也可以修改存储过程的名称 存储过程老名称 存储过程新名称

6.1.5 存储过程的删除 删除存储过程 语法格式： 删除存储过程的注意事项 使用DROP PROCEDURE语句从当前数据库中移除用户定义存储过程 语法格式： DROP PROC[EDURE] { procedure_name } [ ,...n ] 删除存储过程的注意事项 在删除存储过程之前，执行系统存储过程sp_depends检查是否有对象依赖此存储过程。

6.1.5 存储过程的删除 【例6.7】删除例6.2所创建的存储过程Average_Score。 完成操作的语句： USE student_db GO IF EXISTS ( SELECT name FROM sysobjects WHERE name='Average_Score') DROP PROCEDURE Average_Score else print 'Average_Score存储过程不存在' 注意： 不论是重命名存储过程名称还是删除了存储过程，都会影响到引用该存储过程的其他数据库对象。 在系统视图下可以找到系统对象表sysobjects 归属于sys架构，它 保存当前数据库的对象，如约束、默认值、日志、规则、存储过程等 在2008里 sysobjects已经不是表了，而是视图。 可以在SQL Server Management Studio里展开任意数据库，然后依次展开“视图”-“系统视图”，在系统视图下可以找到 sysobjects 归属于sys架构中，也就是你可以找到sys.sysobjects。 视图的基表对用户是不可见的。 例：判断数据库中是否已经存在tabe1表，有的话就删除该表 if exists (select * from dbo.sysobjects where id = object_id(N'dbo.tabe1') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table dbo.tabe1

6.2 触发器概述 触发器的特点和类型 触发器的创建 触发器的查看和修改 触发器的删除 26

6.2.1 触发器的特点和类型 触发器（trigger） 是SQL Server数据库中一种特殊类型的存储过程，不能由用户直接调用，而且可以包含复杂的T-SQL语句。它是一个在修改指定表中的数据时执行的存储过程。用户可以用它来强制实施复杂的业务规则，以此确保数据的完整性。

6.2.1 触发器的特点和类型 触发器的特点 触发器与表紧密相连，可以看作表定义的一部分。 触发器是基于一个表创建的，但是可以针对多个表进行操作，实现数据库中相关表的级联更改。 触发器不能通过名称被直接调用，更不允许带参数，而是当用户对表中的数据进行修改这样的事件发生时，自动执行的行为。 触发器可以用于SQL Server约束、默认值和规则的完整性检查，实施更为复杂的数据完整性约束。 触发器可以评估数据修改前后的表状态，并根据其差异采取对策。 一个表中可以存在多个同类触发器（INSERT、UPDATE或DELETE），对于同一个修改语句可以有多个不同的对策用以响应。

6.2.1 触发器的特点和类型 触发器的类型 按触发事件不同分为2类 （1）DDL（数据定义语言）触发器 是指当服务器或数据库中发生DDL事件时将启用。DDL事件即指在表或索引中的create、alter、drop语句。 （2）DML（ 数据操纵语言 ）触发器 是指触发器在数据库中发生DML事件时将启用。DML事件即指在表或视图中修改数据的insert、update、delete语句。因此DML触发器也可分为3种类型： INSERT触发器、UPDATE触发器、DELETE触发器。

6.2.1 触发器的特点和类型 触发器的类型 按触发器被激活的时机可以分为以下两种类型： （1）AFTER触发器（后触发器） 是在触发动作之后再触动，可视为控制触发器激活时间的机制。在引起触发器执行的更新语句成功完成之后执行。如果更新语句因错误（如违反约束或语法错误）而失败，触发器将不会执行。 此类触发器只能定义在表上，不能创建在视图上。可以为每个触发操作（如INSERT、UPDATE或DELETE）创建多个AFTER触发器。 （2）INSTEAD OF触发器（替代触发器） 将在数据变动以前被触发，该类触发器代替触发操作被执行。 该类触发器既可在表上定义，也可在视图上定义。对于每个触发操作（INSERT、UPDATE和DELETE）只能定义1个INSTEAD OF触发器 30

6.2.2 触发器的创建 与触发器相关的虚拟表 在触发器执行的时候，系统产生两个临时表：inserted 表和deleted 表。 存储着被INSERT和UPDATE语句影响的新的数据记录。当用户执行INSERT和UPDATE语句时，新数据记录的备份被复制到inserted临时表中。 （2）deleted表 存储着被DELETE和UPDATE语句影响的旧数据记录。在执行DELETE和UPDATE语句过程中，指定的旧数据记录被用户从基本表中删除，然后转移到delete表中。

6.2.2 触发器的创建 创建触发器主要有T-SQL语句和对象资源管理器等方式。 1．使用CREATE TRIGGER语句创建触发器 语法格式： CREATE TRIGGER [schema_name.] trigger_name ON { table_name|view_name } [ WITH ENCRYPTION ] { FOR | AFTER | INSTEAD OF } { [DELETE][,][INSERT][,][UPDATE] } AS sql_statement [,...n ] 创建触发器必须指定的选项： 名称； 在其上定义触发器的表； 触发器将何时激发； 激活触发器的数据修改语句； 执行触发操作的编程语句； 对CREATE TRIGGER 语句的文本加密。

6.2.2 触发器的创建 创建触发器注意事项： （1）CREATE TRIGGER语句必须是批处理中的第一条语句。 （2）只能在当前数据库中创建触发器，一个触发器只能对应一个表。 （3）表的所有者具有创建触发器的默认权限，不能将该权限转给其他用户。 （4）不能在视图、临时表、系统表上创建触发器，但是触发器可以引用视图、临时表，但是不能引用系统表。 （5）尽管TRUNCATE TABLE语句类似于没有WHERE子句的DELETE语句，但由于该语句不被记入日志，所以它不会引发DELETE触发器。

6.2.2 触发器的创建 【例6.8】创建DelCourse触发器的语句如下： CREATE TRIGGER DelCourse ON C_Info FOR DELETE AS DELETE S_C_Info WHERE C_No IN (SELECT C_No FROM deleted) 在“新建查询”窗格中输入以下语句并执行： DELETE FROM C_Info WHERE C_No='29000011' 注意：该语句从C_Info表中删除课程编号为“29000011”的数据行，触发DelCourse触发器，产生信息：

6.2.2 触发器的创建 2．使用图形界面方式创建触发器 在表St_Info上创建触发器，操作步骤： PRINT命令的作用是向客户端返回用户定义的消息。 在学生表St_Info中的INSERT操作上创建了一个名称为“st_Insert”的触发器。 当在该表上执行任何有效的插入操作（不论是否实际插入了记录）时，都会激活该触发器，将变量@str的值设为“TRIGGER IS WORKING”。

6.2.3 触发器的查看和修改 1．使用对象资源管理器查看触发器信息

6.2.3 触发器的查看和修改 2．使用系统存储过程查看触发器信息 在SQL Server中，根据不同需要，可以使用sp_helptext、sp_depends、sp_help等系统存储过程来查看触发器的不同信息。 例：

6.2.3 触发器的查看和修改 专门查看触发器属性信息的系统存储过程：sp_helptrigger，语法格式： 操作的语句： sp_helptrigger [ @tabname = ] 'table' [ , [ @triggertype = ] 'type' ] 【例6.9】查看S_C_Info表上存在的触发器的属性信息。 操作的语句： EXEC sp_helptrigger S_C_Info

6.2.3 触发器的查看和修改 3.使用对象资源管理器修改触发器的正文 注意：被设置成“WITH ENCRYPTION”触发器是不能被修改的。

6.2.3 触发器的查看和修改 4．使用ALTER TRIGGER修改触发器的正文 语法格式： ALTER TRIGGER [schema_name.]trigger_name ON { table_name|view_name } [ WITH ENCRYPTION ] { FOR|AFTER|INSTEAD OF } { [DELETE][,][INSERT][,][UPDATE] } AS sql_statement [,...n ] 5.重命名触发器 可以使用系统存储过程sp_rename 语法格式： sp_rename objname, newname 其中的参数与创建触发器语句中的参数相同 原触发器的名称 新触发器名称

6.2.4 触发器的删除 1.使用对象资源管理器删除触发器 2.使用DROP TRIGGER语句删除触发器 语法格式： DROP TRIGGER { trigger } [ ,...n ] 3．删除表的同时删除触发器 当某个表被删除后，该表上的所有触发器将自动被删除，但是删除触发器不会对表中数据产生影响。 n：表示可以指定需要删除的多个触发器

本章小结 （1）存储过程和触发器都是SQL Server数据库中的对象。 存储过程是T-SQL语句和可选控制流语句的预编译集合，SQL Server会将该集合中的语句编译成一个执行单位。 存储过程可包含程序流、逻辑以及对数据库的查询。可以接受输入参数、输出参数、返回单个或多个结果集以及状态值。 （2）触发器是一种特殊的存储过程，它与表紧密相连，基于表而建立，可视作表的一部分。 用户创建触发器后，就能控制与触发器关联的表。当表中的数据发生插入、删除或修改时，触发器自动运行。 触发器是一种维持数据引用完整性的极好方法。 42