存储过程和触发器 讲稿.ppt

《存储过程和触发器 讲稿.ppt》由会员分享，可在线阅读，更多相关《存储过程和触发器 讲稿.ppt（48页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、存储过程和触发器 第一页，讲稿共四十八页哦10.1存储过程10.1.1 存储过程概述 存储过程（Stored Procedure）是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中。一般用来处理需要与数据库进行频繁交互的复杂的业务。因为存储过程具有以下优点：存储过程在服务器端运行，执行速度快。封装业务逻辑也可以使数据库操作人员与应用系统开发人员的分工更明确，支持模块化设计。2022-9-72第二页，讲稿共四十八页哦存储过程执行一次后，其执行计划就驻留在高速缓冲存储器，如果需要再次调用，只需要从高速缓冲存储器中调用，提高系统性能。存储过程可以作为安全机制。通过授予用户对存储过程的操作

2、权限来实现安全机制。使体现企业规则的运算程序放入数据库服务器中，以便集中控制。2022-9-73第三页，讲稿共四十八页哦10.1.2 存储过程的分类 1系统存储过程 系统存储过程存储在master数据库中，以sp_开头，可以在其他数据库中进行调用。如：sp_help就是取得指定对象的相关信息。2扩展存储过程 在SQL Server环境之外执行动态链接库称为扩展存储过程，以XP_开头,用来调用操作系统提供的功能。3用户自定义的存储过程 由用户创建并能完成某一特定功能的存储过程。包括Transact-SQL和CLR两种类型。（1）Transact-SQL存储过程是指保存的Transact-SQL语

3、句集合，可以接受和返回用户提供参数。也可以从数据库向客户端应用程序返回数据。（2）CLR在本书不做详细介绍。2022-9-74第四页，讲稿共四十八页哦10.1.3 建立存储过程 1.使用对象资源管理器建立存储过程 2.使用T-SQL命令建立存储过程 语法形式如下：CREATE PROCEDURE procedure_name parameter data_typeVARYING=defaultOUTPUT ,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR REPLICATIONASsql_statement .n 2022-9-75第

4、五页，讲稿共四十八页哦【例题10.1】创建一个不带参数的存储过程，名称为pro_stu，用于检索学生的姓名、性别和家庭住址。USE scoreGO-判断pro_stu存储过程是否存在，若存在，则删除If exists(select name from sysobjects where name=pro_stu and type=p)DROP PROCEDURE pro_stuGo-建立存储过程CREATE PROC pro_stuAs SELECT student_name,sex,addressFROM student2022-9-76第六页，讲稿共四十八页哦【例题10.2】创建一个带参数的

5、存储过程，名称为pro_class，在执行存储过程的时候输入班级编号，然后检索该班级的学生的详细信息。USE scoreGO-判断pro_class存储过程是否存在，若存在，则删除If exists(select name from sysobjects where name=pro_class and type=p)DROP PROCEDURE pro_classGo-建立存储过程CREATE PROC pro_class c_no char(8)As SELECT *FROM studentWHERE class_id=c_no2022-9-77第七页，讲稿共四十八页哦10.1.4 存储过

6、程的执行 语法形式如下：EXECUTE return_status=procedure_name|procedure_name_var parameter=value|variableOUTPUT|DEFAULT ,.n WITH RECOMPILE 2022-9-78第八页，讲稿共四十八页哦1.无参存储过程的执行 【例题10.3】执行在例题10.1中创建的存储过程pro_stu。EXEC pro_stu在执行带参存储过程的时候参数有以下几种传递方式：2.带参存储过程的执行（1）顺序法 2022-9-79第九页，讲稿共四十八页哦CREATE PROC pro_update s_id char(

7、10),c_id char(5),result int -参数ASUPDATE scoreSET grade=resultWHERE student_id=s_id AND course_id=c_id/*顺序法执行存储过程，把0801101号学生1001号课程的成绩修改为85分*/EXEC pro_update 0801101,1001,85【例题10.4】建立一个存储过程pro_update，通过指定学号和课程编号来把成绩表中的成绩修改为指定的某个值，然后执行该存储过程。2022-9-710第十页，讲稿共四十八页哦EXEC pro_update s_id=0801101,result=85

8、,c_id=1001说明：提示法中的参数顺序可以与建立存储过程时的参数顺序不一致，而顺序法必须保持一致。（2）提示法 【例题10.5】通过提示法来执行存储过程pro_update。2022-9-711第十一页，讲稿共四十八页哦 建立存储过程时，可以指定参数的默认值，默认值必须为常量或者为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

