还剩4页未读,继续阅读
文本内容:
实验六存储过程与触发器
一、存储过程【创建存储过程】CREATEPROCEDURE[OWNER].[PROCEDURENAME]ASSQL块如UsepubsGoCreateprocedureauthor_informationAsselectau_lnameau_fnametitlepub_namefromauthorsajointitleauthortaona.au_id=ta.au_idjointitlestont.title_id=ta.title_idjoinpublisherspont.pub_id=p.pub_idGo【管理存储过程】可以使用sp_helptext命令查看创建存储过程的文本信息UsepubsGoSp_helptextauthor_informationGo可以用sp_help查看存储过程的一般信息UsepubsGoSp_helpauthor_informationGo可以使用系统存储过程sp_rename修改存储过程的名字UsepubsGoSp_renameauthor_informationauthors_informationGo也可以使用企业管理浏览存储过程的信息,具体方法是从树型结构上选中存储过程所在的数据库节点,展开该节点;选中数据库节点下的〖存储过程〗节点,则右边的列表列出了数据库中目前所有的存储过程;选中存储过程,右击,执行〖属性〗命令,则系统将弹出如图所示对话框可以在对话框中修改存储过程内容,并保存修改如果想知道某个表被存储过程引用的情况,可以使用sp_depends,Sp_dependsauthors如果想知道某个存储过程引用表的情况,则可以使用Sp_dependsprocedure_name【执行存储过程】对于存储过程的调用,应使用EXECUTE或EXEC关键字UsepubsGoExecauthors_informationGo【删除存储过程】DROPPROCEDURE{procedure}{……n}UsepubsGoDropprocedureauthors_informationGo也可以使用企业管理器来删除存储过程,方法是从树型结构上选中存储过程所在的数据库节点,展开该节点;选中数据库节点下的〖存储过程〗节点,则右边的列表列出了数据库中目前所有的存储过程;选中要删除的存储过程,右击,执行〖删除〗命令,则系统将弹出如图所示对话框单击〖全部除去〗按钮,将删除所选中的存储过程【修改存储过程】SQLserver允许在不改变存储过程使用许可,不改变名字的情况下,对存储过程进行定义的修改Alterproc[edure]procedure_nameASsql_statement
二、触发器触发器(triegger)是一种特殊的存储过程,它与表格紧密相连,可以看作是表格定义的一部分当使用UPDATE、INSERT或DELETE等语句对表进行修改操作时,DBA常扩触发器来实现自动触发的处理方法当用户修改指定表或视图中的数据时,触发器将会自动执行触发器基于一个表创建,但是可能针对多个表进行操作所以触发器常被用来实现复杂的商业规则例如在pubs数据库晨,存放着出版商publishers的信息,出版物的信息titles、出版特与作者关联的信息titleauthor以及作者作息authors现在,有一条出版商的信息被删除了,则所以由该出版商的出版物都应该将pub_id修改为NULL,或者删除有关的出版物信息同样titleauthor表中的信息也应该相应地得到修改这样关联到三张表的一致性维护问题,可以使用触发器来实现在publishers表上设置一个DELETE触发器,当删除一条publishers信息时,触发器自动执行,对titles表和titleauthor表进行修改在SQLserver中一张表可以有多个触发器用户可以针对INSERT、UPDATE或DELETE语句分别设置触发器,也可以针对一张表上的特定操作设置多个触发器解发器可以容纳非常复杂的Transact-SQL语句但是,不管触发器所进行的操作有多复杂,触发器都只作为一个独立的单元被执行,被看作是一个事务如果在执行触发器的过程中发生了错误,则整个事务将会自动回退触发器在服务器将特定的操作(UPDATE、INSERT、DELETE)执行结束后才执行如果在执行特定数据库操作的过程中,发生了系统错误,则触发器不会被触发这种触发器类型是默认的类型,好AFTER类型在SQLserver2000中引进了一种新的触发器类型INSTEADOF类型这种类型的触发器取代了触发该触发器执行的SQL代码换句话说,它将覆盖该代码引起的变化通常这种类型的触发器用在需要维护数据一致性的地方触发器主要提供下列功能级联修改数据库中的所有相关表撤消或回退违反引用完整性的操作,防止非法修改数据执行比核查约束CHECK更复杂的约束操作查找在数据修改前后的表状态之间的差别,并根据差别分别采取相应的措施在一张表的同一类型的操作(UPDATE、INSERT、DELETE)上设置多个触发器,从而可以针对同样的修改语句执行不同的多种操作〖注意〗只有表的拥有者才可以在表上创建或删除触发器,这样权限不许转授尽管可以在触发器中引用视图或临时表,但不能在视图或临时表或系统表上创建触发器使用UPDATE语句可以一次对多个数据进行修改,但不管修改了多少数据,触发器都中触发一次在执行修改语句过程中,触发器的执行是修改语句事务的一部分所以,如果触发器执行不成功,则整个修改事务将会回退当使用约束、规则、默认值就可以实现预定的数据完整性时,应优先考虑使用这三种措施TRUNCATETABLE虽然在功能上与DELETE操作类似,但是TRUNCATETABLE不会触发DELETE触发器运行【创建触发器】CREATETRIGGERtrigger_nameONtable|viewASSQL块UseNorthwindIfexistsselectnamefromsysobjectsWherename=’tr_procedure_update’ANDtype=’TR’Droptriggertr_product_updateGoCreatetriggertr_product_updateONproductsForUPDATEDeclare@msgvarchar100Select@msg=str@@rowcount+”employeesupdatedbythisstatement”Print@msgReturnGo这个触发器在用户针对products表执行UPDATE执行,返回共修改了多少行数据其中@msg是一个变量,数据类型为varchar100@@rowcount是一个系统存储过程它返回当前被修改的行数创建触发器的语句CREATETRIGGER必须写在批处理的第一行,否则系统将会返回错误信息在CREATETRIGGER语句中,不能使用SELECT语句返回针对表格查询的数据,因为触发器不接收用户应用程序传递的参数,从而也无法向用户应用程序返回查询表格数据所得到的结果〖注意〗由于系统表怕存储数据的特殊性和重要性,所以建议用户不要自己在系统表上建立触发器【管理触发器】触发器是特殊的存储过程,所以适用于存储过程的管理方式,都适用于触发器所以用户完全可以使用sp_helptext,sp_help,sp_depens等系统存储过程,以及使用企业管理器来浏览触发器的有关信息,也可以使用sp_helptrigger来浏览指定表格上,指定类型的触发器的信息,语法是Sp_helptriggertable_name[type]如usenorthwindGoSp_helptriggerproductsdelete〖提示〗如果不设置type的值,则返回定义在该表上的所有触发器的信息【删除触发器】DROPTRIGGER{trigger}{……n}当用户删除某个表格时,所有建立在该表上的触发器都将被删除步骤为
①选中要创建触发器的〖表〗所在的数据库,展开该节点;
②展开该数据库节点下的〖表〗节点;
③选中指定的表,右击,从快捷菜单中展开〖全部任务〗子菜单,执行〖管理触发器〗命令,将弹出如图所示对话框;
④从〖名称〗下拉菜单里选择要删除的触发器;
⑤单击〖删除〗按钮,删除该触发器;
⑥单击〖确定〗按钮完成操作【修改触发器】Altertriggertrigger_nameONtableTransact-SQL编程全局变量以两个@@为标记,如@@CONNECTION局部变量以一个@为标记,如@A定义局部变量DECLARE@local_varible1char4@local_varible2char8显示局部变量SELECT@local_varible1,SELECT@local_varible2给局部变量赋值SET@local_varible=expressionTransact-SQL语句中以BEGIN和END为程序块的关键字无条件退出语句RETURN实验范例
1、用存储过程查询缺成绩的学生学号和课程号createprocedurepro1asselectsnamecnofromstudentscwherestudent.sno=sc.snoandgradeisnull
2、用存储过程查询指定课程选课的学生人数最高成绩以及最低成绩和平均成绩createprocedurepro2AsSelectcountcnoavggradeFromscGroupbysno
3、用存储过程查询指定课程选课的学生人数、最高成绩、最低成绩和平均成绩createprocedurepro3@cnochar4AsSelectcountsnomaxgrademingradeavggradeFromscWherecno=@cnoGroupbycnoExecpro3‘2’
4、用存储过程求某系学生选修的课程号及成绩createprocedurepro4@sdeptchar15@snochar9AsSelectcnogradeFromstudentscWherestudent.sno=sc.snoandsdept=@sdeptandstudent.sno=@snomailto:student.sno=@sno
5、用存储过程查询某门课程成绩大于80分的学生姓名createprocedurepro5@cnochar4AsSelectsnameFromstudentscWherestudent.sno=sc.snoandcno=@cnoandgrade’80’
6、创建sc1表,结构与sc一样,然后创建触发器,当SC表添加数据时,SC1表也同时添加createtablesc1snochar9Cnochar4GradeintPrimarykeysnocnoForeignkeysnoreferencesstudentsnoForeignkeycnoreferencescoursecnoIfexistsselectnamefromsysobjectswherename=’ord’andtype=’TR’DroptriggerordGoCreatetriggerordOnscForinsertIfexistsselect*frominsertedBeginInsertintosc1select*frominsertedEndGoPAGE4。