实验六存储过程和触发器

实用标准文档实用标准文档文案大全文案大全实验六存储过程和触发器142208100149陈聪1、目的与要求(1)掌握存储过程的使用方法。(2)掌握触发器的使用方法。2、实验准备了解存储过程的使用方法。了解触发器的使用过程。了解inserted逻辑表和deleted逻辑表的使用。了解如何编写CRL存储过程与触发器。3、实验内容。(1)存储过程。①创建存储过程，使用Employees表中的员工人数来初始化一个局部变量，并调用这个存储过程。SQLQueryl.sql-lo^.dministrator(52))*useYGGLgocreateproceduretest^nu_l13EFtlintoutputbegindeclare@nujMBER2intsetgnu_MBER2=(selectCOtlNT(*)fromEmployees)set@nu_MBERl={fi)nu_MBER2-end注：此段T-SQL命令，书上变量名书写错误，漏掉了下划线。 (@nu_MBER1和@nuMBER1区别)执行该存储过程，并查看结果。EQLQueryJ^ql-Ioadministrator(531)*□declare[numintEXECTEST@numoutputLselect而num

*3结果直逋息—施闲1 [_12_②创建存储过程，比较两个员工的实际收入，若前者比后者高就输出0,否则输出1.乃Mjul-I占.den-Etrato8,；54ji£QLQueryj.£q-le^.rrrii^i&f-atiTiASQLQutlf)El-redteprcic:wdUr■於caripeStDlchsr(6"fdlD?char；6jflB_intoutput己5U3eglndelin"eSiR1flcat floatselectiS-：1-I^'Ccue-UjtLorre千n。利SalaryEvploycelD^lTDlselectIn-oie-OjtCcniefnotnSalarycHp"pIFnp1门》广三ID-fflTD3if砌[小叩1亮set®2-J-Q-1*pset幽-1-Ed执行该存储过程并查改结果。日declare@31intEXECcompa,800091r/198991,Joutput-主白13亡t2)0]③创建添加职员记录的存储过程 EmployeeAdd-dbc,^dlarySQLQtieryi.sql-ia..idministra：Q"i^4-rSQLQutryLiql-useYGGLgocreatePROCEDUREEmpJ.0产曰山dd(|SGmployeeicl -i^namechar'10''ni|educationchar(4tigbirthdayd^tetime^jorky^iptinyint?@5巳耳bitj(§)addresschar(49)jSnhonemjniberchar12,5departmentlDchar；3)asbeginnInsertintoEmnloy^svalues(^eniplQyeeidj@naniejgedluGdtionj^birthd^yj^Qrkyedrj(^sex^addresSjgphonenumberj@departmentlD)-endreturngo执行该存储过程。C--L9.-GGL-abc.cmplcyees2u^-L9.YjG--doc.Sale-/SQLQueryS.sql-lo.-dministrator(S4)]*SQLQB=XECEmoloyeeAddL '9962^^^!|朝；,本科，J84090912」i'」武汉小洪山2号’」85465213；3‘执行上面T-SQL命令，在结果栏中显示一行受影响，说明结果已经插入成功，在表Employees中也够看到结果已经存在。④创建存储过程，使用游标确定一个员工的实际收入是否排在前 3名。结果为1表示是，结果为0表示否。uery2.sql-SSB.VGGLg(55))SQLQuerylsql-陈琳¥GGL(5a23456189101112131415161?181920-lcreatePROCEDURETOPTHREE(3EMTOchafG•，的KbitOUTPUTasHbeglndeclare^XEMIDchar(6)declare^lACTINint,研EQintEldeclareSALARY_DI5cursorfor /*声明游标*，selectEmployeelD,InCome-OutcomefromSalaryorderbyInCome-Outcomed&scset犯EQ=gset@OK=0open5ALARY_DISfetchSALARY_DISintogX_EM_IDj®ACT_lN /*读取第一行赞据*/T while器EQ<3andgOK-0产压较前三行数摘，/回 beginset@5EQ=@5EQ+3E if§X_EM_ID=^EM_IDset§OK=1fetchSALARY_DI5Into@X_EM」Dj@ACT」Nend212223clo&a£AmY_MSdeallocateSALARY_DI5end执行该存储过程，在结果栏中显示，命令已成功完成，并且在左侧对象资源管理器中进行刷新会显示刚刚创建的存储过程。=|二生糖过程计I」案统存谛过程,4□dbo.COMPAE图dbo.emcursor।国dbo.EmployeeAdd也回dbo.TESTdbo-TOP.THREE执行该存储过程:SQLQueiyZ^ql-除阻YGGL(辐(55)}*TOPJHREE^i^fi.1Eideclare@0Kbit2execTOP_THREE工战99「'做}Koutput3select项汹回皓果岛消息1r'i -.1L*ill上面T-SQL结果中输出“1”，代表着员工“108991”的实际收入排在前三名。【思考与练习】A、创建存储过程，要求当一个员工的工作年份大于 6年时，将其转到经理办公室工作。创建这个存储过程时候，在存储过程的条件判断中我添加了一项，判断工作年份在 6年以上的员工是否在经理办公室， 只有不在经理办公室的时候才进行调整， 这样更符合实际问题些。,¥GGL-dbduErrpl&v^es; 除总忙GL-dbcQepairtriEE&CjlQueryS-Eq- （sa[56犷-icr&attPROCEDUREc1m白3kidf>output部-b^^lndeclarefiU/Tiny!nt/*序或工作年限三Fd4Clir«（itoe^peDepartlDchdr（20）尸存放当前员工谒动前所在邵口史，declare^depHamelDch^r3 ，，经理心心里对应引「口号*，^ct^-.y-dnrkYparfronFnploywhprp*Fmloy亍产ITJ-GphidFfnr^DppflirtlD-D^pjrtiTfcFrtTDfranDp^arttipntwhprp tnantT口-（■争IfctDepartnipntTDfroinFnpln卢whpr*F・pl门户rTD-伊师一id）select@：ppHiwipTD-[>ppdirtim*>ntTDf『口mDepartwh^reDApartra^ntNareF-' 'if（舸y%6储crl（^beforeD^p^rtID1口割行3以巾/1）/”员工工作年限大于6年且原先产在经理办公交"7Q updateE印loygg覃■h芒广冬Emp]oy»sI*_idpriUend然后执行上面的存储过程，并且查看结果:ES^VGGL-dborEmployees陈聪VGGL-dbo.Departments1Bdetl^re5Jefi_idchdr62©丫电£choreeDN。电nid=*000001■fWUWMdWVWli~ ~此时有可能会出现（我就出现了）这样的问题，在之前实验三中就出现了。消息513,级别1存，状态。,第工行刊的推入就更新与先前的TREMIERULE语句所指定的规则发生冲突。该语句已翳止口语句已婚止。解决方法同样是解除规则的绑定。-lexecsp_unbindrule1Departments.DepartmentName';查看表Employees中员工“000001”的DepartmentID 已经修改为"3"（经理办公室）。[ErnpIdYe'HDNameEducatic>nEirehd6H1Wdr-kYcarAddne-s5-ahQfieNiurrber&ef3ErtnrentS| tunooi王林大营l9fif-01-»Tru#a中_J蹲”，通旦打酊双 3

B、创建存储过程，根据每个员工的学历将收入提高 500.12345678g12345678gEbegin"根据什么来增加收入(学历幽du-updateSalarysetInCome-InComer500inhereEmployeelDir(selectEmployeelDfromEmployeeswhereEducation=@edu)end首先创建一个存储过程，利用什么学历增加收入，在这里，本科学历增加收入 500.执行上面的存储过程，查看结果:1l-ldeclare&educhar(4；2set曲du='本科’3exeraddlnComeQedu由于在执行这个存储过程之前,我先执行了一个查询语句，将增加收入之前的所有本科1-由于在执行这个存储过程之前,我先执行了一个查询语句，将增加收入之前的所有本科学历职工的收入输出，便于与执行操作之后的结果进行比较。1SselectInCome2 FromSalary3 whereEntplayeelDin4 (selectEmployeelD-fromEmployeeswhereEducation=( )□结果■消息3结果_J消息1InCome153Z.6212002.622 2539£33 iser.oi4 2980.76 2E31S8£ 3053.S33 243r.014 343075 3031（增加之前）（增加之后）C、创建存储过程，使用游标计算本科及以上学历的员工在总员工数中所占的比例。

1Eldeclare@eduvarchar:10.)j(3part_coLirtintf(Pall_countint234567891023456789101112131415COUNT(Education)over(partitionbyEducation)aspart_count,COUf.T^Education)over 。它allcourtfromEmployeesopenmycursorfetchnextfromniycursgrinto j@pjrt_c幻unt日 while @^FETCH_5TATUS-0E begin日 print @Edu+’占总人数比例:*+convert(varchar(l&0)jczonvertnumeric'38,2tgpart_count/l.B/galLcourit*lW))+"%'fetchnextfrominyttrsorinto^edu}rt^countll_countEnd16closemycursordeallocatemyctrsor18本科：41.67W士熹33.33%破壬，25.mD、使用命令方式修改存储过程的定义。在资源管理器中选择 “可编程性”一“存储过程”一“系统存储过程”右击选择存储器，“修改”在“查询分析器”中修改T-SQL命令行。(2)触发器对于YGGL数据库，表Employees的DepartmentID列和表Departments的DepartmentID 列满足参照完整性规则，即：向Employees表添加记录时，该记录的"DepartmentID”字段值Departments表中应存在。修改Departments表的"DepartmentID”字段值时，该字段在Employees表中的对应值也应该修改。删除Departments 表中记录时，该记录的"DepartmentID”字段值在Employees表中对应记录也应删除。对于上述参照完整性规则，在此听过触发器实现。在“查询分析器”窗口中输入各触发器的代码并执行。①向Employees表插入或修改一个记录时， 通过触发器检查记录的DepartmentID值在Departments 表中是否存在，若不存在，则取消插入或修改操作。/£QLQueiy3.sql-lo...dminl£trator(S2))*|useYGGLgo□createtriggerEmplcyeeslnsondbo.Employeesforinsert?updateasbeginiff[selectDepartmentIDfrominserted)notirselectDepartmentIDfromDepartments"rollick对当前事务进行回滚，恢复到插入之前*/end|向Employees表插入一行记录，查看效果。向表Employees中插入一个DepartmentID为6的职员信息，但是DepartmentID=6并不在Departments表中，因此会出现下面的触发器的提示。useYGGL3losertintoEmployeesvalues>1张三''本科，JLMST ・江汉大学_j消息消息8级别工书状态，第上行事务在触发器中结束』批处理已中止aD修改Departments表“DepartmentID”字段值时，该字段在Employees表中对应值也做相应修改。□createtriggerDepartmentUpdateondbo.Departments■forupdateasbegirupdateErnpLQyeessetDepartmentID=(selectDepartmentIDfrominserted)whereDepartmentID-(.selectDepartmentIDfromdeleted)€ndgdD删除Departments表中记录的同时修改记录 DepartmentID 字段值在Employees表中对应的记录。SQLQueryS.sql-lo...dminiitrator(55))*2O4-5*i.YGGi--dbo.Departnents2C4-5*i.YGGL-doo.Employ0createtriggerDepartmentDeleteondbo.DepartmentsfordeleteasbegindeleteframEmployeeswhereDepartmeritID=(selectDepartmentIDfromdeleted)Lend④创建INSTEADOF触发器，当向Salary表中插入记录时，先检查EmployeelD列上的值在Employees中是否存在，如果存在则执行插入操作，如果不存在则提示“员工号不存在”。SQLQueryl.sqJ-除密YGGL(力(54犷XBcreatetriggerEM_tXISTSonSalaryInsteadofInsertas1=1begindeclaregEnployeelDchar8B selectgEmployeelD-EmployeelDfrominsertedif@EmployeelDin(selectEmpJoyeelDfromEmployees；insertintoSalary select*Frominsertedel&eI print1品丁不&在，「endgo向Salary表中插入一行记录查看效果。ftTTFFTFlz：useYG3LftTTFFTFinsertintoSalaryvaluesH'IHltL'在结果中显示触发器提示的信息:

⑤创建DDL触发器，当删除YGGL数据库一个表时，提示“不能删除表”，并回滚删除-dbo.Salary操作。-dbo.SalarySQLQuery2.sql-^fe.YGGL(前(55])*X-Icreatetriggertable_deleteonDATABUSEafterDROP_TABLEprint'不能删除该表’rollbacktransaction当利用资源管理器中窗口操作时，尝试着鼠标点击删除Salary表时，会出现如下结果:触发器阻止删除YGGL中表。肝1除对于表"曲。,5alarf失败”即krasoftgQiservedmo)其他信息；执行Transa匚b5QL谙句或此处理时发生了异常口 KrqeaitSqlServer.CcnneclionJifo)扁重皆在触友器中结束*批处理已中止。、本悠而^icnosaflSQLSer/cr?错误:36叫【思考与练习】A、对于YGGL数据库，表Employees的EmployeelD列与表Salary的EmployeelD列应满足参照完整性规则，请用触发器实现两个表间的参照完整性规则。1_CreatetriggerSaldryirs&OrtSaidry■forinsertupdate3as1Bbeginif!；selectEmployeelDfrominserted)rotin(selectEmployeelDfrorEmplo/ees)rollbackendBqLdcreatetriggerEmployeesupdateQonduo.Employeesforupdateas4i-ibeginupdateSalarysetEmployeeID=(selectEnployeelD千广ominsertedwhereEmployeeID^(selectEmployeelDfromdeleted)endrL0一^createtriggerEmployeesdeletelJonEmployeesfordeleteas-begin0deletefrotnSalarywhereEnployeeID=<selectEmployeelDfrondeletedend|当删除员工号为“000001”的职工，那么就会在Employees表哥Salary表中删除该员工的所有信息。但是上面的触发器只设定了删除 Salary中数据，并没有删除Employees中数据。另外，如果直接删除Employees中数据：useYGGLgoFdelete千pqeEmployeeswhereEmployeeID="019008r就会出现这样的报错：虫消息消息547；级别10,情怒3室1行CEUTE语句与XErEEEJCE约束\*」“匕口"冲突。谟冲突发生于救据庠裘"a”S*l*ry*二社5工’Em0沙州ID'。语句已终止。由于此时的EmployeelD存在于Salary表中，有外键关联无法删除，但是如果将Salary表中的EmployeeID='010008'的记录删除就能删除了。B、当修改表Employees时，若将Employees表中员工的工作时间增加一年， 则将收入增加500,若增加2年则增加1000,依次增加。若工作时间减少则无变化。

创建一个触发器updateInComeSQLQuery4.5ql-陈聪"GGl(sa।S^.YGGL-SQLQuery4.5ql-陈聪"GGl(sa।1234567891012345678910111213tcreatetriggerupdateInComeonEmployeesaflzerupdateasbegindeclare,declare।selectiselectiset@d=iif；.Sd;>0@dint^old_yeartinyint,|3new_yeartinyintiJiold_year=l<opkYeapfromdeleted3neucyear=WorkYearfrominserted(3nei<_year-^ol<1_yearupdateSalarysetInCome-InCome+500J@dwhareEmployeelD=selectEmployeelDfromdeletedend修改Employees表中职工号为000001的员工的工龄（修改为 9）,刷新Salary表显示工资增加500.-luseYGGLEupdateEmployeessetWorkY«.ar-9whereEmployeeID='000001EmployeelD000001C、创建UPDATE/GGL-dtko.SalaryEmployeelD000001C、创建UPDATE/GGL-dtko.SalaryEmployeelDInComeInCome2100.8触发器，当Salary表中InCome值增加500时，Outcome增加50.000001^QLQuery?.-ECj]-语kSQLQueryS.sql-除魄.WGGL(二a(S3))* SQLQui日—日|臼—

4567日—日|臼—

45671-icreatetrigger*sainconie2onSalaryforupdataasbeginIf((selectIrconefromInserted)-(selectIncomefromdeleted)=500)updateSalaryset□□tccme=outconie-50vnercEmployeelD-selectEmployeelDfnomirserted10|_ gQ113 selectInCome.Outcome12 fromSalary\-/hereETiployeelD-000001将其中职工000001的InCome修改为2600.8（增加500时，Outcome增加50）

£QLQue除fi&Z/GGl-dbo.Salary SQLQue