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

1、實(shí)驗(yàn)6 存儲(chǔ)過程和觸發(fā)器一、實(shí)驗(yàn)?zāi)康?、加深和鞏固對(duì)存儲(chǔ)過程和觸發(fā)器概念的理解。2、掌握觸發(fā)器的簡(jiǎn)單應(yīng)用。3、掌握存儲(chǔ)過程的簡(jiǎn)單應(yīng)用。二、實(shí)驗(yàn)內(nèi)容一）存儲(chǔ)過程：1. 創(chuàng)建一存儲(chǔ)過程，求l+2+3+n，并打印結(jié)果。CREATE PROCEDURE addresult AS DECLARE n int=10, /*最后一個(gè)數(shù)*/ i int=0, result int=0 /*結(jié)果*/BEGIN WHILE(i<=n) BEGIN SET result=result+i SET i=i+1 END PRINT '1+2+3+.+n的結(jié)果是:' PRINT result RET

2、URN(result)ENDGO 2調(diào)用上面的addresult存儲(chǔ)過程，打印l十2+3+10的結(jié)果。 EXEC addresult1 / 193. 修改上述存儲(chǔ)過程為addresult1,使得n為輸入?yún)?shù)，其具體值由用戶調(diào)用此存儲(chǔ)過程時(shí)指定。 CREATE PROCEDURE addresult1 n int=10 /*最后一個(gè)數(shù)*/ AS DECLARE i int=0, result int=0 /*結(jié)果*/BEGIN WHILE(i<=n) BEGIN SET result=result+i SET i=i+1 END PRINT '1+2+3+.+n的結(jié)果是:'

3、 PRINT result RETURN(result)ENDGO4. 調(diào)用上面修改后的addresult1存儲(chǔ)過程，打印l+2+3+100的結(jié)果。 EXEC addresult1 1005修改上述存儲(chǔ)過程為addresult2，將n參數(shù)設(shè)定默認(rèn)值為10，并改設(shè)sum為輸出參數(shù)，讓主程序能夠接收計(jì)算結(jié)果。 CREATE PROCEDURE addresult2 n int=10, /*最后一個(gè)數(shù)*/ sum int out /*結(jié)果*/AS DECLARE i int=0 BEGIN set sum=0 WHILE(i<=n) BEGIN SET sum=sum+i SET i=i+1

4、ENDENDGO6調(diào)用上面修改后的addresult2存儲(chǔ)過程，設(shè)置變量s接收計(jì)算l+2+3+10的結(jié)果。 DECLARE s intset s=0EXEC addresult2 10,sum=s outPRINT '1+2+3+.+n的結(jié)果是:'PRINT s7創(chuàng)建一存儲(chǔ)過程Proc_Student，用于顯示學(xué)號(hào)為“0102”的學(xué)生基本信息（包括學(xué)號(hào)、姓名、性別和系）。 CREATE PROCEDURE Proc_Student AS BEGIN select * from S where S.sno=0102ENDGOEXEC Proc_Student8創(chuàng)建一存儲(chǔ)過程Stu

5、_grade，通過讀取某門課的編號(hào)，求出不及格的學(xué)生的學(xué)號(hào)。 CREATE PROCEDURE Stu_grade n char(10)AS BEGIN select sno from SC where cno=n and grade<60ENDGO9調(diào)用上面的存儲(chǔ)過程Stu_grade，求出課程編號(hào)為“0101”的不及格的學(xué)生。EXEC Stu_grade 0101 10創(chuàng)建一存儲(chǔ)過程avgGrade，通過讀取學(xué)生的學(xué)號(hào)，以參數(shù)形式返回該學(xué)生的平均分。 CREATE PROCEDURE avgGrade n char(10)AS BEGIN select AVG(grade) 平均分

