试验四存储与触发器资料

1、;好衿立工於Z法阎肺依幺挈沈FujianUniversityoft0chn。用用GuomaiInformationCollege学生课程实验报告书2013级工商管理系信息管理与信息系统专业1301班学号3138905150姓名刘玲颖2015-2016学年第二学期一、实验题目存储过程、触发器二、实验目的1.了解存储过程的概念和作用。2、掌握创建存储过程的方法。3、掌握学会创建和使用触发器。4、加深对数据库完整性的理解。三、实验内容1、在“教务管理系统”数据库中创建一个名为ProcStudentInfo的存储过程，它返回学生的学号、姓名、性别、班级编号、年级和籍贯信息。2、用EXECUTE行Pro

2、c_StudentInfo存储过程。3、创建一个存储过程，用于返回“教务管理系统”数据库上某个班级中所有学生的信息。4、创建一个存储过程，用于返回“教务管理系统”数据库上某个班级中所有学生的信息，默认班级编号为'20031340000102'。5、使用不同参数传递值方式执行存储过程。6、创建一个存储过程，用于返回“教务管理系统”数据库上某个班级中的学生人数，默认班级编号为20031340000102。7、定义一个变量用来存储存储过程Proc_GetClassStudent3返回的值。8、创建一个返回执行代码为100的存储过程。9、执行存储过程Proc_GetClassStude

3、nt2和Proc_GetClassStudent4,并定义两个个变量存储执行返回代码。10、在commoditysell中创建一个名为GetFactProd的存储过程，用于实现：由执行该存储过程时提供的厂家的名称，查询其生产的产品信息，返回产品的ProdID、ProdNameUnitPrice和StockAmount。（使用“四川绵阳”和“上海黄埔”两个厂家的名称执行存储过程）11、在commoditysell中创建一个名为SellProdAmount的存储过程，用于实现：由执行该存储过程时提供的商场的名称，查询其销售的产品数量，并用输出参数输出销量数。（使用“双桥子商场”和“十陵商场”两个商

4、场名称执行程存储过程）12、设计一个简单的AFTERNSERT1虫发器，这个触发器的作用是：在插入一条记录的时候，发出“又添加了一个学生的成绩”的友好提示。13、在score表中创建触发器，在向score表中插入数据时，先检待插入的数据中的sno和cno是否存在于student表和sc表中，如果不存在则不允许插入。14、创建一个触发器,当删除student表中的数据时，需要判断该数据对应的学生是否有选课，如果有，也需要将其选课信息全部删除。15、在score表上创建一个insteadofinsert触发器，实现：当向表score插入记录时检查分数的合理性，如果不合理就不进行插入操作，否则允许。

5、16、在score表上创建一个insteadofdelete触发器，实现：当从表score删除记录时检查各课程的成绩是否为空，如果为空就不允许进行删除操作，否则允许。17、在score表上创建一个insteadofupdate触发器，实现：当从表score更新记录时检查当前用户是否是dbo,如果不是dbo就不允许进行更新操作，否则允许。18、给sell表创建一个afterinsert触发器，实现：当向sell插入一条记录时，修改stock表stock_amount（库存数量）,值等于stock_amount-sell_amount,还有修改时间modified_date,并显示相应的提示信息：

6、“库存量还剩余stock_amount-sell_amount“，当（stock_amount-sell_amount）0时，不允许进行插入操作，即操作回滚，并显示相应的提示信息：“库存量不足，只有stock_amount”。四、实验步骤1、在“教务管理系统”数据库中创建一个名为ProcStudentInfo的存储过程，它返回学生的学号、姓名、性别、班级编号、年级和籍贯信息。createprocedureProcStudentInfoasselect学号，姓名，性别，班级编号，年级，籍贯from学生信息rQLQuuryLsql-LB-miniftztor(51).createprocedure

7、ProcStudentInfoas_J结果命令已成功完成亭2、用EXECUT执行Proc_StudentInfo存储过程。executeProcStudentInfo111L二结果1J消息1学号姓名性别班皴弱号|年级籍贯1200030000041徐缸男200213400001012C00湖南2200030000042榻福男200213400001013000湖南3200030000043殷实女200213400001032000湖南4200030000044于莉女2C0213400101022C00湖南5200130000116薜小缸安200313400701022001湖南E20013000

