實驗四存儲與觸發(fā)器資料

1、ujian University of Technoiny Guomai Information College學生課程實驗報告書2013 級 工商管理系信息管理與信息系統(tǒng)專業(yè) 1301 班學號 3138905150 姓名 劉玲穎2015 -2016 學年 第 二 學期一、實驗題目存儲過程、觸發(fā)器二、實驗目的1、了解存儲過程的概念和作用。2、 掌握創(chuàng)建存儲過程的方法。3、 掌握學會創(chuàng)建和使用觸發(fā)器。4、 加深對數(shù)據(jù)庫完整性的理解。三、實驗內(nèi)容1、 在“教務管理系統(tǒng)”數(shù)據(jù)庫中創(chuàng)建一個名為 ProcStude ntl nfo 的存儲過程，它返 回學生的學號、姓名、性別、班級編號、年級和籍貫信息。2

2、、 用 EXECUT 執(zhí)行 Proc_Studentlnfo 存儲過程。3、創(chuàng)建一個存儲過程，用于返回“教務管理系統(tǒng)”數(shù)據(jù)庫上某個班級中所有學生的 信息。4、創(chuàng)建一個存儲過程，用于返回“教務管理系統(tǒng)”數(shù)據(jù)庫上某個班級中所有學生的信息，默認班級編號為20031340000102。5、使用不同參數(shù)傳遞值方式執(zhí)行存儲過程。6、創(chuàng)建一個存儲過程，用于返回“教務管理系統(tǒng)”數(shù)據(jù)庫上某個班級中的學生人數(shù)，默認班級編號為 20031340000102。7、定義一個變量用來存儲存儲過程 Proc_GetClassStudent3 返回的值。8 創(chuàng)建一個返回執(zhí)行代碼為 100 的存儲過程。9、執(zhí)行存儲過程 Pro

3、c_GetClassStudent2 和 Proc_GetClassStudent4，并定義兩個 個變量存儲執(zhí)行返回代碼。10、 在 commoditysell 中創(chuàng)建一個名為 GetFactProd 的存儲過程，用于實現(xiàn)：由執(zhí)行該存儲過程時提供的廠家的名稱，查詢其生產(chǎn)的產(chǎn)品信息，返回產(chǎn)品的ProdID、ProdName UnitPrice 和 StockAmount。（使用“四川綿陽”和“上海黃埔”兩個廠家的名稱執(zhí)行存儲過程）11、 在 commoditysell 中創(chuàng)建一個名為 SellProdAmount 的存儲過程，用于實現(xiàn)：由 執(zhí)行該存儲過程時提供的商場的名稱，查詢其銷售的產(chǎn)品數(shù)量，

4、并用輸出參數(shù)輸出3/ 日tk/.銷量數(shù)。select 學號，姓名，性別，班級編號，年級，籍貫 from學生信息（使用“雙橋子商場”和“十陵商場”兩個商場名稱執(zhí)行程存儲過程）12、 設計一個簡單的 AFTERNSERT 觸發(fā)器，這個觸發(fā)器的作用是：在插入一條記錄 的時候，發(fā)出“又添加了一個學生的成績”的友好提示。13、在 score 表中創(chuàng)建觸發(fā)器，在向 score 表中插入數(shù)據(jù)時，先檢待插入的數(shù)據(jù)中 的 sno和 eno 是否存在于 student 表和 sc 表中，如果不存在則不允許插入。14、 創(chuàng)建一個觸發(fā)器，當刪除 student 表中的數(shù)據(jù)時，需要判斷該數(shù)據(jù)對應的學生 是否有選課，如果

5、有，也需要將其選課信息全部刪除。15、 在 score 表上創(chuàng)建一個 in stead of in sert觸發(fā)器，實現(xiàn)：當向表 score 插入 記錄時檢查分數(shù)的合理性，如果不合理就不進行插入操作，否則允許。16、 在 score 表上創(chuàng)建一個 in stead of delete觸發(fā)器，實現(xiàn)：當從表 score 刪除 記錄時檢查各課程的成績是否為空，如果為空就不允許進行刪除操作，否則允許。17、 在 score 表上創(chuàng)建一個 in stead of update觸發(fā)器，實現(xiàn)：當從表 score 更新 記錄時檢查當前用戶是否是 dbo,如果不是 dbo 就不允許進行更新操作，否則允許。18、

