實(shí)驗(yàn)6數(shù)據(jù)庫實(shí)驗(yàn)

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

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

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

4、后的addresult2存儲過程，設(shè)置變量s接收計(jì)算1+2+3+10的結(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é)號、姓名、性別與系）。CREATE PROCEDURE Proc_StudentASBEGINselect *from Swhere S、sno =0102ENDGOEXEC Proc Student8. 創(chuàng)建一存儲過程Stu_grade通過讀取某門課的編號，求出不及

5、格的學(xué)生的學(xué)號。CREATE PROCEDURE Stu_graden char ( 10)ASBEGINselect snofrom SCwhere eno =n and grade =p1之 間的書的編號，書名，價(jià)格，出舨日期。如果用戶調(diào)用時(shí)沒有指定t參數(shù)的值.則表示可為任意值，如用戶沒有指定 p2,則書本價(jià)格沒有上 限。用到的關(guān)系為: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有關(guān),而且價(jià)格 小于$20大于$10的書。EXEC search computer, 10 , 20二) 觸發(fā)器：1、在學(xué)生成績庫中創(chuàng)建觸發(fā)器triggers實(shí)現(xiàn)如下功能：當(dāng)在學(xué)生成績表(xscj)中插入一條學(xué)生選課信息后，自動實(shí)現(xiàn)更新該學(xué)生在學(xué)生情況 表(xsqk)中的總學(xué)分信息。分析:根據(jù)題意,也即要求在學(xué)生成績表中插入一條記錄時(shí)，自動更新學(xué)生情況表中的相應(yīng)記錄信息?？梢酝ㄟ^在學(xué)生成績表中

7、定義INSERT 類型的觸發(fā)器，觸發(fā)器中語句要完成的功能就是更新學(xué)生情況表中的 相應(yīng)學(xué)生的總學(xué)分信息。其實(shí)，只要在該生原總學(xué)分基礎(chǔ)上加上新選 課程的學(xué)分就可以了。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,實(shí)現(xiàn)當(dāng)修改學(xué)生課程表(xskc)中的數(shù)據(jù)時(shí),顯示create trigger trigger2on

8、 xskcafter updateprint 學(xué)生課程表被修改了as go3、創(chuàng)建觸發(fā)器trigger,實(shí)現(xiàn)當(dāng)刪除學(xué)生課程表中某門課程的記錄時(shí) 對應(yīng)學(xué)生成績表中所有有關(guā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,實(shí)現(xiàn)當(dāng)修改學(xué)生課程表(xskc)中的某門課的課 程號時(shí)，對應(yīng)學(xué)生成績表(xscj)中的課程號也作相應(yīng)

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,實(shí)現(xiàn)當(dāng)向?qū)W生成績表(xscj)中插入一條選課記 錄時(shí),查瞧該學(xué)生的信息就是否存在在學(xué)生信息表中，如果不存在，則把該學(xué)生的基本信息加入到學(xué)生信息表中

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、在學(xué)生成績庫中創(chuàng)建觸發(fā)器trigger6，實(shí)現(xiàn)如下功能：當(dāng)在學(xué)生成績

