存储过程和触发器 .ppt

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

1、存储过程和触发器现在学习的是第1页，共48页10.1存储过程存储过程10.1.1存储过程概述存储过程概述存储过程（存储过程（StoredProcedure）是一组为了完成特定功能的）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。语句集，经编译后存储在数据库中。一般用来处理需要与数据库进行频繁交互的复杂的业务。一般用来处理需要与数据库进行频繁交互的复杂的业务。因为存储过程具有以下优点：因为存储过程具有以下优点：存储过程在服务器端运行，执行速度快。存储过程在服务器端运行，执行速度快。封装业务逻辑也可以使数据库操作人员与应用系统开发人员封装业务逻辑也可以使数据库操作人员与应用系统开发

2、人员的分工更明确，支持模块化设计。的分工更明确，支持模块化设计。2022/9/272现在学习的是第2页，共48页存储过程执行一次后，其执行计划就驻留在高速缓冲存储器，如存储过程执行一次后，其执行计划就驻留在高速缓冲存储器，如果需要再次调用，只需要从高速缓冲存储器中调用，提高系统性能。果需要再次调用，只需要从高速缓冲存储器中调用，提高系统性能。存储过程可以作为安全机制。通过授予用户对存储过程的操作存储过程可以作为安全机制。通过授予用户对存储过程的操作权限来实现安全机制。权限来实现安全机制。使体现企业规则的运算程序放入数据库服务器中，以便集中控制。2022/9/273现在学习的是第3页，共48页1

3、0.1.2存储过程的分类存储过程的分类1系统存储过程系统存储过程系统存储过程存储在系统存储过程存储在master数据库中，以数据库中，以sp_开头，可以在其他数据库中进行调开头，可以在其他数据库中进行调用。用。如：如：sp_help就是取得指定对象的相关信息。就是取得指定对象的相关信息。2扩展存储过程扩展存储过程在在SQLServer环境之外执行动态链接库称为扩展存储过程，以环境之外执行动态链接库称为扩展存储过程，以XP_开头开头,用来调用用来调用操作系统提供的功能。操作系统提供的功能。3用户自定义的存储过程用户自定义的存储过程由用户创建并能完成某一特定功能的存储过程。包括由用户创建并能完成某

4、一特定功能的存储过程。包括Transact-SQL和和CLR两种类型。两种类型。（1）Transact-SQL存储过程是指保存的存储过程是指保存的Transact-SQL语句集合，可以接受和返语句集合，可以接受和返回用户提供参数。也可以从数据库向客户端应用程序返回数据。回用户提供参数。也可以从数据库向客户端应用程序返回数据。（2）CLR在本书不做详细介绍。在本书不做详细介绍。2022/9/274现在学习的是第4页，共48页10.1.3建立存储过程建立存储过程1.使用对象资源管理器建立存储过程使用对象资源管理器建立存储过程2.使用使用T-SQL命令建立存储过程命令建立存储过程语法形式如下：CRE

5、ATEPROCEDUREprocedure_nameparameterdata_typeVARYING=defaultOUTPUT,.nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFORREPLICATIONASsql_statement.n2022/9/275现在学习的是第5页，共48页【例题例题10.1】创建一个不带参数的存储过程，名称为创建一个不带参数的存储过程，名称为pro_stu，用于检，用于检索学生的姓名、性别和家庭住址。索学生的姓名、性别和家庭住址。USEscoreGO-判断判断pro_stu存储过程是否存在，若存在，则删除存储过程是

6、否存在，若存在，则删除Ifexists(selectnamefromsysobjectswherename=pro_stuandtype=p)DROPPROCEDUREpro_stuGo-建立存储过程建立存储过程CREATEPROCpro_stuAsSELECTstudent_name,sex,addressFROMstudent2022/9/276现在学习的是第6页，共48页【例题例题10.2】创建一个带参数的存储过程，名称为创建一个带参数的存储过程，名称为pro_class，在执行存，在执行存储过程的时候输入班级编号，然后检索该班级的学生的详细信息。储过程的时候输入班级编号，然后检索该班级