6、 給 sell 表創(chuàng)建一個 after insert觸發(fā)器，實現(xiàn)：當向 sell 插入一條記錄時，修改 stock 表 stock_amount（庫存數(shù)量），值等于 stock_amount-sell_amount ，還有 修改時間 modified_date ，并顯示相應的提示信息：“庫存量還剩余stock_amount-sell_amount ”，當（stock_amount-sell_amount）0時， 不允許進行插入操作，即操作回滾，并顯示相應的提示信息：“庫存量不足，只有 stock_amount ”。四、實驗步驟1、在“教務管理系統(tǒng)”數(shù)據(jù)庫中創(chuàng)建一個名為ProcStude ntl

7、nfo 的存儲過程，它返回學生的學號、姓名、性別、班級編號、年級和籍貫信息。select 學號，姓名，性別，班級編號，年級，籍貫 from學生信息create procedureProcStude ntl nfoas/SQLQueryl.ql LB.ministrator (53)* create procedure ProcStudentlnfoas嘗曇姓盤性別，班級編懸生級臓from嘗生信息-J結(jié)果 命令已成功完成。2、用 EXECUT 執(zhí)行 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、創(chuàng)建一個存儲過程，用于返回“教務管理系統(tǒng)”數(shù)據(jù)庫上某個班級中所有學生的信息CREATE PROCEDURE Proc_GetClassStudentlClasslD varchar (14)ASSELECT 學號，姓名，性別，班級編號，年級，籍貫 FROM 學生信息WHERE 班級編號=ClasslDSQLQuery5Fsql - LE.ministrator (57)* SQLQuery4.Sql - LBf,.nnini5trator 54)* SQL國查詢已咸功執(zhí)行. CREATE P

10、ROCEDURE Froc_GeT；C：la3 3Sttidentl3C1&3SID varchar 14ASSELECT堂懸姓名性別，班級編昱救籍貫FROM堂生疙息1麗ERE班級編昱=膽歸HD盲消息命令已成功麺4、創(chuàng)建一個存儲過程，用于返回“教務管理系統(tǒng)”數(shù)據(jù)庫上某個班級中所有學生的信息,默認班級編號為20031340000102。REATE PROCEDURE Proc_GetClassStudent2ClasslD varchar ( 14)=20031340000102ASSELECT 學號，姓名，性別，班級編號，年級，籍貫 FROM 學生信息WHERE 班級編號=Classl

11、DNl結(jié)果|力消息字號姓名班級編號|性別| 年馭|政治面貌學籍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群眾祝河蘭孕查誨已成功執(zhí)行.AEB (10.0 RTM) fcEBVAdministralor(52)教吳昔理系統(tǒng)00：00：00 1L疔第 1 行第 1賈_ Inn5、使用不同參數(shù)傳遞值方式執(zhí)行存儲過程。EXEC Proc_GetClassStudent2EXEC Proc_GetClassStudent220031340000101EXEC Proc_GetClassStudent2ClassID =20031340000104createprocedureProc

13、 GetClassStudent3banji char ( 14), out numeric (3,0) outputasselectout =人數(shù) 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結(jié)果命令已成功完成。6、創(chuàng)建一個存儲過程，用于返回“教務管理系統(tǒng)”數(shù)據(jù)庫上某個班級中的學生人數(shù)，默認班級編號為 20031340000102use 教務管理系統(tǒng) goCREATE PROCEDURE Proc GetClassStudent3 ClasslD varchar ( 14)= 20031340000102 asSELECT count （學號）as 學生人數(shù) FROM 學生信息 where 班級編號=classidEXECUTE Proc GetClassStudent37、定義一個變量用來存儲存儲過程Proc_GetClassStudent3 返回的

