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

1、 實(shí)驗(yàn)6 存儲過程和觸發(fā)器一、實(shí)驗(yàn)?zāi)康?、加深和鞏固對存儲過程和觸發(fā)器概念的理解。2、掌握觸發(fā)器的簡單應(yīng)用。3、掌握存儲過程的簡單應(yīng)用。二、實(shí)驗(yàn)容一）存儲過程：1. 創(chuàng)建一存儲過程，求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+1ENDPRINT'1+2+3+.+n的結(jié)果是:'PRINT resultRETURN(result)E

2、NDGO2調(diào)用上面的addresult存儲過程，打印l十2+3+10的結(jié)果。EXEC addresult3. 修改上述存儲過程為addresult1,使得n為輸入?yún)?shù)，其具體值由用戶調(diào)用此存儲過程時(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+1ENDPRINT'1+2+3+.+n的結(jié)果是:'PRINT resultRETURN(result)ENDGO

3、4. 調(diào)用上面修改后的addresult1存儲過程，打印l+2+3+100的結(jié)果。EXEC addresult1 1005修改上述存儲過程為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存儲過程，設(shè)置變量s接收計(jì)算l+2+3+1

4、0的結(jié)果。DECLARE s intset s=0EXEC addresult2 10,sum=s outPRINT'1+2+3+.+n的結(jié)果是:'PRINT s7創(chuàng)建一存儲過程Proc_Student，用于顯示學(xué)號為“0102”的學(xué)生基本信息（包括學(xué)號、性別和系）。CREATEPROCEDURE Proc_Student ASBEGINselect*from Swhere S.sno=0102ENDGOEXEC Proc_Student8創(chuàng)建一存儲過程Stu_grade，通過讀取某門課的編號，求出不及格的學(xué)生的學(xué)號。CREATEPROCEDURE Stu_grade n ch

5、ar(10)ASBEGINselect snofrom SCwhereo=n and grade<60ENDGO9調(diào)用上面的存儲過程Stu_grade，求出課程編號為“0101”的不及格的學(xué)生。EXEC Stu_grade 010110創(chuàng)建一存儲過程avgGrade，通過讀取學(xué)生的學(xué)號，以參數(shù)形式返回該學(xué)生的平均分。CREATEPROCEDURE avgGrade n char(10)ASBEGINselectAVG(grade) 平均分from SCwhere sno=ngroupby snoENDGO11調(diào)用上面的存儲過程avgGrade，求出學(xué)號為“990102014”的平均分。E

6、XEC avgGrade 99010201412刪除上述存儲過程avgGrade。dropprocedure avgGrade13.創(chuàng)建存儲過程search，該存儲過程有三個(gè)參數(shù)，分別為t、p1，p2，根據(jù)這些參數(shù)，找出書名與t有關(guān)，價(jià)格在p1與p2(p2>=p1)之間的書的編號，書名，價(jià)格，出舨日期。如果用戶調(diào)用時(shí)沒有指定t參數(shù)的值則表示可為任意值，如用戶沒有指定p2，則書本價(jià)格沒有上限。用到的關(guān)系為：titles (title_id,title,price,pubdate)。CREATEPROCEDURE search t char(10)="%",p1 char

7、(10),p2 char(10)=NULLASBEGINselect title_id,title,price,pubdatefrom titleswhere title=t and price<=p2 and price>=p1ENDGO14調(diào)用上面的存儲過程search，求出書名與puter有關(guān)，而且價(jià)格小于$20大于$10的書。 EXEC search "puter",10,20二）觸發(fā)器：1、在學(xué)生成績庫中創(chuàng)建觸發(fā)器trigger1，實(shí)現(xiàn)如下功能：當(dāng)在學(xué)生成績表（xscj）中插入一條學(xué)生選課信息后，自動(dòng)實(shí)現(xiàn)更新該學(xué)生在學(xué)生情況表（xsqk）中的

8、總學(xué)分信息。分析：根據(jù)題意，也即要求在學(xué)生成績表中插入一條記錄時(shí)，自動(dòng)更新學(xué)生情況表中的相應(yīng)記錄信息。可以通過在學(xué)生成績表中定義INSERT類型的觸發(fā)器，觸發(fā)器中語句要完成的功能是更新學(xué)生情況表中的相應(yīng)學(xué)生的總學(xué)分信息。其實(shí)，只要在該生原總學(xué)分基礎(chǔ)上加上新選課程的學(xué)分就可以了。createtrigger trigger1on xscjafterinsertasdeclare credit int;select credit=credit from inserted xscj;update xsqk set allcredit=allcredit+credit;go 2、創(chuàng)建觸發(fā)器tr

9、igger2，實(shí)現(xiàn)當(dāng)修改學(xué)生課程表（xskc）中的數(shù)據(jù)時(shí)，顯示提示信息“學(xué)生課程表被修改了”。 createtrigger trigger2on xskcafterupdateasprint'學(xué)生課程表被修改了'go3、創(chuàng)建觸發(fā)器trigger3，實(shí)現(xiàn)當(dāng)刪除學(xué)生課程表中某門課程的記錄時(shí)，對應(yīng)學(xué)生成績表中所有有關(guān)此課程的記錄均刪除。 createtrigger trigger3on xskcafterdeleteasdeclare ame char(10);select ame=ame from deleted xsks;delete xscj wheream

10、e=ame;go4、創(chuàng)建觸發(fā)器trigger4，實(shí)現(xiàn)當(dāng)修改學(xué)生課程表（xskc）中的某門課的課程號時(shí)，對應(yīng)學(xué)生成績表（xscj）中的課程號也作相應(yīng)修改。createtrigger trigger4on xskcafterupdateasdeclare o char(10);declare ame char(10);select ame=xskc.ame,o=xskc.o from updated xsks;update xscj set xscj.o=o where xscj.ame=ame;go5、創(chuàng)建觸發(fā)器trigger5，實(shí)現(xiàn)當(dāng)向?qū)W生成績表（xscj）中插入一條選課記錄時(shí)，查看該學(xué)生的信

