實(shí)驗(yàn)四存儲(chǔ)與觸發(fā)器資料

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

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

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

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

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

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

7、nfo 的存儲(chǔ)過(guò)程，它返回學(xué)生的學(xué)號(hào)、姓名、性別、班級(jí)編號(hào)、年級(jí)和籍貫信息。select 學(xué)號(hào)，姓名，性別，班級(jí)編號(hào)，年級(jí)，籍貫 from學(xué)生信息create procedureProcStude ntl nfoas/SQLQueryl.ql LB.ministrator (53)* create procedure ProcStudentlnfoas嘗曇姓盤(pán)性別，班級(jí)編懸生級(jí)臓from嘗生信息-J結(jié)果 命令已成功完成。2、用 EXECUT 執(zhí)行 Proc_Studentlnfo 存儲(chǔ)過(guò)程。executeProc Studentlnfo學(xué)號(hào)姓名性別班級(jí)編號(hào)年級(jí)藉貫1200030000041徐紅

8、男200213400001012000湖南2200030000CM2楊根男200213400001012000湖南3200030000043殷實(shí)玄200213400001032000湖南4200030000044于莉女20021WJ101022000湖南5200130000116200313400701022001觸南E200130000117姚子龍男200313400701022001湖南7200130000118俞翔女2003134Q0701022001湖南8200130000119男200313400701022001湖聞9200130000120男200313400701022001湖

9、南ABB(100 RTM)miAdminiTtrator(52)教務(wù)管理系揚(yáng)00:0000 232行3、創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，用于返回“教務(wù)管理系統(tǒng)”數(shù)據(jù)庫(kù)上某個(gè)班級(jí)中所有學(xué)生的信息CREATE PROCEDURE Proc_GetClassStudentlClasslD varchar (14)ASSELECT 學(xué)號(hào)，姓名，性別，班級(jí)編號(hào)，年級(jí)，籍貫 FROM 學(xué)生信息WHERE 班級(jí)編號(hào)=ClasslDSQLQuery5Fsql - LE.ministrator (57)* SQLQuery4.Sql - LBf,.nnini5trator 54)* SQL國(guó)查詢(xún)已咸功執(zhí)行. CREATE P

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

11、DNl結(jié)果|力消息字號(hào)姓名班級(jí)編號(hào)|性別| 年馭|政治面貌學(xué)籍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í)行存儲(chǔ)過(guò)程。EXEC Proc_GetClassStudent2EXEC Proc_GetClassStudent220031340000101EXEC Proc_GetClassStudent2ClassID =20031340000104createprocedureProc

13、 GetClassStudent3banji char ( 14), out numeric (3,0) outputasselectout =人數(shù) from 班級(jí)信息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)建一個(gè)存儲(chǔ)過(guò)程，用于返回“教務(wù)管理系統(tǒng)”數(shù)據(jù)庫(kù)上某個(gè)班級(jí)中的學(xué)生人數(shù)，默認(rèn)班級(jí)編號(hào)為 20031340000102use 教務(wù)管理系統(tǒng) goCREATE PROCEDURE Proc GetClassStudent3 ClasslD varchar ( 14)= 20031340000102 asSELECT count （學(xué)號(hào)）as 學(xué)生人數(shù) FROM 學(xué)生信息 where 班級(jí)編號(hào)=classidEXECUTE Proc GetClassStudent37、定義一個(gè)變量用來(lái)存儲(chǔ)存儲(chǔ)過(guò)程Proc_GetClassStudent3 返回的