6、from SC where sno=n group by snoENDGO11調(diào)用上面的存儲(chǔ)過程avgGrade，求出學(xué)號(hào)為“990102014”的平均分。EXEC avgGrade 990102014 12刪除上述存儲(chǔ)過程avgGrade。 drop procedure avgGrade13.創(chuàng)建存儲(chǔ)過程search，該存儲(chǔ)過程有三個(gè)參數(shù)，分別為t、p1，p2，根據(jù)這些參數(shù)，找出書名與t有關(guān)，價(jià)格在p1與p2(p2>=p1)之間的書的編號(hào)，書名，價(jià)格，出舨日期。如果用戶調(diào)用時(shí)沒有指定t參數(shù)的值則表示可為任意值，如用戶沒有指定p2，則書本價(jià)格沒有上限。用到的關(guān)系為：titles (tit

7、le_id,title,price,pubdate)。 CREATE PROCEDURE search t char(10)="%",p1 char(10),p2 char(10)=NULLAS BEGIN select title_id,title,price,pubdate from titles where title=t and price<=p2 and price>=p1ENDGO14調(diào)用上面的存儲(chǔ)過程search，求出書名與computer有關(guān)，而且價(jià)格小于$20大于$10的書。 EXEC search "computer&qu

8、ot;,10,20二）觸發(fā)器：1、在學(xué)生成績(jī)庫中創(chuàng)建觸發(fā)器trigger1，實(shí)現(xiàn)如下功能：當(dāng)在學(xué)生成績(jī)表（xscj）中插入一條學(xué)生選課信息后，自動(dòng)實(shí)現(xiàn)更新該學(xué)生在學(xué)生情況表（xsqk）中的總學(xué)分信息。分析：根據(jù)題意，也即要求在學(xué)生成績(jī)表中插入一條記錄時(shí)，自動(dòng)更新學(xué)生情況表中的相應(yīng)記錄信息?？梢酝ㄟ^在學(xué)生成績(jī)表中定義INSERT類型的觸發(fā)器，觸發(fā)器中語句要完成的功能是更新學(xué)生情況表中的相應(yīng)學(xué)生的總學(xué)分信息。其實(shí)，只要在該生原總學(xué)分基礎(chǔ)上加上新選課程的學(xué)分就可以了。create trigger trigger1on xscjafter insert as declare credit int; s

9、elect credit=credit from inserted xscj; update xsqk set allcredit=allcredit+credit;go 2、創(chuàng)建觸發(fā)器trigger2，實(shí)現(xiàn)當(dāng)修改學(xué)生課程表（xskc）中的數(shù)據(jù)時(shí)，顯示提示信息“學(xué)生課程表被修改了”。 create trigger trigger2on xskcafter update as print '學(xué)生課程表被修改了'go3、創(chuàng)建觸發(fā)器trigger3，實(shí)現(xiàn)當(dāng)刪除學(xué)生課程表中某門課程的記錄時(shí)，對(duì)應(yīng)學(xué)生成績(jī)表中所有有關(guān)此課程的記錄均刪除。 create tri

10、gger trigger3on xskcafter delete as declare cname char(10); select cname=cname from deleted xsks; delete xscj where cname=cname;go4、創(chuàng)建觸發(fā)器trigger4，實(shí)現(xiàn)當(dāng)修改學(xué)生課程表（xskc）中的某門課的課程號(hào)時(shí)，對(duì)應(yīng)學(xué)生成績(jī)表（xscj）中的課程號(hào)也作相應(yīng)修改。create trigger trigger4on xskcafter update as declare cno char(10); declare cname char(10); select cna

11、me=ame,cno=o from updated xsks; update xscj set o=cno where ame=cname;go5、創(chuàng)建觸發(fā)器trigger5，實(shí)現(xiàn)當(dāng)向?qū)W生成績(jī)表（xscj）中插入一條選課記錄時(shí)，查看該學(xué)生的信息是否存在在學(xué)生信息表中，如果不存在，則把該學(xué)生的基本信息加入到學(xué)生信息表中。create trigger trigger5on xscjafter insert as declare sname1 char(10); declare sname2 char(10)=NULL; select sname1=xscj.sname from updated x

12、scj; select sname2=xsqk.sname from xsqk where xsqk.sname=sname1; if sname2=NULL insert into xsqk values(NULL,sname1); go6、在學(xué)生成績(jī)庫中創(chuàng)建觸發(fā)器trigger6，實(shí)現(xiàn)如下功能：當(dāng)在學(xué)生成績(jī)表（xscj）中插入一條學(xué)生選課信息后，查看該學(xué)生的信息是否存在在學(xué)生信息表中，如果不存在，則給出“該記錄不能被插入！”的錯(cuò)誤提示，并撤銷插入操作；同樣，如果課程信息在課程信息表中不存在，給出“該記錄不能被插入！”的錯(cuò)誤提示，并撤銷插入操作。 create trigger tr

