第十章存储过程及触发器.ppt

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,*,*,*,第,十,章,存储过程和触发器,2026/4/2 周四,1,10.1,存储过程,10.1.1,存储过程概述,存储过程（,Stored Procedure,）是一组为了完成特定功能的,SQL,语句集，经编译后存储在数据库中。,一般用来处理需要与数据库进行频繁交互的复杂的业务。因为存储过程具有以下优点：,存储过程在服务器端运行，执行速度快。,封装业务逻辑也可以使数据库操作人员与应用系统开发人员的分工更明确，支持模块化设计。,2026/4/2 周四,2,存储过程执行一次后，其执行计划就驻留在高速缓冲存储器，如果需要再次调用，只需要从高速缓冲存储器中调用，提高系统性能。,存储过程可以作为安全机制。通过授予用户对存储过程的操作权限来实现安全机制。,使体现企业规则的运算程序放入数据库服务器中，以便集中控制。,2026/4/2 周四,3,10.1.2,存储过程的分类,1,系统存储过程,系统存储过程存储在,master,数据库中，以,sp_,开头，可以在其他数据库中进行调用。,如：,sp_help,就是取得指定对象的相关信息。,2,扩展存储过程,在,SQL Server,环境之外执行动态链接库称为扩展存储过程，以,XP_,开头,用来调用操作系统提供的功能。,3,用户自定义的存储过程,由用户创建并能完成某一特定功能的存储过程。包括,Transact-SQL,和,CLR,两种类型。,（,1,）,Transact-SQL,存储过程是指保存的,Transact-SQL,语句集合，可以接受和返回用户提供参数。也可以从数据库向客户端应用程序返回数据。,（,2,）,CLR,在本书不做详细介绍。,2026/4/2 周四,4,10.1.3,建立存储过程,1.,使用对象资源管理器建立存储过程,2.,使用,T-SQL,命令建立存储过程,语法形式如下：,CREATE PROCEDURE,procedure_name,parameter,data_type,VARYING,=,defaultOUTPUT,.n,WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION,FOR REPLICATION,AS,sql_statement,.n,2026/4/2 周四,5,【,例题,10.1】,创建一个不带参数的存储过程，名称为,pro_stu,，用于检索学生的姓名、性别和家庭住址。,USE score,GO,-,判断,pro_stu,存储过程是否存在，若存在，则删除,If,exists(select,name from,sysobjects,where name=,pro_stu,and type=p),DROP PROCEDURE,pro_stu,Go,-,建立存储过程,CREATE PROC,pro_stu,As,SELECT,student_name,sex,address,FROM student,2026/4/2 周四,6,【,例题,10.2】,创建一个带参数的存储过程，名称为,pro_class,，在执行存储过程的时候输入班级编号，然后检索该班级的学生的详细信息。,USE score,GO,-,判断,pro_class,存储过程是否存在，若存在，则删除,If,exists(select,name from,sysobjects,where name=,pro_class,and type=p),DROP PROCEDURE,pro_class,Go,-,建立存储过程,CREATE PROC,pro_class,c_no,char(8),As,SELECT *,FROM student,WHERE,class_id,=,c_no,2026/4/2 周四,7,10.1.4,存储过程的执行,语法形式如下：,EXECUTE,return_status,=,procedure_name|procedure_name_var,parameter=,value|variableOUTPUT|DEFAULT,.n,WITH RECOMPILE,2026/4/2 周四,8,1.,无参存储过程的执行,【,例题,10.3】,执行在例题,10.1,中创建的存储过程,pro_stu,。,EXEC,pro_stu,在执行带参存储过程的时候参数有以下几种传递方式：,2.,带参存储过程的执行,（,1,）顺序法,2026/4/2 周四,9,CREATE PROC,pro_update,s_id,char(10),c_id,char(5),result,int,-,参数,AS,UPDATE score,SET grade=result,WHERE,student_id,=,s_id,AND,course_id,=,c_id,/,*,顺序法执行存储过程，把,0801101,号学生,1001,号课程的成绩修改为,85,分*,/,EXEC,pro_update,0801101,1001,85,【,例题,10.4】,建立一个存储过程,pro_update,，通过指定学号和课程编号来把成绩表中的成绩修改为指定的某个值，然后执行该存储过程。,2026/4/2 周四,10,EXEC,pro_update,s_id,=0801101,result=85,c_id=1001,说明：提示法中的参数顺序可以与建立存储过程时的参数顺序不一致，而顺序法必须保持一致。,（,2,）提示法,【,例题,10.5】,通过提示法来执行存储过程,pro_update,。,2026/4/2 周四,11,建立存储过程时，可以指定参数的默认值，默认值必须为常量或者为,NULL,。,（,3,）使用默认参数,【,例题,10.6】,建立一个存储过程,pro_insert,，用来完成向课程表中插入记录的功能，然后执行该存储过程并检查执行结果。,CREATE PROC,pro_insert,c_id,char(5),c_name,char(20),period,int,=64,c_type char(10)=NULL,AS,INSERT course,VALUES(,c_id,c_name,period,c_type,),GO,-,执行存储过程,pro_insert,EXEC,pro_insert,1006,c+,程序设计,GO,-,查看插入结果,SELECT*FROM course,2026/4/2 周四,12,在建立存储过程时，通过定义输出参数，可以从存储过程中返回一个或多个值。在执行存储过程时，可以将结果返回给返回参数。定义输出参数需要在参数定义的数据类型后使用关键字,OUTPUT,，或省写为,OUT,。,（,4,）使用带,OUTPUT,的返回参数,【,例题,10.7】,建立一个带参数的存储过程，用于求两个整数的和，其中和定义为,OUTPUT,参数，然后执行存储过程并输出和。,CREATE PROC,pro_sum,a,int,b,int,sum,int,output,AS,SET sum=,a+b,GO,-,执行存储过程,DECLARE s,int,EXEC,pro_sum,20,15,s output,PRINT s,2026/4/2 周四,13,10.1.5,存储过程的返回值,存储过程被执行以后可以返回一个整数类型的代码值，该值用来记录存储过程的执行情况。若返回值为,0,，表示执行成功；否则表示执行不成功。,1,）用一个变量来捕捉存储过程执行后返回值。,2,）使用,RETURN,语句为存储过程指定一个确定的返回值，以用来反映存储过程执行的结果情况。,捕捉存储过程返回值的方法,2026/4/2 周四,14,【,例题,10.9】,用变量来捕捉存储过程执行后返回值。建立一个存储过程，完成查询指定学生指定科目的成绩。然后执行该存储过程并捕捉执行是否成功。,CREATE PROC,pro_grade,s_id,char(10),c_id,char(5),AS,SELECT grade,FROM score,WHERE,student_id,=,s_id,AND,course_id,=,c_id,GO,DECLARE status,int,-,声明变量,用来存放执行结果的状态,EXEC status=,pro_grade,0801101,1001,SELECT status -,输出状态值,2026/4/2 周四,15,【,例题,10.10】,使用,RETURN,语句为存储过程指定一个确定的返回值。建立一个存储过程，完成查询指定学生指定科目的成绩，如果成绩大于,60,分，返回,1,；如果等于,60,分，返回,0,；小于,60,分，则返回,-1,。然后执行该存储过程并输出返回值。,CREATE PROC,pro_return,s_id,char(10),c_id,char(5),AS,DECLARE result,int,SELECT result=grade,FROM score,WHERE,student_id,=,s_id,AND,course_id,=,c_id,IF result60 RETURN 1,IF result=60 RETURN 0,IF result=0 AND,成绩,=100,PRINT,插入成功,ELSE,BEGIN,PRINT,成绩值超出范围,不允许插入,ROLLBACK TRANSACTION,END,-,插入记录，检验,insert,触发器的作用,INSERT,grade(student_id,course_id,grade,),VALUES(0801107,1001,-10),2026/4/2 周四,32,(3)UPDATE,触发器,修改触发器和插入触发器的工作过程基本上一致，修改一条记录等于插入了一条新的记录并且删除一条旧的记录。,2026/4/2 周四,33,【,例题,10.19】,假设成绩信息被成功录入到表中以后将允许修改，建立一个触发器，禁止其修改成绩。,CREATE TRIGGER,check_update,ON score,AFTER update,AS,IF,update(grade,),BEGIN,PRINT,修改无效,成绩被录入后,不允许修改,ROLLBACK TRANSACTION,END,-,修改成绩值，检查,update,触发器的作用,UPDATE score,set grade=80,WHERE,student_id,=0801101,AND,course_id,=1001,2026/4/2 周四,34,(4),DELETE,触发器,DELETE,触发器通常用于两种情况，第一种情况是为了防止那些确实需要删除但会引起数据一致性问题的记录的删除。,第二种情况是执行可删除主记录的子记录的级联删除操作。可以使用这样的触发器从主销售记录中删除所有的定单项。,2026/4/2 周四,35,【,例题,10.20】,在学生表中建立一个触发器，若删除学生记录，则把该学生在成绩表中的成绩记录一并删除。,CREATE TRIGGER,check_delete,ON student,AFTER delete,AS,DELETE score,WHERE,student_id,in,(SELECT,student_id,FROM deleted),-,删除学生记录，检查,delete,触发器的作用,DELETE student,WHERE,student_id,=0801103,-,查看成绩表,grade,中,0801103,号学生的成绩是否被删除,SELECT*,FROM grade,2026/4/2 周四,36,10.2.4 DDL,触发器,1,、建立,DDL,触发器,CREATE TRIGGER,trigger_name,ON ALL SERVER|DATABASE,WITH ENCRYPTION,FOR|AFTER,event_type|event_group,n,AS,sql_statement,.n,2026/4/2 周四,37,参数说明：,ALL SERVER,指明触发器的作用域为当前服务器。如果指定了此参数，则只要当前服务器中的任何位置出现,event_type,或,event_group,，就会激活触发器。,DATABASE,指明触发器的作用域为当前数据库。如果指定了此参数，则只要当前数据库中的任何位置出现,event_type,或,event_group,，就会激活触发器。,event_type,将导致激活,DDL,触发器的,T-SQL,语言的名称。,event_type,选项有：,CREATE_TABLE,、,CREATE_ DATABASE,、,ALTER_TABLE,等等。,event_group,预定义的,T-SQL,语言事件分组的名称。执行任何属于,event_group,的,T-SQL,语言事件，都将激活,DDL,触发器。,event_group,有：,DDL_SERVER_SECURITY_EVENTS,代表所有以服务器为目标的各类,DDL,语法语法事件，而,DDL_TABLE_VIEW_EVENTS,代表了针对数据表、视图表、索引与统计的,DDL,事件。,2026/4/2 周四,38,【,例题,10.21】,使用,DDL,触发器来防止数据库,score,中的表被任意修改或删除。,USE score,GO,CREATE TRIGGER safety,ON DATABASE,FOR DROP_TABLE,ALTER_TABLE,AS,BEGIN,PRINT ,禁止修改或删除表,ROLLBACK,END,-,修改表，检验,safety,触发器的功能,ALTER TABLE student,ADD,f_name,char(8),2026/4/2 周四,39,【,例题,10.22】,建立,DDL,触发器来防止在当前服务器下建立数据库。,CREATE TRIGGER,forbid_create,ON ALL SERVER,FOR CREATE_DATABASE,AS,BEGIN,PRINT,禁止建立数据库,ROLLBACK,END,-,建立数据库，检验,forbid_create,触发器的功能,CREATE DATABASE book,2026/4/2 周四,40,10.2.5,查看、修改和删除触发器,1.,查看触发器,（）使用对象资源管理器查看触发器信息,（）使用系统存储过程查看触发器,2026/4/2 周四,41,（,1,）,sp_help,：用于查看触发器的一般信息，如触发器的名称、属性、类型和创建时间。,格式：,sp_help,触发器名称,（,2,）,sp_helptext,：用于查看触发器的正文信息,格式：,sp_helptext,触发器名称,（,3,）,sp_depends,：用于查看指定触发器所引用的表或者指定的表涉及到的所有触发器。,sp_depends,触发器名称,sp_depends,表名,2026/4/2 周四,42,【,例题,10.23】,查看,check_delete,触发器的定义信息。,sp_helptext,check_delete,2026/4/2 周四,43,2.,修改触发器,（,1,）修改,DML,触发器,ALTER TRIGGER,trigger_name,ON,table|view,WITH ENCRYPTION,FOR|AFTER|INSTEAD OF,DELETE ,INSERT ,UPDATE,NOT FOR REPLICATION,AS,sql_statement,.n,2026/4/2 周四,44,（,2,）修改,DDL,触发器,ALTER TRIGGER,trigger_name,ON ALL SERVER|DATABASE,WITH ENCRYPTION,FOR|AFTER,event_type|event_group,n,AS,sql_statement,.n,2026/4/2 周四,45,3.,删除触发器,（）删除,DML,触发器,DROP TRIGGER trigger ,.n,【,例题,10.24】,删除例题,10.19,中建立在成绩表上的,check_update,触发器。,USE,scoure,GO,DROP TRIGGER,check_update,2026/4/2 周四,46,（）删除,DDL,触发器,DROP TRIGGER trigger ,.n,ON,ALL SERVER|DATABASE;,【,例题,10.25】,删除例题,10.21,中建立在,score,数据库中的,safety,触发器。,USE score,GO,DROP TRIGGER safety ON DATABASE,（,3,）删除触发器所在的表时，,SQL Server,将会自动删除与该表相关的触发器。,2026/4/2 周四,47,触发器是特殊的存储过程，存储过程需要程序调用，而触发器会自动执行；函数是根据输入产生输出，自定义函数的输入输出的关系由用户来定义。在什么时候用触发器？要求系统根据某些操作自动完成相关任务，比如，根据买掉的产品的输入数量自动扣除该产品的库存量。什么时候用存储过程？存储过程就是程序，它是经过语法检查和编译的,SQL,语句，所以运行特别快。,思考：触发器、存储过程和函数三者有何区别？,2026/4/2 周四,48,