sql存储过程和触发器

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,大型数据库,SQL Server 2000,程序设计,第7章 存储过程和触发器,存储过程,触发器,7.1 存储过程,存储过程是一种数据库对象，将执行计划存储在数据库的服务器中。它的运行速度比独立运行同样的程序要快。,存储过程类型,创建存储过程,执行存储过程,修改存储过程,删除存储过程,7.1.1 存储过程类型,系统存储过程,存储在,master,数据库中，以,sp_,为前缀。可以在其他,数据库中对其进行调用。,用户自定义存储过程,由用户创建并能完成某一特定功能的存储过程。或,称本地存储过程。包括临时存储过程、远程存储过,程、扩展存储过程。,临时存储过程又包括局部的和全局的临时存储过,程，前者在过程名的前面带#，后者在过程名的前面,带#。全局临时存储过程对所有用户都可见。,扩展存储过程的前缀是,xp,_。,7.1.2 创建存储过程,用企业管理器创建,用,T-SQL,命令创建,存储过程的三个组成部分：,（1）所有的,输入参数,以及传给调用者的,输出参数,；,（2）被执行的针对数据库的,操作语句,，包括调用其他,存储过程的语句；,（3）,返回,给调用者的,状态值,，以指明调用是成功还是,失败。,7.1.2 创建存储过程,T-SQL,创建存储过程的基本语法格式：,CREATE PROCEDURE,存储过程名称 参数定义,AS,SQL,语句,例7.1：创建存储过程，实现查询所有学生信息的功能,。,Create proc,proc_7_1,As,Select *From xsqk,思考：创建存储过程，实现查询所有学生的学号、姓名、所选课程号、课程名、成绩及学分信息的功能。,7.1.2 创建存储过程,例7.2：创建存储过程,proc_7_2，,要求实现根据学生学号，产生不同结果，如果该学生信息不存在，则显示“无此学号的学生！”，否则返回该学生的基本信息,。,Create proc,proc_7_2,sno char(8),As,If exists(Select*From xsqk where,学号=,sno),select *From xsqk where,学号=,sno,Else,print ,无此学号的学生！,思考：创建存储过程,testproc2，,实现根据学生的学号，查询此学生的学号、姓名、所选课程号、课程名、成绩及学分等信息。,7.1.2 创建存储过程,说明：,（1）在一个批处理中，,Create procedure,语句不能与其,他,SQL,语句合并在一起。,（2）数据库所有者具有默认的创建存储过程的权限。,（3）存储过程作为数据库对象其命名必须符合命名规,则。,（4）只能在当前数据库中创建属于当前数据库的存储过,程。,（5）一个存储过程的最大尺寸为128,M。,7.1.3 执行存储过程,语句格式：,EXECUTE,存储过程名称 参数值,例7.3：执行例7.1的存储过程。,exec,proc_7_1,例7.4：执行例7.2的存储过程，查询学号为“02020101”学生的基本信息。,exec,proc_7_2 02020101,或,exec,proc_7_2 sno=02020101,7.1.3 举例,例7.5：在学生成绩库中创建存储过程,proc_7_t1，,要求实现如下功能：产生计算机0203班学生的选课情况列表，其中包括学号、姓名、性别、课程号、课程名称、学分等。并调用此存储过程，显示执行结果。,create proc,proc_7_t1,as,Select xsqk.,学号,姓名,性别,xskc.,课程号,课程名,xskc.,学分,From xsqk,xscj,xskc,Where xsqk.,学号=,xscj.,学号,and xscj.,课程号=,xskc.,课程号,and,班级=计算机0203,例7.6：在学生成绩库中创建存储过程,proc_7_t2，,要求实现如下功能：根据学生学号，如果此学生存在，则产生该生的课程成绩列表，其中包括学号、课程号、课程名称、成绩、学分等；如果此学生不存在，则显示“无此学生！”。并调用此存储过程，显示“02020101”学生的课程成绩情况。,create proc,proc_7_t2,sno char(8),as,if,exists(select*from xsqk where,学号=,sno,),Select,学号,xscj.,课程号,课程名,成绩,xskc.,学分,From xscj,xskc,Where xscj.,课程号=,xskc.,课程号,and,学号=,sno,else,print ,无此学生！,7.1.4 修改存储过程,语法格式：,ALTER PROCEDURE,存储过程名称 参数定义,AS,SQL,语句,例7.7：修改在例7.5中已创建的存储过程,proc_7_t1，,要求在显示列中加入成绩列。,alter proc,proc_7_t1,as,Select xsqk.,学号,姓名,性别,xskc.,课程号,课程名,xskc.,学分,成绩,From xsqk,xscj,xskc,Where xsqk.,学号=,xscj.,学号,and xscj.,课程号=,xskc.,课程号,and,班级=计算机0203,7.1.5 删除存储过程,语法格式：,DROP PROCEDURE,存储过程名称,例7.8：删除存储过程,proc_7_1。,drop proc,proc_7_1,一般地，在用,T-SQL,命令创建存储过程时，总是先确定要创建的存储过程是否已经存在，如果存在，那么就删除重建。我们可以用如下语句实现：,If exists(select name from sysobjects,where name=proc_7_1 and type=P),drop proc,proc_7_1,7.2 触发器,触发器是一种特殊类型的存储过程，当表中数据被修改时，,SQL Server,自动执行触发器中定义的,T-SQL,语句。使用触发器可以实施更为复杂的数据完整性约束。,触发器概述,创建触发器,修改触发器,删除触发器,7.2.1 触发器概述,触发器的概念及作用,触发器是一种特殊类型的存储过程，主要,通过事件进行触发执行的,是维护数据库完整,性最强大的工具，能在任何情况下维护数据库,的完整性。,7.2.1 触发器概述,触发器可以完成的任务,数据库中相关表的级联操作,不接受或回滚违反参照完整性的数据修改事务,强制执行远复杂于,CHECK,约束的限制约束,通过,INSERT,UPDATE,和,DELETE,语句比较数据修改前后的状态,并根据修改,执行一定的操作,.,7.2.1 触发器概述,触发器的种类,AFTER,触发器,只有对表执行某一操作之后，才能被触发。可以为,表的同一操作定义多个触发器，其触发次序可使用,sp_settriggerorder,来完成。,INSTEAD OF,触发器,并不执行其所定义的操作而仅是执行触发器本身，,对同一操作只能定义一个,instead of,触发器。,7.2.1 触发器概述,INSERTED,和,DELETED,临时表,触发器代码最强大的功能之一是能够比较记录的新旧,版本，用户可以根据记录的改变适时地做出决定。,插入一行时，,INSERTED,表保存了一份插入行的拷贝。,删除一行时，,DELETED,表保存了删除行的拷贝。,更新一行时，,INSERTED,表中保存了一份新行的拷贝，,DELETED,表中保存了一份旧行的拷贝。,可以使用这两个临时的驻留内存的表测试某些数据修,改的效果及设置触发器操作的条件，但不能直接对这,两个临时表中的数据进行更改。,利用,CREATE TRIGGER,命令创建触发器,CREATE TRIGGER trigger_name,ON(,table|view),WITH ENCRYPTION,FOR|AFTER|INSTEAD OF,DELETE ,INSERT ,UPDATE ,WITH APPEND,NOT FOR REPLICATION,AS,Sql_statementn,|,FOR|AFTER|INSTEAD OF DELETE ,INSERT ,UPDATE ,WITH APPEND,NOT FOR REPLICATION,AS,IF UPDATE(column),AND|OR UPDATE(column),.n,|IF(COLUMNS_UPDATED()bitwise_operator updated_bitmask),comparison_operator column_bitmask .n,sql_statement .n,7.2.2 创建触发器,用企业管理器创建,用,T-SQL,命令创建,语法格式：,CREATE TRIGGER,触发器名称,ON,表或视图,FOR|AFTER|INSTEAD OF,DELETE，INSERT，UPDATE,AS,SQL,语句,7.2.2 创建触发器,CTEATE TRIGGER,语句必须是批处理的第一个语句；,表的所有者具有创建触发器的默认权限，表的所有者,不能把该权限传给其他用户；,触发器是数据库对象，其命名必须符合命名规则。,尽管在触发器的,SQL,语句中可以参照其他数据库中的对,象，但是，触发器只能创建在当前数据库中。,一个触发器只能对应一个表，这是由触发器的机制决,定的。,7.2.3 修改触发器,1.,修改触发器的名字,sp_rename,触发器原名称,，,新名称,2.修改触发器正文,使用企业管理器,使用,T-SQL,命令,语法格式：,ALTER TRIGGER,触发器名称,ON,表或视图,FOR|AFTER|INSTEAD OF,DELETE，INSERT，UPDATE,AS,SQL,语句,7.2.4 删除触发器,语法格式：,DROP TRIGGER,触发器名称,如果删除触发器所在的表时，系统将自动删除与该表相关的触发器。,