实验四存储与触发器资料

1、ujian University of Technoiny Guomai Information College学生课程实验报告书2013 级 工商管理系信息管理与信息系统专业 1301 班学号 3138905150 姓名 刘玲颖2015 -2016 学年 第 二 学期一、实验题目存储过程、触发器二、实验目的1、了解存储过程的概念和作用。2、 掌握创建存储过程的方法。3、 掌握学会创建和使用触发器。4、 加深对数据库完整性的理解。三、实验内容1、 在“教务管理系统”数据库中创建一个名为 ProcStude ntl nfo 的存储过程，它返 回学生的学号、姓名、性别、班级编号、年级和籍贯信息。2

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

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

4、并用输出参数输出3/ 日tk/.销量数。select 学号，姓名，性别，班级编号，年级，籍贯 from学生信息（使用“双桥子商场”和“十陵商场”两个商场名称执行程存储过程）12、 设计一个简单的 AFTERNSERT 触发器，这个触发器的作用是：在插入一条记录 的时候，发出“又添加了一个学生的成绩”的友好提示。13、在 score 表中创建触发器，在向 score 表中插入数据时，先检待插入的数据中 的 sno和 eno 是否存在于 student 表和 sc 表中，如果不存在则不允许插入。14、 创建一个触发器，当删除 student 表中的数据时，需要判断该数据对应的学生 是否有选课，如果

5、有，也需要将其选课信息全部删除。15、 在 score 表上创建一个 in stead of in sert触发器，实现：当向表 score 插入 记录时检查分数的合理性，如果不合理就不进行插入操作，否则允许。16、 在 score 表上创建一个 in stead of delete触发器，实现：当从表 score 删除 记录时检查各课程的成绩是否为空，如果为空就不允许进行删除操作，否则允许。17、 在 score 表上创建一个 in stead of update触发器，实现：当从表 score 更新 记录时检查当前用户是否是 dbo,如果不是 dbo 就不允许进行更新操作，否则允许。18、

6、 给 sell 表创建一个 after insert触发器，实现：当向 sell 插入一条记录时，修改 stock 表 stock_amount（库存数量），值等于 stock_amount-sell_amount ，还有 修改时间 modified_date ，并显示相应的提示信息：“库存量还剩余stock_amount-sell_amount ”，当（stock_amount-sell_amount）0时， 不允许进行插入操作，即操作回滚，并显示相应的提示信息：“库存量不足，只有 stock_amount ”。四、实验步骤1、在“教务管理系统”数据库中创建一个名为ProcStude ntl

7、nfo 的存储过程，它返回学生的学号、姓名、性别、班级编号、年级和籍贯信息。select 学号，姓名，性别，班级编号，年级，籍贯 from学生信息create procedureProcStude ntl nfoas/SQLQueryl.ql LB.ministrator (53)* create procedure ProcStudentlnfoas尝昙姓盘性别，班級編悬生级臓from尝生信息-J结果 命令已成功完成。2、用 EXECUT 执行 Proc_Studentlnfo 存储过程。executeProc Studentlnfo学号姓名性别班级編号年级藉贯1200030000041徐红

8、男200213400001012000湖南2200030000CM2杨根男200213400001012000湖南3200030000043殷实玄200213400001032000湖南4200030000044于莉女20021WJ101022000湖南5200130000116200313400701022001觸南E200130000117姚子龙男200313400701022001湖南7200130000118俞翔女2003134Q0701022001湖南8200130000119男200313400701022001湖聞9200130000120男200313400701022001湖

9、南ABB(100 RTM)miAdminiTtrator(52)教务管理系扬00:0000 232行3、创建一个存储过程，用于返回“教务管理系统”数据库上某个班级中所有学生的信息CREATE PROCEDURE Proc_GetClassStudentlClasslD varchar (14)ASSELECT 学号，姓名，性别，班级编号，年级，籍贯 FROM 学生信息WHERE 班级编号=ClasslDSQLQuery5Fsql - LE.ministrator (57)* SQLQuery4.Sql - LBf,.nnini5trator 54)* SQL国查询已咸功执行. CREATE P