7、的学生的详细信息。USEscoreGO-判断判断pro_class存储过程是否存在，若存在，则删除存储过程是否存在，若存在，则删除Ifexists(selectnamefromsysobjectswherename=pro_classandtype=p)DROPPROCEDUREpro_classGo-建立存储过程建立存储过程CREATEPROCpro_classc_nochar(8)AsSELECT*FROMstudentWHEREclass_id=c_no2022/9/277现在学习的是第7页，共48页10.1.4存储过程的执行存储过程的执行语法形式如下：语法形式如下：EXECUTEret

8、urn_status=procedure_name|procedure_name_varparameter=value|variableOUTPUT|DEFAULT,.nWITHRECOMPILE2022/9/278现在学习的是第8页，共48页1.无参存储过程的执行无参存储过程的执行【例题例题10.3】执行在例题执行在例题10.1中创建的存储过程中创建的存储过程pro_stu。EXECpro_stu在执行带参存储过程的时候参数有以下几种传递方式：在执行带参存储过程的时候参数有以下几种传递方式：2.带参存储过程的执行带参存储过程的执行（1）顺序法）顺序法2022/9/279现在学习的是第9页，共

9、48页CREATEPROCpro_updates_idchar(10),c_idchar(5),resultint-参数参数ASUPDATEscoreSETgrade=resultWHEREstudent_id=s_idANDcourse_id=c_id/*顺序法执行存储过程，把顺序法执行存储过程，把0801101号学生号学生1001号课程的成绩修改号课程的成绩修改为为85分分*/EXECpro_update0801101,1001,85【例题【例题10.4】建立一个存储过程】建立一个存储过程pro_update，通过指定学号和课程，通过指定学号和课程编号来把成绩表中的成绩修改为指定的某个值，

10、然后执行该存储过编号来把成绩表中的成绩修改为指定的某个值，然后执行该存储过程。程。2022/9/2710现在学习的是第10页，共48页EXECpro_updates_id=0801101,result=85,c_id=1001说明：提示法中的参数顺序可以与建立存储过程时的参数顺序不一致，而顺序法必须保持一致。（2）提示法）提示法【例题例题10.5】通过提示法来执行存储过程通过提示法来执行存储过程pro_update。2022/9/2711现在学习的是第11页，共48页建立存储过程时，可以指定参数的默认值，默认值必须为常量或者为NULL。（3）使用默认参数）使用默认参数【例题例题10.6】建立一

11、个存储过程建立一个存储过程pro_insert，用来完成向课程表中插入记，用来完成向课程表中插入记录的功能，然后执行该存储过程并检查执行结果。录的功能，然后执行该存储过程并检查执行结果。CREATEPROCpro_insertc_idchar(5),c_namechar(20),periodint=64,c_typechar(10)=NULLASINSERTcourseVALUES(c_id,c_name,period,c_type)GO-执行存储过程执行存储过程pro_insertEXECpro_insert1006,c+程序设计程序设计GO-查看插入结果查看插入结果SELECT*FROMc

12、ourse2022/9/2712现在学习的是第12页，共48页在建立存储过程时，通过定义输出参数，可以从存储过程中返回一个或多个值。在执行存储过程时，可以将结果返回给返回参数。定义输出参数需要在参数定义的数据类型后使用关键字OUTPUT，或省写为OUT。（4）使用带）使用带OUTPUT的返回参数的返回参数【例题例题10.7】建立一个带参数的存储过程，用于求两个整数的和，其建立一个带参数的存储过程，用于求两个整数的和，其中和定义为中和定义为OUTPUT参数，然后执行存储过程并输出和。参数，然后执行存储过程并输出和。CREATEPROCpro_sumaint,bint,sumintoutputAS

13、SETsum=a+bGO-执行存储过程执行存储过程DECLAREsintEXECpro_sum20,15,soutputPRINTs2022/9/2713现在学习的是第13页，共48页10.1.5存储过程的返回值存储过程的返回值存储过程被执行以后可以返回一个整数类型的代码值，该值用来记录存储过程的执行情况。若返回值为0，表示执行成功；否则表示执行不成功。1）用一个变量来捕捉存储过程执行后返回值。2）使用RETURN语句为存储过程指定一个确定的返回值，以用来反映存储过程执行的结果情况。捕捉存储过程返回值的方法捕捉存储过程返回值的方法2022/9/2714现在学习的是第14页，共48页【例题例题1

