sql存储过程和触发器ppt课件

1、大型数据库大型数据库SQL Server 2000SQL Server 2000程序设计程序设计1第7章 存储过程和触发器n存储过程存储过程n触发器触发器大型数据库大型数据库SQL Server 2000SQL Server 2000程序设计程序设计27.1 7.1 存储过程存储过程 存储过程是一种数据库对象，将执行计划存储在数据库的服务器中。存储过程是一种数据库对象，将执行计划存储在数据库的服务器中。它的运行速度比独立运行同样的程序要快。它的运行速度比独立运行同样的程序要快。存储过程类型存储过程类型创建存储过程创建存储过程执行存储过程执行存储过程修改存储过程修改存储过程删除存储过程删除存储过

2、程大型数据库大型数据库SQL Server 2000SQL Server 2000程序设计程序设计37.1.1 7.1.1 存储过程类型存储过程类型 系统存储过程系统存储过程 存储在存储在mastermaster数据库中，以数据库中，以sp_sp_为前缀。可以在其他为前缀。可以在其他 数据库中对其进行调用。数据库中对其进行调用。 用户自定义存储过程用户自定义存储过程 由用户创建并能完成某一特定功能的存储过程。或由用户创建并能完成某一特定功能的存储过程。或 称本地存储过程。包括临时存储过程、远程存储过称本地存储过程。包括临时存储过程、远程存储过 程、扩展存储过程。程、扩展存储过程。 临时存储过程

3、又包括局部的和全局的临时存储过临时存储过程又包括局部的和全局的临时存储过 程，前者在过程名的前面带程，前者在过程名的前面带# #，后者在过程名的前面，后者在过程名的前面 带带#。全局临时存储过程对所有用户都可见。全局临时存储过程对所有用户都可见。 扩展存储过程的前缀是扩展存储过程的前缀是xp_ xp_ 。 大型数据库大型数据库SQL Server 2000SQL Server 2000程序设计程序设计47.1.2 7.1.2 创建存储过程创建存储过程用企业管理器创建用企业管理器创建用用T-SQLT-SQL命令创建命令创建 存储过程的三个组成部分：存储过程的三个组成部分：（1 1）所有的输入参数

4、以及传给调用者的输出参数；）所有的输入参数以及传给调用者的输出参数；（2 2）被执行的针对数据库的操作语句，包括调用其他）被执行的针对数据库的操作语句，包括调用其他 存储过程的语句；存储过程的语句；（3 3）返回给调用者的状态值，以指明调用是成功还是）返回给调用者的状态值，以指明调用是成功还是 失败。失败。大型数据库大型数据库SQL Server 2000SQL Server 2000程序设计程序设计57.1.2 7.1.2 创建存储过程创建存储过程T-SQLT-SQL创建存储过程的基本语法格式：创建存储过程的基本语法格式：CREATE PROCEDURE CREATE PROCEDURE 存

5、储过程名称存储过程名称 参数定义参数定义AS AS SQLSQL语句语句例例7.17.1：创建存储过程，实现查询所有学生信息的功能：创建存储过程，实现查询所有学生信息的功能。Create proc proc_7_1Create proc proc_7_1AsAsSelect Select * * From xsqk From xsqk思考：创建存储过程，实现查询所有学生的学号、姓名、所选课程号、课思考：创建存储过程，实现查询所有学生的学号、姓名、所选课程号、课程名、成绩及学分信息的功能。程名、成绩及学分信息的功能。大型数据库大型数据库SQL Server 2000SQL Server 2000

