Oracle实验

2015-2016-2学期。

oracle数据库应用技术。

实验报告。班级13级软件工程2班。

学号2220131011

姓名蒋生栋。

指导教师史金余。

成绩。2024年 5 月 24 日。

目录。2.1 触发器设计 2

2.2 存储过程、自定义函数设计 2

2.3 程序包设计 3

3.1 创建表空间restaurant,创建用户diner 3

3.2 创建餐饮系统数据库的所有表，并向各表插入演示数据 3

3.3 完成【实验内容】中的触发器、存储过程、函数和程序包等功能设计，将程序脚本保存到文本文件中 4

pl/sql程序设计。

1. 实验目的。

掌握pl/sql程序设计基本技巧，包括基本数据类型、表类型、数组类型、匿名程序块、控制语句、pl/sql中使用sql语句、游标、错误处理等。

熟悉和掌握pl/sql中关于存储过程、函数、包和触发器程序设计技术。

2. 实验内容。

实验平台：pl/sql developer或oracle的其它客户端管理工具。

某餐饮系统数据库（加粗字段为主键，斜体字段为外键），请创建如下各数据表，并实现如下存储过程、函数、包和触发器等功能设计，将程序脚本保存到文本文件中：

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是赠菜。

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，输出2024年餐台类别名为“包间”的成本金额合计；调用存储过程pgetkindsum，输出2024年菜肴类别名为“鱼类”的盈利金额合计。

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】中程序包的函数和存储过程，输出2024年餐台类别名为“散台”的成本金额合计，输出2024年菜肴类别名为“蔬菜类”的盈利金额合计。

3. 实验步骤。

备注：如果用实验室微机，请从【3.2】开始做，登录用户diner改为stuxx）

3.1 创建表空间restaurant,创建用户diner

3.1.2 用户system登录oracle

3.1.3 创建表空间restaurant，大小10m。

-oracle 的 for each row 触发器，不允许在触发器里面， select / update / delete 自己这个表。

-创建表空间restaurant，大小10m。（用户system登录oracle）

create tablespace restaurant

datafile 'h:\'size 10m不能使用相对路径。

default storage

initial 10k

next 50k

minextents 1

maxextents 99

pctincrease 10 )

online ;

3.1.4 创建用户diner，口令xxx，默认表空间restaurant，给该用户授予角色权限connect、resource。

create user diner identified by m123 default tablespace restaurant;

grant connect to diner

grant resource to diner

3.2 创建餐饮系统数据库的所有表，并向各表插入演示数据。

3.2.1启动pl/sql developer（或者启动sql*plus、enterprise manager console、浏览器模式的em（企业管理器）等工具均可以），用户diner登录oracle。

3.2.2 创建实验内容中的餐饮系统数据库的所有表(菜肴类别表mk、菜单信息表mlist、餐台类别表dk、餐台信息表dinfo、消费单主表c、消费单明细表clist)。

3.2.3依次向菜肴类别表mk、菜单信息表mlist、餐台类别表dk、餐台信息表dinfo插入足够多的演示数据。

-（1）菜肴类别表mk（菜肴类别编号mkid，菜肴类别名称mkname）,菜肴类别名称：鱼类、蔬菜类、凉。

菜类、肉类、主食类和酒水类等。

create sequence seq_mk

start with 0

increment by 1

minvalue 0

maxvalue 30000

nocycle

nocache ;

create table mk(

mkid number,mkname varchar2(64),constraint pk_mkid primary key (mkid)

insert into mk values(seq_'鱼类')

insert into mk values(seq_'蔬菜类')

insert into mk values(seq_'凉菜类')

insert into mk values(seq_'肉类')

insert into mk values(seq_'主食类')

insert into mk values(seq_'酒水')

-（2）菜单信息表mlist（菜肴编号mid，菜肴名称mname，菜肴类别mkid，菜肴单价mprice，菜肴成本单价mcost，更新日期mdate)。

Oracle实验

oracle实验

Oracle实验

Oracle实验

其他用户还读了