實驗6數(shù)據(jù)庫實驗

1、實驗6存儲過程與觸發(fā)器、實驗目的1、加深與鞏固對存儲過程與觸發(fā)器概念的理解。2、掌握觸發(fā)器的簡單應用。3、掌握存儲過程的簡單應用。二、實驗內(nèi)容一) 存儲過程：1、創(chuàng)建一存儲過程，求1+2+3+n并打印結果。CREATE PROCEDURE addresultASDECLARE n int =10, /* 最后一個數(shù) */iint =0,resultint =0/* 結果 */BEGINWHILE (i =r)BEGINSET result =result +iSET i =i+1ENDPRINT 1+2+3+、+n 的結果就是:PRINT resultRETURN( result )ENDGO

2、2調(diào)用上面的addresult存儲過程打印l十2+3+10的結果EXEC addresult3、修改上述存儲過程為addresultl,使得n為輸入?yún)?shù)，其具體值由 用戶調(diào)用此存儲過程時指定。CREATE PROCEDURE addresultln int =10 /* 最后一個數(shù) */ASDECLARE i int =0,resultint =0/* 結果 */BEGINWHILE (i =r)BEGINSET result =result +iSET i =i+1ENDPRINT 1+2+3+、+n 的結果就是：PRINT resultRETURN( result )ENDGO*命令已成9

3、閘克4、調(diào)用上面修改后的addresultl存儲過程，打印1+2+3十+100的結 果。EXEC addresultl 100心消息1+2+3+.- 如瞬果是:他05修改上述存儲過程為addresult2,將n參數(shù)設定默認值為10,并改設sum為輸出參數(shù)，讓主程序能夠接收計算結果。CREATE PROCEDURE addresult2n int =10,/* 最后一個數(shù) */sum int out /* 結果 */ASDECLARE i int =0BEGINset sum =0WHILE (i =r)BEGINSET sum =sum+iSET i =i+1ENDENDGO6. 調(diào)用上面修改

4、后的addresult2存儲過程，設置變量s接收計算1+2+3+10的結果。DECLARE s intset s = 0EXEC addresult2 10, sum=s outPRINT 1+2+3+、+n 的結果就是：PRINT s7. 創(chuàng)建一存儲過程Proc_Student用于顯示學號為“ 0102”的學生基本 信息（包括學號、姓名、性別與系）。CREATE PROCEDURE Proc_StudentASBEGINselect *from Swhere S、sno =0102ENDGOEXEC Proc Student8. 創(chuàng)建一存儲過程Stu_grade通過讀取某門課的編號，求出不及

5、格的學生的學號。CREATE PROCEDURE Stu_graden char ( 10)ASBEGINselect snofrom SCwhere eno =n and grade =p1之 間的書的編號，書名，價格，出舨日期。如果用戶調(diào)用時沒有指定t參數(shù)的值.則表示可為任意值，如用戶沒有指定 p2,則書本價格沒有上 限。用到的關系為:titles (title_id,title,price,pubdate)。CREATE PROCEDURE searcht char ( 10 )= %,p1 char (10 ),p2 char (10)=NULLASBEGINselect title_

6、idfrom titles,title , price , pubdatewhere title =t and price =p1ENDGO14. 調(diào)用上面的存儲過程search求出書名與computer有關,而且價格 小于$20大于$10的書。EXEC search computer, 10 , 20二) 觸發(fā)器：1、在學生成績庫中創(chuàng)建觸發(fā)器triggers實現(xiàn)如下功能：當在學生成績表(xscj)中插入一條學生選課信息后，自動實現(xiàn)更新該學生在學生情況 表(xsqk)中的總學分信息。分析:根據(jù)題意,也即要求在學生成績表中插入一條記錄時，自動更新學生情況表中的相應記錄信息。可以通過在學生成績表中

7、定義INSERT 類型的觸發(fā)器，觸發(fā)器中語句要完成的功能就是更新學生情況表中的 相應學生的總學分信息。其實，只要在該生原總學分基礎上加上新選 課程的學分就可以了。create trigger trigger1on xscjafterin sertasdeclare credit int ;select credit =credit from in serted xscj;update xsqk set allcredit =allcredit +credit ;go2、創(chuàng)建觸發(fā)器trigger2,實現(xiàn)當修改學生課程表(xskc)中的數(shù)據(jù)時,顯示create trigger trigger2on

8、 xskcafter updateprint 學生課程表被修改了as go3、創(chuàng)建觸發(fā)器trigger,實現(xiàn)當刪除學生課程表中某門課程的記錄時 對應學生成績表中所有有關此課程的記錄均刪除。create trigger trigger3on xskcafter deleteasdeclarecname char (10);select cname =cname from deleted xsks delete xscj where cn ame =c name;go4、創(chuàng)建觸發(fā)器trigger4,實現(xiàn)當修改學生課程表(xskc)中的某門課的課 程號時，對應學生成績表(xscj)中的課程號也作相應

