学期
Oracle 数据库应用技术
实验报告
选课序号：
班 级：
学 号：
姓 名：
指导教师： 史金余
成 绩：
2017年 月 日
目 录
1. 实验目的 1
2. 实验内容 1
2.1 触发器设计 2
2.2 存储过程、自定义函数设计 2
2.3 程序包设计 3
3. 实验步骤 3
3.1 创建表空间 RESTAURANT,创建用户 DINER 3
3.2 创建餐饮系统数据库的所有表，并向各表插入演示数据 4
3.3 完成【实验内容】中的触发器、存储过程、函数和程序包等
功能设计，将程序脚本保存到文本文件 Source.sql中 7
4. 实验总结 13
PL/SQL 程序设计
实验目的掌握 PL/SQL 程序设计基本技巧，包括基本数据类型、表类型、数组类型、匿名程序块、控制语句、 PL/SQL 中使用 SQL 语句、游标、错误处理等。
熟悉和掌握 PL/SQL 中关于存储过程、函数、包和触发器程序设计技术。
实验内容
实验平台： PL/SQL Developer 或 Oracle 的其它客户端管理工具。
某餐饮系统数据库（加粗字段为主键，斜体字段为外键） ，请创建如下各数据表，并实现如下存储过程、函数、包和触发器等功能设计，将程序脚本保存到文本文件 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，餐台编号 Did ，消费开始时间 StartTime，结账时间 EndTime，消费金额合计 Smoney，盈利金额合计 SPsum)，其中，消费金额合计 =消费单明细表 CList 中该消费单号的所有消费记录的消费
金额的合计，即 SUM（消费金额）或 SUM（菜肴单价 × 消费数量），盈利金额合计 =消费单明细表 CList 中该消费单号的 所有消费记录的盈利合计，即 SUM （ (菜肴单价 - 菜肴成本单价 ) ×消费数量）。
（ 6） 消费单明细表 CList (消费单号 Cid，序号 Sid，菜肴编号 Mid ，菜肴名称Mname，消费数量 Cqty，菜肴单价 Mprice ，菜肴成本单价 Mcost，消费金额 Cmoney) ，消费金额 =消费数量 ×菜肴单价；消费数量为 正数是正常点菜，消费数量为 负数是退菜，消费数量为 0 是赠菜。
1/13
2.1 触发器设计
2.1.1 为消费单明细表 CList 定义一个触发器，每插入（ INSERT）一条消费单明
细记录（消费单号，序号 ，菜肴编号 ，消费数量），自动根据菜肴编号从菜单
信息表 MList 中读取菜肴名称 Mname、菜肴单价 Mprice 、菜肴成本单价 Mcost，然后计算其消费金额（ =消费数量 ×菜肴单价）、以及消费单主表 C 的消费金
额合计、盈利金额合计。编写相应的插入语句（ INSERT ）和查询语句
（ SELECT）测试该触发器效果。
2.1.2 为消费单明细表 CList 定义一个触发器，每更新 UPDATE 一条消费单明细
表记录，自动修改其消费金额、以及消费单主表 C 的消费金额合计、盈利金
额合计。编写相应的更新语句（ UPDATE）和查询语句（ SELECT）测试该触
发器效果。
2.1.3 为消费单明细表 CList 定义一个触发器，每删除 DELETE 一条消费单明细
表记录自动修改其消费单主表 C 的消费金额合计、盈利金额合计。编写相应
的删除语句（ DELETE ）和查询语句（ SELECT）测试该触发器效果。
2.1.4 将【 2.1.1】、【2.1.2】、【 2.1.3】三个触发器禁用 disable，重新编写一个触发
器实现这三个触发器的全部功能。编写相应的插入语句（ INSERT）、更新语句（ UPDATE）、删除语句（ DELETE ）和查询语句（ SELECT）测试该触发器效果。
2.2 存储过程、自定义函数设计
2.2.1 设计一个自定义函数 fGetDTSum，实现统计某年份给定餐台类别的成本金
额合计的功能， 输入参数是统计年份和餐台类别， 返回数据是成本金额合计。
成本金额 = 消费数量 ×菜肴成本单价。求年份的函数为 EXTRACT(YEAR
FROM 日期字段 )，本题：统计年份 = EXTRACT(YEAR FROM EndTime)，
EndTime 为结账时间字段。
2.2.2 设计一个存储过程 pGetKindSum，实现统计某年份给定菜肴类别的盈利金
额合计的功能， 输入参数是统计年份和菜肴类别， 输出参数是盈利金额合计。
盈利金额 =消费数量 ×(菜肴单价 - 菜肴成本单价 )。
2.2.3 编写一段匿名 PL/SQL 程序块，调用函数 fGetDTSum，输出 2013 年餐台类
2/13
别名为“包间”的成本金额合计；调用存储过程 pGetKindSum，输出 2013
年菜肴类别名为“鱼类”的盈利金额合计。
2.3 程序包设计
2.3.1 设计一个程序包，包名为 pkSUM ，包括并实现【 2.2.1】和【 2.2.2】的函数
及存储过程功能，注意：先创建包头 package，包头创建成功后，再创建包体
package body。
2.3.2 设计一个匿名 PL/SQL 程序块，参照【 2.2.3】调用【 2.3.1】中程序包的函
数和存储过程，输出 2013 年餐台类别名为“散台”的成本金额合计，输出
2013 年菜肴类别名为“蔬菜类”的盈利金额合计。
实验步骤
（备注：如果用实验室微机，请从【 3.2 】开始做，登录用户 DINER改为 stu XX） 3.1 创建表空间 RESTAURANT,创建用户 DINER
3.1.2 用户 SYSTEM 登录 Oracle
3.1.3 创建表空间 RESTAURANT ，大小 10M 。
CREATETABLESPACERESTAURANT
DATAFILE 'F:\RESTAURANT.ora' SIZE 10M
DEFAULTSTORAGE
( INITIAL 10K
NEXT50K
MINEXTENTS1
MAXEXTENTS99
PCTINCREASE10)
ONLINE;
3.1.4 创建用户 DINER ，口令 XXX ，默认表空间 RESTAURANT ，给该用户授予
角色权限 CONNECT 、RESOURCE。
3/13
CREATEUSERDINER IDENTIFIED BY "wzl123" DEFAULTTABLESPACERESTAURANT; GRANTCONNECTODINER;
GRANTRESOURCETO DINER;
3.2 创建餐饮系统数据库的所有表，并向各表插入演示数据
3.2.1 启动 PL/SQLDeveloper（或者启动 SQL*PLUS、Enterprise Manager Console 、
浏览器模式的 EM（企业管理器）等工具均可以） ，用户 DINER 登录 Oracle。
3.2.2 创建实验内容中的餐饮系统数据库的所有表 (菜肴类别表 MK 、菜单信息表 MList 、餐台类别表 DK 、餐台信息表 Dinfo 、消费单主表 C、消费单明细表 CList) 。
create table MK(
MKid number,
MkName varchar2 ( 64),
constraint pk_MKid primary key ( MKid)
);
create table MList (
Mid number,
Mname varchar2 ( 64),
MKid number references MK( MKid),
Mprice number( 8, 2),
Mcost number( 8, 2),
Mdate date ,
constraint pk_Mid primary key ( Mid)
);
create table DK(
DKid number,
DkName varchar2 ( 64),
constraint pk_DKid primary key ( DKid)
);
4/13
create table Dinfo (
Did number,
Dname varchar2 ( 64),
DKid number references DK( DKid),
Dseats number,
Ddate date ,
constraint pk_Did primary key ( Did)
);
create table C (
Cid number,
Did number references Dinfo ( Did ),
StartTime date ,
EndTime date ,
Smoney number( 8, 2),
SPsum number( 8, 2),
constraint pk_Cid primary key ( Cid)
);
create table
CList
(
Sid
number,
Cid
number references
C ( Cid ),
Mid
number references
MList
( Mid),
Mname
varchar2 ( 64),
Cqty
number,
Mprice
number( 8, 2),
Mcost
number( 8, 2),
Cmoney
number( 8, 2),
constraint
pk_Sid primary
key ( Sid )
);
3.2.3 依次向菜肴类别表 MK 、菜单信息表 MList 、餐台类别表 DK 、餐台信息表
Dinfo 插入足够多的演示数据。
insert
into
MK values ( 1, ' 鱼类 ' );
insert
into
MK values ( 2, ' 蔬菜类 ' );
insert
into
MK values ( 3, ' 凉菜类 ' );
insert
into
MK values ( 4, ' 肉类 ' );
insert
into
MK values ( 5, ' 主食类 ' );
insert
into
MK values ( 6, ' 酒水 ' );
5/13
insert
into
MList
values ( 1, ' 鲤鱼 ' , 1,
50.00
, 30.00 , sysdate );
insert
into
MList
values ( 2, ' 三文鱼 ' ,
1, 120.00
, 80.00 , sysdate );
insert
into
MList
values ( 3, ' 白菜 ' , 2,
15.00
, 5.00 , sysdate );
insert
into
MList
values ( 4, ' 土豆 ' , 2,
12.00
, 4.00 , sysdate );
insert
into
MList
values ( 5, ' 油麦菜 ' ,
2, 12.00 ,
5.00 , sysdate );
insert
into
MList
values ( 6, ' 凉拌黄瓜
' , 3, 5.00
, 3.00 , sysdate );
insert
into
MList
values ( 7, ' 鸡肉 ' , 4,
30.00
, 10.00 , sysdate );
insert
into
MList
values ( 8, ' 米饭 ' , 5,
1.50 , 0.50 , sysdate );
insert
into
MList
values ( 9, ' 二锅头 ' ,
6, 50.00 ,
30.00 , sysdate );
insert into DK values ( 1, ' 包间 ' );
insert into DK values ( 2, ' 散台 ' );
insert
into
Dinfo
values ( 1, '1 号包间 '
insert
into
Dinfo
values ( 2, '2 号包间 '
insert
into
Dinfo
values ( 3, '3 号包间 '
insert
into
Dinfo
values ( 4, '1 号散台 '
insert
into
Dinfo
values ( 5, '2 号散台 '
insert
into
Dinfo
values ( 6, '3 号散台 '
1, 20, sysdate );
1, 30, sysdate );
1, 50, sysdate );
2, 8, sysdate );
2, 8, sysdate );
2, 15, sysdate );
6/13
insert
into
C
values ( 1, 2, sysdate , sysdate , 275,
150);
insert
into
C
values
( 2, 2, sysdate , sysdate , 155,
80);
insert
into
C
values ( 3, 1, sysdate , sysdate , 566, 302);
insert
into
C
values
( 4, 2, sysdate , sysdate
, 89, 53);
insert
into
C
values ( 5, 1, sysdate , sysdate
, 798, 435);
insert
into
CList
values
( 1, 1, 2, ' 三文鱼 ' , 1, 120.00
, 80.00
, 120.00 );
insert
into
CList
values
(2,1,5,'
油麦菜 ' , 1, 12.00 ,
5.00 ,
12.00 );
insert
into
CList
values
(3,1,9,'
二锅头 ' , 2, 50.00 ,
30.00
,
100.00 );
3.3 完成【实验内容】中的触发器、存储过程、函数和程序包等功能设计，将程序脚本保存到文本文件 Source.sql中
3.3.1 在 PL/SQL Developer 环境下，用户 DINER 登录 Oracle
3.3.2 新建 SQL 窗口
3.3.3 完成【 2.1 触发器设计】
(1)2.1.1
CREATEORREPLACETRIGGERtri_CList
BEFOREINSERT ONCList FOREACHROW
DECLARE
t_Smoney C.Smoney%type ;
t_SPsum C.SPsum%type ;
BEGIN
-- 补全Mlist
SELECTMname, Mprice , Mcost ,: new.Cqty *Mprice
INTO : new.Mname,: new.Mprice ,: new.Mcost ,: new.Cmoney
FROMMlist WHEREMlist.Mid =: new.Mid ;
计算Mlist 的增加量
SELECTNVL( SUM( Cqty * Mprice ), 0), NVL( SUM( Cqty *( Mprice - Mcost)), 0)
7/13
INTO v_Smoney, v_SPsum FROMClist WHEREClist.Cid =: new.Cid ; -- 更新C
update C set Smoney=t_Smoney+: new.Cmoney, SPsum=t_SPsum+(: new.Cqty *(: new.Mprice -: new.Mcost ))
where C.Cid =: new.Cid ;
ENDtri_CList ;
(2)2.1.2
CREATEORREPLACETRIGGERtri_upCList BEFOREUPDATEONCList FOREACHROW
BEGIN
SELECTMname, Mprice , Mcost INTO : new.Mname,: new.Mprice ,: new.Mcost
FROMMlist WHEREMlist.Mid =: new.Mid ;
: new.Cmoney:= NVL(: new.Cqty ,: old.Cqty )* NVL(: new.Mprice ,: old.Mprice );
UPDATEC SETSmoney=Smoney-: old.Cmoney +: new.Cmoney,
SPsum =SPsum-: old.Cqty *(: old.Mprice -: old.Mcost )+: new.Cqty *(: new.Mprice -: new.Mcost )
WHEREC.Cid =: old.Cid ;
ENDtri_upCList ;
(3)2.1.3
CREATEORREPLACETRIGGERtri_delCList
BEFOREDELETEONCList FOREACHROW
BEGIN
UPDATEC SETC.Smoney =C.Smoney-: old.Cmoney ,
C.SPsum =C.SPsum-(: old.Cqty *(: old.Mprice -: old.Mcost ))
WHEREC.Cid =: old.Cid ;
8/13
ENDtri_delCList ;
(4)2.1.4
CREATEORREPLACETRIGGERtri_all
BEFOREINSERT OR UPDATEOR DELETE
ONCList
FOREACHROW
DECLARE
t_Smoney C.Smoney %type ;
t_SPsum C.SPsum %type ;
BEGIN
IF INSERTING THEN
Select 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 t_Smoney , t_SPsum
from Clist
where Clist.Cid =: new.Cid ;
update C
set Smoney=t_Smoney+: new.Cmoney, SPsum=t_SPsum+(: new.Cqty *(: new.Mprice -: new.Mcost ))
where C.Cid =: new.Cid ;
ELSIF UPDATING THEN
select 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 );
Update 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 ;
ELSE
UPDATEC SET
C.Smoney=C.Smoney-: old.Cmoney , C.SPsum=C.SPsum-(: old.Cqty *(: old.Mprice -: old.Mcost ));
9/13
ENDIF ;
ENDtri_all ;
3.3.4 完成【 2.2 存储过程、自定义函数设计】
(1)2.2.1
CREATEORREPLACEFUNCTIONfGetDTSum
( Cyear char , Ctable dk.dkname %type )
RETURNC.spsum %TYPE
AS
cons C.spsum %TYPE;
BEGIN
select nvl ( sum( spsum), 0) into cons from C
where did in (
select did from dinfo where dkid in (
select dkid from dk where dkname=Ctable ))
and ( to_char ( endtime , 'YYYY' )= Cyear);
RETURNcons ;
EXCEPTION
WHENO_DATA_FOUNDTHEN
DBMS_OUTPUT.PUT_LINE ( 'The data is invalid!' );
ENDfGetDTSum;
(2)2.2.2
CREATEORREPLACEPROCEDUREpGetKindSum(
Cyear char , Cname char ,
cons out clist.mcost %TYPE)
AS
BEGIN
select nvl ( sum( mprice - mcost ), 0) into cons from clist
where mid in
( select mid from mlist where mkid in
( select mkid from mk where mkname=Cname))
and
cid in
( select cid from C where to_char ( endtime , 'YYYY' )= Cyear );
EXCEPTION
WHENO_DATA_FOUNDTHEN
DBMS_OUTPUT.PUT_LINE( 'The data doesn ’’ t exists!' );
ENDpGetKindSum ;
(3)2.2.3
DECLARE
cons1 C.spsum %TYPE;
cons2 clist.mcost %TYPE;
10/13
BEGIN
cons1 := fGetDTSum( '2013' , ' 包间 ' );
pGetKindSum ( '2013' , ' 鱼类 ' , cons2 );
DBMS_OUTPUT.PUT_LINE( 'cons1 ' || cons1|| 'cons2 ' || cons2|| '' ); END;
3.3.5 完成【 2.3 程序包设计】
(1)2.3.1
CREATEORREPLACEPACKAGEpkSUM
AS
FUNCTIONfGetDTSum
( Cyear char , Ctable dk.dkname %type )
RETURNC.spsum %TYPE;
PROCEDUREpGetKindSum (
Cyear char , Cname char ,
cons out clist.mcost %TYPE);
ENDpkSUM;
CREATEORREPLACEPACKAGEBODYpkSUM
AS
CREATEOR REPLACEFUNCTIONfGetDTSum
( Cyear char , Ctable dk.dkname %type )
RETURNC.spsum %TYPE
AS
cons C.spsum %TYPE;
BEGIN
select nvl ( sum( spsum), 0) into cons from C
where did in (
select did from dinfo where dkid in (
select dkid from dk where dkname=Ctable ))
and ( to_char ( endtime , 'YYYY' )= Cyear);
RETURNcons ;
EXCEPTION
WHENO_DATA_FOUNDTHEN
11/ 13
DBMS_OUTPUT.PUT_LINE ( 'The data is invalid!' );
ENDfGetDTSum;
CREATEOR REPLACEPROCEDUREpGetKindSum(
Cyear char , Cname char ,
cons out clist.mcost %TYPE)
AS
BEGIN
select nvl ( sum( mprice - mcost ), 0) into cons from clist
where mid in
( select mid from mlist where mkid in
( select mkid from mk where mkname=Cname))
and
cid in
( select cid from C where to_char ( endtime , 'YYYY' )= Cyear);
EXCEPTION
WHENO_DATA_FOUNDTHEN
DBMS_OUTPUT.PUT_LINE ( 'The data doesn ’’ t exists!' );
ENDpGetKindSum ;
ENDpkSUM;
CREATEORREPLACEPACKAGEBODYpkSUM
AS
CREATEOR REPLACEFUNCTIONfGetDTSum
( Cyear char , Ctable dk.dkname %type )
RETURNC.spsum %TYPE
AS
cons C.spsum %TYPE;
BEGIN
select nvl ( sum( spsum), 0) into cons from C
where did in (
select did from dinfo where dkid in (
select dkid from dk where dkname=Ctable ))
and ( to_char ( endtime , 'YYYY' )= Cyear);
RETURNcons ;
EXCEPTION
WHENO_DATA_FOUNDTHEN
DBMS_OUTPUT.PUT_LINE ( 'The data is invalid!' );
ENDfGetDTSum;
CREATEOR REPLACEPROCEDUREpGetKindSum(
Cyear char , Cname char ,
cons out clist.mcost %TYPE)
AS
12/13
BEGIN
select nvl ( sum( mprice - mcost ), 0) into cons from clist
where mid in
( select mid from mlist where mkid in
( select mkid from mk where mkname=Cname))
and
cid in
( select cid from C where to_char ( endtime , 'YYYY' )= Cyear);
EXCEPTION
WHENO_DATA_FOUNDTHEN
DBMS_OUTPUT.PUT_LINE ( 'The data doesn ’’ t exists!' );
ENDpGetKindSum ;
ENDpkSUM;
(2)2.3.2
DECLARE
cons1 C.spsum %TYPE;
cons2 clist.mcost %TYPE;
BEGIN
cons1 := pkSUM.fGetDTSum1( '2013' , ' 散台 ' );
pkSUM.pGetKindSum1 ( '2013' , ' 蔬菜类 ' , cons2);
DBMS_OUTPUT.PUT_LINE( 'cons1:' || cons1|| ' cons2:' || cons2|| '' );
END;
实验总结
这次实验让我学会了许多东西， 比如 PLSQL Developer 的使用，高级 PL/SQL
程序的编写，其中包括触发器，储存过程，自定义函数，匿名函数，程序包
的设计与编写。另外，也让我又温习了一遍基础 SQL 语言。我也明白了一个
道理，“纸上得来终觉浅，绝知此事要躬行” ，学好这门课最好的办法还是上
级亲自实践一遍。总之，这门课令我受益匪浅。
13/13