10、ROCEDURE Froc_GeT；C：la3 3Sttidentl3C1&3SID varchar 14ASSELECT堂悬姓名性别，班级编昱救籍贯FROM堂生疙息1丽ERE班级编昱=胆歸HD盲消息命令已成功麺4、创建一个存储过程，用于返回“教务管理系统”数据库上某个班级中所有学生的信息,默认班级编号为20031340000102。REATE PROCEDURE Proc_GetClassStudent2ClasslD varchar ( 14)=20031340000102ASSELECT 学号，姓名，性别，班级编号，年级，籍贯 FROM 学生信息WHERE 班级编号=Classl

11、DNl结果|力消息字号姓名班级編号|性别| 年驭|政治面貌学籍J_20053000002220031340000102另20062融亍0顽葩亍20031340000102男2006群众河南 在校p200530000024刘徳2003134OOOU1022005群朮汉河南 在校42005300000252003134000U102男2005群女況河南在校5200530000026陆儒忠20031340000102男2005河南 在校6200530000027吕军20Q31340000102男2005河南72OQ530000Q2Q20031340000102男2005群让河南 在校_J_20053

12、000002920031340000102男2005河南 在校g20053000003020031340000102立2005群众祝河蘭孕查诲已成功执行.AEB (10.0 RTM) fcEBVAdministralor(52)教吳昔理系统00：00：00 1L疔第 1 行第 1賈_ Inn5、使用不同参数传递值方式执行存储过程。EXEC Proc_GetClassStudent2EXEC Proc_GetClassStudent220031340000101EXEC Proc_GetClassStudent2ClassID =20031340000104createprocedureProc

13、 GetClassStudent3banji char ( 14), out numeric (3,0) outputasselectout =人数 from 班级信息5QLQuery7ql - LB.ministrator (59)* SQLQueryS.sql * LB“,miniwtr9tor (53)* SQLQueryS.&ql - L&“miniEXEC Frac_GetClassStudent2EXEC PracGetClassStudentS 20031340000101EXEC Proc G&tCla335tudent2 &Clas3lD=20

14、031340000101-li结果命令已成功完成。6、创建一个存储过程，用于返回“教务管理系统”数据库上某个班级中的学生人数，默认班级编号为 20031340000102use 教务管理系统 goCREATE PROCEDURE Proc GetClassStudent3 ClasslD varchar ( 14)= 20031340000102 asSELECT count （学号）as 学生人数 FROM 学生信息 where 班级编号=classidEXECUTE Proc GetClassStudent37、定义一个变量用来存储存储过程Proc_GetClassStudent3 返回的