11、表(xscj)中插入一條學(xué)生選課信息后，查瞧該學(xué)生的信息就是否存在在 學(xué)生信息表中，如果不存在，則給出“該記錄不能被插入！ ”的錯(cuò)誤提 示，并撤銷插入操作；同樣，如果課程信息在課程信息表中不存在，給出“該記錄不能被插入！ ”的錯(cuò)誤提示，并撤銷插入操作。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,強(qiáng)制實(shí)現(xiàn)業(yè)務(wù)規(guī)則：當(dāng)向?qū)W生成績表中插入一 條記錄時(shí)，自動修改學(xué)生情況表中該學(xué)生的總學(xué)分，要求總學(xué)分為該學(xué) 生所有已修課程的學(xué)分總與。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ā)器與存儲過程實(shí)現(xiàn)對學(xué)生情況表(xsqk與學(xué)生成績表(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,要求實(shí)現(xiàn)：當(dāng)向xscj表插入一條記錄時(shí)，判斷該學(xué)生的 總學(xué)分，如果總學(xué)分大于等于 25,則給出“該學(xué)生已修滿，不需要再選修！”的提示信息；否則，自動更新該學(xué)生的總學(xué)分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 (該學(xué)生已修滿，不需要再選修！，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該學(xué)生.已倚;苗

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,銷售單價(jià)MONEY NULL,銷售金額 MONEY NULL）GO-業(yè)務(wù)規(guī)則 :庫存金額 = 庫存數(shù)量 * 庫存單價(jià) 業(yè)務(wù)規(guī)則。CREATE TABLE煙庫存表（卷煙品牌 VARCHAR（40） PRIMA

18、RY KEY NOT NULL,庫存數(shù)量 INT NULL,庫存單價(jià) MONEY NULL,庫存金額 MONEY NULL）GO創(chuàng)建觸發(fā)器T_INSERT卷煙庫存表,實(shí)現(xiàn)每當(dāng)卷煙庫存表發(fā)生 INSERT動作，則引發(fā)該觸發(fā)器。觸發(fā)器功能：強(qiáng)制執(zhí)行業(yè)務(wù)規(guī)則，保證插 入的數(shù)據(jù)中 ,庫存金額 = 庫存數(shù)量 * 庫存單價(jià)。針對卷煙庫存表 ,插入測試數(shù)據(jù) ：注意 ,第一條數(shù)據(jù) （紅塔山新勢力 ）中 的數(shù)據(jù)符合業(yè)務(wù)規(guī)則 ,第二條數(shù)據(jù) （紅塔山人為峰 ）中,庫存金額 空,不 符合業(yè)務(wù)規(guī)則 ,第三條數(shù)據(jù) （云南映像 ）中,庫存金額 不等于 庫存數(shù)量 乘以庫存單價(jià) ,不符合業(yè)務(wù)規(guī)則。第四條數(shù)據(jù)庫存數(shù)量為 0。請注

19、意 在插入數(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à)，庫存金額）values（云南映像,100,60,500）INSERT INTO卷煙庫存表（卷煙品牌，庫存數(shù)量，庫存單價(jià)，庫存金額） insert into譽(yù)煙庫存表卷姻品牌r庫存魏量庫仔單價(jià)

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

21、山人拘幡T 10022.002200図2紅増山新犧力1001ZM1200.M_3_玉濯0乳皿Q.W4云南腆嫁100S0.00000 0011、創(chuàng)建觸發(fā)器T_INSERT卷煙銷售表,實(shí)現(xiàn)每當(dāng)卷煙庫存表發(fā)生INSERT動作，則引發(fā)該觸發(fā)器。觸發(fā)器功能：實(shí)現(xiàn)業(yè)務(wù)規(guī)則：如果銷售 的卷煙品牌不存在庫存或者庫存為零，則返回錯(cuò)誤。否則則自動減少卷煙庫存表中對應(yīng)品牌卷煙的庫存數(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ù)量*庫存單價(jià)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ù)量）*庫存單價(jià) where 卷煙品牌=卷煙品牌committran sacti ongo命令已咸功完成。三）T-SQL編程1、從學(xué)生_課程數(shù)據(jù)庫中查詢所有學(xué)生選課成績情況：姓名、課程名、 成績。要求:將學(xué)生的百分制轉(zhuǎn)換為5級評分制，成績大于等于90顯示 為“優(yōu)秀”，成績在80-89分顯示為“良好” ，“70-79”分顯示為“中 等”，成績在60-69顯示為“及格”，60以下顯示為“不及格”，沒成績 的顯示為“未考”。并且輸出記錄按下列要求排序 ：先按學(xué)號升序，再 按課程號升序，最后按成績降序。CREATE PROCEDURE選課成績情況ASBEGINselect S、sno 學(xué)號，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