9、修改。create trigger trigger4on xskcafter updateasdeclare cno char (10 );declare cname char ( 10);selectcname =xskc、cname , cno=xskc、eno from updated xsks ;update xscj set xscj 、eno =cno where xscj 、cname =cname;go5、創(chuàng)建觸發(fā)器trigger5,實現(xiàn)當向?qū)W生成績表(xscj)中插入一條選課記 錄時,查瞧該學生的信息就是否存在在學生信息表中，如果不存在，則把該學生的基本信息加入到學生信息表中

10、。create trigger trigger5on xscjafterin sertasdeclaresnamelchar (10);declaresname2 char (10)=NULL;select sname1 =xscj 、sname from updated xscj ;select sname2 =xsqk、sname from xsqk where xsqk 、sname =sname1 ;if s name2 =NULLin sertinto xsqk values (NULL, sn ame1);go6、在學生成績庫中創(chuàng)建觸發(fā)器trigger6，實現(xiàn)如下功能：當在學生成績

11、表(xscj)中插入一條學生選課信息后，查瞧該學生的信息就是否存在在 學生信息表中，如果不存在，則給出“該記錄不能被插入！ ”的錯誤提 示，并撤銷插入操作；同樣，如果課程信息在課程信息表中不存在，給出“該記錄不能被插入！ ”的錯誤提示，并撤銷插入操作。create trigger trigger6on xscjfor in sertasbeginif not exists(select xsqk、sname from xsqk where xsqk、sname in( select xscj 、snamefrom in serted xscj)beginraiserror (該記錄不能被插入！

12、，16, 1)rollbackreturnendendgo7、創(chuàng)建觸發(fā)器trigger7,強制實現(xiàn)業(yè)務規(guī)則：當向?qū)W生成績表中插入一 條記錄時，自動修改學生情況表中該學生的總學分，要求總學分為該學 生所有已修課程的學分總與。create trigger trigger7on xscjforin sertasbeg intran sacti ondeclare credit int ;select credit =credit from in serted xscj;update xsqk set allcredit =allcredit +credit ;commit tran sacti on

