實(shí)驗(yàn)6數(shù)據(jù)庫實(shí)驗(yàn)-存儲(chǔ)過程和觸發(fā)器

1、-. z.實(shí)驗(yàn)6 存儲(chǔ)過程和觸發(fā)器一、實(shí)驗(yàn)?zāi)康?、加深和穩(wěn)固對(duì)存儲(chǔ)過程和觸發(fā)器概念的理解。2、掌握觸發(fā)器的簡單應(yīng)用。3、掌握存儲(chǔ)過程的簡單應(yīng)用。二、實(shí)驗(yàn)內(nèi)容一存儲(chǔ)過程：1. 創(chuàng)立一存儲(chǔ)過程，求l+2+3+n，并打印結(jié)果。CREATEPROCEDURE addresult ASDECLARE n int=10,/*最后一個(gè)數(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存儲(chǔ)過程，打印l十2+3+10的結(jié)果。E*EC addresult3. 修改上述存儲(chǔ)過程為addresult1,使得n為輸入?yún)?shù)，其具體值由用戶調(diào)用此存儲(chǔ)過程時(shí)指定。CREATEPROCEDURE addresult1 n int=10 /*最后一個(gè)數(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存儲(chǔ)過程

3、，打印l+2+3+100的結(jié)果。E*EC addresult1 1005修改上述存儲(chǔ)過程為addresult2，將n參數(shù)設(shè)定默認(rèn)值為10，并改設(shè)sum為輸出參數(shù)，讓主程序能夠接收計(jì)算結(jié)果。CREATEPROCEDURE addresult2 n int=10,/*最后一個(gè)數(shù)*/sum intout/*結(jié)果*/ASDECLARE i int=0BEGINset sum=0WHILE(i=n)BEGINSET sum=sum+iSET i=i+1ENDENDGO6調(diào)用上面修改后的addresult2存儲(chǔ)過程，設(shè)置變量s接收計(jì)算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)立一存儲(chǔ)過程Proc_Student，用于顯示*為0102”的學(xué)生根本信息包括*、性別和系。CREATEPROCEDURE Proc_Student ASBEGINselect*from Swhere S.sno=0102ENDGOE*EC Proc_Student8創(chuàng)立一存儲(chǔ)過程Stu_grade，通過讀取*門課的編號(hào)，求出不及格的學(xué)生的*。CREATEPROCEDURE Stu_grade n char(10)ASBEGINselect snofrom SCwhereo=n and gr

5、ade=p1)之間的書的編號(hào)，書名，價(jià)格，出舨日期。如果用戶調(diào)用時(shí)沒有指定t參數(shù)的值則表示可為任意值，如用戶沒有指定p2，則書本價(jià)格沒有上限。用到的關(guān)系為：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)用上面的存儲(chǔ)過程search，求出書名與puter有關(guān)，而且價(jià)格小于

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

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

8、erdeleteasdeclare ame char(10);select ame=ame from deleted *sks;delete *scj whereame=ame;go4、創(chuàng)立觸發(fā)器trigger4，實(shí)現(xiàn)當(dāng)修改學(xué)生課程表*skc中的*門課的課程號(hào)時(shí)，對(duì)應(yīng)學(xué)生成績表*scj中的課程號(hào)也作相應(yīng)修改。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，實(shí)現(xiàn)當(dāng)向?qū)W生成績表*scj中插入一條選課記錄時(shí)，查看該學(xué)生的信息是否存在在學(xué)生信息表中，如果不存在，則把該學(xué)生的根本信息參加到學(xué)生信息表中。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、在學(xué)生成績庫中創(chuàng)立觸發(fā)器trigger6，實(shí)現(xiàn)如下功能：當(dāng)在學(xué)生成績表*scj中插入一條學(xué)生選課信息后，查看該學(xué)生的信息是否存在在學(xué)生信息表中，如果不存在，則給出該記錄不能被插入！的錯(cuò)誤提示，并撤銷插入操作；同樣，如果課程信息在課程信息表中不存在，給出該記錄不能被插入！的錯(cuò)誤提示，并撤銷插入操作。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，強(qiáng)制實(shí)現(xiàn)業(yè)務(wù)規(guī)則：當(dāng)向?qū)W生成績表中插入一條記錄時(shí)，自動(dòng)修改學(xué)生情況表中該學(xué)生的總學(xué)分，要求總學(xué)分為該學(xué)生所有已修課程的學(xué)分總和。createtrigger trigger7on *scjforinsertasbegintransactiondeclare credit int;select credit=credit from inserted *

12、scj;update *sqk set allcredit=allcredit+credit;mittransactiongo8、分別用觸發(fā)器和存儲(chǔ)過程實(shí)現(xiàn)對(duì)學(xué)生情況表*sqk和學(xué)生成績表*scj表的級(jí)聯(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)：當(dāng)向*scj表插入一條記錄時(shí)，判斷該學(xué)生的總學(xué)分，如果總學(xué)分大于等于25，則給出該學(xué)生已修滿，不需要再選修！的提示信息；否則，自動(dòng)更新該學(xué)生的總學(xué)分。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(該學(xué)生已修滿，不需要再選修！,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,銷售單價(jià) MONEY NULL,銷售金額 MONEY NULL)GO-業(yè)務(wù)規(guī)則：庫存金額 = 庫存數(shù)量 * 庫存單價(jià)業(yè)務(wù)規(guī)則。CREATE TABLE 卷煙庫存表(卷煙品牌 VARCHAR(40) PRIMARY KEY NOT NULL,庫存數(shù)量 INT NULL,庫存單價(jià) MONEY NULL,庫存金額 MONEY NULL)GO創(chuàng)立觸發(fā)器T_INSERT_卷煙庫存表，實(shí)現(xiàn)每當(dāng)卷煙庫存表發(fā)生 INSERT 動(dòng)作，則引發(fā)該觸發(fā)器。觸發(fā)器功能：強(qiáng)制執(zhí)行業(yè)務(wù)規(guī)則，保證插入的數(shù)據(jù)中，庫存金額 = 庫存數(shù)量 * 庫存單價(jià)。針對(duì)卷煙庫存表，插入測試數(shù)據(jù)：注

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

17、存單價(jià),庫存金額)values( *映像,100,60,500)INSERT INTO 卷煙庫存表(卷煙品牌,庫存數(shù)量,庫存單價(jià),庫存金額)values( ,0,30,0)11、創(chuàng)立觸發(fā)器T_INSERT_卷煙銷售表，實(shí)現(xiàn)每當(dāng)卷煙庫存表發(fā)生 INSERT 動(dòng)作，則引發(fā)該觸發(fā)器。觸發(fā)器功能：實(shí)現(xiàn)業(yè)務(wù)規(guī)則：如果銷售的卷煙品牌不存在庫存或者庫存為零，則返回錯(cuò)誤。否則則自動(dòng)減少卷煙庫存表中對(duì)應(yīng)品牌卷煙的庫存數(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、利用學(xué)生_課程數(shù)據(jù)庫中的SC表，編程實(shí)現(xiàn)：如果所有學(xué)生所有課程的平均成績高于80分，使用while循環(huán)就將每門成績減5分，然后查找所有學(xué)生所有課程中的最