6、程序设计程序设计67.1.2 7.1.2 创建存储过程创建存储过程例例7.27.2：创建存储过程：创建存储过程proc_7_2proc_7_2，要求实现根据学生学号，产生不同结果，要求实现根据学生学号，产生不同结果，如果该学生信息不存在，则显示如果该学生信息不存在，则显示“无此学号的学生！无此学号的学生！”，否则返回该学生的，否则返回该学生的基本信息基本信息。Create proc proc_7_2 sno char(8)Create proc proc_7_2 sno char(8)AsAsIf exists(Select If exists(Select * * From xsqk whe

7、re From xsqk where 学号学号= = sno)sno) select select * * From xsqk where From xsqk where 学号学号= = snosnoElse Else print print 无此学号的学生！无此学号的学生！思考：创建存储过程思考：创建存储过程testproc2testproc2，实现根据学生的学号，查询此学生的学号、实现根据学生的学号，查询此学生的学号、姓名、所选课程号、课程名、成绩及学分等信息。姓名、所选课程号、课程名、成绩及学分等信息。大型数据库大型数据库SQL Server 2000SQL Server 2000程序设

8、计程序设计77.1.2 7.1.2 创建存储过程创建存储过程说明：说明：（1 1）在一个批处理中，）在一个批处理中，Create procedureCreate procedure语句不能与其语句不能与其 他他SQLSQL语句合并在一起。语句合并在一起。（2 2）数据库所有者具有默认的创建存储过程的权限。）数据库所有者具有默认的创建存储过程的权限。（3 3）存储过程作为数据库对象其命名必须符合命名规）存储过程作为数据库对象其命名必须符合命名规 则。则。（4 4）只能在当前数据库中创建属于当前数据库的存储过）只能在当前数据库中创建属于当前数据库的存储过 程。程。（5 5）一个存储过程的最大尺寸为

9、）一个存储过程的最大尺寸为128128M M。大型数据库大型数据库SQL Server 2000SQL Server 2000程序设计程序设计87.1.3 7.1.3 执行存储过程执行存储过程语句格式：语句格式： EXECUTE EXECUTE 存储过程名称存储过程名称 参数值参数值例例7.37.3：执行例：执行例7.17.1的存储过程。的存储过程。 exec proc_7_1 exec proc_7_1例例7.47.4：执行例：执行例7.27.2的存储过程，查询学号为的存储过程，查询学号为“”“”学生的基本信息。学生的基本信息。 exec proc_7_2 exec proc_7_2 或或

10、exec proc_7_2 sno= exec proc_7_2 sno= 大型数据库大型数据库SQL Server 2000SQL Server 2000程序设计程序设计97.1.3 7.1.3 举例举例例例7.57.5：在学生成绩库中创建存储过程：在学生成绩库中创建存储过程proc_7_t1proc_7_t1，要求实现如下功能：产生要求实现如下功能：产生计算机计算机02030203班学生的选课情况列表，其中包括学号、姓名、性别、课程号、班学生的选课情况列表，其中包括学号、姓名、性别、课程号、课程名称、学分等。并调用此存储过程，显示执行结果。课程名称、学分等。并调用此存储过程，显示执行结果。

11、create proc proc_7_t1asSelect xsqk.学号学号, 姓名姓名, 性别性别, xskc.课程号课程号, 课程名课程名, xskc.学分学分 From xsqk, xscj, xskc Where xsqk.学号学号 = xscj.学号学号 and xscj.课程号课程号 = xskc.课程号课程号 and 班级班级 = 计算机计算机0203大型数据库大型数据库SQL Server 2000SQL Server 2000程序设计程序设计10例例7.67.6：在学生成绩库中创建存储过程：在学生成绩库中创建存储过程proc_7_t2proc_7_t2，要求实现如下功能：根