13、igger6on xscjfor insert 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 go7、創(chuàng)建觸發(fā)器trigger7，強(qiáng)制實(shí)現(xiàn)業(yè)務(wù)規(guī)則：當(dāng)向?qū)W生成績(jī)表中插入一條記錄時(shí)，自動(dòng)修改學(xué)生情況表中該學(xué)生的總學(xué)分，要求總學(xué)分為該學(xué)生所有已修課程的學(xué)分總和。create trigger t

14、rigger7on xscjfor insert asbegin transaction declare credit int; select credit=credit from inserted xscj; update xsqk set allcredit=allcredit+credit;commit transactiongo8、分別用觸發(fā)器和存儲(chǔ)過程實(shí)現(xiàn)對(duì)學(xué)生情況表（xsqk）和學(xué)生成績(jī)表（xscj）表的級(jí)聯(lián)刪除。create trigger trigger8 on xsqk after delete as delete from xscj where xscj.sname in

15、(select xsqk.sname from deleted xsqk)goCREATE PROCEDURE del_qk_cj sname char(10)=NULLAS BEGIN delete from xscj where xscj.sname=sname; delete from xsqk where xsqk.sname=sname; ENDGO9、創(chuàng)建觸發(fā)器8，要求實(shí)現(xiàn)：當(dāng)向xscj表插入一條記錄時(shí)，判斷該學(xué)生的總學(xué)分，如果總學(xué)分大于等于25，則給出“該學(xué)生已修滿，不需要再選修！”的提示信息；否則，自動(dòng)更新該學(xué)生的總學(xué)分。 create trigger trigger

16、88on xscjfor insert as begin declare credit1 int; declare credit2 int; declare sname char(10); select credit1=xscj.credit,sname=xscj.sname from inserted xscj; select credit2=xsqk.allcredit from xsqk where xsqk.sname=sname; if(credit1+credit2)>=25 begin raiserror('該學(xué)生已修滿，不需要再選修！',16,1) rol

17、lback return end else insert into xscj values(credit1,NULL,NULL,sname); end go10、在數(shù)據(jù)庫中用以下語句創(chuàng)建兩張表：CREATE TABLE 卷煙銷售表(卷煙品牌 VARCHAR(40) PRIMARY KEY NOT NULL,購貨商 VARCHAR(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

18、 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ì)卷煙庫存表，插入測(cè)試數(shù)據(jù)：注意，第一條數(shù)據(jù)（紅塔山新勢(shì)力）中的數(shù)據(jù)符合業(yè)務(wù)規(guī)則，第二條數(shù)據(jù)（紅塔山人為峰）中，庫存金額空，不符合業(yè)務(wù)規(guī)則，第三條數(shù)據(jù)（云南映像）中，庫存金額不等于庫存數(shù)量乘以庫存單價(jià)，不符合業(yè)務(wù)規(guī)則。第四條數(shù)據(jù)庫存數(shù)量為0。請(qǐng)注意在插入數(shù)據(jù)后，檢查卷煙庫存表中的數(shù)據(jù)是否 庫存金

19、額 = 庫存數(shù)量 * 庫存單價(jià)。INSERT INTO 卷煙庫存表(卷煙品牌,庫存數(shù)量,庫存單價(jià),庫存金額)values( '紅塔山新勢(shì)力',100,12,1200)INSERT INTO 卷煙庫存表(卷煙品牌,庫存數(shù)量,庫存單價(jià),庫存金額)values( '紅塔山人為峰',100,22,null)INSERT INTO 卷煙庫存表(卷煙品牌,庫存數(shù)量,庫存單價(jià),庫存金額)values( '云南映像',100,60,500)INSERT INTO 卷煙庫存表(卷煙品牌,庫存數(shù)量,庫存單價(jià),庫存金額)values( '玉溪',0,3

20、0,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ù)量和庫存金額。create trigger T_INSERT_卷煙銷售表on 卷煙銷售表for insertas begin transaction if not exists(select 卷煙品牌 from 卷煙庫存表 where 卷煙品牌in(select 卷煙品牌 from inserted 卷煙庫存表) begin raiserror('

21、;該卷煙不存在于庫存，不能銷售！',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ù)量* 庫存單價(jià) where 卷煙品牌in (select 卷煙品牌from inserted 卷煙庫存表) declare

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