1、實驗6 存儲過程和觸發器一、實驗目的1、加深和鞏固對存儲過程和觸發器概念的理解。2、掌握觸發器的簡單應用。3、掌握存儲過程的簡單應用。二、實驗內容一）存儲過程：1. 創建一存儲過程，求l+2+3+n，并打印結果。CREATE PROCEDURE addresult AS DECLARE n int=10, /*最后一個數*/ i int=0, result int=0 /*結果*/BEGIN WHILE(i<=n) BEGIN SET result=result+i SET i=i+1 END PRINT '1+2+3+.+n的結果是:' PRINT result RET

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

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

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

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

6、from SC where sno=n group by snoENDGO11調用上面的存儲過程avgGrade，求出學號為“990102014”的平均分。EXEC avgGrade 990102014 12刪除上述存儲過程avgGrade。 drop procedure avgGrade13.創建存儲過程search，該存儲過程有三個參數，分別為t、p1，p2，根據這些參數，找出書名與t有關，價格在p1與p2(p2>=p1)之間的書的編號，書名，價格，出舨日期。如果用戶調用時沒有指定t參數的值則表示可為任意值，如用戶沒有指定p2，則書本價格沒有上限。用到的關系為：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調用上面的存儲過程search，求出書名與computer有關，而且價格小于$20大于$10的書。 EXEC search "computer&qu

8、ot;,10,20二）觸發器：1、在學生成績庫中創建觸發器trigger1，實現如下功能：當在學生成績表（xscj）中插入一條學生選課信息后，自動實現更新該學生在學生情況表（xsqk）中的總學分信息。分析：根據題意，也即要求在學生成績表中插入一條記錄時，自動更新學生情況表中的相應記錄信息。可以通過在學生成績表中定義INSERT類型的觸發器，觸發器中語句要完成的功能是更新學生情況表中的相應學生的總學分信息。其實，只要在該生原總學分基礎上加上新選課程的學分就可以了。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、創建觸發器trigger2，實現當修改學生課程表（xskc）中的數據時，顯示提示信息“學生課程表被修改了”。 create trigger trigger2on xskcafter update as print '學生課程表被修改了'go3、創建觸發器trigger3，實現當刪除學生課程表中某門課程的記錄時，對應學生成績表中所有有關此課程的記錄均刪除。 create tri

10、gger trigger3on xskcafter delete as declare cname char(10); select cname=cname from deleted xsks; delete xscj where cname=cname;go4、創建觸發器trigger4，實現當修改學生課程表（xskc）中的某門課的課程號時，對應學生成績表（xscj）中的課程號也作相應修改。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、創建觸發器trigger5，實現當向學生成績表（xscj）中插入一條選課記錄時，查看該學生的信息是否存在在學生信息表中，如果不存在，則把該學生的基本信息加入到學生信息表中。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、在學生成績庫中創建觸發器trigger6，實現如下功能：當在學生成績表（xscj）中插入一條學生選課信息后，查看該學生的信息是否存在在學生信息表中，如果不存在，則給出“該記錄不能被插入！”的錯誤提示，并撤銷插入操作；同樣，如果課程信息在課程信息表中不存在，給出“該記錄不能被插入！”的錯誤提示，并撤銷插入操作。 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、創建觸發器trigger7，強制實現業務規則：當向學生成績表中插入一條記錄時，自動修改學生情況表中該學生的總學分，要求總學分為該學生所有已修課程的學分總和。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、分別用觸發器和存儲過程實現對學生情況表（xsqk）和學生成績表（xscj）表的級聯刪除。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、創建觸發器8，要求實現：當向xscj表插入一條記錄時，判斷該學生的總學分，如果總學分大于等于25，則給出“該學生已修滿，不需要再選修！”的提示信息；否則，自動更新該學生的總學分。 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('該學生已修滿，不需要再選修！',16,1) rol

17、lback return end else insert into xscj values(credit1,NULL,NULL,sname); end go10、在數據庫中用以下語句創建兩張表：CREATE TABLE 卷煙銷售表(卷煙品牌 VARCHAR(40) PRIMARY KEY NOT NULL,購貨商 VARCHAR(40) NULL,銷售數量 INT NULL,銷售單價 MONEY NULL,銷售金額 MONEY NULL)GO-業務規則：庫存金額 = 庫存數量 * 庫存單價 業務規則。CREATE TABLE 卷煙庫存表(卷煙品牌 VARCHAR(40) PRIMARY KEY

18、 NOT NULL,庫存數量 INT NULL,庫存單價 MONEY NULL,庫存金額 MONEY NULL)GO創建觸發器T_INSERT_卷煙庫存表，實現每當卷煙庫存表發生 INSERT 動作，則引發該觸發器。觸發器功能：強制執行業務規則，保證插入的數據中，庫存金額 = 庫存數量 * 庫存單價。針對卷煙庫存表，插入測試數據：注意，第一條數據（紅塔山新勢力）中的數據符合業務規則，第二條數據（紅塔山人為峰）中，庫存金額空，不符合業務規則，第三條數據（云南映像）中，庫存金額不等于庫存數量乘以庫存單價，不符合業務規則。第四條數據庫存數量為0。請注意在插入數據后，檢查卷煙庫存表中的數據是否 庫存金

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

20、0,0) 11、創建觸發器T_INSERT_卷煙銷售表，實現每當卷煙庫存表發生 INSERT 動作，則引發該觸發器。觸發器功能： 實現業務規則：如果銷售的卷煙品牌不存在庫存或者庫存為零，則返回錯誤。否則則自動減少卷煙庫存表中對應品牌卷煙的庫存數量和庫存金額。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 庫存數量 from 卷煙庫存表 where 卷煙品牌in(select 卷煙品牌 from inserted 卷煙庫存表) and 庫存數量<=0) begin raiserror('該卷煙庫存小于等于，不能銷售！',16,1); rollback return end update 卷煙庫存表 set 庫存金額= 庫存數量* 庫存單價 where 卷煙品牌in (select 卷煙品牌from inserted 卷煙庫存表) declare

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