8、0117姚子龙男200313400701022001湖南7200130000118俞翔女2003134Q0701022001湖南6200130000119袁学世男200313400701022001湖面9200130000120张斌男200313400701022001湖南zl与查询已成功执行.ABB(100RTM)m'iAdininLTtrator班)教势管理系既00:00:00£32行第1行第I列Ins3、创建一个存储过程,用于返回“教务管理系统”数据库上某个班级中所有学生的信息CREATEPROCEDUREProc_GetClassStudent1ClassIDvarc

9、har(14)ASSELECT学号，姓名，性别，班级编号，年级，籍贯FROM学生信息WHERE班级编号=ClassIDSQLQuery5房ql-LE.ministrator(57)*SQLQuery4.Sql-LBf,.nnini5trator54)*SQL日CREATEPROCEDUREFroc_GeT；C：la33SttidentlSClassIDvarchar14A5SELECT室，哙，FR0MWHERE班级编悬三151ssM%消息命令已成功竞菰4、创建一个存储过程，用于返回“教务管理系统”数据库上某个班级中所有学生的信息默认班级编号为'20031340000102'。R

10、EATEPROCEDUREProc_GetClassStudent2ClassIDvarchar(14)='20031340000102'ASSELECT学号，姓名，性别，班级编号，年级，籍贯FROM学生信息WHERE班级编号=ClassID20二I结果|消息|学号姓名班级编号性别|年期|政治面貌民族籍贯学籍4J_200530000022李爻军20031340000102男2006群众汉河南在校2“痴而前西李晓玉20031340000102男2006群众汉河南在校3200530000024刘葩2003134OOOU102女2005群众汉河南在校4200530000025刘联昱2

11、003134000U102男2005群众汉河南在校5200530000026陆帏忠20031340000102男2005群众汉河南在校5200530000027吕军20Q31340000102男2005群众汉河南在校72OQ530000Q2Q珈20031340000102男2005群众滤河南在校g200530000029甄立新20031340000102男2005群众双河南在校3200530000030君也20031340000102女2005群众汉河南在校zJ查询已成功执行.福E(10.0RTW)AEBVAdministraLor(52)教务管理系统00：00：00II行第I行第1列Inn5

12、、使用不同参数传递值方式执行存储过程。EXECProc_GetClassStudent2EXECProc_GetClassStudent2'20031340000101'EXECProc_GetClassStudent2ClassID='20031340000104'5QLQuery7ql-LB.ministrator(59)*SQLQueryS.sql-LBMministrstor(53)*SQLQueryS.&qlL3”,miniEXECFrac_GetClassStudent2EXECPracGetClassStudentS'2003134

13、0000101,EXECProcGetCla335tudent2&Clas3lD='20031340000101第心结果命令已晟功完成广6、创建一个存储过程，用于返回“教务管理系统”数据库上某个班级中的学生人数，默认班级编号为20031340000102'use教务管理系统goCREATEPROCEDUREProc_GetClassStudent3ClassIDvarchar(14)='20031340000102'asSELECTcount(学号)as学生人数FROM学生信息where班级编号=classidEXECUTEProc_GetClassSt

14、udent3'20031340000102'o7、定义一个变量用来存储存储过程Proc_GetClassStudent3返回的值use教务管理系统goasselectout=人数from班级信息宜“切fli曰nr»-aiKtJQWOI二HEBZiWiHqmfelHi1Ml温gI事因中的，wnpmjmv.rI.4ieuCiMCyUT|=7，00where班级编号=banjiandbanji='20031340000102'tdb(kE<rv>w<Ha3Pr»4i<*Ao«.J«,“目师<

15、7;rj强iz，dS-T#1- j注足»«$*±114£工金JcJE1E"<orj(|f.,-iMnu上ned；«wnrtr-mewiN-n«*n;ri1sg4SjFRiHtL苔ymrmoi?wideclareoutnumeric(3,0)executeProc_GetClassStudent3'20031340000102',outoutputselectoutas人数SQLQuerylS.sql-L«dministrator60)"SQLQueryl2.ql-£QLQu

16、eryl4L5ql-I_dministrator(53)M0declareg二二七nurteiie3r0IexecuteProcGetClassStudentS,20031340000102'Foutoutput产广-一广-一,/-一一广，一广一广-产产4-_:一广r-select$outasJ教口结果J消息油iimnt-iiir-in1i338、创建一个返回执行代码为100的存储过程。USE教务管理系统GOCREATEPROCEDUREProc_GetClassStudent4ClassIDvarchar(14)ASBEGINSELECT学号，姓名，性别，班级编号，年级，籍贯FROM

17、学生信息WHERE班级编号=ClassIDRETURN100END*MEA也.f)Bftnani9、执行存储过程Proc_GetClassStudent2和Proc_GetClassStudent4,并定义两个个变量存储执行返回代码。DECLAREreturn1int,return2int'20031340000102''20031340000102'EXECreturn1=Proc_GetClassStudent2EXECreturn2=Proc_GetClassStudent4SELECTreturn1SELECTreturn2结果消息学号420053000

