第第7章章存储过程和触发器存储过程和触发器第第7章章SQLServer存储过程和触发器存储过程和触发器任课教师：郭黎明任课教师：郭黎明第第7章章存储过程和触发器存储过程和触发器7.2存储过程的运用存储过程的运用v运用实例运用实例1统计每一个学生的平均成果统计每一个学生的平均成果,要求显示学要求显示学生姓名和平均成果。生姓名和平均成果。v分析分析:关联查询和分组查询关联查询和分组查询v运用运用T-SQL语句实现：语句实现：SELECTsname,avg(grade)FROMsc,studentWHEREsc.sno=student.snoGROUPBYsname第第7章章存储过程和触发器存储过程和触发器7.1存储过程的运用存储过程的运用v运用实例运用实例1统计学生所选课程所得的总学分统计学生所选课程所得的总学分,要求显要求显示系科名示系科名,学号学号,姓名和总学分。姓名和总学分。v运用存储过程实现：运用存储过程实现：CREATEPROCEDUREAvg_of_GradeASSELECTsname,avg(grade)FROMsc,studentWHEREsc.sno=student.snoGROUPBYsname在查询分析器中运转过程：在查询分析器中运转过程：EXECAvg_of_Grade第第7章章存储过程和触发器存储过程和触发器7.1存储过程概述存储过程概述v存储过程的概念存储过程的概念v存储过程是存储过程是SQLServer效力器上一组预先定义效力器上一组预先定义并编译好的并编译好的Transact-SQL语句，它可以接受参语句，它可以接受参数，前往形状值和参数值。数，前往形状值和参数值。v存储过程运用存储过程运用v涉及效力器处置多，而与用户较少的程序涉及效力器处置多，而与用户较少的程序v存储过程的分类存储过程的分类v系统存储过程：系统存储过程：sp开头开头SP_databasev本地存储过程本地存储过程v暂时存储过程暂时存储过程v远程存储过程远程存储过程v扩展存储过程扩展存储过程第第7章章存储过程和触发器存储过程和触发器7.1存储过程概述存储过程概述v存储过程的优点存储过程的优点v模块化的程序设计模块化的程序设计v创建一个存储过程存放在数据库中后，就可创建一个存储过程存放在数据库中后，就可以被其他程序反复运用以被其他程序反复运用v快速执行当存储过程被编译并存储在高速缓快速执行当存储过程被编译并存储在高速缓冲区中时，它能以相当高的效率执行。冲区中时，它能以相当高的效率执行。v减少网络通讯量时常会有长达数百行的减少网络通讯量时常会有长达数百行的SQL语句。假设能把那些功能封装进存储过程，语句。假设能把那些功能封装进存储过程，就可以经过向就可以经过向SQLServer发送一行代码起到成发送一行代码起到成百行的语句的作用，从而节省大量的网络带宽百行的语句的作用，从而节省大量的网络带宽v平安性存储过程能作为一种平安性机制运用。平安性存储过程能作为一种平安性机制运用。当他授权答应一个用户或一组用户运用存储过当他授权答应一个用户或一组用户运用存储过程，他们将可以执行这个存储过程而不用有访程，他们将可以执行这个存储过程而不用有访问在存储过程中被访问的下层对象的答应。问在存储过程中被访问的下层对象的答应。第第7章章存储过程和触发器存储过程和触发器7.2存储过程的运用存储过程的运用v存储过程创建存储过程创建v存储过程的执行存储过程的执行v存储过程修正存储过程修正v存储过程删除存储过程删除第第7章章存储过程和触发器存储过程和触发器7.2存储过程的运用存储过程的运用v存存储过程程创建建v存存储过程的程的创建方法建方法v利用利用SQLServer企企业管理器管理器创建存建存储过程。程。v运用运用Transact-SQL语句中的句中的CREATE。v运用运用创建存建存储过程程导游游创建存建存储过程。程。v存存储过程的程的组成成v一切的一切的输入参数以及入参数以及传给调用者的用者的输出参数。出参数。v被被执行的行的针对数据数据库的操作的操作语句，包括句，包括调用其它存用其它存储过程的程的语句。句。v前往前往给调用者的形状用者的形状值，以指明，以指明调用是用是胜利利还是失是失败。第第7章章存储过程和触发器存储过程和触发器7.2存储过程的运用存储过程的运用v存储过程创建存储过程创建v运用运用Transact-SQL语句中的语句中的CREATE。CREATEPROCEDUREprocedure_name;numberparameterdata_typeVARYING=defaultOUTPUT,.nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFORREPLICATIONASsql_statement.n第第7章章存储过程和触发器存储过程和触发器7.2存储过程的运用存储过程的运用v存储过程创建存储过程创建vprocedure_name：用于指定要创建的存储过程的称号。：用于指定要创建的存储过程的称号。vnumber：该参数是可选的整数，它用来对同名的存储：该参数是可选的整数，它用来对同名的存储过程分组，以便用一条过程分组，以便用一条DROPPROCEDURE语句即语句即可将同组的过程一同除去。可将同组的过程一同除去。vparameter：过程中的参数。在：过程中的参数。在CREATEPROCEDURE语句中可以声明一个或多个参数。语句中可以声明一个或多个参数。vdata_type：用于指定参数的数据类型。：用于指定参数的数据类型。vVARYING：用于指定作为输出：用于指定作为输出OUTPUT参数支持的参数支持的结果集。结果集。vDefault：用于指定参数的默许值。：用于指定参数的默许值。vOUTPUT：阐明该参数是一个前往参数。：阐明该参数是一个前往参数。第第7章章存储过程和触发器存储过程和触发器7.2存储过程的运用存储过程的运用v存储过程创建存储过程创建v【实例【实例1无参数的存储过程】：统计学生所选课程无参数的存储过程】：统计学生所选课程所得的总学分所得的总学分,要求显示系科名要求显示系科名,学号学号,姓名和总学分。姓名和总学分。v分析分析:关联查询和分组查询关联查询和分组查询CREATEPROCEDURETotal_of_CreditASSELECTdname,student.sno,sname,sum(ccredit)FROMstudent,sc,course,deptWHEREstudent.sno=sc.snoANDcourseo=scoanddept.dno=student.dnoGROUPBYdname,student.sno,student.snameEXECTotal_of_CreditTotal_of_Credit第第7章章存储过程和触发器存储过程和触发器7.2存储过程的运用存储过程的运用v存储过程创建存储过程创建v【实例【实例2-带输入参数的存储过程】带输入参数的存储过程】建立一建立一存储过程，用户输入学生姓名，学号，假设存储过程，用户输入学生姓名，学号，假设student表中存在此项，那么删除后再插入，假表中存在此项，那么删除后再插入，假设不存在此学生，那么直接插入该记录。设不存在此学生，那么直接插入该记录。v阐明：阐明：exists(select_statement)函数是指函数是指select语语句能否能前往行，假设能前往，那么为真，否句能否能前往行，假设能前往，那么为真，否那么为假。那么为假。v分析分析:显然学号和姓名应该作为存储过程的传显然学号和姓名应该作为存储过程的传入参数入参数第第7章章存储过程和触发器存储过程和触发器7.2存储过程的运用存储过程的运用v【Ins_Del_S】代码】代码vCREATEPROCEDUREIns_Del_SvsnoASCHAR(6),vsnameASCHAR(10)vASvIFEXISTS(SELECT*FROMstudentvWHEREsno=sno)vBEGINvDELETEstudentWHEREsno=snovINSERTINTOstudent(sno,sname)VALUES(sno,sname)vENDvELSEvINSERTINTOstudent(sno,sname)VALUES(sno,sname)vGOexec ins_del_s 1244,df第第7章章存储过程和触发器存储过程和触发器7.2存储过程的运用存储过程的运用v存储过程创建存储过程创建v【实例【实例3带输出参数的存储过程】：根据用户键入带输出参数的存储过程】：根据用户键入的学生姓名，查询学生的年龄。的学生姓名，查询学生的年龄。CREATEPROCEDUREGetagenameCHAR(8)=NULL,ageINTOUTPUTASIFname=NULLBEGINPRINT查找哪个同窗的年龄查找哪个同窗的年龄RETURNENDSELECTage=YEAR(Getdate()-YEAR(sbirthday)FROMstudentWHEREsname=nameRETURNDECLAREageINTEXECGetage李一名李一名,ageOUTPUTSELECTage第第7章章存储过程和触发器存储过程和触发器7.2存储过程的运用存储过程的运用v存储过程创建存储过程创建v【实例【实例4经过经过Return参数前往形状】：创建一个存参数前往形状】：创建一个存储过程储过程,检查某学生的选课的门数检查某学生的选课的门数vRETURN语句前往的是整形值语句前往的是整形值CREATEPROCEDURESelCountsnoCHAR(8)=NULLASDECLAREcountintegerSELECTcount=count(*)FROMscWHEREsno=snoRETURNcountdeclarecountintegerexecutecount=selcount95001printcount第第7章章存储过程和触发器存储过程和触发器7.2存储过程的运用存储过程的运用v存储过程的执行存储过程的执行v直接执行存储过程可以运用直接执行存储过程可以运用EXECUTE命令来执行，命令来执行，其语法方式如下：其语法方式如下：EXECUTEreturn_status=procedure_name;number|procedure_name_varparameter=value|variableOUTPUT|DEFAULT,.nWITHRECOMPILE。第第7章章存储过程和触发器存储过程和触发器7.2存储过程的运用存储过程的运用v存储过程的修正存储过程的修正v运用运用Transact-SQL语句中的语句中的ALTER。ALTERPROCEDUREprocedure_name;numberparameterdata_typeVARYING=defaultOUTPUT,.nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFORREPLICATIONASsql_statement.n第第7章章存储过程和触发器存储过程和触发器7.2存储过程的运用存储过程的运用v存储过程的删除存储过程的删除v运用运用Transact-SQL语句中的语句中的DROP。DROPPROCEDURE第第7章章存储过程和触发器存储过程和触发器7.2存储过程的运用存储过程的运用v存储过程的错误处置存储过程的错误处置v可以运用可以运用ERROR捕捉错误代码捕捉错误代码,ERROR在执行每一在执行每一个个T-SQL语句之后都会得到一个值语句之后都会得到一个值,对于胜利的执行对于胜利的执行,ERROR的值为的值为0,假设出现错误假设出现错误ERROR的值大于的值大于0CREATEPROCEDUREINSERT_STUDENTsnochar(5),snamechar(10),ssexchar(2)asinsertintostudent(sno,sname,ssex)values(sno,sname,ssex)iferror=0print执行胜利执行胜利elseprint执行失败执行失败printerror第第7章章存储过程和触发器存储过程和触发器练习练习综合练习综合练习1.根据用户键入的学生学号和课程号，查询学生的成根据用户键入的学生学号和课程号，查询学生的成果。果。CREATEPROCGetgradesnoVARCHAR(10),cnoVARCHAR(10)ASSELECTstudent.sno,sname,cno,gradeFROMscWHEREsno=snoandcno=cno第第7章章存储过程和触发器存储过程和触发器练习练习二、存储过程综合练习二、存储过程综合练习2.建立一存储过程，运用户输入系别，查询该系学生建立一存储过程，运用户输入系别，查询该系学生选择各课程的情况。模糊查询列出系别、课程号选择各课程的情况。模糊查询列出系别、课程号以及对应的人数，并按系别排序。【以及对应的人数，并按系别排序。【Sel_Course】3.建立一存储过程，运用户输入课程名时，列出该课建立一存储过程，运用户输入课程名时，列出该课程名、对应的课程号、选择该课程的学生总人数、平程名、对应的课程号、选择该课程的学生总人数、平均分【均分【Sel_Stu】第第7章章存储过程和触发器存储过程和触发器参考答案参考答案二、存储过程综合练习二、存储过程综合练习【2】CREATEPROCEDURESel_CoursednoASCHAR(2)ASSELECTdept.dno,dname,sco,count(*)FROMsc,dept,studentWHEREsc.sno=student.snoANDstudent.dno=dept.dnoANDdept.dno=dnoGROUPBYdept.dno,dname,scoGO第第7章章存储过程和触发器存储过程和触发器参考答案参考答案二、存储过程综合练习二、存储过程综合练习【3】CREATEPROCEDURESel_StucnameASCHAR(20)ASSELECTsco,cname,count(*),avg(grade)FROMsc,courseWHEREsco=courseoANDcname=cnameGROUPBYsco,cnameGO第第7章章存储过程和触发器存储过程和触发器7.3触发器触发器v7.3.1触发器概述触发器概述v问题的引入问题的引入v触发器的概念触发器的概念v触发器的原理触发器的原理v触发器的分类触发器的分类v7.3.2触发器的创建触发器的创建,删除删除v运用例如运用例如第第7章章存储过程和触发器存储过程和触发器7.3.1触发器概述触发器概述v问题的引入问题的引入v在在学生管理数据库学生管理数据库中中,某学生的某学生的选课门数选课门数是一个经常访问的属性是一个经常访问的属性,因此在因此在student表中添表中添加一个属性加一个属性sselnum(smallintv在实践运用在实践运用v实例展现实例展现添加一个记录添加一个记录删除一个记录删除一个记录(“95003,002,56950010069041第第7章章存储过程和触发器存储过程和触发器7.3.1触发器概述触发器概述v问题的引入的引入v思索思索该问题涉及两个表之涉及两个表之间的关系，可否用参照的关系，可否用参照完好性来完好性来实现？v可否用嵌套的可否用嵌套的SQL语句来句来实现?vupdatestudentsetsselnum=(selectcount(*)fromscwheresc.sno=student.sno)v存在的存在的问题v问题的的处理：运用触理：运用触发器器triggerv触触发器的主要作用就是其可以器的主要作用就是其可以实现由主由主键和外和外键所不能保所不能保证的复的复杂的参照完好性和数据的一致的参照完好性和数据的一致性性第第7章章存储过程和触发器存储过程和触发器7.3.1触发器概述触发器概述v触触发器的概念器的概念v触触发器是一种特殊器是一种特殊类型的存型的存储过程。在程。在SQLServer里面也里面也就是就是对某一个表的一定的操作，触某一个表的一定的操作，触发某种条件，从而某种条件，从而执行行的一段程序。的一段程序。v触触发器触器触发缘由由对表表进展插入、更新或展插入、更新或删除操作除操作触触发事事件。件。v触触发结果：另一段程序的果：另一段程序的执行行v思索思索：在【：在【问题的引入】中，？是触的引入】中，？是触发缘由由v？触？触发结果果v触触发器主要是器主要是经过事件触事件触发而而执行的，而存行的，而存储过程是程是经过调用存用存储过程称号而程称号而执行的。行的。vINSERTINTOscVALUES(95001,006,90)vEXECGetgrade00001,02v触触发器可以用于器可以用于SQLServer约束、默束、默许值和和规那么的完那么的完好性好性检查。第第7章章存储过程和触发器存储过程和触发器7.3.1触发器概述触发器概述v触发器的原理触发器的原理v在对表进展【在对表进展【INSERT】【】【DELETE】【】【UPDATE】操作时，】操作时，SQLServer自动执行触发器定义的一段自动执行触发器定义的一段程序程序v触发器用到的公用暂时表：触发器用到的公用暂时表：Inserted和和Deletedv特点特点(内存，时间，构造，只读内存，时间，构造，只读v存储在内存中存储在内存中v触发事件执行以后，逻辑表即刻被删除触发事件执行以后，逻辑表即刻被删除v构造同触发事件所操作的表一致构造同触发事件所操作的表一致v只读只读操作操作Inserted表表Deleted表表Insert存放插入的记录存放插入的记录Delete存放删除的记录存放删除的记录Update存放要更新的新行存放要更新的新行存放更新前的行存放更新前的行第第7章章存储过程和触发器存储过程和触发器7.3.1触发器概述触发器概述v触触发器的原理器的原理vInserted表和表和Deleted表的了解：表的了解：v1假假设基于基于SC表的表的UPDATE操作定操作定义了触了触发器器v2再再执行行语句：句：vUPDATESCSETgrade=70WHEREsno=95001ANDcno=001v在在UPDATE语句句执行行过程中程中vDeleted表的内容：表的内容：vInserted表的内容：表的内容：SC表表第第7章章存储过程和触发器存储过程和触发器7.3.1触发器概述触发器概述v触发器的分类触发器的分类vAFTER类型触发器类型触发器:v只需执行某一操作只需执行某一操作INSERTUPDATEDELETE之后，触发器才被触发之后，触发器才被触发v只能在表上定义。只能在表上定义。vINSTEADOF触发器触发器:v并不执行其所定义的操作并不执行其所定义的操作INSERT、UPDATE、DELETE，而仅是执行触发器本身。，而仅是执行触发器本身。v既可在表上定义既可在表上定义INSTEADOF触发器，也可以在视触发器，也可以在视图上定义图上定义INSTEADOF触发器触发器.第第7章章存储过程和触发器存储过程和触发器7.3.2触发器的运用触发器的运用v触发器的创建触发器的创建v触发器创建的方法触发器创建的方法v用用EM创建创建v用右键单击该表，从弹出的快捷菜单中选择一用右键单击该表，从弹出的快捷菜单中选择一切义务子菜单下的管理触发器选项，那么会出切义务子菜单下的管理触发器选项，那么会出现触发器属性对话框现触发器属性对话框v用用SQL创建创建第第7章章存储过程和触发器存储过程和触发器7.3.2触发器的运用触发器的运用v触发器的创建触发器的创建v创建触发器的语法创建触发器的语法CREATETRIGGERtrigger_nameONtable|viewFOR|AFTER|INSTEADOFDELETE,INSERT,UPDATEAS.触发事件所操作触发事件所操作的表或视图的表或视图触发事件触发事件被触发的程序段被触发的程序段第第7章章存储过程和触发器存储过程和触发器7.3.2触发器的运用触发器的运用v触发器的创建触发器的创建v【实例【实例1】创建一个】创建一个Insert触发器，功能是当在触发器，功能是当在sc上插入一行数据时，自动使得上插入一行数据时，自动使得student表对应学号表对应学号上的上的sselnum字段的值加字段的值加1v【分析】【分析】Insert操作的表是操作的表是sc,而要求系统自动更新而要求系统自动更新的表是的表是student,因此关键是获得因此关键是获得sc表上新插入记录表上新插入记录的学号的学号,然后在然后在student表上修正该学号的表上修正该学号的sselnum的值的值v【思索】【思索】1如何获得如何获得sc表上新插入记录的学号表上新插入记录的学号?v2触发器定义在？一个表上触发器定义在？一个表上SELECT sno FROM Inserted第第7章章存储过程和触发器存储过程和触发器7.3.2触发器的运用触发器的运用v触发器的创建触发器的创建v【实例【实例1】创建一个】创建一个Insert触发器，功能是当在触发器，功能是当在sc上插入一行数据时，自动使得上插入一行数据时，自动使得student表对应表对应学号上的学号上的sselnum字段的值加字段的值加1CREATETRIGGERsc_insertONscFORINSERTASUPDATEstudentSETsselnum=sselnum+1FROMstudentWHEREsnoIN(SELECTsnoFROMinserted)留意：留意：Sselnum默默许初初值设置置为0，否那么，否那么NULL值不能不能参与算参与算术运算。运算。局限性：只能局限性：只能对单行的行的处置，能置，能够存存在一条在一条语句影响多句影响多条条记录的情况的情况第第7章章存储过程和触发器存储过程和触发器7.3.2触发器的运用触发器的运用v触触发器的器的创建建v【实例例1的运用】的运用】创建了触建了触发器器sc_insert以后以后v【测试】在】在sc表中插入新的表中插入新的记录(“95001,006,90),vINSERTINTOscVALUES(95001,006,90)v检测student表中表中95001的的sselnum能否自能否自动添加添加1v【解析】【解析】【Insert】触】触发器的器的执行行过程程v首先首先执行行insert语句，假句，假设语句正确，插入句正确，插入记录v执行被触行被触发的程序段：的程序段：update语句句v留意：触留意：触发器触器触发的的语句和触句和触发它的它的语句是同一事句是同一事务来来执行的行的v本人上机本人上机检测第第7章章存储过程和触发器存储过程和触发器7.3.2触发器的运用触发器的运用v触发器的创建触发器的创建v【实例【实例2】创建一个】创建一个Insert触发器，功能是当在触发器，功能是当在sc上插入数据时，自动更新上插入数据时，自动更新student表对应学号上的表对应学号上的sselnum字段，应思索成批修正数据的情况。字段，应思索成批修正数据的情况。v【举例】合并【举例】合并sc1表的数据到表的数据到sc表中表中(两个表的构造两个表的构造一样一样)vINSERTINTOscSELECT*FROMsc1v【第第7章章存储过程和触发器存储过程和触发器7.3.2触发器的运用触发器的运用【实实例例2】创创建建一一个个Insert触触发发器器，功功能能是是当当在在sc上上插插入入 数数 据据 时时 ， 自自 动动 更更 新新 student表表 对对 应应 学学 号号 上上 的的sselnum字段，应思索成批修正数据的情况。字段，应思索成批修正数据的情况。【分析】分成两种情况【分析】分成两种情况(1)单行记录插入单行记录插入,同同实例实例1(2)多多行行记记录录插插入入,从从inserted表表中中可可以以获获得得新新插插入入记记录录的的一一切切的的sno,以以及及在在每每个个sno上上插插入入的的记记录的个数，以此更新录的个数，以此更新Student表中的记录表中的记录(3)如如何何区区别别单单行行记记录录操操作作和和多多行行记记录录操操作作？第第7章章存储过程和触发器存储过程和触发器7.3.2触发器的运用触发器的运用v触发器的创建触发器的创建v【实例【实例2】创建一个】创建一个Insert触发器，功能是当在触发器，功能是当在sc上插入数据时，自动更新上插入数据时，自动更新student表对应学号表对应学号上的上的sselnum字段，应思索成批修正数据的情字段，应思索成批修正数据的情况。况。CREATETRIGGERsc_insertONscFORINSERTASIFROWCOUNT=1BEGINUPDATEstudentSETsselnum=sselnum+1FROMstudentWHEREstudent.snoIN(SELECTsnoFROMinserted)ENDELSEBEGINUPDATEstudentSETsselnum=sselnum+(SELECTCOUNT(cno)FROMinsertedWHEREstudent.sno=inserted.sno)WHEREstudent.snoIN(SELECTsnoFROMinserted)END第第7章章存储过程和触发器存储过程和触发器7.3.2触发器的运用触发器的运用v触发器的创建触发器的创建v【实例【实例2的运用】创建了触发器的运用】创建了触发器sc_insert以后以后v【测试】执行【测试】执行INSERTINTOscSELECT*FROMsc1v检测检测STUDENT表中表中Sselnum字段的值字段的值v【问题】检测正确【问题】检测正确?v【扩展思索】【扩展思索】v假设在假设在SC表的表的Insert操作上定义两个不同的触发器，系操作上定义两个不同的触发器，系统怎样处置？统怎样处置？第第7章章存储过程和触发器存储过程和触发器7.3.2触发器的运用触发器的运用v触发器的创建触发器的创建v【实例【实例3】建立一触发器，当删除】建立一触发器，当删除student表中表中某学生记录时，同时删除某学生记录时，同时删除sc表中一切该学生的表中一切该学生的选课记录。选课记录。CREATETRIGGERDel_s_scONstudentFORDELETEASDELETEscWHEREsnoIN(SELECTsnoFROMdeleted)该问题还可以用该问题还可以用其他方法处理。其他方法处理。第第7章章存储过程和触发器存储过程和触发器7.3.2触发器的运用触发器的运用v触发器的创建触发器的创建v【实例【实例4】创建一个】创建一个Update触发器，功能是当在触发器，功能是当在sc上修上修正一行数据时，显示修正的记录的修正前的值和修正正一行数据时，显示修正的记录的修正前的值和修正后的值后的值CREATETRIGGERsc_updateONscFORUPDATEASSELECT*,修正前修正前FROMdeletedSELECT*,修正后修正后FROMinserted第第7章章存储过程和触发器存储过程和触发器7.3.2触发器的运用触发器的运用v触发器的创建触发器的创建v在有些更新中，更新的内容并不是整个记录，而仅仅在有些更新中，更新的内容并不是整个记录，而仅仅是一列或几列，这时就要用到用于检查列改动的更新是一列或几列，这时就要用到用于检查列改动的更新型触发器。型触发器。IFUPDATE(column)AND|ORUPDATE(column)第第7章章存储过程和触发器存储过程和触发器7.3.2触发器的运用触发器的运用v触发器的创建触发器的创建v【实例【实例5】运用】运用IFUPDATE保管字，实现当修正保管字，实现当修正Student表中的表中的sno属性时，属性时，sc表中相应的表中相应的sno也要修正也要修正CREATETRIGGERs_updateONstudentFORUPDATEASIFrowcount1PRINT不允不允许同同时修正多行修正多行记录的学号的学号SNO)ELSEIFUPDATE(sno)UPDATEscsetsno=(selectsnofrominserted)WHEREsno=(selectsnofromdeleted)第第7章章存储过程和触发器存储过程和触发器7.3.2触发器的运用触发器的运用v触发器的创建触发器的创建INSTEADOF)vINSTEADOF触发器的主要优点是使不可被修正触发器的主要优点是使不可被修正的视图可以支持修正。其中典型的例子是分割视的视图可以支持修正。其中典型的例子是分割视图图partitionedview。分割视图通常是一个来。分割视图通常是一个来自多个表的结果集自多个表的结果集v【举例】全校学生人数多【举例】全校学生人数多v将学生表按系科分成假设干表，将学生表按系科分成假设干表，为了教务处一为了教务处一致管理数据，合并表到分割视图致管理数据，合并表到分割视图v例如例如：将：将student分成三个表分成三个表Student01,Student02,Student03,v表示表示01系科，系科，02系科，系科，03系科的学生系科的学生,创创建分割视图建分割视图student_view一致管理数据一致管理数据vcreateviewstudent_viewasselect*fromstudent01unionallselect*fromstudent02unionallselect*fromstudent03存在问题：存在问题：不能更新不能更新第第7章章存储过程和触发器存储过程和触发器7.3.2触发器的运用触发器的运用【分析】触发器创建在【分析】触发器创建在视图上视图上触发事件是触发事件是Insert可以经过暂时表可以经过暂时表Inserted获得插入的记录的系获得插入的记录的系科和其他属性科和其他属性在该系科对应表的上插入在该系科对应表的上插入inserted中的记录中的记录.【实例【实例5】如何运用】如何运用INSTEADOF触发器来支持对分割视图所援用的根本表的修正。触发器来支持对分割视图所援用的根本表的修正。第第7章章存储过程和触发器存储过程和触发器7.3.2触发器的运用触发器的运用CREATETRIGGERstudentviewtrgONstudent_viewINSTEADOFinsertASBEGINDEClAREdeptnoCHAR(3)SELECTdeptno=dnoFROMinsertedIFdeptno=01BEGININSERTINTOstudent01SELECT*FROMinsertedENDIFdeptno=02BEGININSERTINTOstudent02SELECT*FROMinsertedENDIFdeptno=03BEGININSERTINTOstudent03SELECT*FROMinsertedENDEND【实例【实例5】如何运用】如何运用INSTEADOF触发器来支持对分割视图所援用的根本表的修正。触发器来支持对分割视图所援用的根本表的修正。思索：在视图上的insert语句能否执行第第7章章存储过程和触发器存储过程和触发器7.3.2触发器的运用触发器的运用执行INSERT INTO student_View(sno，dno) VALUES(“95010,01)视图本身不能更新，但是数据发生了改动，为什么？【实例【实例5】运用】运用第第7章章存储过程和触发器存储过程和触发器7.3.2触发器的运用触发器的运用v触发器的修正触发器的修正v修正触发器的语法修正触发器的语法ALTERTRIGGERtrigger_nameONtable|viewWITHENCRYPTIONFOR|AFTER|INSTEADOFDELETE,INSERT,UPDATEWITHAPPENDNOTFORREPLICATIONAS第第7章章存储过程和触发器存储过程和触发器7.3.2触发器的运用触发器的运用v触发器的删除触发器的删除v删除触发器的语法删除触发器的语法DROPTRIGGERtrigger_name第第7章章存储过程和触发器存储过程和触发器练习练习根底练习题根底练习题1、触发器分为、触发器分为_、_两种。两种。2、激活触发器的操作有、激活触发器的操作有_、_、_。3、触发器定义在、触发器定义在SC表上，触发事件是表上，触发事件是UPDATE，那么在，那么在SC表表S上执行上执行UPDATE语句，会产生逻辑表语句，会产生逻辑表_、_。第第7章章存储过程和触发器存储过程和触发器练习练习二、触二、触发器器综合合练习1.建立一触建立一触发器，当向器，当向SC表插入表插入记录时，假，假设对应的的课程号的程号的选课人数小于等于人数小于等于60人，那么可插入，否人，那么可插入，否那么那么发出出出出错信息信息“该课程程选课人数已人数已满！请另另选其其他他课程。程。sc_ins_602.建立一触建立一触发器，当修正某学生的系器，当修正某学生的系别时或插入或插入记录时，判，判别该系学生人数能否小于等于系学生人数能否小于等于100，假，假设是，是，那么允那么允许修正或插入，否那么修正或插入，否那么发出出出出错信息信息“该系人系人数已数已满!Ins_Up_100第第7章章存储过程和触发器存储过程和触发器练习练习二、触发器综合练习二、触发器综合练习3.建立一触发器，当删除建立一触发器，当删除student表中某学生记录时，表中某学生记录时，同时删除同时删除sc表中一切该学生的选课记录。表中一切该学生的选课记录。Del_s_sc第第7章章存储过程和触发器存储过程和触发器参考答案参考答案三、触发器综合练习三、触发器综合练习【1】CREATETRIGGERsc_ins_60ONdbo.scFORINSERTASIF(SELECTCOUNT(*)FROMsc,insertedWHEREsco=insertedo)=5BEGINPRINT该课程选课人数已满！请另选其他课该课程选课人数已满！请另选其他课程程ROLLBACKEND第第7章章存储过程和触发器存储过程和触发器参考答案参考答案三、触发器综合练习三、触发器综合练习【2】CREATETRIGGERIns_Up_100ONdbo.studentFORINSERT,UPDATEASIF(SELECTCOUNT(*)FROMstudent,insertedWHEREstudent.dno=inserted.dno)=5BEGINPRINT系人数已满，不能插入或修正！系人数已满，不能插入或修正！ROLLBACKEND第第7章章存储过程和触发器存储过程和触发器参考答案参考答案三、触发器综合练习三、触发器综合练习【3】CREATETRIGGERDel_s_scONdbo.studentFORDELETEASDELETEscWHEREsnoIN(SELECTsnoFROMdeleted)留意留意：要设置：要设置student的关系中，设置的关系中，设置第第7章章存储过程和触发器存储过程和触发器7.4游标游标v问题的引入问题的引入v关系数据库管理系统本质是面向集合的，除非运关系数据库管理系统本质是面向集合的，除非运用用where子句来限制只需一条记录被选中子句来限制只需一条记录被选中v借助于游标来进展面向单条记录的数据处置借助于游标来进展面向单条记录的数据处置v游标允许运用程序对查询语句游标允许运用程序对查询语句select前往的行结果前往的行结果集中每一行进展一样或不同的操作，而不是一次集中每一行进展一样或不同的操作，而不是一次对整个结果集进展同一种操作对整个结果集进展同一种操作v它还提供对基于游标位置而对表中数据进展删除它还提供对基于游标位置而对表中数据进展删除或更新的才干或更新的才干v游标把作为面向集合的数据库管理系统和面向行游标把作为面向集合的数据库管理系统和面向行的程序设计两者联络起来的程序设计两者联络起来第第7章章存储过程和触发器存储过程和触发器7.4游标游标v7.4.1游标的根底游标的根底v游标的概念游标的概念:游标提供一种机制游标提供一种机制,使得运用程序每使得运用程序每次处置结果集中的一行或一部分次处置结果集中的一行或一部分v游标的功能游标的功能v定位在结果集的特定行定位在结果集的特定行v从结果集当前位置检索一行或多行从结果集当前位置检索一行或多行v修正结果集当前位置的行修正结果集当前位置的行v在存储过程或触发器中运用在存储过程或触发器中运用第第7章章存储过程和触发器存储过程和触发器7.4游标游标v7.4.1游标的根底游标的根底v游标的种类游标的种类v1Transact_SQL游标游标vTransact_SQL游标是由游标是由DECLARECURSOR语法定义、主要用在语法定义、主要用在Transact_SQL脚本、存储过程和触发器中。脚本、存储过程和触发器中。Transact_SQL游标主要用在效力器上。游标主要用在效力器上。v2API游标游标vAPI游标支持在游标支持在OLEDB，ODBC以及以及DB_library中运用游标函数，主要用在效力器中运用游标函数，主要用在效力器上。上。v3客户游标客户游标v客户游标主要是当在客户机上缓存结果集时客户游标主要是当在客户机上缓存结果集时才运用。才运用。第第7章章存储过程和触发器存储过程和触发器7.4游标游标v7.4.2游标的运用游标的运用v每一个游标必需有四个组成部分这四个每一个游标必需有四个组成部分这四个关键部分必需符合下面的顺序关键部分必需符合下面的顺序1.DECLARE游标游标2.OPEN游标游标3.从一个游标中从一个游标中FETCH信息信息4.CLOSE游标游标第第7章章存储过程和触发器存储过程和触发器7.4游标游标v7.4.2游标的运用游标的运用v每一个游标必需有四个组成部分这四个每一个游标必需有四个组成部分这四个关键部分必需符合下面的顺序关键部分必需符合下面的顺序1.DECLARE游标游标2.OPEN游标游标3.从一个游标中从一个游标中FETCH信息信息4.CLOSE游标和游标和Deallocate游标游标第第7章章存储过程和触发器存储过程和触发器7.4游标游标v7.4.2游游标的运用的运用v声明游声明游标v通常我通常我们运用运用DECLARE来声明一个游来声明一个游标声明声明v游游标名字名字v数据来源表和列数据来源表和列v选取条件取条件v属性属性仅读或可修正或可修正v其其语法格式如下：法格式如下：vDECLAREcursor_nameINSENSITIVESCROLLCURSORvFORselect_statementvFORREADONLY|UPDATEOFcolumn_name,.n第第7章章存储过程和触发器存储过程和触发器7.4游标游标v7.4.2游标的运用游标的运用v例如例如1定义一个查询定义一个查询student表的规范游表的规范游标标declare cur_st cursorfor select * from studentFor readonly-只读游标的定义For Update-更新游标的定义第第7章章存储过程和触发器存储过程和触发器7.4游标游标v7.4.2游游标的运用的运用v翻开游翻开游标v翻开游翻开游标游游标在声明以后，假在声明以后，假设要从游要从游标中中读取数据必需翻开游取数据必需翻开游标。vOPENGLOBALcursor_name|cursor_variable_namev各参数各参数阐明如下：明如下：vGLOBALv定定义游游标为一全局游一全局游标。vcursor_namev为声明的游声明的游标名字。名字。vcursor_variable_namev为游游标变量。量。v留意：在游留意：在游标被被胜利翻开之后，利翻开之后，CURSOR_ROWS全局全局变量将用来量将用来记录游游标内内数据行数。数据行数。第第7章章存储过程和触发器存储过程和触发器7.4游标游标v7.4.2游标的运用游标的运用v读游标读游标v当游标被胜利翻开以后，就可以从游标当游标被胜利翻开以后，就可以从游标中逐行地读取数据，以进展相关处置。中逐行地读取数据，以进展相关处置。第第7章章存储过程和触发器存储过程和触发器7.4游标游标v7.4.2游标的运用游标的运用v封锁游标封锁游标v运用运用CLOSE命令封锁游标命令封锁游标v在处置完游标中数据之后必需封锁游标来释在处置完游标中数据之后必需封锁游标来释放数据结果集和定位于数据记录上的锁。放数据结果集和定位于数据记录上的锁。vCLOSEGLOBALcursor_name|cursor_variable_namev释放游标释放游标v因此常运用因此常运用DEALLOCATE命令。经过该命命令。经过该命令可以删除掉游标与游标名或游标变量之间的令可以删除掉游标与游标名或游标变量之间的联络，并且释放游标占用的一切系统资源。其联络，并且释放游标占用的一切系统资源。其语法规那么为：语法规那么为：vDEALLOCATEGLOBALcursor_name|cursor_variable_name第第7章章存储过程和触发器存储过程和触发器7.4游标游标v7.4.2游标的运用游标的运用v例如例如2运用游标读取运用游标读取student表中的数据表中的数据declarest_cursorcursorforselectsno,sname,sbirthdayfromstudentopenst_cursorfetchnextfromst_cursorwhilefetch_status=0beginfetchnextfromst_cursorendclosest_cursordeallocatest_cursor第第7章章存储过程和触发器存储过程和触发器7.4游标游标v7.4.2游游标的运用的运用v游游标变量量v首先先声明一个游首先先声明一个游标。declarecur_stcursorvforvselect*fromstudentv运用运用SET语句将一游句将一游标赋值给游游标变量量vdeclarecur_varvsetcur_var=cur_st第第7章章存储过程和触发器存储过程和触发器7.4游标游标v7.4.2游标的运用游标的运用v例如例如3存储过程存储过程createprocedureopenStoutStcursorvaryingoutputassetoutSt=cursorforselectsno,snamefromstudentopenoutStgodeclarecurvarcursorexecopenStoutSt=curvaroutputdeclaresnochar(5),snamechar(10)fetchnextfromcurvarintosno,snamewhile(fetch_status-1)beginprintsnofetchnextfromcurvarintosno,snameendclosecurvardeallocatecurvargo第第7章章存储过程和触发器存储过程和触发器7.4游标游标v7.4.2游游标的运用的运用v游游标变量量v首先先声明一个游首先先声明一个游标。declarecur_stcursorvforvselect*fromstudentv运用运用SET语句将一游句将一游标赋值给游游标变量量vdeclarecur_varvsetcur_var=cur_st第第7章章存储过程和触发器存储过程和触发器7.4游标游标v7.4.2游标的运用游标的运用v假设在声明游标时运用了假设在声明游标时运用了FORUPDATE语句，那么在语句，那么在UPDATE或或DELETE命令命令中以中以WHERECURRENTOF关键字直关键字直接修正或删除当前游标中所存储的数据。接修正或删除当前游标中所存储的数据。v进展定位修正或删除游标中数据的语法进展定位修正或删除游标中数据的语法规那么为：规那么为：第第7章章存储过程和触发器存储过程和触发器7.4游标游标v7.4.2游标的运用游标的运用v例如例如4经过游标修正数据经过游标修正数据declaresnochar(5),snamechar(10)declarest_curcursorforselectsno,snamefromstudentforupdateofsno,snameopenst_curfetchnextfromst_curintosno,snamewhilefetch_status=0beginifsno=95001updatestudentsetsname=黎明黎明wherecurrentofst_curfetchnextfromst_curintosno,snameendclosest_curdeallocatest_cur