Oracle触发器过程等的实验

1、实验四：PL/SQL程序设计一、实验目的u 掌握PL/SQL程序设计基本技巧，包括基本数据类型、表类型、数组类型、匿名程序块、控制语句、PL/SQL中使用SQL语句、游标、错误处理等。u 熟悉和掌握PL/SQL中关于存储过程、函数、包和触发器程序设计技术。二、实验内容某餐饮系统数据库，请创建如下各数据表，并实现如下存储过程、函数、包和触发器等功能设计，将程序脚本保存到文本文件Source.sql中：（1） 菜肴类别表MK（菜肴类别编号MKid，菜肴类别名称MkName）,菜肴类别名称：鱼类、蔬菜类、凉菜类、肉类、主食类和酒水类等。（2） 菜单信息表MList（菜肴编号Mid，菜肴名称Mname

2、，菜肴类别MKid，菜肴单价Mprice，菜肴成本单价Mcost，更新日期Mdate)。（3） 餐台类别表DK（餐台类别编号DKid，餐台类别名称DkName），餐台类别：包间和散台等。（4） 餐台信息表Dinfo (餐台编号Did，餐台名称Dname，餐台类别DKid，座位数Dseats，更新日期Ddate)。（5） 消费单主表C (消费单号Cid，餐台编号Did，消费开始时间StartTime，结账时间EndTime，消费金额合计Smoney，盈利金额合计SPsum)，其中，消费金额合计=消费单明细表CList中该消费单号的所有消费记录的消费金额的合计，即SUM（消费金额）或SUM（菜肴单

3、价 × 消费数量），盈利金额合计=消费单明细表CList中该消费单号的所有消费记录的盈利合计，即SUM（(菜肴单价 - 菜肴成本单价)× 消费数量）。（6）消费单明细表CList (消费单号Cid，序号Sid，菜肴编号Mid，菜肴名称Mname，消费数量Cqty，菜肴单价Mprice，菜肴成本单价Mcost，消费金额Cmoney) ，消费金额=消费数量×菜肴单价；消费数量为正数是正常点菜，消费数量为负数是退菜，三、实验步骤及相关程序截图3.1 创建表空间RESTAURANT,创建用户DINER3.1.1 创建表空间RESTAURANT，大小10M。3.1.2创建用

4、户DINER，口令XXX，默认表空间RESTAURANT，给该用户授予角色权限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 创建餐饮系统数据库的所有表，并向各表插入演示数据。3.2.1创建实验内容中的餐饮系统数据库的所有表(菜肴类别表MK、菜单

5、信息表MList、餐台类别表DK、餐台信息表Dinfo、消费单主表C、消费单明细表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 n

6、umber(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 num

7、ber(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、餐台类别表DK、餐台信息表Dinfo插入足够多的演示数据。将日期的表示形式改变：Alter session set nls_date_format=yyyy-mm-d

8、d;（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,201

9、2-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,白菜豆

10、腐,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 v

11、alues(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

12、 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,2

13、014-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,老村长,06,15,12,2014-04-12);Insert into MList values(30,林海雪原,06,18,14,2014-04-12);（3） 餐台类别表DKInsert into DK

14、 values(01,大包); Insert into DK values(02,中包);Insert into DK values(03,小包); Insert into DK values(04,散台);（4） 餐台信息表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,中包一

15、,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,小包三

16、,03,10,2014-01-01);Insert into Dinfo values(11,小包四,03,10,2014-04-01);Insert into Dinfo values(12,散台一,04,8,2014-04-01);Insert into Dinfo values(13,散台二,04,8,2014-04-01);Insert into Dinfo values(14,散台三,04,6,2014-04-01);Insert into Dinfo values(15,散台四,04,6,2014-04-10);Insert into Dinfo values(16,散台五,04,

17、4,2014-04-10);Insert into Dinfo values(17,散台六,04,4,2014-04-15);Insert into Dinfo values(18,散台七,04,4,2014-04-15);Insert into Dinfo values(19,散台八,04,2,2014-04-20);Insert into Dinfo values(20,散台九,04,2,2014-04-20);3.3 完成【实验内容】中的触发器、存储过程、函数和程序包等功能设计3.3.1完成【2.1 触发器设计】（1）为消费单明细表CList定义一个触发器，每插入（INSERT）一条消费

18、单明细记录（消费单号，序号，菜肴编号，消费数量），自动根据菜肴编号从菜单信息表MList中读取菜肴名称Mname、菜肴单价Mprice、菜肴成本单价Mcost，然后计算其消费金额（=消费数量×菜肴单价）、以及消费单主表C的消费金额合计、盈利金额合计。编写相应的插入语句（INSERT）和查询语句（SELECT）测试该触发器效果。create or replace trigger InsertClistbefore insert on Clist for each rowdeclarev_Smoney C.Smoney%type;v_SPsum C.SPsum%type;BeginSel

19、ect 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_SPsu

20、m+(:new.Cqty*(:new.Mprice-:new.Mcost) where C.Cid=:new.Cid;end InsertClist;测试触发器：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

21、 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) value

22、s(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,

23、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); ns

24、ert 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) va

25、lues(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);改变时间格式： Alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'向C表中插入其他信息：insert into C(Cid,Did,StartTime,EndTime)

26、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 i

27、nto 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',

28、9;2014-4-26 13:30:20');select cl.*,Cqty*(Mprice-Mcost) as "单项盈利金额" 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） 为消费单明细表CList定义一个触发器，每更新UPDATE一条消费单明细表记录，自动修改其消费金额、以及消费单

29、主表C的消费金额合计、盈利金额合计。编写相应的更新语句（UPDATE）和查询语句（SELECT）测试该触发器效果。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

30、);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,S