15、值use 教務管理系統(tǒng)go20031340000102貉果| _J消息|屮二結(jié)果二消息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 人數(shù)20031340000102,out output!期K ftVQL-QMIYIwn FmjiMiF*. rI. 4iQ 魚 QLQuery 14.sqI - Idministrator (53) SQLQueryl3,sq| - administrator (60)*SQLQuery 12-qI 8 創(chuàng)建一個返回執(zhí)行代碼為 100 的存儲過程。USE 教務管理系統(tǒng)GOCREATE PROCEDURE Proc_GetClassStudent4ClasslD varchar ( 14)ASBEGINSELECT 學號，姓名，性別，班級編號，年級，籍貫

17、FROM 學生信息 WHERE 班級編號=ClassID RETURN100ENDIfihMXt jj強9、執(zhí)行存儲過程 Proc_GetClassStudent2 和 Proc_GetClassStudent4，并定義兩個個變量 存儲執(zhí)行返回代碼。DECLARE return1 int , return2intEXEC return1= Proc_GetClassStudent2EXEC return2= Proc_GetClassStudent4SELECT return1SELECT return2結(jié)果J消息2003134000010220031340000102學號性別42005300

18、00025劉軼旻男52D0530D0M2S陸偉忠G200530000027呂軍720O5S0OK02882005S0000029萌立劭92005S000003D毛艷女10200530000031孟麗女n200530000032里班級騙號年級 薯貫200313400001D2 2005河南200313400001022005河南200313400001022005河南南可可可可HEF聊MknSM,I丄VWJkB2003134OT00102200520031340000102200520031340000102200520031340000102200510、在 commoditysell 中創(chuàng)建

19、一個名為 GetFactProd 的存儲過程，用于實現(xiàn)：由執(zhí)行該存 儲過程時提供的廠家的名稱，查詢其生產(chǎn)的產(chǎn)品信息，返回產(chǎn)品的ProdID、ProdNameUnitPrice 和 StockAmount。（使用“四川綿陽”和“上海黃埔”兩個廠家的名稱執(zhí)行存儲過程）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*上海黃土甫二結(jié)果J消息PnodiDUni PticeStock Arountj 060101甲電視機200Q120t050201甲空調(diào)30QQ115PtodlDProdNarrteUnitPftceStockMiourrt1i 060303丙洗衣機800如2060103丙電視機1580S911、在 commoditysell 中創(chuàng)建一個名為 SellPro

22、dAmount 的存儲過程，用于實現(xiàn)：由執(zhí)行該 存儲過程時提供的商場的名稱，查詢其銷售的產(chǎn)品數(shù)量，并用輸出參數(shù)輸出銷量數(shù)。(使用“雙橋子商場”和“十陵商場”兩個商場名稱執(zhí)行程存儲過程)use commoditysellgocreate procedureSellProdAmount解稱 varchar ( 10), 數(shù)量 varchar (10) outputasselect數(shù)量=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=結(jié)果j消息Prod ID Prod Name UnitPnce StockAmourt磧金叭 2H!g 十*M* iWrf gg*11*n X胄目曲工ISICI MiHiProd ID ProdMame IhitPnce StockAnourrt11、設計一個簡單的 AFTERNSERT 觸發(fā)器，這個觸發(fā)器的作用是：在插入一條記錄的時候, 發(fā)出“又添加了一個學生的成績”的友好提示。先建立三個表：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 表中創(chuàng)建觸發(fā)器，在向 score 表中插入數(shù)據(jù)時，先檢待插入的數(shù)據(jù)中的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狀態(tài)丄，第】行 事務在觸發(fā)器中結(jié)束。批處理已中止。I13、創(chuàng)建一個觸發(fā)器，當刪除 stude nt 表中的數(shù)據(jù)時，需要判斷該數(shù)據(jù)對應的學生是否有 選課，如果有，也需要將其選課信息全部刪除。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 該學生的選課記錄也已經(jīng)刪除！ 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 表上創(chuàng)建一個 in stead of in sert觸發(fā)器，實現(xiàn)：當向表 score 插入記錄時檢查分數(shù)的合理性，如果不合理就不進行插入操作，否則允許。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 表上創(chuàng)建一個 in stead of delete 觸發(fā)器，實現(xiàn)：當從表 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成績?yōu)榭眨辉试S刪除該記錄！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 皿