12、要求实现如下功能：根据学生学号，如果此学生存在，则产生该生的课程成绩列表，其中包括学据学生学号，如果此学生存在，则产生该生的课程成绩列表，其中包括学号、课程号、课程名称、成绩、学分等；如果此学生不存在，则显示号、课程号、课程名称、成绩、学分等；如果此学生不存在，则显示“无无此学生！此学生！”。并调用此存储过程，显示。并调用此存储过程，显示“”“”学生的课程成绩情况。学生的课程成绩情况。 create proc proc_7_t2 sno char(8)create proc proc_7_t2 sno char(8) as as if exists(select if exists(selec

13、t * * from xsqk where from xsqk where 学号学号=sno) sno) Select Select 学号学号, ,xscj.xscj.课程号课程号, ,课程名课程名, ,成绩成绩, ,xskc.xskc.学分学分 From xscj, xskc From xscj, xskc Where xscj. Where xscj.课程号课程号= =xskc.xskc.课程号课程号 and and 学号学号= = snosno else else print print 无此学生！无此学生！大型数据库大型数据库SQL Server 2000SQL Server 2000

14、程序设计程序设计117.1.4 7.1.4 修改存储过程修改存储过程语法格式：语法格式：ALTER PROCEDURE ALTER PROCEDURE 存储过程名称存储过程名称 参数定义参数定义AS AS SQLSQL语句语句例例7.77.7：修改在例：修改在例7.57.5中已创建的存储过程中已创建的存储过程proc_7_t1proc_7_t1，要求在显示列中加要求在显示列中加入成绩列。入成绩列。alter proc proc_7_t1alter proc proc_7_t1asas Select xsqk. Select xsqk.学号学号, ,姓名姓名, ,性别性别, ,xskc.xskc

15、.课程号课程号, , 课程名课程名, , xskc.xskc.学分学分, , 成绩成绩 From xsqk, xscj, xskc From xsqk, xscj, xskc Where xsqk. Where xsqk.学号学号 = = xscj.xscj.学号学号 and xscj. and xscj.课程号课程号 = = xskc.xskc.课程号课程号 and and 班级班级 = = 计算机计算机02030203大型数据库大型数据库SQL Server 2000SQL Server 2000程序设计程序设计127.1.5 7.1.5 删除存储过程删除存储过程语法格式：语法格式：DRO

16、P PROCEDURE 存储过程名称存储过程名称例例7.8：删除存储过程：删除存储过程proc_7_1。 drop proc proc_7_1 一般地，在用一般地，在用T-SQLT-SQL命令创建存储过程时，总是先确定要创建的存储过程命令创建存储过程时，总是先确定要创建的存储过程是否已经存在，如果存在，那么就删除重建。我们可以用如下语句实现：是否已经存在，如果存在，那么就删除重建。我们可以用如下语句实现： If exists( select name from sysobjects where name = proc_7_1 and type = P) drop proc proc_7_1大型

17、数据库大型数据库SQL Server 2000SQL Server 2000程序设计程序设计137.2 7.2 触发器触发器 触发器是一种特殊类型的存储过程，当表中数据被修改时，触发器是一种特殊类型的存储过程，当表中数据被修改时，SQL ServerSQL Server自动执行触发器中定义的自动执行触发器中定义的T-SQLT-SQL语句。使用触发器可语句。使用触发器可以实施更为复杂的数据完整性约束。以实施更为复杂的数据完整性约束。触发器概述触发器概述创建触发器创建触发器修改触发器修改触发器删除触发器删除触发器大型数据库大型数据库SQL Server 2000SQL Server 2000程序设

18、计程序设计147.2.1 7.2.1 触发器概述触发器概述 触发器的概念及作用触发器的概念及作用 触发器是一种特殊类型的存储过程，主要触发器是一种特殊类型的存储过程，主要通过事件进行触发执行的通过事件进行触发执行的, ,是维护数据库完整是维护数据库完整性最强大的工具，能在任何情况下维护数据库性最强大的工具，能在任何情况下维护数据库的完整性。的完整性。大型数据库大型数据库SQL Server 2000SQL Server 2000程序设计程序设计157.2.1 7.2.1 触发器概述触发器概述触发器可以完成的任务触发器可以完成的任务l数据库中相关表的级联操作数据库中相关表的级联操作l不接受或回滚