15、值use 教務(wù)管理系統(tǒng)go20031340000102貉果| _J消息|屮二結(jié)果二消息pi( (iiimnmik1 380 declare二匸nutteric 3r0execute Proc GetCLaflident3 20031340000102 .黒cut output -select電out aa”人姿攵where 班級(jí)編號(hào)=banji and banji =20031340000102*MMFTEVJWnin卻硝PHIEE. “ im-PFIWKinrjfcHfh.IT藥時(shí)KKMQ U琦declareout n umeric (3,0)executeProc_GetClassStud

16、e nt3select out as 人數(shù)20031340000102,out output!期K ftVQL-QMIYIwn FmjiMiF*. rI. 4iQ 魚(yú) QLQuery 14.sqI - Idministrator (53) SQLQueryl3,sq| - administrator (60)*SQLQuery 12-qI 8 創(chuàng)建一個(gè)返回執(zhí)行代碼為 100 的存儲(chǔ)過(guò)程。USE 教務(wù)管理系統(tǒng)GOCREATE PROCEDURE Proc_GetClassStudent4ClasslD varchar ( 14)ASBEGINSELECT 學(xué)號(hào)，姓名，性別，班級(jí)編號(hào)，年級(jí)，籍貫

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

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

19、一個(gè)名為 GetFactProd 的存儲(chǔ)過(guò)程，用于實(shí)現(xiàn)：由執(zhí)行該存 儲(chǔ)過(guò)程時(shí)提供的廠(chǎng)家的名稱(chēng)，查詢(xún)其生產(chǎn)的產(chǎn)品信息，返回產(chǎn)品的ProdID、ProdNameUnitPrice 和 StockAmount。（使用“四川綿陽(yáng)”和“上海黃埔”兩個(gè)廠(chǎng)家的名稱(chēng)執(zhí)行存儲(chǔ)過(guò)程）use commoditysell gocreateprocedureGetFactProd 名char (8)asselectProdID ,ProdName , UnitPrice, StockAmou ntfrom ProductI nfowhereFactAddr=T 名W.WKUI 411 HLIWI HI；i *，* e

20、. 乂廠(chǎng)斗 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四川綿陽(yáng)上海黃埔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甲電視機(jī)200Q120t050201甲空調(diào)30QQ115PtodlDProdNarrteUnitPftceStockMiourrt1i 060303丙洗衣機(jī)800如2060103丙電視機(jī)1580S911、在 commoditysell 中創(chuàng)建一個(gè)名為 SellPro

22、dAmount 的存儲(chǔ)過(guò)程，用于實(shí)現(xiàn)：由執(zhí)行該 存儲(chǔ)過(guò)程時(shí)提供的商場(chǎng)的名稱(chēng)，查詢(xún)其銷(xiāo)售的產(chǎn)品數(shù)量，并用輸出參數(shù)輸出銷(xiāo)量數(shù)。(使用“雙橋子商場(chǎng)”和“十陵商場(chǎng)”兩個(gè)商場(chǎng)名稱(chēng)執(zhí)行程存儲(chǔ)過(guò)程)use commoditysellgocreate procedureSellProdAmount解稱(chēng) varchar ( 10), 數(shù)量 varchar (10) outputasselect數(shù)量=SellAmoutfrom EmporiumSellwhere EmpID =( select EmpID from EmporiumInfowhere EmpName = 名稱(chēng) )胄目曲工ISICI MiHi A

23、EMSE祠IIIWHWIMM|WI中 3 活*”髦 “二CftA 3 dk.E.Ivfn-uT4rdc-i- 3i m豪沁黑詢(xún)乂*MU丄A A 可怖今亠訂件riff*Ji WHlN|J bporeblH9Pitxntsri j遜E說(shuō)陽(yáng)Eign . i rtnut omM-亠WHFVJH4nTjQUi?aFi；tVH用科叭.MTFFvlbdE理.QC?n口兩executeGetFactProd雙橋子商場(chǎng)executeGetFactProd十陵商場(chǎng)|/5Q|jQueryl2jql - Ujninistrator(M)j* &QLQyeryll.sql * Lministrator (5SJ

24、)* execute GecFacProd取橋干裔場(chǎng),execute GecFacProd十顯費(fèi)商場(chǎng)*r=結(jié)果j消息Prod ID Prod Name UnitPnce StockAmourt磧金叭 2H!g 十*M* iWrf gg*11*n X胄目曲工ISICI MiHiProd ID ProdMame IhitPnce StockAnourrt11、設(shè)計(jì)一個(gè)簡(jiǎn)單的 AFTERNSERT 觸發(fā)器，這個(gè)觸發(fā)器的作用是：在插入一條記錄的時(shí)候, 發(fā)出“又添加了一個(gè)學(xué)生的成績(jī)”的友好提示。先建立三個(gè)表：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 又添加了一個(gè)學(xué)生的成績(jī)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消息又添加了一個(gè)學(xué)生的成績(jī)1行受彩I向）12、在 score 表中創(chuàng)建觸發(fā)器，在向 score 表中插入數(shù)據(jù)時(shí)，先檢待插入的數(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 不存在這樣的學(xué)生學(xué)號(hào)！-raiserror(不存在這樣的學(xué)生學(xué)號(hào)！,16,20)endendvrywv4iTnri0wi UJjjCREATE-i r19ge Ean s聶beinch4X 1 9from臭匚悄&匸where gg- select總

28、玷frcaif gm no _*二：beginrollback transactionPtint不存在這樣的學(xué)生學(xué)號(hào)！ *-raiserrort*不存在這樣的學(xué)生學(xué)是!endin sertinto score values ( 20110523,13,80 )endQLQueryi7+sqi -特trmt圳QLQuer/lb.sql - L.mimstra.nsert into score values(20110523,13,SO)又添加了一個(gè)學(xué)生的成績(jī): 1行愛(ài)影響1USE commoditysellGOINSERT INTO scorevalues (20110529,13,80)SQL

29、Queryl7j - L-ministrator (BSB*SQLQuerylg.sql -USE cozrancditysellGO INSERT INTO score-valuesr13f80：J消息又添力口了 一個(gè)學(xué)生的成績(jī)又灘加了一個(gè)學(xué)主即愈績(jī)不存在這樣的學(xué)主學(xué)號(hào)！消息歸曲，級(jí)別i6j狀態(tài)丄，第】行 事務(wù)在觸發(fā)器中結(jié)束。批處理已中止。I13、創(chuàng)建一個(gè)觸發(fā)器，當(dāng)刪除 stude nt 表中的數(shù)據(jù)時(shí)，需要判斷該數(shù)據(jù)對(duì)應(yīng)的學(xué)生是否有 選課，如果有，也需要將其選課信息全部刪除。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 該學(xué)生的選課記錄也已經(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消息又濟(jì)加f一個(gè)學(xué)勺 不存在這祥的學(xué)2匚的成績(jī)-學(xué)虧！14、在 score 表上創(chuàng)建一個(gè) in stead of in sert觸發(fā)器，實(shí)現(xiàn)：當(dāng)向表 score 插入記錄時(shí)檢查分?jǐn)?shù)的合理性，如果不合理就不進(jìn)行插入操作，否則允許。CREATE trigger add_sc3 on scorein stead of insertasbegindeclare grade intselect grade =gradefromin sertedif grade 100beginrollbacktran sa

32、cti onpri nt該學(xué)生的成績(jī)不合理！end end* jfc!財(cái)齢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)建一個(gè) in stead of delete 觸發(fā)器，實(shí)現(xiàn)：當(dāng)從表 score 刪除記錄時(shí) 檢查各課程的成績(jī)是否為空，如果為空就不允許進(jìn)行刪除操作，否則允許。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買(mǎi)吃IQLHA 戢”if ( grade is NULL)print成績(jī)?yōu)榭?，不允許刪除該記錄！elsedelete from score where sno = sno and eno =cno end3EW*Inrr*i MArM MbUi -JHI HUIW| mili VBIMI2*| 上j j *JIM 二二 見(jiàn)h翠417】Hri VA miiFiKUttf 1(14：時(shí)*-枷1EILTE x*l$gBE勻HC3ISCm；JfFr KMffiO Jh- NOVis一 岸廠(chǎng)*龍打llkiw hnrwf iwI ECmvWHihiMls N BlEwXViflUA4 皿