Oracle触发器过程等的实验.docx
实验四:PL/SQL程序设计一、实验目的 掌握PL/SQL程序设计基本技巧，包括基本数据类型、表类型、数组类型、匿名程序块、控制语句、PL/SQL中使用SQL语句、游标、错误处理等。 熟悉和掌握PL/SQL中关于存储过程、函数、包和触发器程序设计技术。二、实验内容某餐饮系统数据库，请创建如下各数据表，并实现如下存储过程、函数、包和触发器等功能设计，将程序脚本保存到文本文件Source, sql中：（1） 菜肴类别表MK （菜肴类别编号MKid,菜肴类别名称MkName）,菜肴类别名称：鱼类、蔬菜类、凉菜类、肉类、主食类和酒水类等。（2） 菜单信息表MList（菜肴编号Mid,菜肴名称Mname,菜肴类别MKid,菜肴单价Mprice,菜肴成本单价Mcost,更新日期Mdate）。（3） 餐台类别表DK （餐台类别编号DKid,餐台类别名称DkName）,餐台类别：包间和散台等。（4） 餐台信息表Dinfo （餐台编号Did,餐台名称Dname,餐台类物DKid,座位数Dseats,更新日期Ddate）。（5） 消费单主表C （消费单号Cid,餐台编号灯"消费开始时间StartTimc,结账时间EndTine,消费金额合计Smoney,盈利金额合计SPsum）,其中，消费金额合计二消费单明细表CList中该消费单号的所有消费记录的消费金额的合计，即SUM （消费金额）或SUM （菜肴单价X消费数量），盈利金额合计二消费单明细表CList中该消费单号的所有消费记录的盈利合计，即SUM （菜肴单价-菜肴成本单价）X消费数量）。（6）消费单明细表CList （消费单号Cid,序号Sid,菜肴编号Mid,菜肴名称Mname,消费数量Cqty,菜肴单价Mprice,菜肴成本单价Mcost,消费金额Cmoney）,消费金额二消费数量X菜肴单价；消费数量为正数是正常点菜，消费数量为负数是退菜，三、实验步骤及相关程序截图3. 1创建表空间RESTAURANT,创建用户DINER3. 1. 1创建表空间RESTAURANT,大小10Mo3. 1. 2创建用户DINER, 口令XXX,默认表空间RESTAURANT,给该用户授予角色权限CONNECT>RESOURCEoCreate tablespace restautant datafile T:oradataorclrestautant_ 1 .dbsize 10M;Create user diner identified by zw!2011 default tablespace restaurant;Grant connect .resource to diner;3.2创建餐饮系统数据库的所有表，并向各表插入演示数据。3.2. 1创建实验内容中的餐饮系统数据库的所有表(菜肴类别表MK、菜单信息表MList、餐台类别表DK、餐台信息表Dinfo、消费单主表C、消费单明细表CList)°Create table MK (MKid number(2) primary kcy,MKnamc varchar2(20);Create table DK(DKid number(2) primay key,DKname varchar2(20);CreatetableMList(Midnumber(2) primary key,Mname varchar2(20),MKidnumber(2)referencesMK(MKid),Mprice number(7,2),Mcost number(7,2),Mdate dale);CreatetableDinfo(Didnumber(2) primary key,Dname varchar2(20), DKidnumber(2)referencesDK(DKid),Dseats numbcr(4),Ddate date);Create table CList(Cid number(2),Sid number(2),primary key(Cid,Sid),Mid number(2) referencesMList(Mid),Mname varchar2(20),Cqty number(4),Mprice number(7,2),Mcost number(7,2),Cmoney number。,2);Create table C(Cid number(2) primary key, Did number(2) references Dinfo(Did),SlartTime date,EndTimedate,Smoney number(7,2), Spsum number(7,2);SQL> Create table I1K WKi d nunber (2) primary keyz MKname varcar2 (20);Table createdSQL> Create table DK(DKd number (2) primary key, DKnane varchar2 (20)：Tabi。crgtodSQD CreateMLi st (Mid number (2) primry cey? n vrchr2 (2O)/ HKi d number (2) rferencx MK OWKi d)z Mpri c nunbr (7, 2)z Mcost number C?Table created.SQL> Create table Dinfo (Did number (2) primary key, Dname varchar2 (20), DKid nxnber (2) references DK(DKdX Dseats number (4), Ddate date);Table created SQL> Crete tabl< CLi st (Cid number (2) primary kqy, Sid nunb«r (2), Mi d nsber (2) references MList (Wi d)z Mnne vrchar2 20)z Cqty nunbr (4)/ MprI Table createdSQL> Create tabl< C (Cid number ) primary key. Did number (2) references Dinfo (Di d), Star tTine EndTine date, Smoney nsber(7,2) Spsun>I Tkl 人八八/13. 2. 2依次向菜肴类别表MK、菜单信息表MList、餐台类别表DK、餐台信息表Dinfo插入足够多的演示数据。将口期的表示形式改变：Alter session set nls date fbrmat=,yyyy-mm-dd,i(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,'酒水类')； A AtkM ll Y LJL 3Y S 2 jJ row i nsrtdJL>JL> Insert into MK values (02,蔬菜类)：row inserted)L> XnsQi*t into MK(03/京录):row insertedJL> Insert into MK values (04, z 肉类)：row inserted)L> Insert into MK values (05主食类)：row insertedJL Ixter t into MK values (06/')：row inserted(2)菜单信息表MListInsert into MList values(01,'鳄鱼',01,30,24,'2012-()5-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-10Insert into MList values(08,'白菜豆腐,02,13,10,'2012-06-15');Insert into MList values(09,'菠菜',02,8,5,'2013-04-05Insert into MList valucs(l0,'花生米',03,12,10,'2013-04-05');Insert into MList values。1,'皮蛋豆腐',03,13,10,'2013-05-06');Insert into MList values。2,'海带',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。5,'牛排',04,48,40,'2013-12-01');Insert into MList values(16,'羊排',04,45,36,'2013-12-01Insert into MList values(l 7,'炖鸡',04,42,38,'2013-12-31');Insert into MList values。8,'猪头肉',04,28,24J2013-12-3T);Insert into MList values。9,'猪蹄',04,36,30,'2014-01-01');Insert into MList values(20,'宫保鸡丁',04,20/8,'2014-01-10');Insert into MList values(21,'米饭',05,3,2.5,'20140201');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.5,'2014-03-01'）;Insert into MList values（26,®JS,0
