Oracle觸發(fā)器,過(guò)程等的實(shí)驗(yàn).doc

實(shí)驗(yàn)四：PL/SQL程序設(shè)計(jì)一、實(shí)驗(yàn)?zāi)康膗 掌握PL/SQL程序設(shè)計(jì)基本技巧，包括基本數(shù)據(jù)類型、表類型、數(shù)組類型、匿名程序塊、控制語(yǔ)句、PL/SQL中使用SQL語(yǔ)句、游標(biāo)、錯(cuò)誤處理等。u 熟悉和掌握PL/SQL中關(guān)于存儲(chǔ)過(guò)程、函數(shù)、包和觸發(fā)器程序設(shè)計(jì)技術(shù)。二、實(shí)驗(yàn)內(nèi)容某餐飲系統(tǒng)數(shù)據(jù)庫(kù)，請(qǐng)創(chuàng)建如下各數(shù)據(jù)表，并實(shí)現(xiàn)如下存儲(chǔ)過(guò)程、函數(shù)、包和觸發(fā)器等功能設(shè)計(jì)，將程序腳本保存到文本文件Source.sql中：（1） 菜肴類別表MK（菜肴類別編號(hào)MKid，菜肴類別名稱MkName）,菜肴類別名稱：魚類、蔬菜類、涼菜類、肉類、主食類和酒水類等。（2） 菜單信息表MList（菜肴編號(hào)Mid，菜肴名稱Mname，菜肴類別MKid，菜肴單價(jià)Mprice，菜肴成本單價(jià)Mcost，更新日期Mdate)。（3） 餐臺(tái)類別表DK（餐臺(tái)類別編號(hào)DKid，餐臺(tái)類別名稱DkName），餐臺(tái)類別：包間和散臺(tái)等。（4） 餐臺(tái)信息表Dinfo (餐臺(tái)編號(hào)Did，餐臺(tái)名稱Dname，餐臺(tái)類別DKid，座位數(shù)Dseats，更新日期Ddate)。（5） 消費(fèi)單主表C (消費(fèi)單號(hào)Cid，餐臺(tái)編號(hào)Did，消費(fèi)開始時(shí)間StartTime，結(jié)賬時(shí)間EndTime，消費(fèi)金額合計(jì)Smoney，盈利金額合計(jì)SPsum)，其中，消費(fèi)金額合計(jì)=消費(fèi)單明細(xì)表CList中該消費(fèi)單號(hào)的所有消費(fèi)記錄的消費(fèi)金額的合計(jì)，即SUM（消費(fèi)金額）或SUM（菜肴單價(jià) 消費(fèi)數(shù)量），盈利金額合計(jì)=消費(fèi)單明細(xì)表CList中該消費(fèi)單號(hào)的所有消費(fèi)記錄的盈利合計(jì)，即SUM（(菜肴單價(jià) - 菜肴成本單價(jià)) 消費(fèi)數(shù)量）。（6）消費(fèi)單明細(xì)表CList (消費(fèi)單號(hào)Cid，序號(hào)Sid，菜肴編號(hào)Mid，菜肴名稱Mname，消費(fèi)數(shù)量Cqty，菜肴單價(jià)Mprice，菜肴成本單價(jià)Mcost，消費(fèi)金額Cmoney) ，消費(fèi)金額=消費(fèi)數(shù)量菜肴單價(jià)；消費(fèi)數(shù)量為正數(shù)是正常點(diǎn)菜，消費(fèi)數(shù)量為負(fù)數(shù)是退菜，三、實(shí)驗(yàn)步驟及相關(guān)程序截圖3.1 創(chuàng)建表空間RESTAURANT,創(chuàng)建用戶DINER3.1.1 創(chuàng)建表空間RESTAURANT，大小10M。3.1.2創(chuàng)建用戶DINER，口令XXX，默認(rèn)表空間RESTAURANT，給該用戶授予角色權(quán)限CONNECT、RESOURCE。Create tablespace restautant datafile F:oradataorclrestautant_1.dbfsize 10M;Create user diner identified by zwl2011 default tablespace restaurant ;Grant connect ,resource to diner;3.2 創(chuàng)建餐飲系統(tǒng)數(shù)據(jù)庫(kù)的所有表，并向各表插入演示數(shù)據(jù)。3.2.1創(chuàng)建實(shí)驗(yàn)內(nèi)容中的餐飲系統(tǒng)數(shù)據(jù)庫(kù)的所有表(菜肴類別表MK、菜單信息表MList、餐臺(tái)類別表DK、餐臺(tái)信息表Dinfo、消費(fèi)單主表C、消費(fèi)單明細(xì)表CList)。Create table MK (MKid number(2) primary key,MKname varchar2(20);Create table DK(DKid number(2) primary key,DKname varchar2(20);Create table MList(Mid number(2) primary key,Mname varchar2(20),MKid number(2) references MK(MKid),Mprice number(7,2),Mcost number(7,2),Mdate date);Create table Dinfo(Did number(2) primary key,Dname varchar2(20), DKid number(2) references DK(DKid),Dseats number(4),Ddate date);Create table CList(Cid number(2),Sid number(2),primary key(Cid,Sid),Mid number(2) references MList(Mid),Mname varchar2(20),Cqty number(4),Mprice number(7,2),Mcost number(7,2),Cmoney number(7,2);Create table C(Cid number(2) primary key, Did number(2) references Dinfo(Did),StartTime date,EndTime date,Smoney number(7,2), Spsum number(7,2);3.2.2依次向菜肴類別表MK、菜單信息表MList、餐臺(tái)類別表DK、餐臺(tái)信息表Dinfo插入足夠多的演示數(shù)據(jù)。將日期的表示形式改變：Alter session set nls_date_format=yyyy-mm-dd;（1） 菜肴類別表MKInsert into MK values(01,魚類);Insert into MK values(02,蔬菜類);Insert into MK values(03,涼菜類);Insert into MK values(04,肉類);Insert into MK values(05,主食類);Insert into MK values(06,酒水類);（2） 菜單信息表MListInsert into MList values(01,鱈魚,01,30,24,2012-05-17);Insert into MList values(02,鯉魚,01,42,28,2012-05-17);Insert into MList values(03,帶魚,01,25,20,2012-05-20);Insert into MList values(04,黃花魚,01,34,28,2012-05-20);Insert into MList values(05,鲅魚,01,18,15,2012-05-30);Insert into MList values(06,芹菜,02,15,12,2012-06-01);Insert into MList values(07,韭菜,02,10,8,2012-06-10);Insert into MList values(08,白菜豆腐,02,13,10,2012-06-15);Insert into MList values(09,菠菜,02,8,5,2013-04-05);Insert into MList values(10,花生米,03,12,10,2013-04-05);Insert into MList values(11,皮蛋豆腐,03,13,10,2013-05-06);Insert into MList values(12,海帶,03,7,4,2013-05-06);Insert into MList values(13,辣白菜,03,8,5,2013-06-01);Insert into MList values(14,豬肉,04,34,25,2013-06-01);Insert into MList values(15,牛排,04,48,40,2013-12-01);Insert into MList values(16,羊排,04,45,36,2013-12-01);Insert into MList values(17,燉雞,04,42,38,2013-12-31);Insert into MList values(18,豬頭肉,04,28,24,2013-12-31);Insert into MList values(19,豬蹄,04,36,30,2014-01-01);Insert into MList values(20,宮保雞丁,04,20,18,2014-01-10);Insert into MList values(21,米飯,05,3,2.5,2014-02-01);Insert into MList values(22,饅頭,05,2,1.5,2014-02-01);Insert into MList values(23,面條,05,4,2.6,2014-03-01);Insert into MList values(24,青島啤酒,06,5,3.5,2014-03-01);Insert into MList values(25,散白酒,06,3,1.5,2014-03-01);Insert into MList values(26,雪花啤酒,06,6,4,2014-04-01);Insert into MList values(27,哈爾濱啤酒,06,5,4,2014-04-12);Insert into MList values(28,燕京啤酒,06,5,4,2014-04-12);Insert into MList values(29,老村長(zhǎng),06,15,12,2014-04-12);Insert into MList values(30,林海雪原,06,18,14,2014-04-12);（3） 餐臺(tái)類別表DKInsert into DK values(01,大包); Insert into DK values(02,中包);Insert into DK values(03,小包); Insert into DK values(04,散臺(tái));（4） 餐臺(tái)信息表Dinfo Insert into Dinfo values(01,大包一,01,50,2012-01-01);Insert into Dinfo values(02,大包二,01,45,2012-01-01);Insert into Dinfo values(03,大包三,01,40,2012-01-01);Insert into Dinfo values(04,中包一,02,30,2013-04-05);Insert into Dinfo values(05,中包二,02,25,2013-04-05);Insert into Dinfo values(06,中包三,02,20,2013-07-01);Insert into Dinfo values(07,中包四,02,20,2013-08-01);Insert into Dinfo values(08,小包一,03,15,2014-01-01);Insert into Dinfo values(09,小包二,03,15,2014-01-01);Insert into Dinfo values(10,小包三,03,10,2014-01-01);Insert into Dinfo values(11,小包四,03,10,2014-04-01);Insert into Dinfo values(12,散臺(tái)一,04,8,2014-04-01);Insert into Dinfo values(13,散臺(tái)二,04,8,2014-04-01);Insert into Dinfo values(14,散臺(tái)三,04,6,2014-04-01);Insert into Dinfo values(15,散臺(tái)四,04,6,2014-04-10);Insert into Dinfo values(16,散臺(tái)五,04,4,2014-04-10);Insert into Dinfo values(17,散臺(tái)六,04,4,2014-04-15);Insert into Dinfo values(18,散臺(tái)七,04,4,2014-04-15);Insert into Dinfo values(19,散臺(tái)八,04,2,2014-04-20);Insert into Dinfo values(20,散臺(tái)九,04,2,2014-04-20);3.3 完成【實(shí)驗(yàn)內(nèi)容】中的觸發(fā)器、存儲(chǔ)過(guò)程、函數(shù)和程序包等功能設(shè)計(jì)3.3.1完成【2.1 觸發(fā)器設(shè)計(jì)】（1）為消費(fèi)單明細(xì)表CList定義一個(gè)觸發(fā)器，每插入（INSERT）一條消費(fèi)單明細(xì)記錄（消費(fèi)單號(hào)，序號(hào)，菜肴編號(hào)，消費(fèi)數(shù)量），自動(dòng)根據(jù)菜肴編號(hào)從菜單信息表MList中讀取菜肴名稱Mname、菜肴單價(jià)Mprice、菜肴成本單價(jià)Mcost，然后計(jì)算其消費(fèi)金額（=消費(fèi)數(shù)量菜肴單價(jià)）、以及消費(fèi)單主表C的消費(fèi)金額合計(jì)、盈利金額合計(jì)。編寫相應(yīng)的插入語(yǔ)句（INSERT）和查詢語(yǔ)句（SELECT）測(cè)試該觸發(fā)器效果。create or replace trigger InsertClistbefore insert on Clist for each rowdeclarev_Smoney C.Smoney%type;v_SPsum C.SPsum%type;BeginSelect Mname,Mprice,Mcost,:new.Cqty*Mprice into :new.Mname,:new.Mprice,:new.Mcost,:new.Cmoney from Mlist where Mlist.Mid=:new.Mid;Select nvl(sum(Cqty*Mprice),0),nvl(sum(Cqty*(Mprice-Mcost),0) into v_Smoney,v_SPsum from Clist where Clist.Cid=:new.Cid;update C set Smoney=v_Smoney+:new.Cmoney,SPsum=v_SPsum+(:new.Cqty*(:new.Mprice-:new.Mcost) where C.Cid=:new.Cid;end InsertClist;測(cè)試觸發(fā)器：insert into CList(Cid,Sid,Mid,Cqty) values(1,1,1,1); insert into CList(Cid,Sid,Mid,Cqty) values(1,2,6,3);insert into CList(Cid,Sid,Mid,Cqty) values(1,3,14,2); insert into CList(Cid,Sid,Mid,Cqty) values(1,4,22,10);insert into CList(Cid,Sid,Mid,Cqty) values(1,5,28,6); insert into CList(Cid,Sid,Mid,Cqty) values(1,6,29,2);insert into CList(Cid,Sid,Mid,Cqty) values(2,1,2,2); insert into CList(Cid,Sid,Mid,Cqty) values(2,2,3,1);insert into CList(Cid,Sid,Mid,Cqty) values(2,3,9,2); insert into CList(Cid,Sid,Mid,Cqty) values(2,4,21,5);insert into CList(Cid,Sid,Mid,Cqty) values(2,5,28,6); insert into CList(Cid,Sid,Mid,Cqty) values(2,6,14,2);insert into CList(Cid,Sid,Mid,Cqty) values(2,7,20,1); insert into CList(Cid,Sid,Mid,Cqty) values(3,1,4,2);insert into CList(Cid,Sid,Mid,Cqty) values(3,2,9,3); insert into CList(Cid,Sid,Mid,Cqty) values(3,3,23,5);insert into CList(Cid,Sid,Mid,Cqty) values(3,4,15,2); insert into CList(Cid,Sid,Mid,Cqty) values(4,1,4,2);insert into CList(Cid,Sid,Mid,Cqty) values(4,2,16,3); insert into CList(Cid,Sid,Mid,Cqty) values(4,3,13,2);insert into CList(Cid,Sid,Mid,Cqty) values(4,4,22,6); nsert into CList(Cid,Sid,Mid,Cqty) values(4,5,28,2);insert into CList(Cid,Sid,Mid,Cqty) values(5,1,4,2); insert into CList(Cid,Sid,Mid,Cqty) values(5,2,8,3);insert into CList(Cid,Sid,Mid,Cqty) values(5,3,20,2); insert into CList(Cid,Sid,Mid,Cqty) values(5,4,22,5);insert into CList(Cid,Sid,Mid,Cqty) values(6,1,2,1); insert into CList(Cid,Sid,Mid,Cqty) values(6,2,19,2);insert into CList(Cid,Sid,Mid,Cqty) values(6,3,16,3); insert into CList(Cid,Sid,Mid,Cqty) values(6,4,23,6);改變時(shí)間格式： Alter session set nls_date_format=yyyy-mm-dd hh24:mi:ss;向C表中插入其他信息：insert into C(Cid,Did,StartTime,EndTime) values(1,2,2013-3-15 18:30:00,2013-3-15 20:10:15);insert into C(Cid,Did,StartTime,EndTime) values(2,15,2014-4-1 12:30:00,2014-4-1 14:20:00);insert into C(Cid,Did,StartTime,EndTime) values(3,20,2014-4-12 17:30:02,2014-4-12 19:20:06);insert into C(Cid,Did,StartTime,EndTime) values(4,9,2014-4-18 12:10:05,2014-4-18 13:30:20);insert into C(Cid,Did,StartTime,EndTime) values(5,14,2014-4-20 17:20:10,2014-4-20 18:40:10);insert into C(Cid,Did,StartTime,EndTime) values(6,13,2014-4-26 12:05:40,2014-4-26 13:30:20);select cl.*,Cqty*(Mprice-Mcost) as 單項(xiàng)盈利金額 from Clist cl order by Cid,Sid;select Cid,sum(Cmoney),sum(Cqty*Mprice),sum(Cqty*(Mprice-Mcost) from Clist group by Cid order by Cid;select* from C order by Cid;（2） 為消費(fèi)單明細(xì)表CList定義一個(gè)觸發(fā)器，每更新UPDATE一條消費(fèi)單明細(xì)表記錄，自動(dòng)修改其消費(fèi)金額、以及消費(fèi)單主表C的消費(fèi)金額合計(jì)、盈利金額合計(jì)。編寫相應(yīng)的更新語(yǔ)句（UPDATE）和查詢語(yǔ)句（SELECT）測(cè)試該觸發(fā)器效果。create or replace trigger UpdateClistbefore update on Clist for each rowbeginselect Mname,Mprice,Mcost into :new.Mname,:new.Mprice,:new.Mcost from Mlist where Mlist.Mid=:new.Mid;:new.Cmoney:=nvl(:new.Cqty,:old.Cqty)*nvl(:new.Mprice,:old.Mprice);if :new.Cid is not null thenupdate C set Smoney=Smoney-:old.Cmoney,SPsum=SPsum-:old.Cqty*(:old.Mprice-:old.Mcost) where C.Cid=:old.Cid;update C set Smoney=Smoney+:new.Cmoney,SPsum=SPsum+:new.Cqty*(:new.Mprice-:new.Mcost) where C.Cid=:new.Cid;elseUpdate C set Smoney=Smoney-:old.Cmoney+:new.Cmoney,SPsum=SPsum-:old.Cqty*(:old.Mprice-:old.Mcost)+:new.Cqty*(:new.Mprice-:new.Mcost) where C.Cid=:old.Cid;end if;end UpdateClsit;測(cè)試觸發(fā)器：消費(fèi)單號(hào)改變：select cl.*,Smoney,SPsum from Clist cl,C where cl.Cid=1 and cl.Sid=3 and C.Cid=1;update Clist set Cid=4 where Cid=1 and Sid=6;select cl.*,Smoney,SPsum from Clist cl,C where cl.Cid=1 and cl.Sid=3 and C.Cid=1;消費(fèi)單號(hào)未改變：select cl.*,Smoney,SPsum from Clist cl,C where cl.Cid=3 and cl.Sid=4 and C.Cid=3;update Clist set Cqty=4 where Cid= 3 and Sid= 4 ;select cl.*,Smoney,SPsum from Clist cl,C where cl.Cid=3 and cl.Sid=4 and C.Cid=3;（3） 為消費(fèi)單明細(xì)表CList定義一個(gè)觸發(fā)器，每刪除DELETE一條消費(fèi)單明細(xì)表記錄自動(dòng)修改其消費(fèi)單主表C的消費(fèi)金額合計(jì)、盈利金額合計(jì)。編寫相應(yīng)的刪除語(yǔ)句（DELETE）和查詢語(yǔ)句（SELECT）測(cè)試該觸發(fā)器效果。create or replace trigger DeleteClist before delete on Clist for each rowbegin update C set Smoney=Smoney-:old.Cmoney,SPsum=SPsum-:old.Cqty*(:old.Mprice-:old.Mcost) where C.Cid=:old.Cid;end DeleteClist;測(cè)試觸發(fā)器：select * from C where Cid=2;delete from Clist where Cid=2 and Sid=4;select * from C where Cid=2;（4）將【2.1.1】、【2.1.2】、【2.1.3】三個(gè)觸發(fā)器禁用disable，重新編寫一個(gè)觸發(fā)器實(shí)現(xiàn)這三個(gè)觸發(fā)器的全部功能。編寫相應(yīng)的插入語(yǔ)句（INSERT）、更新語(yǔ)句（UPDATE）、刪除語(yǔ)句（DELETE）和查詢語(yǔ)句（SELECT）測(cè)試該觸發(fā)器效果。禁用觸發(fā)器：Alter table Clist disable all triggers;創(chuàng)建觸發(fā)器：create or replace trigger DML_Clist before insert or update or delete on Clist for each rowdeclarev_Smoney C.Smoney%type;v_SPsum C.SPsum%type;beginif inserting thenselect Mname,Mprice,Mcost,:new.Cqty*Mprice into :new.Mname,:new.Mprice,:new.Mcost,:new.Cmoney from Mlist where Mlist.Mid=:new.Mid;select nvl(sum(Cqty*Mprice),0),nvl(sum(Cqty*(Mprice-Mcost),0) into v_Smoney,v_SPsum from Clist where Clist.Cid=:new.Cid;update C set Smoney=v_Smoney+:new.Cmoney,SPsum=v_SPsum+(:new.Cqty*(:new.Mprice-:new.Mcost) where C.Cid=:new.Cid;elsif updating thenselect Mname,Mprice,Mcost into :new.Mname,:new.Mprice,:new.Mcost from Mlist where Mlist.Mid=:new.Mid;:new.Cmoney:=nvl(:new.Cqty,:old.Cqty)*nvl(:new.Mprice,:old.Mprice);if :new.Cid is not null thenupdate C set Smoney=Smoney-:old.Cmoney,SPsum=SPsum-:old.Cqty*(:old.Mprice-:old.Mcost) where C.Cid=:old.Cid;update C set Smoney=Smoney+:new.Cmoney,SPsum=SPsum+:new.Cqty*(:new.Mprice-:new.Mcost) where C.Cid=:new.Cid;elseupdate C set Smoney=Smoney-:old.Cmoney+:new.Cmoney,SPsum=SPsum-:old.Cqty*(:old.Mprice-:old.Mcost)+:new.Cqty*(:new.Mprice-:new.Mcost) where C.Cid=:old.Cid;end if;elseupdate C set Smoney=Smoney-:old.Cmoney,SPsum=SPsum-:old.Cqty*(:old.Mprice-:old.Mcost) where C.Cid=:old.Cid;end if;end DML_Clist; 測(cè)試觸發(fā)器：Insert 測(cè)試;select Smoney,SPsum from C where C.cid=6;insert into clist (cid,sid,mid,cqty)values(6,5,7,1);select cl.*,Smoney,SPsum from clist cl,C where cl.cid=6 and sid=5 and C.cid=6;Update 測(cè)試：select cl.*,Smoney,SPsum from clist cl,C where cl.cid=4 and sid=3 and C.cid=4;update clist set mid=6,cqty=5 where cid=4 and sid=3;select cl.*,Smoney,SPsum from clist cl,C where cl.cid=4 and sid=3 and C.cid=4;Delete 測(cè)試：select* from c where cid=6;delete from clist where cid=6 and sid=5;select* from c where cid=6;經(jīng)過(guò)測(cè)試可知，這個(gè)觸發(fā)器有著與前面三個(gè)觸發(fā)器共同的作用。3.3.2完成【2.2 存儲(chǔ)過(guò)程、自定義函數(shù)設(shè)計(jì)】（1）設(shè)計(jì)一個(gè)自定義函數(shù)fGetDTSum，實(shí)現(xiàn)統(tǒng)計(jì)某年份給定餐臺(tái)類別的成本金額合計(jì)的功能，輸入?yún)?shù)是統(tǒng)計(jì)年份和餐臺(tái)類別，返回?cái)?shù)據(jù)是成本金額合計(jì)。成本金額=消費(fèi)數(shù)量菜肴成本單價(jià)。求年份的函數(shù)為EXTRACT(YEAR FROM 日期字段)，本題：統(tǒng)計(jì)年份= EXTRACT(YEAR FROM EndTime)，EndTime為結(jié)賬時(shí)間字段。create or replace function fGetDTSum (v_year number ,v_DkName Dk.DkName%type)return CList.Mcost%typeas v_Scost CList.Mcost%type;beginselect sum(Cqty*Mcost) into v_Scost from CList CL,C,DK,Dinfo D where CL.Cid=C.Cid and C.Did=D.Did and D.Dkid=DK.Dkid and Dk.DkName=v_DkName and extract(year from EndTime)=v_year;return v_Scost;exceptionwhen no_data_found thendbms_output.put_line(該餐臺(tái)信息不存在!);end fGetDTSum;（2）設(shè)計(jì)一個(gè)存儲(chǔ)過(guò)程pGetKindSum，實(shí)現(xiàn)統(tǒng)計(jì)某年份給定菜肴類別的盈利金額合計(jì)的功能，輸入?yún)?shù)是統(tǒng)計(jì)年份和菜肴類別，輸出參數(shù)是盈利金額合計(jì)。盈利金額=消費(fèi)數(shù)量 (菜肴單價(jià) - 菜肴成本單價(jià))。create or replace procedure pGetKindSum(v_year number,v_MkName Mk.MkName%type)asv_GetMoney C.SPsum%type;beginselect sum(Cqty*(CL.Mprice-CL.Mcost)into v_GetMoney from CList CL,C,Mk,MList ML whereCL.Cid=C.Cid and CL.Mid=ML.Mid and ML.Mkid=Mk.Mkid and Mk.MkName=v_MkName and extract(year from EndTime)=v_year;dbms_output.put_line(v_year|年|v_MkName|的盈利金額為：|v_GetMoney);exceptionwhen no_data_found thendbms_output.put_line(該年份該種菜肴無(wú)消費(fèi)!);end pGetKindSum;（3）編寫一段匿名PL/SQL程序塊，調(diào)用函數(shù)fGetDTSum，輸出2013年餐臺(tái)類別名為“包間”的成本金額合計(jì)；調(diào)用存儲(chǔ)過(guò)程pGetKindSum，輸出2013年菜肴類別名為“魚類”的盈利金額合計(jì)。declare v_1 CList.Mcost%type;beginpgetkindsum(2014,魚類);v_1:=fgetdtsum( 2013,大包);dbms_output.put_line(2013年大包的成本金額為：|v_1);end;輸出結(jié)果：3.3.3完成【2.3 程序包設(shè)計(jì)】（1）設(shè)計(jì)