数据库实验九-存储过程和触发器班.docx

实验09存储过程和触发器K仪器设备每位学生一台PC机(标准配制，IG内存)，装有SQLSerVer2005或者其他DBMSK目的要求1 .了解几个常用的系统存储过程。2 .掌握如何使用T-SQL语句创立、执行和重编译存储过程。3 .了解如何使用企业管理器管理存储过程。4 .掌握如何使用T-SQL语句创立触发器。5 .了解如何使用企业管理器创立触发器。6 .掌握AFTER触发器的用法，并了解INSTEADOF触发器的用法。K实验内容1 .创立存储过程。(D创立一个存储过程，查看“2”号课程的选修情况，包括选修该课程的学号、姓名和成绩。usexskcgocreateproceduressc3asselect学生.学号,姓名,年级学期成绩学号LWhere学期成绩.课程编号="002,from学生join学期成绩on学生.学号goJj消息命令已成功完成。(2)执行存储过程(1)上面所建立的存储过程只能对“2”号课程的选修情况进行查看，要想对所有课程进行随机查看，需要进行参数的传递。usexsJccgo createproceduressc_4Qcnumberchar(5)asselect学生.学号,姓名,年级from笑生3oxn学期成绩on学生.学号=学期成绩.学号LWhere学期成绩.课程编号=cnunbergoI消息口令已成功完成。(2)按位置传递参数executessc4'J003,结果.J消息学号姓名年级123100110071008王晓明NULL刘晨null王敏NULL(3)通过参数名传递参数11executessc4-W*WS*A1.CnUInber='J003'口结果L消息三号姓名年级11001J王晓明NULL21007刘晨NULL31008王NULL(1)执行存储过程ssc4时;如果没有给出参数，系统会报错。如果希望不给参数时，能查询所有课程的进修情况，那么可以使用默认参数值来实现。usexskcg。createproceduressc_5Qcnuniberchar(5)=null3ifQcnuniberisnullbeginSeles学生.学号,姓名,年级from学生joxn学期成绩on学生.学号=学期成绩.学号endelsebeginSeIeS学生.学号,姓名,年级from笔生join学期成绩on学生.学号=学期成绩.学号-where学期成绩.课程编号=0cnumberendgo4.使用输出参数。(1)创立一个存储过程SSC6,获得选修某门课程的总人数。USeXSkcgo createproceduressc_6cnunberchar(5)Accountintoutputas selectAccount=count(*)from笑期成绩1.where学期成绩.课程编号="numbergo(2)执行存储过程ssc6odeclareccountintexecutessc6,J002*zccountoutputselect，结果是:,AccountIH结果.J消息优列名)一优列名)!绪藁豆"I25.使用返PI值。(1)创立一个返回执行状态码的存储过程ssc_7,它接受课程号为输入参数，如果执行成功，返PlO;如果没有给出课程号，返回错误码1；如果给出的课程号不存在，返回错误码2；如果出现其他错误，返回错误码3。usexskcgo2 createproceduressc_7cnumberchar(5)=nullasifcnumberisnullbeginPrinC，错误：你必须输入课程号。，return(1)endelsebeginiffselectCOSCL)from学期成绩where课程编号=2C二LmI:sr.=0beginPrim，错误：你给出的课程号不存在。，return(2)卜endFendselectcnurcberas课程编号from学期成绩where课程编号=Qcnumber3 if0error<>O-beginreturn(3)卜endelse1.return(0)gu(2)执行存储过程ssc_7opeclareQresultintIexecresult三ss71.select，其结果是：,resultt川一口结果3消息阮列名)田列名)1三SS:516.修改存储过程-使用对象资源管理器修改存储过程。口可编程性Q口存储过程田 国田 田田王LJ函数sLj系统存储过程新建存储过程(N).修改(Y)执行存储过程(E).编写存储过程脚本7.查看存储过程。execsp_helpVlf一结果上消息NameOwnerObjectJypessc_3dbostoredprocedureSSC_4dbostoredprocedureSSC_5dbostoredproceduressc_6dbostoredprocedureSSCJdbostoredprocedure8 .删除存储过程。HrOPproceduressc_3消息命令已成功完成。9 .创立触发器。(1)在表student中建立删除触发器，实现表student和表SC中数据的级联删除。psexskcgocreateCriggerSdeIeCeon学生afterdeleteasdeletefrom学期成绩-where学号in(select学号fromdeleted)在对象资源管理器中，向表COUrSe中插入一列StatUS(Char(D),并且默认值为“0”。在表SC上建立一个InSert触发器，当向表SC中插入一行时，检查课程表中的课程是否正在准备中(查看对应课程在COUrSe中的状态是否为1),如果是在准备中，那么不能进行选修。statusnchar(l)列属性)1（常双）（名称）status长度1默认值或绑定CO'）usexskcgocreatetriggerscinserto11学期成绩afterinsertasif(selectscacusfromi果不呈注册，insertedWhere课程注册.课程编号=Inserced.课程编号）=,1beginPrint，该课程正在准备中，Print，该次选课操作失败，rollbacktransaction-end（3）运行以上创立触发器的代码，然后再向表course中插入以下记录。useXSkCinsertinto课程注册（课程编号,课程名称,或笈忌短）VaIUeS（，J009，J体系结构，31，）二消息 L课程编号）use XSkcinsert into学期成绩(学号, values (,1013 - J009')J消息该课程正在准备中该次选课操作失败消息3609,级别16,状态力第2行事务在触发器中结束。批处理已中止。<1行受影响）-,JIJ*-»WkkWJOO9体系结构NULLNULLNULL1（4）向SC表参加“9”号课程的选修记录，查看执行情况。将“9”号课程的状态必为“0"。向表SC参加“9”号课程的选修记录，查看执行情况。EJusexskcinsertinto学期成绩(学号,课程编号)Lvalues(1013',J009,)LEupdate课程注册Isetstatus三,0,1.where谟程编号='J009'J消息<1行受影响)(6)表SC的cno列设置了外键约束，试用触发器实现这一功能。useXSkCg。createtriggerSSinSerton学期成绩afterInsertasif(selectcount(*)from课程注册，insertedwhere课程注册.课程编号=inserced.课程编号)=CbeginPrinC，该课程不存在，该次选课失败，rollbacktransactionend(7)在上例中，只针对插入单行数据，不包括对多行的判断，如果要利用触发器对插入的数据进行逐行检查，并将那些课程号在表C。UrSe中不存在的行删掉，请修改以上触发器。usexskcgoDcreatetriggerSXinSerton学期成绩afterinsertasif(selectcount(*)from课程注册，insertedwhere课程注册.课程编号=inserced.课程编号)<>©©roWCOUnCbegindelete学期成绩from学期成绩,insertedwhere学期成绩.课程编*in3产ced.课程编号andinserced.课程编号notir.(select课程编号from课程注册)-end10 .使触发器无效或重新有效。(1)使表SC上的Scinsert触发器无效。3LuseXSkcaltertable学期成绩disabletriggerscinserd(2)使表SC上的Scinsert触发器重新有效。3LuseXSkcaltertable学期成绩enabletriggerscinserd11 .删除Scinsert触发器。3usexskc1.droPtriggerscinsertK问题解答1.创立存储过程之前应考虑什么？创立存储过程之前应考虑以下4点: CREATEPROCEDURE语句不能与别的SQL语句位于同一个批中； 创立存储过程的权限默认属于数据库拥有者，该权限可以授予他人； 存储过程是数据库对象，它的命名应符合标识符的命名标准； 只能在当前数据库中创立存储过程。2.什么是INSERTED表和DELETED表？SQLSerVer为每个触发器都创立了两个专用表：INSERTED表和DELETED表。这是两个逻辑表，由系统来维护，用户不能对它们进行修改。它们存放在内存而不是数据库中。这两个表的结构总是与被该触发器作用的表的结构相同。触发器执行完成后，与该触发器相关的这两个表也被删除。DELETED表存放由于执行DELETE或UPDATE语句而要从表中删除的所有行。在执行DELETE或UPDATE操作时，被删除的行从激活触发器的表中被移动到DELETED表，这两个表不会有共同的行。INSERTED表存放由于执行INSERT或UPDATE语句而要向表中插入的所有行。在执行INSERT或UPDATE操作时，新的行同时添加到激活触发器的表和INSERTED表中，INSERTED表的内容是激活触发器的表中新行的备份。K思考、练习题1.使用对象资源管理器创立存储过程ssc.3o(Jxskc图6数据库关系图囹d表田口视图田同义词S口可编程也(+)口W¾fffllS(R国Ca刈筛选器(L) CJ CJ S CJ C2启动 PowerShe报表(P)刷新(F)jCREATEPROCEDUREssc_3Addtheparametersforthescoredprocedure-<Paraml,SySname,pl><Dacatype_For_Paraml,-<Param2rSySnaIne,p2><Datatype_For_Param21AS3BEGIN-SETNOCOUNTONaddedtopreventextraresult-interferingwichSELECTstatements.SETNOCOUNTON;Insertstatementsforprocedurehere3 SELECT学生.学号,姓名,年级FROM学生JOIN学期成绩on学生.学号=学期成绩WHERE学期成绩.课程编号=3002，1.ENDGC4 .使用对象资源管理器查看存储过程ssc_3的定义。LJ可编程性Ba存储过程dbo.sLJ系统存储过程S国dbdss存储B国dbo.ssl修改(Y)国dbo.ss执行存储;ALTERPROCEDUREdbo.ssc_3- Addtheparametersforthestoredprocedure<ParamlrSySname,pl><Datacype_For_Paraml,- -<Param2rSySnane,p2><Datatype_For_Param2rASBEGIN- -SETNOCOUNTONaddedtopreventextraresult一一interferingwithSELECTstatements.SETNOCOUNTON;- InsertstatementsforprocedurehereSELECT学生.学号,姓名,年级FRoM学生JOIN学期成绩on学生.学号=学期成绩WHERE学期成绩.学号=J002END5 .使用对象资源管理器删除存储过程ssc_3o6 .在创立触发器之前要考虑哪些问题？CREATETRlGGER必须是批处理中的第一条语句，并且只能应用到一个表中。 触发器只能在当前的数据库中创立，不过触发器可以引用当前数据库的外部对象。 如果指定触发器所有者名称以限定触发器，请以相同的方式限定表名。 在同一条CREATETRIGGER语句中，可以为多种用户操作（如INSERT和UPDATE）定义相同的触发器操作。 如果一个表的外键在DELETE/UPDATE操作上定义了级联，那么不能在该表上定义INSTEADOFDELETE/UPDATE触发器。 在触发器内可以指定任意的SET语句。所选择的SET选项在触发器执行期间有效，并在触发器执行完后恢复到以前的设置。 与使用存储过程一样，当触发器激发时，将向调用应用程序返回结果。假设要防止由于触发器激发而向应用程序返回结果，请不要包含返回结果的SELECT语句，也不要包含在触发器中进行变量赋值的语句。包含向用户返回结果的SELECT语句或进行变量赋值的语句的触发器需要特殊处理；这些返回的结果必须写入允许修改触发器表的每个应用程序中。如果必须在触发器中进行变量赋值，那么应该在触发器的开头使用SETN0C0UNT语句以防止返回任何结果集。 DELETE触发器不能捕获TRUNCATETABLE语句。尽管TRUNCATETABLE语句实际上是没有WHERE子句的DELETE（它删除所有行），但它是无日志记录的，因而不能执行触发器。因为TRUNCATETABLE语句的权限默认授予表所有者且不可转让，所以只有表所有者才褥要考虑无意中用TRUNCATETABLE语句躲避DELETE触发器的问题。无论有日志记录还是无日志记录，WRITETEXT语句都不激活触发器。触发器中不允许以下Transact-SQL语句：ALTERDATABASECREATEDATABASEDISKINITDISKRESIZEDROPDATABASELOADDATABASELOADLOGRECONFIGURERESTOREDATABASERESTORELOG5.通过ALTERTRIGGER语句可以修改触发器吗？使用ALTERTRIGGER语句可以更改原来由CREATETRIGGER语句创立的触发器定义。