第10章存储过程和触发器

通过隔离和加密的方法提高了数据库的安全性，通过 授权可以让用户只能执行存储过程而不能直接访问数 据库对象。
10
SQL SERVER 2005
在下例情况下，将使用到存储过程：
（ 1）不需要任何客户端信息而在服务器端完成的操
作；
（2）涉及到大量行的操作、运算；
（3）频繁的、周期性的操作； （4）那些被多个模块共同执行的操作。
EXEC p_StudentPara ‘20010051’
25
SQL SERVER 2005
比较：
按位置传递参数值比按参数名传递参数值简洁。比
较适合参数值较少的情况。
而按参数名传递的方法使程序可读性增强，特别是
在参数数量较多时，建议使用按参数名传递参数的
方法，这样的程序可读性、可维护性都要好一些。
11
存储过程和视图的比较
SQL SERVER 2005
视图 语句
只能是SELECT语句 不能接受参数，只能 返回结果集
存储过程
可以包含程序流、逻 辑以及SELECT语句 可以有输入输出参数， 也可以有返回值 完成某个特定的较复
输入、
返回结果 典型应用
多个表格的连接查询
杂的任务
12
第二节 创建、执行简单的存储过程
编码和学生学号，显示相应学生的信息。
CREATE PROCEDURE p_StuByPara
@ClassNo CHAR(8) ，@StuNo char（8）
AS SELECT * FROM Student WHERE ClassNo=@ClassNo and StuNo=@StuNo
20
执行带输入参数的存储过程
1. 模块化编程
创建一个存储过程存放在数据库中后，就可以被
其他程序反复使用。
2. 快速执行
存储过程第一次被执行后，就驻留在内存中。以
后执行就省去了重新分析、优化、编译的过程。
9
SQL SERVER 2005
3. 减少网络通信量
有了存储过程后，在网络上只要一条语句就能执行一
个存储过程。
4. 安全机制
使用T-SQL语言查看存储过程的信息 （1）查看定义存储过程的文本： sp_helptext [ @objname = ] 'procedure_name'
28
SQL SERVER 2005
（2）查看存储过程的名称、所有者、建立时间： sp_help [ @objname = ] 'procedure_name' （3）查看存储过程所引用的表（或查看一个表被哪些存储过
SQL SERVER 2005
使用参数名传递参数值
通过@para_name = value 给出参数的传递值。当
存储过程含有多个输入参数时，参数值可以按任
意顺序设定，对于允许空值和具有默认值的输入
参数可以不给出参数的传递值。
EXEC proc_name
[@para_name = value ][, …n]
32
触发器的作用
SQL SERVER 2005
在指定的表中数据发生变化时被调用以响应 INSERT、UPDATE或DELETE事件。 强制执行业务规则。
33
触发器类型
SQL SERVER 2005
DML触发器
当数据库服务器中发生数据操作语言（DML）事件时要执
行的操作，包括对表或视图发出的UPDATE、INSERT或 DELETE语句。 DDL触发器 DDL触发器将激发存储过程以响应事件，他们会响应数据 定义语言（DDL），包括以CREATE、ALTER、DROP开 头的语句。
程所引用）：
sp_depends [ @objname = ] 'table_name‘ （4）查看一个存储过程引用了哪些表及其列： sp_depends [ @objname = ] 'procedure_name'
29
重命名、删除存储过程
SQL SERVER 2005
重命名 Sp_rename 旧存储过程名，新存储过程名 删除格式 DROP PROCEDURE proc_name
26
第三节 存储过程的管理和维护
SQL SERVER 2005
修改格式 ALTER PROCEDURE proc_name @para_name datatype [=default], … [WITH ENCRYPTION] AS Sql_statements
27
查看存储过程
SQL SERVER 2005
13
SQL SERVER 2005
【问题】创建一个名为p_Student的存储过程，返回student表
中班级编码为20000001的学生信息。
CREATE PROCEDURE p_Student
AS
SELECT *
FROM Student
WHERE ClassNo='20000001'
14
SQL SERVER 2005
30
系统存储过程
SQL SERVER 2005
在SQL Server中的许多管理过程都是通过执行系统 存储过程来完成的。 系统存储过程保存在master数据库中，都是以 “sp_”为前缀的，可以在任何数据库中使用系统存 储过程。
31
第四节 创建和管理触发器
SQL SERVER 2005
触发器是一种特殊的存储过程。存储过程是通过存 储过程名被调用执行的，而触发器主要是通过事件 触发而被执行的。
22
SQL SERVER 2005
按位置传递参数值
在执行存储过程的语句中，不通过参数名传递参数
值而直接给出参数的传递值。当存储过程有多个输
入参数时，传递值的顺序必须与存储过程中定义的
输入顺序相一致。按位置传递参数时，也可以忽略
允许空值和具有默认值的参数，但不能因此破坏输
入参数的设定顺序。
23
SQL SERVER 2005
21
SQL SERVER 2005
【例】用使用参数名传递参数值的方法执行存储过 程p_StudentPara，分别查找班级代码为20000001 和20000002的学生记录。 EXEC p_StudentPara @ClassNo=‘20000001’ EXEC p_StudentPara @ClassNo=‘20000002’
在查询编辑器中运行如下命令测试触发器： UPDATE Student SET Pwd=‘11111111’ WHERE StuNo=‘00000001’
ห้องสมุดไป่ตู้39
创建DDL触发器
SQL SERVER 2005
创建一个当用户删除表时触发的触发器 CREATE TRIGGER Test2 On DATABASE FOR DROP_TABLE AS PRINT ‘不允许删除表!’
执行格式
EXEC proc_name
【例】使用Transact-SQL语句执行前例中创建的名为 p_Student的存储过程。
EXEC p_Student
15
练习1
SQL SERVER 2005
1. 创建一个名为p_StuByClass的存储过程，返回 “00电子商务”班的所有学生的信息。 2. 执行该存储过程，并使用管理器查看该存储过程 的定义。
根据给定的班级编码显示该班级所有学生的信息。
CREATE PROCEDURE p_StudentPara @ClassNo VARCHAR(8) AS SELECT * FROM Student WHERE ClassNo=@ClassNo
19
练习2
SQL SERVER 2005
创建一个名为p_StuByPara的存储过程，根据给定的班级
郑州经贸职业学院计算机系
数据库应用技术 SQL Server 2005
第10章 存储过程和触发器
SQL SERVER 2005
学习目标：
1.理解存储过程和触发器的作用
2.熟练管理（如创建，删除）存储过程和触
发器
2
第10章 存储过程和触发器
SQL SERVER 2005
学习重点：
管理存储过程和触发器
各参数的意义如下：
@para_name 存储过程的参数名，必须以符号@为前缀。
datatype 参数的数据类型。
default 参数的默认值，如果执行存储过程时未提供该参数
的变量值，则使用default值。
18
SQL SERVER 2005
【例】创建一个名为p_StudentPara的存储过程，该存储过程
SQL SERVER 2005
创建格式 CREATE PROCEDURE proc_name [WITH ENCRYPTION] [WITH RECOMPILE] AS Sql_statements 其中： WITH ENCRYPTION对存储过程进行加密。 WITH RECOMPILE对存储过程重新编译。
ROLLBACK
40
查看触发器
SQL SERVER 2005
sp_help：用于查看触发器的一般信息，如触发器 的名称、属性、类型和创建时间。 sp_help ‘触发器名称’ sp_helptext：用于查看触发器的正文信息 sp_helptext ‘触发器名称’ sp_depends：用于查看指定触发器所引用的表或 指定的表涉及到的所有触发器。 sp_depends ’触发器名称’
CREATE TRIGGER 语句必须是批处理中的第一个语句。
创建触发器的权限默认分配给表的所有者，且不能将该权限 转给其他用户。 触发器是数据库对象，其名称必须遵循标识符的命名规则。 虽然触发器可以引用当前数据库以外的对象，但只能在当前 数据库中创建触发器。
36
创建触发器的语法格式
SQL SERVER 2005
7
SQL SERVER 2005
注意：存储过程是SQL语句和部分流控语句的预编 译集合，存储过程被进行了编译和优化。当存储过 程第一次执行时，SQL Server为其产生查询计划并 将其保留在内存中，这样以后在调用该存储过程时 就不必再进行编译，这能在一定程度上改善系统的
性能。
8
存储过程的作用
SQL SERVER 2005
语法如下：
EXEC procedure_name
[value1,value2…]
24
SQL SERVER 2005
【例】用按位置传递参数的方法执行存储过程 p_StudentPara ，分别查找班级代码为20010051和 20010059的学生记录。
EXEC p_StudentPara ‘20010051’
16
创建带输入参数的存储过程
SQL SERVER 2005