9、)=NULLAS INSERT courseVALUES(c_id,c_name,period,c_type )GO-执行存储过程pro_insertEXEC pro_insert 1006,c+程序设计GO-查看插入结果SELECT*FROM course2022-9-712第十二页，讲稿共四十八页哦 在建立存储过程时，通过定义输出参数，可以从存储过程中返回一个或多个值。在执行存储过程时，可以将结果返回给返回参数。定义输出参数需要在参数定义的数据类型后使用关键字OUTPUT，或省写为OUT。（4）使用带OUTPUT的返回参数 【例题10.7】建立一个带参数的存储过程，用于求两个整数的和，其中

10、和定义为OUTPUT参数，然后执行存储过程并输出和。CREATE PROC pro_sum a int,b int,sum int outputASSET sum=a+bGO-执行存储过程DECLARE s intEXEC pro_sum 20,15,s outputPRINT s2022-9-713第十三页，讲稿共四十八页哦10.1.5 存储过程的返回值 存储过程被执行以后可以返回一个整数类型的代码值，该值用来记录存储过程的执行情况。若返回值为0，表示执行成功；否则表示执行不成功。1）用一个变量来捕捉存储过程执行后返回值。2）使用RETURN语句为存储过程指定一个确定的返回值，以用来反映存储

11、过程执行的结果情况。捕捉存储过程返回值的方法 2022-9-714第十四页，讲稿共四十八页哦【例题10.9】用变量来捕捉存储过程执行后返回值。建立一个存储过程，完成查询指定学生指定科目的成绩。然后执行该存储过程并捕捉执行是否成功。CREATE PROC pro_grade s_id char(10),c_id char(5)ASSELECT gradeFROM scoreWHERE student_id=s_id AND course_id=c_idGODECLARE status int -声明变量,用来存放执行结果的状态EXEC status=pro_grade 0801101,1001S

12、ELECT status -输出状态值2022-9-715第十五页，讲稿共四十八页哦【例题10.10】使用RETURN语句为存储过程指定一个确定的返回值。建立一个存储过程，完成查询指定学生指定科目的成绩，如果成绩大于60分，返回1；如果等于60分，返回0；小于60分，则返回-1。然后执行该存储过程并输出返回值。CREATE PROC pro_return s_id char(10),c_id char(5)ASDECLARE result intSELECT result=gradeFROM scoreWHERE student_id=s_id AND course_id=c_idIF res

13、ult60 RETURN 1IF result=60 RETURN 0IF result=0 AND 成绩=100vPRINT 插入成功vELSEvBEGIN v PRINT 成绩值超出范围,不允许插入v ROLLBACK TRANSACTIONvENDv-插入记录，检验insert触发器的作用vINSERT grade(student_id,course_id,grade)vVALUES(0801107,1001,-10)2022-9-732第三十二页，讲稿共四十八页哦v(3)UPDATE触发器触发器 修改触发器和插入触发器的工作过程基本上一致，修改一条记录等于插入了一条新的记录并且删除一条

14、旧的记录。2022-9-733第三十三页，讲稿共四十八页哦v【例题例题10.19】假设成绩信息被成功录入到表中以后将允许修改，建立一个触发器，禁止其修改成绩。vCREATE TRIGGER check_update ON scorevAFTER updatevASvIF update(grade)vBEGINv PRINT 修改无效,成绩被录入后,不允许修改v ROLLBACK TRANSACTIONvENDv-修改成绩值，检查 update触发器的作用vUPDATE scorevset grade=80vWHERE student_id=0801101 v AND course_id=100

15、12022-9-734第三十四页，讲稿共四十八页哦v(4)DELETE触发器v DELETE触发器通常用于两种情况，第一种情况是为了防止那些确实需要删除但会引起数据一致性问题的记录的删除。v 第二种情况是执行可删除主记录的子记录的级联删除操作。可以使用这样的触发器从主销售记录中删除所有的定单项。2022-9-735第三十五页，讲稿共四十八页哦v【例题例题10.20】在学生表中建立一个触发器，若删除学生记录，则把该学生在成绩表中的成绩记录一并删除。vCREATE TRIGGER check_delete ON studentvAFTER deletevASvDELETE scorevWHERE

16、student_id in v (SELECT student_idv FROM deleted)v-删除学生记录，检查 delete触发器的作用vDELETE studentvWHERE student_id=0801103v-查看成绩表 grade中0801103号学生的成绩是否被删除vSELECT*vFROM grade2022-9-736第三十六页，讲稿共四十八页哦v10.2.4 DDL触发器触发器v1、建立DDL触发器CREATE TRIGGER trigger_name ON ALL SERVER|DATABASE WITH ENCRYPTION FOR|AFTERevent_ty

