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

1、-. z.實驗6 存儲過程和觸發(fā)器一、實驗目的1、加深和穩(wěn)固對存儲過程和觸發(fā)器概念的理解。2、掌握觸發(fā)器的簡單應用。3、掌握存儲過程的簡單應用。二、實驗內(nèi)容一存儲過程：1. 創(chuàng)立一存儲過程，求l+2+3+n，并打印結(jié)果。CREATEPROCEDURE addresult ASDECLARE n int=10,/*最后一個數(shù)*/ i int=0, result int=0 /*結(jié)果*/BEGINWHILE(i=n)BEGINSET result=result+iSET i=i+1ENDPRINT1+2+3+.+n的結(jié)果是:PRINT resultRETURN(result)ENDGO2調(diào)用上面的

2、addresult存儲過程，打印l十2+3+10的結(jié)果。E*EC addresult3. 修改上述存儲過程為addresult1,使得n為輸入?yún)?shù)，其具體值由用戶調(diào)用此存儲過程時指定。CREATEPROCEDURE addresult1 n int=10 /*最后一個數(shù)*/ASDECLARE i int=0, result int=0 /*結(jié)果*/BEGINWHILE(i=n)BEGINSET result=result+iSET i=i+1ENDPRINT1+2+3+.+n的結(jié)果是:PRINT resultRETURN(result)ENDGO4.調(diào)用上面修改后的addresult1存儲過程

3、，打印l+2+3+100的結(jié)果。E*EC addresult1 1005修改上述存儲過程為addresult2，將n參數(shù)設定默認值為10，并改設sum為輸出參數(shù)，讓主程序能夠接收計算結(jié)果。CREATEPROCEDURE addresult2 n int=10,/*最后一個數(shù)*/sum intout/*結(jié)果*/ASDECLARE i int=0BEGINset sum=0WHILE(i=n)BEGINSET sum=sum+iSET i=i+1ENDENDGO6調(diào)用上面修改后的addresult2存儲過程，設置變量s接收計算l+2+3+10的結(jié)果。DECLARE s intset s=0E*EC

4、 addresult2 10,sum=s outPRINT1+2+3+.+n的結(jié)果是:PRINT s7創(chuàng)立一存儲過程Proc_Student，用于顯示*為0102”的學生根本信息包括*、性別和系。CREATEPROCEDURE Proc_Student ASBEGINselect*from Swhere S.sno=0102ENDGOE*EC Proc_Student8創(chuàng)立一存儲過程Stu_grade，通過讀取*門課的編號，求出不及格的學生的*。CREATEPROCEDURE Stu_grade n char(10)ASBEGINselect snofrom SCwhereo=n and gr

5、ade=p1)之間的書的編號，書名，價格，出舨日期。如果用戶調(diào)用時沒有指定t參數(shù)的值則表示可為任意值，如用戶沒有指定p2，則書本價格沒有上限。用到的關系為：titles (title_id,title,price,pubdate)。CREATEPROCEDURE search t char(10)=%,p1 char(10),p2 char(10)=NULLASBEGINselect title_id,title,price,pubdatefrom titleswhere title=tand price=p1ENDGO14調(diào)用上面的存儲過程search，求出書名與puter有關，而且價格小于

6、$20大于$10的書。E*EC search puter,10,20二觸發(fā)器：1、在學生成績庫中創(chuàng)立觸發(fā)器trigger1，實現(xiàn)如下功能：當在學生成績表*scj中插入一條學生選課信息后，自動實現(xiàn)更新該學生在學生情況表*sqk中的總學分信息。分析：根據(jù)題意，也即要求在學生成績表中插入一條記錄時，自動更新學生情況表中的相應記錄信息?？梢酝ㄟ^在學生成績表中定義INSERT類型的觸發(fā)器，觸發(fā)器中語句要完成的功能是更新學生情況表中的相應學生的總學分信息。其實，只要在該生原總學分根底上加上新選課程的學分就可以了。createtrigger trigger1on *scjafterinsertasdecla

7、re credit int;select credit=credit from inserted *scj;update *sqk set allcredit=allcredit+credit;go2、創(chuàng)立觸發(fā)器trigger2，實現(xiàn)當修改學生課程表*skc中的數(shù)據(jù)時，顯示提示信息學生課程表被修改了。createtrigger trigger2on *skcafterupdateasprint學生課程表被修改了;go3、創(chuàng)立觸發(fā)器trigger3，實現(xiàn)當刪除學生課程表中*門課程的記錄時，對應學生成績表中所有有關此課程的記錄均刪除。createtrigger trigger3on *skcaft

8、erdeleteasdeclare ame char(10);select ame=ame from deleted *sks;delete *scj whereame=ame;go4、創(chuàng)立觸發(fā)器trigger4，實現(xiàn)當修改學生課程表*skc中的*門課的課程號時，對應學生成績表*scj中的課程號也作相應修改。createtrigger trigger4on *skcafterupdateasdeclare o char(10);declare ame char(10);select ame=*skc.ame,o=*skc.o from updated *sks;update *scj set

9、*scj.o=o where *scj.ame=ame;go5、創(chuàng)立觸發(fā)器trigger5，實現(xiàn)當向?qū)W生成績表*scj中插入一條選課記錄時，查看該學生的信息是否存在在學生信息表中，如果不存在，則把該學生的根本信息參加到學生信息表中。createtrigger trigger5on *scjafterinsertasdeclare sname1 char(10);declare sname2 char(10)=NULL;select sname1=*scj.sname from updated *scj;select sname2=*sqk.sname from *sqk where *sqk.