创建格式
CREATE PROCEDURE proc_name
@para_name datatype [=default], …
[WITH ENCRYPTION]
[WITH RECOMPILE]
AS
Sql_statements
17
SQL SERVER 2005
37
SQL SERVER 2005
【问题】创建一个触发器Test1，要求每当在Student表中修 改数据时，向客户端显示一条“记录已修改！”的消息。
CREATE TRIGGER Test1 On Student FOR UPDATE
AS
PRINT ‘记录已修改!’
38
SQL SERVER 2005
34
创建DML触发器
SQL SERVER 2005
在创建触发器时，需要指定触发器的名称、包含触 发器的表、引发触发器的条件以及当触发器启动后 要执行的语句等内容。和创建维护存储过程一样， 可以通过CREATE TRIGGER语句或管理器来创建 触发器。
35
SQL SERVER 2005
创建触发器应该考虑以下几个问题
学习难点：
理解存储过程和触发器的作用
3
教学内容
SQL SERVER 2005
1 2
存储过程概述
创建和执行存储过程 存储过程的管理和维护
创建和管理触发器
3
4
5
本章小结
4
第一节 存储过程概述
SQL SERVER 2005
什么是存储过程？
是一组被编译在一起的T-SQL语句的集合，它们被集合 在一起以完成一个特定的任务。
CREATE TRIGGER 触发器名 ON {表|视图} [ WITH ENCRYPTION ] { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ]
[ , ] [DELETE] }
[ NOT FOR REPLICATION ]
AS
[{IF UPDATE(列名)[{AND|OR} UPDATE(列名)][ ...n ]} SQL语句
5
SQL SERVER 2005
存储过程的分类 系统存储过程。 扩展存储过程（提供从SQL Server到外部程序的 接口，以便进行各种维护活动）。 用户自定义的存储过程。
6
SQL SERVER 2005
系统执行T-SQL语句的步骤 T-SQL语句发送到服务器 服务器编译T-SQL语句 优化产生查询执行计划 数据库引擎执行查询计划 执行结果发回客户程序