31、Psum=SPsum-:old.Cqty*(:old.Mprice-:old.Mcost)+:new.Cqty*(:new.Mprice-:new.Mcost) where C.Cid=:old.Cid;end if;end UpdateClsit;测试触发器：消费单号改变：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

32、cl,C where cl.Cid=1 and cl.Sid=3 and C.Cid=1;消费单号未改变：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） 为消费单明细表CList定义一个触发器，每删除DELETE一条消

33、费单明细表记录自动修改其消费单主表C的消费金额合计、盈利金额合计。编写相应的删除语句（DELETE）和查询语句（SELECT）测试该触发器效果。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;测试触发器：select * from C wher

34、e 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】三个触发器禁用disable，重新编写一个触发器实现这三个触发器的全部功能。编写相应的插入语句（INSERT）、更新语句（UPDATE）、删除语句（DELETE）和查询语句（SELECT）测试该触发器效果。禁用触发器：Alter table Clist disable all triggers;创建触发器：create or replace trigger DML_Clist before

35、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*(Mpri

36、ce-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.M

37、id;: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.C

38、id=: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 D

39、ML_Clist; 测试触发器：Insert 测试;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 测试：select cl.*,Smoney,SPsum from clist cl,C where cl.cid=4 and sid=3 and C.cid=4;update clist se

40、t 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 测试：select* from c where cid=6;delete from clist where cid=6 and sid=5;select* from c where cid=6;经过测试可知，这个触发器有着与前面三个触发器共同的作用。3.3.2完成【2.2 存储过程、自定义函数设计】（1）设计一个自定义函数fGetDTSum，实现统计某年

41、份给定餐台类别的成本金额合计的功能，输入参数是统计年份和餐台类别，返回数据是成本金额合计。成本金额=消费数量×菜肴成本单价。求年份的函数为EXTRACT(YEAR FROM 日期字段)，本题：统计年份= EXTRACT(YEAR FROM EndTime)，EndTime为结账时间字段。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*Mc

42、ost) 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('该餐台信息不存在!');end fGetDTSum;（2）设计一个存储过程pGetKindSum，实现统计某年份给定菜肴类别的

43、盈利金额合计的功能，输入参数是统计年份和菜肴类别，输出参数是盈利金额合计。盈利金额=消费数量 × (菜肴单价 - 菜肴成本单价)。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 M

44、L.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('该年份该种菜肴无消费!');end pGetKindSum;（3）编写一段匿名PL/SQL程序块，调用函数fGetDTSum，输出2013年餐台类别名为“包间”的成本金额合计；调用存储过程pGetKindSum，输出2013年菜肴类别名为“鱼类”的盈利金额合计。declare v_1 CList.Mcost%type;beginpgetkindsum(2014,'鱼类');v_1:=fgetdtsum( 2013,'大包');dbms_output.put_line('2013年大包的成本金额为：'|v_1);end;输出结果：3.3.3完成【2.3 程序包设计】（1）设计一个程序包，包名为pkSUM，包括并实现【2.2.1】和【2.2.2】的函数