14、0.9】用变量来捕捉存储过程执行后返回值。建立一个存储用变量来捕捉存储过程执行后返回值。建立一个存储过程，完成查询指定学生指定科目的成绩。然后执行该存储过程过程，完成查询指定学生指定科目的成绩。然后执行该存储过程并捕捉执行是否成功。并捕捉执行是否成功。CREATEPROCpro_grades_idchar(10),c_idchar(5)ASSELECTgradeFROMscoreWHEREstudent_id=s_idANDcourse_id=c_idGODECLAREstatusint-声明变量,用来存放执行结果的状态EXECstatus=pro_grade0801101,1001SELEC

15、Tstatus-输出状态值2022/9/2715现在学习的是第15页，共48页【例题例题10.10】使用使用RETURN语句为存储过程指定一个确定的返回值。语句为存储过程指定一个确定的返回值。建立一个存储过程，完成查询指定学生指定科目的成绩，如果成绩大建立一个存储过程，完成查询指定学生指定科目的成绩，如果成绩大于于60分，返回分，返回1；如果等于；如果等于60分，返回分，返回0；小于；小于60分，则返回分，则返回-1。然后执。然后执行该存储过程并输出返回值。行该存储过程并输出返回值。CREATEPROCpro_returns_idchar(10),c_idchar(5)ASDECLAREres

16、ultintSELECTresult=gradeFROMscoreWHEREstudent_id=s_idANDcourse_id=c_idIFresult60RETURN1IFresult=60RETURN0IFresult=0AND成绩=100vPRINT插入成功vELSEvBEGINvPRINT成绩值超出范围,不允许插入vROLLBACKTRANSACTIONvENDv-插入记录，检验insert触发器的作用vINSERTgrade(student_id,course_id,grade)vVALUES(0801107,1001,-10)2022/9/2732现在学习的是第32页，共48页

17、v(3)UPDATE触发器触发器修改触发器和插入触发器的工作过程基本上一致，修改一条记录等于插入了一条新的记录并且删除一条旧的记录。2022/9/2733现在学习的是第33页，共48页v【例题例题10.19】假设成绩信息被成功录入到表中以后将允许修改，建立一个触发器，禁止其修改成绩。vCREATETRIGGERcheck_updateONscorevAFTERupdatevASvIFupdate(grade)vBEGINvPRINT修改无效,成绩被录入后,不允许修改vROLLBACKTRANSACTIONvENDv-修改成绩值，检查update触发器的作用vUPDATEscorevsetgra

18、de=80vWHEREstudent_id=0801101vANDcourse_id=10012022/9/2734现在学习的是第34页，共48页v(4)DELETE触发器vDELETE触发器通常用于两种情况，第一种情况是为了防止那些确实需要删除但会引起数据一致性问题的记录的删除。v第二种情况是执行可删除主记录的子记录的级联删除操作。可以使用这样的触发器从主销售记录中删除所有的定单项。2022/9/2735现在学习的是第35页，共48页v【例题例题10.20】在学生表中建立一个触发器，若删除学生记录，则把该学生在成绩表中的成绩记录一并删除。vCREATETRIGGERcheck_deleteO

19、NstudentvAFTERdeletevASvDELETEscorevWHEREstudent_idinv(SELECTstudent_idvFROMdeleted)v-删除学生记录，检查delete触发器的作用vDELETEstudentvWHEREstudent_id=0801103v-查看成绩表grade中0801103号学生的成绩是否被删除vSELECT*vFROMgrade2022/9/2736现在学习的是第36页，共48页v10.2.4DDL触发器触发器v1、建立DDL触发器CREATETRIGGERtrigger_nameONALLSERVER|DATABASEWITHENCR