10、sname=sname1;if sname2=NULLinsertinto *sqk values(NULL,sname1);go6、在學生成績庫中創(chuàng)立觸發(fā)器trigger6，實現(xiàn)如下功能：當在學生成績表*scj中插入一條學生選課信息后，查看該學生的信息是否存在在學生信息表中，如果不存在，則給出該記錄不能被插入！的錯誤提示，并撤銷插入操作；同樣，如果課程信息在課程信息表中不存在，給出該記錄不能被插入！的錯誤提示，并撤銷插入操作。createtrigger trigger6on *scjforinsertasbeginifnote*ists(select *sqk.snamefrom *sqk

11、where *sqk.sname in(select *scj.sname from inserted *scj)beginraiserror(該記錄不能被插入！,16,1)rollbackreturnendendgo7、創(chuàng)立觸發(fā)器trigger7，強制實現(xiàn)業(yè)務規(guī)則：當向?qū)W生成績表中插入一條記錄時，自動修改學生情況表中該學生的總學分，要求總學分為該學生所有已修課程的學分總和。createtrigger trigger7on *scjforinsertasbegintransactiondeclare credit int;select credit=credit from inserted *

12、scj;update *sqk set allcredit=allcredit+credit;mittransactiongo8、分別用觸發(fā)器和存儲過程實現(xiàn)對學生情況表*sqk和學生成績表*scj表的級聯(lián)刪除。createtrigger trigger8 on *sqk afterdeleteasdeletefrom *scj where *scj.sname in(select *sqk.sname from deleted *sqk)goCREATEPROCEDURE del_qk_cj sname char(10)=NULLASBEGINdeletefrom *scj where *sc

13、j.sname=sname;deletefrom *sqk where *sqk.sname=sname;ENDGO9、創(chuàng)立觸發(fā)器8，要*現(xiàn)：當向*scj表插入一條記錄時，判斷該學生的總學分，如果總學分大于等于25，則給出該學生已修滿，不需要再選修！的提示信息；否則，自動更新該學生的總學分。createtrigger trigger88on *scjforinsertasbegindeclare credit1 int;declare credit2 int;declare sname char(10);select credit1=*scj.credit,sname=*scj.sname f

14、rom inserted *scj;select credit2=*sqk.allcredit from *sqk where *sqk.sname=sname;if(credit1+credit2)=25beginraiserror(該學生已修滿，不需要再選修！,16,1)rollbackreturnendelseinsertinto *scj values(credit1,NULL,NULL,sname);endgo10、在數(shù)據(jù)庫中用以下語句創(chuàng)立兩*表：CREATE TABLE 卷煙銷售表(卷煙品牌 VARCHAR(40) PRIMARY KEY NOT NULL,購貨商 VARCHAR(

15、40) NULL,銷售數(shù)量 INT NULL,銷售單價 MONEY NULL,銷售金額 MONEY NULL)GO-業(yè)務規(guī)則：庫存金額 = 庫存數(shù)量 * 庫存單價業(yè)務規(guī)則。CREATE TABLE 卷煙庫存表(卷煙品牌 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è)務規(guī)則，保證插入的數(shù)據(jù)中，庫存金額 = 庫存數(shù)量 * 庫存單價。針對卷煙庫存表，插入測試數(shù)據(jù)：注

16、意，第一條數(shù)據(jù)紅塔山新勢力中的數(shù)據(jù)符合業(yè)務規(guī)則，第二條數(shù)據(jù)紅塔山人為峰中，庫存金額空，不符合業(yè)務規(guī)則，第三條數(shù)據(jù)*映像中，庫存金額不等于庫存數(shù)量乘以庫存單價，不符合業(yè)務規(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 卷煙庫存表(卷煙品牌,庫存數(shù)量,庫

17、存單價,庫存金額)values( *映像,100,60,500)INSERT INTO 卷煙庫存表(卷煙品牌,庫存數(shù)量,庫存單價,庫存金額)values( ,0,30,0)11、創(chuàng)立觸發(fā)器T_INSERT_卷煙銷售表，實現(xiàn)每當卷煙庫存表發(fā)生 INSERT 動作，則引發(fā)該觸發(fā)器。觸發(fā)器功能：實現(xiàn)業(yè)務規(guī)則：如果銷售的卷煙品牌不存在庫存或者庫存為零，則返回錯誤。否則則自動減少卷煙庫存表中對應品牌卷煙的庫存數(shù)量和庫存金額。createtrigger T_INSERT_卷煙銷售表on 卷煙銷售表forinsertasbegintransactionifnote*ists(select 卷煙品牌from

18、卷煙庫存表where 卷煙品牌in(select 卷煙品牌from inserted 卷煙庫存表)beginraiserror(該卷煙不存在于庫存，不能銷售！,16,1);rollbackreturnendife*ists(select 庫存數(shù)量from 卷煙庫存表where 卷煙品牌in(select 卷煙品牌from inserted 卷煙庫存表)and 庫存數(shù)量=90 then優(yōu)秀when SC.grade=80 then良好when SC.grade=70 then中等when SC.grade=60 then及格when SC.grade=0 then不及格else未考endfrom SC,S,Cwhere SC.o=C.o and SC.sno=S.snoorderby SC.grade descENDGO2、利用學生_課程數(shù)據(jù)庫中的SC表，編程實現(xiàn)：如果所有學生所有課程的平均成績高于80分，使用while循環(huán)就將每門成績減5分，然后查找所有學生所有課程中的最