實驗6數(shù)據(jù)庫實驗——存儲過程和觸發(fā)器

1、實驗6存儲過程和觸發(fā)器 、實驗?zāi)康?1、加深和鞏固對存儲過程和觸發(fā)器概念的理解。 2、掌握觸發(fā)器的簡單應(yīng)用。 3、掌握存儲過程的簡單應(yīng)用。 二、實驗內(nèi)容 一) 存儲過程： 1. 創(chuàng)建一存儲過程，求1+2+3+n,并打印結(jié)果。 CREATEPROCEDUFaddresult AS DECLAREn int =10, /* 最后一個數(shù) */ iint =0, resultint=0/* 結(jié)果 */ BEGIN WHILE =n BEGIN SETresult =result +i SETi =i+1 END PRINT 1+2+3+.+n 的結(jié)果是: PRINTresult RETURNresul

2、t ) END GO 2. 調(diào)用上面的addresult存儲過程，打印l十2+3+10的結(jié)果 EXECaddresult 3修改上述存儲過程為addresultl,使得n為輸入?yún)?shù)，其具體值 由用戶調(diào)用此存儲過程時指定。 CREATEPROCEDURSdresuItl n int =10 /* 最后一個數(shù) */ AS DECLAREi int =0, resultint =0/* 結(jié)果 */ BEGIN WHILE =n BEGIN SETresult =result +i SETi =i+1 END PRINT1+2+3+.+n 的結(jié)果是: PRINTresult RETURNresult

3、) END GO * 命令已成9閘克 4. 調(diào)用上面修改后的addresult1存儲過程，打印l+2+3+100的 結(jié)果。 EXECaddresult1 100 Jj消息 1+23+._ 令啲結(jié)果是: 5. 修改上述存儲過程為 addresult2，將n參數(shù)設(shè)定默認值為10, 并改設(shè)sur為輸出參數(shù)，讓主程序能夠接收計算結(jié)果。 CREATEPR0CEDUREddresult2 n int =10, /* 最后一個數(shù) */ sumint out /* 結(jié)果 */ AS DECLAREi int =0 BEGIN set sum=0 WHILE =n BEGIN SETsum=sumi SETi

4、=i+1 END END GO 6. 調(diào)用上面修改后的addresult2 存儲過程，設(shè)置變量$接收計算 1+2+3+ 10的結(jié)果 DECLARERS int set s=0 EXECaddresult2 10, sums out PRINT 1+2+3+.+n 的結(jié)果是: PRINT s 7. 創(chuàng)建一存儲過程Proc_Student，用于顯示學(xué)號為“ 0102”的學(xué)生 基本信息（包括學(xué)號、姓名、性別和系）。 CREATEPROCEDUFPfoc_Stude nt AS BEGIN select * from S where S . sno=0102 END GO EXECProc_Stude

5、 nt 8創(chuàng)建一存儲過程Stu_grade，通過讀取某門課的編號，求出不及 格的學(xué)生的學(xué)號。 CREATEPROCEDUFStu_grade n char ( 10) AS BEGIN select sno from SC where eno =n and grade =p1) 之間的書的編號，書名，價格，出舨日期。如果用戶調(diào)用時沒有指定 七參數(shù)的值.則表示可為任意值，如用戶沒有指定p2則書本價格 沒有上限。用到的關(guān)系為：titles (title_id,title,price,pubdate)。 CREATEPROCEDUREsarch t char ( 10)=%, p1 char (10

6、), p2 char (10)=NULL AS BEGIN select title_id , title , price , pubdate from titles where title =t and price =p1 END GO 14. 調(diào)用上面的存儲過程search，求出書名與computer有關(guān)，而且 價格小于$20大于$10的書。 EXECsearch computer , 10, 20 二）觸發(fā)器： 1、在學(xué)生成績庫中創(chuàng)建觸發(fā)器trigger1，實現(xiàn)如下功能：當在學(xué)生 成績表（xscj ）中插入一條學(xué)生選課信息后，自動實現(xiàn)更新該學(xué)生在 學(xué)生情況表（xsqk）中的總學(xué)分信息。

7、分析：根據(jù)題意，也即要求在學(xué)生成績表中插入一條記錄時，自動更 新學(xué)生情況表中的相應(yīng)記錄信息?？梢酝ㄟ^在學(xué)生成績表中定義 INSERT類型的觸發(fā)器，觸發(fā)器中語句要完成的功能是更新學(xué)生情況 表中的相應(yīng)學(xué)生的總學(xué)分信息。其實，只要在該生原總學(xué)分基礎(chǔ)上加 上新選課程的學(xué)分就可以了。 create trigger trigger1 on xscj after in sert as declare credit int ; select credit =credit from in serted xscj ; update xsqk set allcredit =allcredit +credit ; g

8、o 2、創(chuàng)建觸發(fā)器trigger2 ，實現(xiàn)當修改學(xué)生課程表(xskc )中的數(shù)據(jù) 時，顯示提示信息“學(xué)生課程表被修改了”。 create trigger trigger2 on xskc after update as print 學(xué)生課程表被修改了 ； go 3、 創(chuàng)建觸發(fā)器trigger，實現(xiàn)當刪除學(xué)生課程表中某門課程的記錄 時，對應(yīng)學(xué)生成績表中所有有關(guān)此課程的記錄均刪除。 create trigger trigger3 on xskc after delete as declare cname char (10); select cname=cname from deleted xsks

