第10章存储过程和触发器

13
SQL SERVER 2005
【问题】创建一个名为p_Student的存储过程，返回student表
中班级编码为20000001的学生信息。
CREATE PROCEDURE p_Student
AS
SELECT *
FROM Student
WHERE ClassNo='20000001'
14
SQL SERVER 2005
程所引用）：
sp_depends [ @objname = ] 'table_name‘ （4）查看一个存储过程引用了哪些表及其列： sp_depends [ @objname = ] 'procedure_name'
29
重命名、删除存储过程
SQL SERVER 2005
重命名 Sp_rename 旧存储过程名，新存储过程名 删除格式 DROP PROCEDURE proc_name
根据给定的班级编码显示该班级所有学生的信息。
CREATE PROCEDURE p_StudentPara @ClassNo VARCHAR(8) AS SELECT * FROM Student WHERE ClassNo=@ClassNo
19
练习2
SQL SERVER 2005
创建一个名为p_StuByPara的存储过程，根据给定的班级
5
SQL SERVER 2005
存储过程的分类 系统存储过程。 扩展存储过程（提供从SQL Server到外部程序的 接口，以便进行各种维护活动）。 用户自定义的存储过程。
6
SQL SERVER 2005
系统执行T-SQL语句的步骤 T-SQL语句发送到服务器 服务器编译T-SQL语句 优化产生查询执行计划 数据库引擎执行查询计划 执行结果发回客户程序
语法如下：
EXEC procedure_name
[value1,value2…]
24
SQL SERVER 2005
【例】用按位置传递参数的方法执行存储过程 p_StudentPara ，分别查找班级代码为20010051和 20010059的学生记录。
EXEC p_StudentPara ‘20010051’
使用T-SQL语言查看存储过程的信息 （1）查看定义存储过程的文本： sp_helptext [ @objname = ] 'procedure_name'
28
SQL SERVER 2005
（2）查看存储过程的名称、所有者、建立时间： sp_help [ @objname = ] 'procedure_name' （3）查看存储过程所引用的表（或查看一个表被哪些存储过
CREATE TRIGGER 触发器名 ON {表|视图} [ WITH ENCRYPTION ] { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ]
[ , ] [DELETE] }
[ NOT FOR REPLICATION ]
AS
[{IF UPDATE(列名)[{AND|OR} UPDATE(列名)][ ...n ]} SQL语句
执行格式
EXEC proc_name
【例】使用Transact-SQL语句执行前例中创建的名为 p_Student的存储过程。
EXEC p_Student
15
练习1
SQL SERVER 2005
1. 创建一个名为p_StuByClass的存储过程，返回 “00电子商务”班的所有学生的信息。 2. 执行该存储过程，并使用管理器查看该存储过程 的定义。
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开 头的语句。
ROLLBACK
40
查看触发器
SQL SERVER 2005
sp_help：用于查看触发器的一般信息，如触发器 的名称、属性、类型和创建时间。 sp_help ‘触发器名称’ sp_helptext：用于查看触发器的正文信息 sp_helptext ‘触发器名称’ sp_depends：用于查看指定触发器所引用的表或 指定的表涉及到的所有触发器。 sp_depends ’触发器名称’
编码和学生学号，显示相应学生的信息。
CREATE PROCEDURE p_StuByPara
@ClassNo CHAR(8) ，@StuNo char（8）
AS SELECT * FROM Student WHERE ClassNo=@ClassNo and StuNo=@StuNo
20
执行带输入参数的存储过程
SQL SERVER 2005
创建格式 CREATE PROCEDURE proc_name [WITH ENCRYPTION] [WITH RECOMPILE] AS Sql_statements 其中： WITH ENCRYPTION对存储过程进行加密。 WITH RECOMPILE对存储过程重新编译。
学习难点：
理解存储过程和触发器的作用
3
教学内容
SQL SERVER 2005
1 2
存储过程概述
创建和执行存储过程 存储过程的管理和维护
创建和管理触发器
3
4
5
本章小结
4
第一节 存储过程概述
SQL SERVER 2005
什么是存储过程？
是一组被编译在一起的T-SQL语句的集合，它们被集合 在一起以完成一个特定的任务。
CREATE TRIGGER 语句必须是批处理中的第一个语句。
创建触发器的权限默认分配给表的所有者，且不能将该权限 转给其他用户。 触发器是数据库对象，其名称必须遵循标识符的命名规则。 虽然触发器可以引用当前数据库以外的对象，但只能在当前 数据库中创建触发器。
36
创建触发器的语法格式
SQL 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 ‘不允许删除表!’
郑州经贸职业学院计算机系
数据库应用技术 SQL Server 2005
第10章 存储过程和触发器
SQL SERVER 2005
学习目标：
1.理解存储过程和触发器的作用
2.熟练管理（如创建，删除）存储过程和触
发器
2
第10章 存储过程和触发器
SQL SERVER 2005
学习重点：
管理存储过程和触发器
34
创建DML触发器
SQL SERVER 2005
在创建触发器时，需要指定触发器的名称、包含触 发器的表、引发触发器的条件以及当触发器启动后 要执行的语句等内容。和创建维护存储过程一样， 可以通过CREATE TRIGGER语句或管理器来创建 触发器。
35
SQL SERVER 2005
创建触发器应该考虑以下几个问题
EXEC p_StudentPara ‘20010051’
25
SQL SERVER 2005
比较：
按位置传递参数值比按参数名传递参数值简洁。比
较适合参数值较少的情况。
而按参数名传递的方法使程序可读性增强，特别是
在参数数量较多时，建议使用按参数名传递参数的
方法，这样的程序可读性、可维护性都要好一些。
26
第三节 存储过程的管理和维护
SQL SERVER 2005
修改格式 ALTER PROCEDURE proc_name @para_name datatype [=default], … [WITH ENCRYPTION] AS Sql_statements
27
查看存储过程
SQL SERVER 2005
30
系统存储过程
SQL SERVER 2005
在SQL Server中的许多管理过程都是通过执行系统 存储过程来完成的。 系统存储过程保存在master数据库中，都是以 “sp_”为前缀的，可以在任何数据库中使用系统存 储过程。
31
第四节 创建和管理触发器