15、值use 教务管理系统go20031340000102貉果| _J消息|屮二结果二消息pi( (iiimnmik1 380 declare二匸nutteric 3r0execute Proc GetCLaflident3 20031340000102 .黒cut output -select电out aa”人姿攵where 班级编号=banji and banji =20031340000102*MMFTEVJWnin却硝PHIEE. “ im-PFIWKinrjfcHfh.IT药时KKMQ U琦declareout n umeric (3,0)executeProc_GetClassStud

16、e nt3select out as 人数20031340000102,out output!期K ftVQL-QMIYIwn FmjiMiF*. rI. 4iQ 鱼 QLQuery 14.sqI - Idministrator (53) SQLQueryl3,sq| - administrator (60)*SQLQuery 12-qI 8 创建一个返回执行代码为 100 的存储过程。USE 教务管理系统GOCREATE PROCEDURE Proc_GetClassStudent4ClasslD varchar ( 14)ASBEGINSELECT 学号，姓名，性别，班级编号，年级，籍贯

17、FROM 学生信息 WHERE 班级编号=ClassID RETURN100ENDIfihMXt jj强9、执行存储过程 Proc_GetClassStudent2 和 Proc_GetClassStudent4，并定义两个个变量 存储执行返回代码。DECLARE return1 int , return2intEXEC return1= Proc_GetClassStudent2EXEC return2= Proc_GetClassStudent4SELECT return1SELECT return2结果J消息2003134000010220031340000102学号性别42005300

18、00025刘轶旻男52D0530D0M2S陆伟忠G200530000027吕军720O5S0OK02882005S0000029萌立劭92005S000003D毛艳女10200530000031孟丽女n200530000032里班级骗号年级 薯贯200313400001D2 2005河南200313400001022005河南200313400001022005河南南可可可可HEF聊MknSM,I丄VWJkB2003134OT00102200520031340000102200520031340000102200520031340000102200510、在 commoditysell 中创建

19、一个名为 GetFactProd 的存储过程，用于实现：由执行该存 储过程时提供的厂家的名称，查询其生产的产品信息，返回产品的ProdID、ProdNameUnitPrice 和 StockAmount。（使用“四川绵阳”和“上海黄埔”两个厂家的名称执行存储过程）use commoditysell gocreateprocedureGetFactProd 名char (8)asselectProdID ,ProdName , UnitPrice, StockAmou ntfrom ProductI nfowhereFactAddr=T 名W.WKUI 411 HLIWI HI；i *，* e

20、. 乂厂斗 Q.V Q -Z * * S( (gna, 3MtQLQua3!|l Wk nlBhw9rq= - MJ d=.T fHii.1岀 删UK赳jwawHam罷IIQcediriS：e :丁 h*“ _rl.l毛毛otiai flu* Jlr J(中3心1沖WQJUPHI：帚 ji t用 f 血砂町莎叭c诵-丄 J鼻”1ig；rTr-|kT1 F * T 5. 宙；头：-百&丁石卩 f IW rT rJFG CTIBHiCBt*；沾；1：ft| 件P .nRexecuteGetFactProdexecuteGetFactProdSQLQuerylZ.sql -_ WIN tin

21、 JUCOUHEI nJ四川绵阳上海黄埔L.minirtrator(54)* SQLQueryll,sql - L.,.mini stre sc匕uut芒Ge t F atet F工Dd四J11纟郡日|execute GetFactProd*上海黄土甫二结果J消息PnodiDUni PticeStock Arountj 060101甲电视机200Q120t050201甲空调30QQ115PtodlDProdNarrteUnitPftceStockMiourrt1i 060303丙洗衣机800如2060103丙电视机1580S911、在 commoditysell 中创建一个名为 SellPro

22、dAmount 的存储过程，用于实现：由执行该 存储过程时提供的商场的名称，查询其销售的产品数量，并用输出参数输出销量数。(使用“双桥子商场”和“十陵商场”两个商场名称执行程存储过程)use commoditysellgocreate procedureSellProdAmount解称 varchar ( 10), 数量 varchar (10) outputasselect数量=SellAmoutfrom EmporiumSellwhere EmpID =( select EmpID from EmporiumInfowhere EmpName = 名称 )胄目曲工ISICI MiHi A

23、EMSE祠IIIWHWIMM|WI中 3 活*”髦 “二CftA 3 dk.E.Ivfn-uT4rdc-i- 3i m豪沁黑询乂*MU丄A A 可怖今亠訂件riff*Ji WHlN|J bporeblH9Pitxntsri j逊E说阳Eign . i rtnut omM-亠WHFVJH4nTjQUi?aFi；tVH用科叭.MTFFvlbdE理.QC?n口两executeGetFactProd双桥子商场executeGetFactProd十陵商场|/5Q|jQueryl2jql - Ujninistrator(M)j* &QLQyeryll.sql * Lministrator (5SJ

24、)* execute GecFacProd取桥干裔场,execute GecFacProd十显费商场*r=结果j消息Prod ID Prod Name UnitPnce StockAmourt磧金叭 2H!g 十*M* iWrf gg*11*n X胄目曲工ISICI MiHiProd ID ProdMame IhitPnce StockAnourrt11、设计一个简单的 AFTERNSERT 触发器，这个触发器的作用是：在插入一条记录的时候, 发出“又添加了一个学生的成绩”的友好提示。先建立三个表：create table score(sno char ( 9),eno int ,grade

25、int)create table stock (prod_id char ( 10),prod_namevarchar ( 10 ),uni t_priceint ,stock_amou nt int , modified date datetime sell_date datetime ,Saler varchar ( 10)接着：create trigger add_sc on scoreafter in sertasbeginprint 又添加了一个学生的成绩endcreatetablesell (order idintide ntity(1,1),prod_idchar(10),sel

26、l amountintin sert into score values (20110523,23,50 )SQLQuerylB.sql - I-“miinistrator (S6)* SQLQuerylX.sql * L.H.ministrtor I 54 insert into score values (1201105(23 *f23, SO)fII-J消息又添加了一个学生的成绩1行受彩I向）12、在 score 表中创建触发器，在向 score 表中插入数据时，先检待插入的数据中的sno和 eno 是否存在于 student 表和 sc 表中，如果不存在则不允许插入。CREATE tr

27、igger add_sc2 on scoreafterin sertasbegindeclareselectsno char ( 9)sno = snofrom stude ntwhere sno =( select sno frominserted)if sno is nullbeginrollbacktran sacti onprint 不存在这样的学生学号！-raiserror(不存在这样的学生学号！,16,20)endendvrywv4iTnri0wi UJjjCREATE-i r19ge Ean s聶beinch4X 1 9from臭匚悄&匸where gg- select总

28、玷frcaif gm no _*二：beginrollback transactionPtint不存在这样的学生学号！ *-raiserrort*不存在这样的学生学是!endin sertinto score values ( 20110523,13,80 )endQLQueryi7+sqi -特trmt圳QLQuer/lb.sql - L.mimstra.nsert into score values(20110523,13,SO)又添加了一个学生的成绩: 1行爱影响1USE commoditysellGOINSERT INTO scorevalues (20110529,13,80)SQL

29、Queryl7j - L-ministrator (BSB*SQLQuerylg.sql -USE cozrancditysellGO INSERT INTO score-valuesr13f80：J消息又添力口了 一个学生的成绩又滩加了一个学主即愈绩不存在这样的学主学号！消息歸曲，级别i6j状态丄，第】行 事务在触发器中结束。批处理已中止。I13、创建一个触发器，当删除 stude nt 表中的数据时，需要判断该数据对应的学生是否有 选课，如果有，也需要将其选课信息全部删除。CREATE trigger del_stu on studentafter delete asbegindeclar

30、e sno char ( 9)select sno=snofrom scorewhere sno =( select sno from deleted ) if sno is not nullbegindelete from score where sno =s no pri nt 该学生的选课记录也已经删除！ endend二 IKuffYMwi MiAiiiw 4fhrdGlAIT pMD EhkJ Vegi.也T41 lfc4 nr ET-aFiMciiVI734SQLQuery2.ql - WL.miniistrater (S3)*n insert into score values12

31、0LL0529,13f801=1 - -r - rJselect from score* l_in消息又济加f一个学勺 不存在这祥的学2匚的成绩-学亏！14、在 score 表上创建一个 in stead of in sert触发器，实现：当向表 score 插入记录时检查分数的合理性，如果不合理就不进行插入操作，否则允许。CREATE trigger add_sc3 on scorein stead of insertasbegindeclare grade intselect grade =gradefromin sertedif grade 100beginrollbacktran sa

32、cti onpri nt该学生的成绩不合理！end end* jfc!财齢t J* KAL3 d b&. L mpenumAMe9- ri bb. IIi 3 H -” J. 54-r-ir* ad* -HIM* j. s-Hik J *epO zuin匸曲匸曲-tzm叩丄“士- J fiMfifTnddrff-MI* 丄IT窪BIT A EMV driw.tHW*w! di IK i HyCVvMlM I nL N U，诃frt1I J呼friilJiri A M*ffffl-卜J fartbnfiSQLLmf%J- | ie岬T*Tl岡LDM灯EpOJi MnKMA 盲1-J血匚f

33、l HW!n15、在 score 表上创建一个 in stead of delete 触发器，实现：当从表 score 删除记录时 检查各课程的成绩是否为空，如果为空就不允许进行删除操作，否则允许。USE commoditysellGOCREATE TRIGGER score_insert_deleteon scorein stead of delete asbeginSET NOCOUNT ONdeclare sno char (9), cno int , grade intselect sno=sno , cno=cno , grade =grade from deletedseleie-

34、y比丄J at0 um却冲.da買吃IQLHA 戢”if ( grade is NULL)print成绩为空，不允许删除该记录！elsedelete from score where sno = sno and eno =cno end3EW*Inrr*i MArM MbUi -JHI HUIW| mili VBIMI2*| 上j j *JIM 二二 見h翠417】Hri VA miiFiKUttf 1(14：时*-枷1EILTE x*l$gBE勻HC3ISCm；JfFr KMffiO Jh- NOVis一 岸厂*龙打llkiw hnrwf iwI ECmvWHihiMls N BlEwXViflUA4 皿