13、go&分別用觸發(fā)器與存儲過程實現(xiàn)對學生情況表(xsqk與學生成績表(xscj表的級聯(lián)刪除。create trigger trigger8on xsqkafter deleteasdelete from xscjwhere xscj 、sname in(select xsqk 、sname from deleted xsqk )go1-耳uqK.nnuirf frcn1.xsqk：CREATE PROCEDURE del_qk_cj sname char (10)=NULL ASBEGINdeletefromxscjwherexscj 、sn ame =s name;deletefromxsqk

14、wherexsqk 、sn ame =s name;ENDGOE CREATE PROCEDURE del_qk_cj Ssnair.e char (10 =1RJLLAS(i BEGINEdelete fron xacjwhexe xscj .且namg=世且口師十： rdelete fror xqk燉b電工皀xsqk 朋克昭=臨呂席址已：Lend-命晶成功完成。9、創(chuàng)建觸發(fā)器8,要求實現(xiàn)：當向xscj表插入一條記錄時，判斷該學生的 總學分，如果總學分大于等于 25,則給出“該學生已修滿，不需要再選修！”的提示信息；否則，自動更新該學生的總學分create trigger trigger88

15、on xscjfor in sert asbegi ndeclarecredit1int ;declarecredit2int ;declaresn amechar ( 10);selectcredit1=xscj 、credit,sname=xscj 、sname fromin serted xscj;selectcredit2= xsqk、allcreditfromxsqkwhere xsqk、sname =sname;if (credit1 +credit2 )= 25beginraiserror (該學生已修滿，不需要再選修！，16, 1)rollbackreturnendelsein

16、sertinto xscj values (credit1 ,NULL,NULL,sname);endgo匚3 ex eate Lzigyet2 2ok xaej fcr ins ext a雷be-gmicreditl int;ID匸匚dsslai!0 s eat趙 eh&r (10) ?sslsct- cr edit-,囂gcj ” 羽凰賤 f s sm.acsc j :select Screait-L-xs口kaiicreoix rroni xsai wneie xsaic超厲圧=越毀ehik: if (3czedxcl+eredic2)-2&Leglaraiarror i該學生.已倚;苗

17、不需要再選瞭！ *”!.)rcllbaLCkxeturn-endxntQ 搖values (ScicdjLtl r tlULLp 4UXL P ;*nd.LTr命令已威功殼成:10、在數(shù)據(jù)庫中用以下語句創(chuàng)建兩張表：CREATE TABLE煙銷售表(卷煙品牌 VARCHAR(40) PRIMARY KEY NOT NULL, 購貨商 VARCHAR(40) NULL,銷售數(shù)量INT NULL,銷售單價MONEY NULL,銷售金額 MONEY NULL）GO-業(yè)務規(guī)則 :庫存金額 = 庫存數(shù)量 * 庫存單價 業(yè)務規(guī)則。CREATE TABLE煙庫存表（卷煙品牌 VARCHAR（40） PRIMA

18、RY 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ù) ：注意 ,第一條數(shù)據(jù) （紅塔山新勢力 ）中 的數(shù)據(jù)符合業(yè)務規(guī)則 ,第二條數(shù)據(jù) （紅塔山人為峰 ）中,庫存金額 空,不 符合業(yè)務規(guī)則 ,第三條數(shù)據(jù) （云南映像 ）中,庫存金額 不等于 庫存數(shù)量 乘以庫存單價 ,不符合業(yè)務規(guī)則。第四條數(shù)據(jù)庫存數(shù)量為 0。請注

19、意 在插入數(shù)據(jù)后 ,檢查 卷煙庫存表 中的數(shù)據(jù)就是否 庫存金額 = 庫存 數(shù)量 * 庫存單價。INSERT INTO卷煙庫存表（卷煙品牌，庫存數(shù)量，庫存單價，庫存金額）values（紅塔山新勢力,100,12,1200）INSERT INTO卷煙庫存表（卷煙品牌，庫存數(shù)量，庫存單價，庫存金額）values（紅塔山人為峰,100,22,null）INSERT INTO卷煙庫存表（卷煙品牌，庫存數(shù)量，庫存單價，庫存金額）values（云南映像,100,60,500）INSERT INTO卷煙庫存表（卷煙品牌，庫存數(shù)量，庫存單價，庫存金額） insert into譽煙庫存表卷姻品牌r庫存魏量庫仔單價

20、，庫存金額;values（玉溪,0,30,0）-p且 Lue 3 （ ，紅塔山新勢力IITSERT INTO卷煙庫存表I卷煙品牌土庫存數(shù)量f庫存單價庫存金額I valuefl （紅塔山人為峰 100,22,7.11）valueB 云南映像 100, SO, 5DC工砧皿工1HTO 煙庫存表（卷煙品牌庫存數(shù)量庫存單價，庫存金頼 values （玉，奚” 0 r dCL 0A INSERT INTO卷煙厘存表（卷煙品牌盧庫存數(shù)量*庫存單價”庫存金訓；rrr由消息丄行受覺響）L彳亍受影響）行受覺響）1行受影響行登影響）ri行受影響彳亍受覺響）u行受影響）J消息卷咽品牌庫存數(shù)重庫存單價庫存1i SL埸

21、山人拘幡T 10022.002200図2紅増山新犧力1001ZM1200.M_3_玉濯0乳皿Q.W4云南腆嫁100S0.00000 0011、創(chuàng)建觸發(fā)器T_INSERT卷煙銷售表,實現(xiàn)每當卷煙庫存表發(fā)生INSERT動作，則引發(fā)該觸發(fā)器。觸發(fā)器功能：實現(xiàn)業(yè)務規(guī)則：如果銷售 的卷煙品牌不存在庫存或者庫存為零，則返回錯誤。否則則自動減少卷煙庫存表中對應品牌卷煙的庫存數(shù)量與庫存金額。create trigger T_INSERT_ 卷煙銷售表on卷煙銷售表for in sertasbegi n tra nsacti onif not exists（ select 卷煙品牌from 卷煙庫存表where

22、 卷煙品牌in（ select 卷煙品牌from inserted卷煙庫存表）beginraiserror （該卷煙不存在于庫存，不能銷售！ ，16, 1）;rollbackreturnendif exists（ select庫存數(shù)量from 卷煙庫存表where 卷煙品牌in（ select卷煙品牌from inserted卷煙庫存表）and庫存數(shù)量=0）beginraiserror （該卷煙庫存小于等于，不能銷售！ ，16,1）;rollbackreturnendupdate卷煙庫存表set庫存金額=庫存數(shù)量*庫存單價where 卷煙品牌in （ select卷煙品牌from insert

23、ed 卷煙庫存表）declare卷煙品牌 VARCHAR（40）卷煙銷售表）卷煙銷售表）set 卷煙品牌=（select卷煙品牌from inserteddeclare銷售數(shù)量INTset 銷售數(shù)量 =（select銷售數(shù)量 from insertedupdate 卷煙庫存表set庫存數(shù)量=庫存數(shù)量-銷售數(shù)量，庫存金額=（庫存數(shù)量-銷售數(shù)量）*庫存單價 where 卷煙品牌=卷煙品牌committran sacti ongo命令已咸功完成。三）T-SQL編程1、從學生_課程數(shù)據(jù)庫中查詢所有學生選課成績情況：姓名、課程名、 成績。要求:將學生的百分制轉(zhuǎn)換為5級評分制，成績大于等于90顯示 為“優(yōu)秀”，成績在80-89分顯示為“良好” ，“70-79”分顯示為“中 等”，成績在60-69顯示為“及格”，60以下顯示為“不及格”，沒成績 的顯示為“未考”。并且輸出記錄按下列要求排序 ：先按學號升序，再 按課程號升序，最后按成績降序。CREATE PROCEDURE選課成績情況ASBEGINselect S、sno 學號，S、sname 姓名，C、cno 課程號，C、cname 課程名，成績=casewhe nSC、grade=90the n優(yōu)秀whe nSC、grade=80the n良好whe nSC、grade=70the n中等whe nSC、grade=60the