11、息是否存在在學(xué)生信息表中，如果不存在，則把該學(xué)生的基本信息加入到學(xué)生信息表中。createtrigger trigger5on xscjafterinsertasdeclare sname1 char(10);declare sname2 char(10)=NULL;select sname1=xscj.sname from updated xscj;select sname2=xsqk.sname from xsqk where xsqk.sname=sname1;if sname2=NULLinsertinto xsqk values(NULL,sname1);go6、在學(xué)生成績庫中創(chuàng)建觸

12、發(fā)器trigger6，實(shí)現(xiàn)如下功能：當(dāng)在學(xué)生成績表（xscj）中插入一條學(xué)生選課信息后，查看該學(xué)生的信息是否存在在學(xué)生信息表中，如果不存在，則給出“該記錄不能被插入！”的錯(cuò)誤提示，并撤銷插入操作；同樣，如果課程信息在課程信息表中不存在，給出“該記錄不能被插入！”的錯(cuò)誤提示，并撤銷插入操作。 createtrigger trigger6on xscjforinsertasbeginifnotexists(select xsqk.snamefrom xsqk where xsqk.sname in(select xscj.sname from inserted xscj)beginrai

13、serror('該記錄不能被插入！',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 xscjforinsertasbegintransactiondeclare credit int;select credit=credit from inserted xscj;update xsqk set allcredit=allcredit+credit;mittrans

14、actiongo8、分別用觸發(fā)器和存儲過程實(shí)現(xiàn)對學(xué)生情況表（xsqk）和學(xué)生成績表（xscj）表的級聯(lián)刪除。createtrigger trigger8 on xsqk afterdeleteasdeletefrom xscj where xscj.sname in(select xsqk.sname from deleted xsqk)goCREATEPROCEDURE del_qk_cj sname char(10)=NULLASBEGINdeletefrom xscj where xscj.sname=sname;deletefrom xsqk where xsqk.sname=snam

15、e;ENDGO9、創(chuàng)建觸發(fā)器8，要現(xiàn)：當(dāng)向xscj表插入一條記錄時(shí)，判斷該學(xué)生的總學(xué)分，如果總學(xué)分大于等于25，則給出“該學(xué)生已修滿，不需要再選修！”的提示信息；否則，自動(dòng)更新該學(xué)生的總學(xué)分。 createtrigger trigger88on xscjforinsertasbegindeclare credit1 int;declare credit2 int;declare sname char(10);select credit1=xscj.credit,sname=xscj.sname from inserted xscj;select credit2=xsqk.allcre

16、dit from xsqk where xsqk.sname=sname;if(credit1+credit2)>=25beginraiserror('該學(xué)生已修滿，不需要再選修！',16,1)rollbackreturnendelseinsertinto xscj values(credit1,NULL,NULL,sname);endgo10、在數(shù)據(jù)庫中用以下語句創(chuàng)建兩表：CREATE TABLE 卷煙銷售表(卷煙品牌 VARCHAR(40) PRIMARY KEY NOT NULL,購貨商 VARCHAR(40) NULL,銷售數(shù)量 INT NULL,銷售單價(jià) MON

17、EY 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à)。針對卷煙庫存表，插入測試數(shù)據(jù)：注意，第一條數(shù)據(jù)（紅塔山新勢力）中的數(shù)據(jù)符合業(yè)務(wù)規(guī)則，第二條數(shù)據(jù)

18、（紅塔山人為峰）中，庫存金額空，不符合業(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ù)量,庫存單價(jià),庫存

19、金額)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)減少卷煙庫存表中對應(yīng)品牌卷煙的庫存數(shù)量和庫存金額。createtrigger T_INSERT_卷煙銷售表on 卷煙銷售表forinsertasbegintransactionifnotexists(sel

20、ect 卷煙品牌from 卷煙庫存表where 卷煙品牌in(select 卷煙品牌from inserted 卷煙庫存表)beginraiserror('該卷煙不存在于庫存，不能銷售！',16,1);rollbackreturnendifexists(select 庫存數(shù)量from 卷煙庫存表where 卷煙品牌in(select 卷煙品牌from inserted 卷煙庫存表)and 庫存數(shù)量<=0)beginraiserror('該卷煙庫存小于等于，不能銷售！',16,1);rollbackreturnendupdate 卷煙庫存表set 庫存金額=

21、 庫存數(shù)量* 庫存單價(jià)where 卷煙品牌in(select 卷煙品牌from inserted 卷煙庫存表)declare 卷煙品牌VARCHAR(40)set 卷煙品牌=(select 卷煙品牌from inserted 卷煙銷售表)declare 銷售數(shù)量INTset 銷售數(shù)量=(select 銷售數(shù)量from inserted 卷煙銷售表)update 卷煙庫存表set 庫存數(shù)量=庫存數(shù)量-銷售數(shù)量, 庫存金額=(庫存數(shù)量-銷售數(shù)量)*庫存單價(jià)where 卷煙品牌=卷煙品牌mittransactiongo 三）T-SQL編程1、從學(xué)生_課程數(shù)據(jù)庫中查詢所有學(xué)生選課成績情況：、課程名、成績。要求：將學(xué)生的百分制轉(zhuǎn)換為5級評分制，成績大于等于90顯示為“優(yōu)秀”，成績在80-89分顯示為“良好”，“70-79”分顯示為“中等”，成績在60-69顯示為“及格”，60以下顯示為“不及格”，沒成績的顯示為“未考”。并且輸出記錄按下列要求排序：先按學(xué)號升序，再按課程號升序，最后按成績降序。 CREATEPROCEDURE