20、YPTIONFOR|AFTERevent_type|event_group,nASsql_statement.n2022/9/2737现在学习的是第37页，共48页v参数说明：vALLSERVERv指明触发器的作用域为当前服务器。如果指定了此参数，则只要当前服务器中的任何位置出现event_type或event_group，就会激活触发器。vDATABASEv指明触发器的作用域为当前数据库。如果指定了此参数，则只要当前数据库中的任何位置出现event_type或event_group，就会激活触发器。vevent_typev将导致激活DDL触发器的T-SQL语言的名称。event_type选项

21、有：CREATE_TABLE、CREATE_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/2738现在学习的是第38页，共48页v【例题例题10.21】使用DDL触发器来防止数据库score中的表被任意修改或删除。vUSE

22、scorevGOvCREATETRIGGERsafetyvONDATABASEvFORDROP_TABLE,ALTER_TABLEvASvBEGINvPRINT禁止修改或删除表vROLLBACKvENDv-修改表，检验safety触发器的功能vALTERTABLEstudentvADDf_namechar(8)2022/9/2739现在学习的是第39页，共48页v【例题例题10.22】建立建立DDL触发器来防止在当前服务器下建立数据库。vCREATETRIGGERforbid_createONALLSERVERFORCREATE_DATABASEASBEGINPRINT禁止建立数据库ROLLB

23、ACKEND-建立数据库，检验forbid_create触发器的功能CREATEDATABASEbook2022/9/2740现在学习的是第40页，共48页10.2.5查看、修改和删除触发器查看、修改和删除触发器v1.查看触发器v（）使用对象资源管理器查看触发器信息v（）使用系统存储过程查看触发器2022/9/2741现在学习的是第41页，共48页v（1）sp_help：用于查看触发器的一般信息，如触发器的名称、属性、类型和创建时间。格式：sp_help触发器名称触发器名称v（2）sp_helptext：用于查看触发器的正文信息格式：sp_helptext触发器名称触发器名称v（3）sp_de

24、pends：用于查看指定触发器所引用的表或者指定的表涉及到的所有触发器。sp_depends触发器名称触发器名称sp_depends表名表名2022/9/2742现在学习的是第42页，共48页【例题例题10.23】查看查看check_delete触发器的定义信息。sp_helptextcheck_delete2022/9/2743现在学习的是第43页，共48页v2.修改触发器修改触发器v（1）修改）修改DML触发器触发器ALTERTRIGGERtrigger_nameONtable|viewWITHENCRYPTIONFOR|AFTER|INSTEADOFDELETE,INSERT,UPDAT

25、ENOTFORREPLICATIONASsql_statement.n2022/9/2744现在学习的是第44页，共48页v（2）修改DDL触发器ALTERTRIGGERtrigger_nameONALLSERVER|DATABASEWITHENCRYPTIONFOR|AFTERevent_type|event_group,nASsql_statement.n2022/9/2745现在学习的是第45页，共48页v3.删除触发器删除触发器（）删除DML触发器触发器DROPTRIGGERtrigger,.n【例题例题10.24】删除例题删除例题10.19中建立在成绩表上的中建立在成绩表上的chec

26、k_update触触发器。发器。USEscoureGODROPTRIGGERcheck_update2022/9/2746现在学习的是第46页，共48页v（）删除（）删除DDL触发器触发器DROPTRIGGERtrigger,.nONALLSERVER|DATABASE;【例题10.25】删除例题10.21中建立在score数据库中的safety触发器。USEscoreGODROPTRIGGERsafetyONDATABASEv（3）删除触发器所在的表时，SQLServer将会自动删除与该表相关的触发器。2022/9/2747现在学习的是第47页，共48页v触发器是特殊的存储过程，存储过程需要程序调用，而触发器会自动执行；函数是根据输入产生输出，自定义函数的输入输出的关系由用户来定义。在什么时候用触发器？要求系统根据某些操作自动完成相关任务，比如，根据买掉的产品的输入数量自动扣除该产品的库存量。什么时候用存储过程？存储过程就是程序，它是经过语法检查和编译的SQL语句，所以运行特别快。思考：触发器、存储过程和函数三者有何区别？2022/9/2748现在学习的是第48页，共48页