18、00255200530000026620053000002772DD5MOD002882005S0000029920D5300D003D1020053000003111200530CWC32姓名刘轶昊陆伟忠吕军罗斌萌立新毛艳孟丽但志仁性别班级编号男20031340000102里20031340000102里20031340000102界20031340000102更2003134OT00102女20031340000102女20031340000102里200313400001D2年级籍贯2005河南2005河南2005河南200520052D0520052005南南南南可可可可10、在com

19、moditysell中创建一个名为GetFactProd的存储过程，用于实现：由执行该存储过程时提供的厂家的名称，查询其生产的产品信息，返回产品的ProdID、ProdNameUnitPrice和StockAmount。(使用“四川绵阳”和“上海黄埔”两个厂家的名称执行存储过程)usecommoditysellgocreateprocedureGetFactProdT名char(8)asselectProdID,ProdName,UnitPrice,StockAmountfromProductInfowhereFactAddr=T名xfivwjnnriwuijjwibutwiuiat3EwnJ

20、UdJ4fhi,!siftiKi*耳.FTQICiO二二*if，-AnB*:*WtMrv修理的WMExrigifBnrnixq<j一.一_1一_-1-PffijLT>73-r-4-h胃rrF-«#ilEEC?lvCVIIII*mb7i，b7u-m'_1一一nawirmEEep吨厂名ihfi/MEWwamiwf即eonums«-C#Hrr»白mMwiiBMFjo.fmitt_win工门JuiorUnM-oumb口，executeGetFactProd'四川绵阳executeGetFactProd'上海黄埔SQLQuerylZ.sq

21、l-L.minirtrator(54)*SQLQueryll.sql-L,.ministr9*已61七EGetFautParod卬”格郡口|uKBUii七已GEtFactPrcKi*卜海黄j：甫日结果心消息ProdIDPnodNameUniPticeStockArount1|060101I甲电视机2000120L050201“甲空调30QQ115ProdIDProdNanwUnitPftceStock/ount1060303i丙洗衣机晒2即2060103内电视机1580S911、在commoditysell中创建一个名为SellProdAmount的存储过程，用于实现：由执行该存储过程时提供的

22、商场的名称，查询其销售的产品数量，并用输出参数输出销量数。(使用“双桥子商场”和“十陵商场”两个商场名称执行程存储过程)usecommoditysellgocreateprocedureSellProdAmount5称varchar(10),C量varchar(10)outputasselectH=SellAmoutfromEmporiumSellwhereEmpID=(selectEmpIDfromEmporiumInfowhereEmpName=§称)，Q*1-*g*«1-*口s】KUSEmMWHmMN|rwl%LM-BAV2EjM-ndb&LliaaruTAn

23、tic-ndbi.1fBbrupAdti口dli6-h*Arf*de>MU注k：£.1K,F电上M®#®年|Jyeslwf4R1H空rij1a/MMirUFKli小.1fuMWM-t由酒厅-WmF曰JHMOTLiQijmF514刊KMTrF>Wf历.rWMriliy4眄叫为口槎executeGetFactProd'双桥子商场'executeGetFactProd'十陵商场'l/SQljQuerylZsql-Ujninistrator(M)j*&QLQyeryll.sql-Lministrator(5SJ)*臼uj

24、cuuuteGecFacProd'双桥千百场executeGecFacProd，十陵商场，ProdIDProdMameUnitPriceStockAnourrt11、设计一个简单的AFTERNSERK虫发器，这个触发器的作用是：在插入一条记录的时候,发出“又添加了一个学生的成绩”的友好提示。先建立三个表：createtablescore(snochar(9),cnoint,gradeint)createtablestock(prod_idchar(10),prod_namevarchar(10),unit_priceint,stock_amountint,modifieddatedat

25、etimesell(identity(1,1),)createtableorder_idintchar(10),prod_idsell_amountintsell_datedatetime,Salervarchar(10)接着：createtriggeradd_sconscoreafterinsertasbeginprint'又添加了一个学生的成绩endinsertintoscorevalues('20110523,23,50)SQLQuerylfi.sql-Imiinistrator(S6)*SQLQuerylX.sql*L.H.ministrdtorI54insertint