17、pe|event_group,nAS sql_statement.n 2022-9-737第三十七页，讲稿共四十八页哦v参数说明：vALL SERVERv指明触发器的作用域为当前服务器。如果指定了此参数，则只要当前服务器中的任何位置出现event_type或event_group，就会激活触发器。vDATABASEv指明触发器的作用域为当前数据库。如果指定了此参数，则只要当前数据库中的任何位置出现event_type或event_group，就会激活触发器。vevent_typev将导致激活DDL触发器的T-SQL语言的名称。event_type选项有：CREATE_TABLE、CREATE_

18、 DATABASE、ALTER_TABLE等等。vevent_groupv预定义的T-SQL语言事件分组的名称。执行任何属于event_group的T-SQL语言事件，都将激活DDL触发器。vevent_group有：DDL_SERVER_SECURITY_EVENTS代表所有以服务器为目标的各类DDL语法语法事件，而 DDL_TABLE_VIEW_EVENTS代表了针对数据表、视图表、索引与统计的DDL事件。2022-9-738第三十八页，讲稿共四十八页哦v【例题例题10.21】使用 DDL 触发器来防止数据库score中的表被任意修改或删除。vUSE scorevGOvCREATE TRI

19、GGER safetyvON DATABASE vFOR DROP_TABLE,ALTER_TABLEvASvBEGINvPRINT 禁止修改或删除表v ROLLBACK vENDv-修改表，检验safety触发器的功能vALTER TABLE studentvADD f_name char(8)2022-9-739第三十九页，讲稿共四十八页哦v【例题例题10.22】建立建立 DDL 触发器来防止在当前服务器下建立数据库。vCREATE TRIGGER forbid_createON ALL SERVERFOR CREATE_DATABASEASBEGIN PRINT 禁止建立数据库 ROLL

20、BACK END-建立数据库，检验forbid_create触发器的功能CREATE DATABASE book2022-9-740第四十页，讲稿共四十八页哦10.2.5查看、修改和删除触发器查看、修改和删除触发器v1.查看触发器v（）使用对象资源管理器查看触发器信息v（）使用系统存储过程查看触发器2022-9-741第四十一页，讲稿共四十八页哦v（1）sp_help：用于查看触发器的一般信息，如触发器的名称、属性、类型和创建时间。格式：sp_help 触发器名称触发器名称 v（2）sp_helptext：用于查看触发器的正文信息 格式：sp_helptext 触发器名称触发器名称 v（3）s

21、p_depends：用于查看指定触发器所引用的表或者指定的表涉及到的所有触发器。sp_depends 触发器名称触发器名称 sp_depends 表名表名 2022-9-742第四十二页，讲稿共四十八页哦【例题10.23】查看check_delete触发器的定义信息。sp_helptext check_delete 2022-9-743第四十三页，讲稿共四十八页哦v2.修改触发器修改触发器v（1）修改）修改DML触发器触发器ALTER TRIGGER trigger_name ON table|view WITH ENCRYPTION FOR|AFTER|INSTEAD OF DELETE ,

22、INSERT ,UPDATE NOT FOR REPLICATIONAS sql_statement.n 2022-9-744第四十四页，讲稿共四十八页哦v（2）修改DDL触发器ALTER TRIGGER trigger_name ON ALL SERVER|DATABASE WITH ENCRYPTION FOR|AFTERevent_type|event_group,nAS sql_statement.n 2022-9-745第四十五页，讲稿共四十八页哦v3.删除触发器删除触发器（）删除DML触发器 DROP TRIGGER trigger ,.n【例题10.24】删除例题10.19中建立

23、在成绩表上的check_update触发器。USE scoureGODROP TRIGGER check_update2022-9-746第四十六页，讲稿共四十八页哦v（）删除（）删除DDL触发器触发器 DROP TRIGGER trigger ,.n ON ALL SERVER|DATABASE;【例题10.25】删除例题10.21中建立在score数据库中的safety触发器。USE scoreGODROP TRIGGER safety ON DATABASEv（3）删除触发器所在的表时，SQL Server将会自动删除与该表相关的触发器。2022-9-747第四十七页，讲稿共四十八页哦v触发器是特殊的存储过程，存储过程需要程序调用，而触发器会自动执行；函数是根据输入产生输出，自定义函数的输入输出的关系由用户来定义。在什么时候用触发器？要求系统根据某些操作自动完成相关任务，比如，根据买掉的产品的输入数量自动扣除该产品的库存量。什么时候用存储过程？存储过程就是程序，它是经过语法检查和编译的SQL语句，所以运行特别快。思考：触发器、存储过程和函数三者有何区别？2022-9-748第四十八页，讲稿共四十八页哦