9、 ; delete xscj where cname=cname go t 4、 創(chuàng)建觸發(fā)器trigger4，實現(xiàn)當修改學(xué)生課程表(xskc )中的某門 課的課程號時，對應(yīng)學(xué)生成績表(xscj )中的課程號也作相應(yīng)修改。 create trigger trigger4 on xskc after update as declare cno char (10); declare cname char (10); select cname=xskc . cname, cn(=xskc . eno from updated xsks update xscj set xscj .eno =c no w

10、here xscj . cn ame=c name go 命令已成功芫成鼻 5、 創(chuàng)建觸發(fā)器trigger5 ，實現(xiàn)當向?qū)W生成績表(xscj )中插入一條 選課記錄時，查看該學(xué)生的信息是否存在在學(xué)生信息表中，如果不存 在，則把該學(xué)生的基本信息加入到學(xué)生信息表中。 create trigger trigger5 on xscj after in sert as declare sname1 char (10); declare sname2 char (10)=NULL; select sname仁xscj . sname from updated xscj ; select sname2=xs

11、qk. sname from xsqk where xsqk . sname=sname1 if sname2=NULL insert into xsqk values (NULL, sname); go 岳酒JS 命令已成功 6、 在學(xué)生成績庫中創(chuàng)建觸發(fā)器trigger6 ，實現(xiàn)如下功能：當在學(xué)生 成績表(xscj )中插入一條學(xué)生選課信息后，查看該學(xué)生的信息是否 存在在學(xué)生信息表中，如果不存在，則給出“該記錄不能被插入！” 的錯誤提示，并撤銷插入操作；同樣，如果課程信息在課程信息表中 不存在，給出“該記錄不能被插入！ ”的錯誤提示，并撤銷插入操作。 create trigger trigg

12、er6 on xscj for in sert as begin if not exists (select xsqk . sname from xsqk where xsqk . sname in( select xscj . sname from inserted xscj ) ) begin raiserror (該記錄不能被插入！，16, 1) rollback return end end go 7、創(chuàng)建觸發(fā)器trigger7 ，強制實現(xiàn)業(yè)務(wù)規(guī)則：當向?qū)W生成績表中插 入一條記錄時，自動修改學(xué)生情況表中該學(xué)生的總學(xué)分，要求總學(xué)分 為該學(xué)生所有已修課程的學(xué)分總和。 create trig

13、ger trigger7 on xscj for in sert as beg in tra nsacti on declare credit int ; select credit =credit from in serted xscj ; update xsqk set allcredit =allcredit +credit ; commit transaction go 8分別用觸發(fā)器和存儲過程實現(xiàn)對學(xué)生情況表（xsqk）和學(xué)生成績 表（xscj ）表的級聯(lián)刪除。 create trigger trigger8 on xsqk after delete as delete from x

