Oracle-实验4(实验报告)-PL-SQL程序设计

学期学期 Oracle 数据库应用技术数据库应用技术 实验报告实验报告 选课序号 班 级 学 号 姓 名 指导教师 史金余 成 绩 2017 年 月 日 目目 录录 1 1 实验目的实验目的 1 2 2 实验内容实验内容 1 2 1 触发器设计触发器设计 2 2 2 存储过程 自定义函数设计存储过程 自定义函数设计 2 2 3 程序包设计程序包设计 3 3 3 实验步骤实验步骤 3 3 13 1 创建表空间创建表空间 RESTAURANT RESTAURANT 创建用户创建用户 DINERDINER 3 3 23 2 创建餐饮系统数据库的所有表 并向各表插入演示数据创建餐饮系统数据库的所有表 并向各表插入演示数据 4 3 3 完成完成 实验内容实验内容 中的触发器 存储过程 函数和程序包等中的触发器 存储过程 函数和程序包等 功能设计 将程序脚本保存到文本文件功能设计 将程序脚本保存到文本文件 Source sql 中中 7 4 4 实验总结实验总结 13 1 13 PL SQL 程序设计程序设计 1 1 实验目的实验目的 掌握 PL SQL 程序设计基本技巧 包括基本数据类型 表类型 数组类 型 匿名程序块 控制语句 PL SQL 中使用 SQL 语句 游标 错误处 理等 熟悉和掌握 PL SQL 中关于存储过程 函数 包和触发器程序设计技术 2 2 实验内容实验内容 实验平台 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 消费金额 消费数量 菜肴单价 消费数量为正数是正常 2 13 点菜 消费数量为负数是退菜 消费数量为 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 实现统计某年份给定菜肴类别的盈利金 额合计的功能 输入参数是统计年份和菜肴类别 输出参数是盈利金额合计 盈利金额 消费数量 菜肴单价 菜肴成本单价 3 13 2 2 3 编写一段匿名 PL SQL 程序块 调用函数 fGetDTSum 输出 2013 年餐台 类别名为 包间 的成本金额合计 调用存储过程 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 3 实验步骤实验步骤 备注 如果用实验室微机 请从 3 2 开始做 登录用户 DINER 改为 stuXX 3 13 1 创建表空间创建表空间 RESTAURANT RESTAURANT 创建用户创建用户 DINERDINER 3 1 2 用户 SYSTEM 登录 Oracle 3 1 3 创建表空间 RESTAURANT 大小 10M CREATE TABLESPACE RESTAURANT DATAFILE F RESTAURANT ora SIZE 10M DEFAULT STORAGE INITIAL 10K NEXT 50K MINEXTENTS 1 MAXEXTENTS 99 PCTINCREASE 10 ONLINE 4 13 3 1 4 创建用户 DINER 口令 XXX 默认表空间 RESTAURANT 给该用户授 予角色权限 CONNECT RESOURCE CREATE USER DINER IDENTIFIED BY wzl123 DEFAULT TABLESPACE RESTAURANT GRANT CONNECT TO DINER GRANT RESOURCE TO DINER 3 23 2 创建餐饮系统数据库的所有表 并向各表插入演示数据创建餐饮系统数据库的所有表 并向各表插入演示数据 3 2 1 启动 PL SQL Developer 或者启动 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 5 13 DKid number DkName varchar2 64 constraint pk DKid primary key DKid 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 主食类 6 13 insert into MK values 6 酒水 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号包间 1 20 sysdate insert into Dinfo values 2 2号包间 1 30 sysdate insert into Dinfo values 3 3号包间 1 50 sysdate insert into Dinfo values 4 1号散台 2 8 sysdate insert into Dinfo values 5 2号散台 2 8 sysdate insert into Dinfo values 6 3号散台 2 15 sysdate 7 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 CREATE OR REPLACE TRIGGER tri CList BEFORE INSERT ON CList FOR EACH ROW DECLARE t Smoney C Smoney type t SPsum C SPsum type BEGIN 补全Mlist SELECT Mname Mprice Mcost new Cqty Mprice INTO new Mname new Mprice new Mcost new Cmoney FROM Mlist WHERE Mlist Mid new Mid 计算Mlist的增加量 8 13 SELECT NVL SUM Cqty Mprice 0 NVL SUM Cqty Mprice Mcost 0 INTO v Smoney v SPsum FROM Clist WHERE Clist 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 END tri CList 2 2 1 2 CREATE OR REPLACE TRIGGER tri upCList BEFORE UPDATE ON CList FOR EACH ROW BEGIN 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 END tri upCList 3 2 1 3 CREATE OR REPLACE TRIGGER tri delCList BEFORE DELETE ON CList FOR EACH ROW BEGIN UPDATE C SET C Smoney C Smoney old Cmoney 9 13 C SPsum C SPsum old Cqty old Mprice old Mcost WHERE C Cid old Cid END tri delCList 4 2 1 4 CREATE OR REPLACE TRIGGER tri all BEFORE INSERT OR UPDATE OR DELETE ON CList FOR EACH ROW 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 10 13 UPDATE C SET C Smoney C Smoney old Cmoney C SPsum C SPsum old Cqty old Mprice old Mcost END IF END tri all 3 3 4 完成 2 2 存储过程 自定义函数设计 1 2 2 1 CREATE OR REPLACE FUNCTION fGetDTSum Cyear char Ctable dk dkname type RETURN C 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 RETURN cons EXCEPTION WHEN NO DATA FOUND THEN DBMS OUTPUT PUT LINE The data is invalid END fGetDTSum 2 2 2 2 CREATE OR REPLACE PROCEDURE pGetKindSum 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 WHEN NO DATA FOUND THEN DBMS OUTPUT PUT LINE The data doesn t exists END pGetKindSum 3 2 2 3 DECLARE 11 13 cons1 C spsum TYPE cons2 clist mcost TYPE 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 CREATE OR REPLACE PACKAGE pkSUM AS FUNCTION fGetDTSum Cyear char Ctable dk dkname type RETURN C spsum TYPE PROCEDURE pGetKindSum Cyear char Cname char cons out clist mcost TYPE END pkSUM CREATE OR REPLACE PACKAGE BODY pkSUM AS CREATE OR REPLACE FUNCTION fGetDTSum Cyear char Ctable dk dkname type RETURN C 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 RETURN cons 12 13 EXCEPTION WHEN NO DATA FOUND THEN DBMS OUTPUT PUT LINE The data is invalid END fGetDTSum CREATE OR REPLACE PROCEDURE pGetKindSum 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 WHEN NO DATA FOUND THEN DBMS OUTPUT PUT LINE The data doesn t exists END pGetKindSum END pkSUM CREATE OR REPLACE PACKAGE BODY pkSUM AS CREATE OR REPLACE FUNCTION fGe