26、oscorevalues(1201105(23*f23,SO)Aywwwvw''一一一一r-rf|CII又:藐二不学生的成绩1行受葡响)sno12、在score表中创建触发器，在向score表中插入数据时，先检待插入的数据中的和cno是否存在于student表和sc表中，如果不存在则不允许插入。CREATEtriggeradd_sc2onscoreafterinsertasbegindeclaresnochar(9)selectsno=snofromstudentwheresno=(selectsnofrominserted)ifsnoisnullbeginrollbacktr

27、ansactionprint'不存在这样的学生学号！-raiserror（'不存在这样的学生学号！,16,20）end分产endCREAte'-cr1gerad'ianafinsertbc9in2m二二(9selectar"fTamB;二招二Lwhereselect个工;frcir.ted工工4mnei3ej!1beginrollbacktranaetidnPxint不存在这样的学生学号.工“"工工仃工厂不存在这样的学生学£!3"eridendJ结果命令已成功完成insertintoscorevalues('201

28、10523',13,80)SQLQuuryu占qi-3tgmr(5刃QLQuerylb.sql-L“.mini允2.nsertintoscorevalues('20110523,13,SO)又添加了一个学生的成绩；工行受影响1USEcommoditysellGOINSERTINTOscorevalues('20110529',13,80)SQLQueryl7j<<L-ministrator(59)rSQLQuerylg.sql-USEcozrancditysellGOINSERTINTOscore-valuesr13f80：消息又添加了一个学生的赚又

29、添加了一个学生的成绩不存在这样的学生学号！消息“口与级别1%状态，第工行事务在触发器中结束。批处理已中止。I13、创建一个触发器,当删除student表中的数据时，需要判断该数据对应的学生是否有选课，如果有，也需要将其选课信息全部删除。CREATEtriggerdel_stuonstudentafterdeleteasbegindeclaresnochar(9)selectsno=snofromscorewheresno=(selectsnofromdeleted)ifsnoisnotnullbegindeletefromscorewheresno=snoprint'该学生的选课记录也

30、已经删除！'endendjEEBiV.MMOlXBTlVCiWl4=SpOMi网二卡-Mcwmumv*11moqIn图，oIbIq：M，If咻i何匚一二七日.不，FrifcffiS*ISwJS-S4UCINJIijj4V*产加口1kJMnM.二J声纲Iinsertintostudentvalues('20110526'，'李明'，'男'，19,'计算机')deletefromstudentwheresno='20110526'selectfromscoreSQLQuery2.£ql-WLr.min

31、iistrater(53)*ninsertintoscorevalues'工门二二052913ROselect-fromscoreFTI口消息又添加了一不学生的成绩不存在这样的学生学号！14、在score表上创建一个insteadofinsert触发器，实现：当向表score插入记录时检查分数的合理性，如果不合理就不进行插入操作，否则允许。CREATEtriggeradd_sc3onscoreinsteadofinsertasbegindeclaregradeintselectgrade=gradefrominsertedifgrade<0orgrade>100beginr

32、ollbacktransactionprint'该学生的成绩不合理！endend期却算Mt>睥mkqMtntaici网J*="J.Jii-11w!ixi»KEKFEvrimvr”f-terMLtL«i?trt1tAasitieJ函dffelvLrMfflFE-TTnFWE：；"Tnv：i*盾由wii工j门二附-tzinmi-mrinsertintoscorevalues('20110525,13,-80)埠Wl%事EMULQLIJtMJ；&|lQi"廉.)！!|._'f'-dLWEfi百，日aEM，

33、ndb4EHvvw*4diK>iH¥CV«»4MJiUin白口,i司1t1j/Si»nArikIRportbmMiSiaLlJfHUM.1iegflSEt<fliQQMf印Ep-0immiM删除记录时15、在score表上创建一个insteadofdelete触发器，实现：当从表score检查各课程的成绩是否为空，如果为空就不允许进行删除操作，否则允许。USEcommoditysellGOCREATETRIGGERscore_insert_deleteonscoreinsteadofdeleteasbeginSETNOCOUNTONsnochar(9),cnoint,gradeintdeclareselectsno=sno,cno=cno,grade=gradefromdeletedif(gradeisNULL)print'成绩为空，不允许删除该记录