19、违反参照完整性的数据修改事务不接受或回滚违反参照完整性的数据修改事务l强制执行远复杂于强制执行远复杂于CHECKCHECK约束的限制约束约束的限制约束l通过通过INSERT,UPDATEINSERT,UPDATE和和DELETEDELETE语句比较数据修改前后的状态语句比较数据修改前后的状态, ,并根并根据修改据修改, ,执行一定的操作执行一定的操作. .大型数据库大型数据库SQL Server 2000SQL Server 2000程序设计程序设计167.2.1 7.2.1 触发器概述触发器概述 触发器的种类触发器的种类 AFTERAFTER触发器触发器 只有对表执行某一操作之后，才能被触发

20、。可以为只有对表执行某一操作之后，才能被触发。可以为 表的同一操作定义多个触发器，其触发次序可使用表的同一操作定义多个触发器，其触发次序可使用 sp_settriggerorder sp_settriggerorder来完成。来完成。 INSTEAD OFINSTEAD OF触发器触发器 并不执行其所定义的操作而仅是执行触发器本身，并不执行其所定义的操作而仅是执行触发器本身， 对同一操作只能定义一个对同一操作只能定义一个instead of instead of 触发器。触发器。大型数据库大型数据库SQL Server 2000SQL Server 2000程序设计程序设计177.2.1 7.

21、2.1 触发器概述触发器概述 INSERTEDINSERTED和和DELETEDDELETED临时表临时表 触发器代码最强大的功能之一是能够比较记录的新旧触发器代码最强大的功能之一是能够比较记录的新旧 版本，用户可以根据记录的改变适时地做出决定。版本，用户可以根据记录的改变适时地做出决定。 插入一行时，插入一行时，INSERTEDINSERTED表保存了一份插入行的拷贝。表保存了一份插入行的拷贝。 删除一行时，删除一行时，DELETEDDELETED表保存了删除行的拷贝。表保存了删除行的拷贝。 更新一行时，更新一行时，INSERTEDINSERTED表中保存了一份新行的拷贝，表中保存了一份新行

22、的拷贝， DELETED DELETED表中保存了一份旧行的拷贝。表中保存了一份旧行的拷贝。 可以使用这两个临时的驻留内存的表测试某些数据修可以使用这两个临时的驻留内存的表测试某些数据修 改的效果及设置触发器操作的条件，但不能直接对这改的效果及设置触发器操作的条件，但不能直接对这 两个临时表中的数据进行更改。两个临时表中的数据进行更改。大型数据库大型数据库SQL Server 2000SQL Server 2000程序设计程序设计18利用 CREATE TRIGGER 命令创建触发器CREATE TRIGGER trigger_name ON ( table | view ) WITH ENC

23、RYPTION FOR | AFTER | INSTEAD OF DELETE , INSERT , UPDATE WITH APPEND NOT FOR REPLICATION ASSql_statementn | FOR | AFTER | INSTEAD OF DELETE , INSERT , UPDATE WITH APPEND NOT FOR REPLICATION AS IF UPDATE (column) AND | OR UPDATE (column) .n | IF ( C O LUMN S_UPDA T E D () bitwise_operator updated_bi

24、tmask) comparison_operator column_bitmask .n sql_statement .n 大型数据库大型数据库SQL Server 2000SQL Server 2000程序设计程序设计197.2.2 7.2.2 创建触发器创建触发器用企业管理器创建用企业管理器创建用用T-SQLT-SQL命令创建命令创建语法格式：语法格式：CREATE TRIGGER CREATE TRIGGER 触发器名称触发器名称ON ON 表或视图表或视图 FOR | AFTER | INSTEAD OF FOR | AFTER | INSTEAD OF DELETEDELETE，INSERTINSERT，UPDATEUPDATEAS AS SQLSQL语句语句 大型数据库大型数据库SQL Server 2000SQL Server 2000程序设计程序设计207.2.2 7.2.2 创建触发器创建触发器v CTEATE TRIGGER CTEATE TRIGGER 语句必须是批处理的第一个语句