14、scj where xscj . sname in (select xsqk . sname from deleted xsqk ) go - (seised.nnanvs Ercn1.xsqk； J消息 命令已成功完成。 CREATEPROCEDURBI_qk_cj snamechar (10)=NULL AS BEGIN delete from xscj where xscj . sname=sname delete from xsqk where xsqk . sname=sname END GO G CREATE PRO匚EDURE del_qk_cj Ssnair.e char (1

15、0 =1IULL AS i BEGIN Edelete fror xacj where xscj .3name=3nairer Edelete Iron xsqk where xsqk廳斑慫=姑二邁已: Lend 9、創(chuàng)建觸發(fā)器8,要求實現(xiàn)：當向xscj表插入一條記錄時，判斷該 學(xué)生的總學(xué)分，如果總學(xué)分大于等于 25,則給出“該學(xué)生已修滿， 不需要再選修！”的提示信息；否則，自動更新該學(xué)生的總學(xué)分。 create trigger trigger88 on xscj for in sert as begin declare credit1int ; declare credit2int ; de

16、clare sname char (10); select credit1 =xscj . credit , snamexscj . sname from inserted xscj ; select credit2 =xsqk. allcredit from xsqk where xsqk . snameesname if (credit1 +credit2 )=25 begin raiserror (該學(xué)生已修滿，不需要再選修！，16,1) rollback return end else insertinto xscj values ( credit1 ,NULL,NULL, sname

17、 end go H EXeate 匕工jiggut C1 qyel _ 2 fcr inseit b llejldle丄2 LDLr delaze ch虻 不需要再選謬！，”応1 xeJtuzn. end cl insezc xnts xsjj values (ficiediblrirCTLLr JULLP Ssiisiite) *nd 10、在數(shù)據(jù)庫中用以下語句創(chuàng)建兩張表: CREATE TABLED煙銷售表 ( 卷煙品牌 VARCHAR(40) PRIMARY KEY NOT NULL, 購貨商 VARCHAR(40) NULL, 銷售數(shù)量 INT NULL, 銷售單價 MONEY NUL

18、L, 銷售金額 MONEY NULL ） GO - 業(yè)務(wù)規(guī)則：庫存金額 = 庫存數(shù)量 * 庫存單價 業(yè)務(wù)規(guī)則。 CREATE TABL卷煙庫存表 （ 卷煙品牌 VARCHAR（40） PRIMARY KEY NOT NULL, 庫存數(shù)量 INT NULL, 庫存單價 MONEY NULL, 庫存金額 MONEY NULL ） GO 創(chuàng)建觸發(fā)器T_INSERT_卷煙庫存表，實現(xiàn)每當卷煙庫存表發(fā)生 INSERT 動作，則引發(fā)該觸發(fā)器。觸發(fā)器功能：強制執(zhí)行業(yè)務(wù)規(guī)則， 保證插入的數(shù)據(jù)中，庫存金額 = 庫存數(shù)量 * 庫存單價。 針對 卷煙庫存表 ，插入測試數(shù)據(jù)：注意，第一條數(shù)據(jù)（紅塔山新勢 力）中的數(shù)據(jù)

19、符合業(yè)務(wù)規(guī)則，第二條數(shù)據(jù)（紅塔山人為峰）中， 庫 存金額 空，不符合業(yè)務(wù)規(guī)則，第三條數(shù)據(jù)（云南映像）中， 庫存金 額 不等于 庫存數(shù)量 乘以 庫存單價 ，不符合業(yè)務(wù)規(guī)則。第四條數(shù) 據(jù)庫存數(shù)量為 精品 0。請注意在插入數(shù)據(jù)后， 檢查卷煙庫存表 中的數(shù)據(jù)是否 庫存金額 = 庫存數(shù)量 * 庫存單價。 INSERT INTO 卷煙庫存表 （卷煙品牌,庫存數(shù)量 ,庫存單價,庫存金額） values（ 紅塔山新勢力 ,100,12,1200） INSERT INTO 卷煙庫存表 （卷煙品牌,庫存數(shù)量 ,庫存單價,庫存金額） values（ 紅塔山人為峰 ,100,22,null） INSERT INTO

20、卷煙庫存表 （卷煙品牌,庫存數(shù)量 ,庫存單價,庫存金額） values（ 云南映像 ,100,60,500） INSERT INTO 卷煙庫存表 （卷煙品牌,庫存數(shù)量 ,庫存單價,庫存金額） values（ 玉溪 ,0,30,0） IERT IWTO總煙庫存表（卷煙品牌庫存魏量庫存單價庫存金額! -val口電呂I 1紅塔山新勢力MOO, 12,1200J iert into卷煙庫存表港煙品牌#庫存數(shù)量庫存單價庫存金諏 valuesi 紅塔山人為峰 :1 JNSEKT INTO卷煙宦存表（卷煙品牌#庫彳子齣量*庫存單價”庫存金飆 -values 云南映橡 1 ,100, 60r S0C 卜対吋I

21、NTO罷煙庫存表（卷煙品牌庫存數(shù)量，庫存單價，庫存金額 L values （ 1 FEjB , 0,30, 0 由涓息 門行受購向） 1行受臺響 11行受勒向 1彳亍受覺響 （1行受影響） U疔受影的 1行登覺響） 丄彳亍受覺響） 站果 J消息 卷咽品牌庫存數(shù)里庫存單價庫存金額 1 100 22.M) 2200 00 2 100 12.M 120000 3 玉溪 0 D.W 云南腆隰 100 M.W 00000 11、創(chuàng)建觸發(fā)器T_INSERT_卷煙銷售表，實現(xiàn)每當卷煙庫存表發(fā)生 INSERT動作，則引發(fā)該觸發(fā)器。觸發(fā)器功能：實現(xiàn)業(yè)務(wù)規(guī)則：如果 銷售的卷煙品牌不存在庫存或者庫存為零，則返回錯誤

22、。否則則自動 減少卷煙庫存表中對應(yīng)品牌卷煙的庫存數(shù)量和庫存金額。 create trigger T_INSERT_ 卷煙銷售表 on卷煙銷售表 for in sert as begin transaction if not exists（ select卷煙品牌 from卷煙庫存表 where卷煙品牌in（ select 卷煙品牌 from inserted卷煙庫存表） begin raiserror （該卷煙不存在于庫存，不能銷售！，16,1）; rollback return end if exists（ select庫存數(shù)量 from卷煙庫存表 where卷煙品牌in（ select卷煙品牌 from inserted 卷煙庫存表） and庫存數(shù)量=0） begin raiserror （該卷煙庫存小于等于，不能銷售！ , 16, 1）; rollback return end update卷煙庫存表 set庫存金額=庫存數(shù)量*庫存單價 where卷煙品牌in （select 卷煙品牌from inserted卷煙庫存表） declare 卷煙品牌 VARCHAR40） set 卷煙品牌=（select 卷煙品牌from inserted卷煙銷售表） declare 銷售數(shù)量INT set 銷售數(shù)量=（select 銷售數(shù)量from inserted卷煙銷售表） updat