实验七：存储过程

hellenionia

于 2022-12-29 15:05:20 发布

阅读量376

点赞数 1

分类专栏：【数据库原理】数据库系统概论（第五版）文章标签：数据库 java 开发语言

本文链接：https://blog.csdn.net/weixin_56463218/article/details/128481970

版权

【数据库原理】数据库系统概论（第五版）专栏收录该内容

6 篇文章 2 订阅

订阅专栏

【实验目的】

掌握PL/SQL编程语言，以及数据库存储过程的设计和使用方法

【实验准备】

一、完成实验四完整性实验1-4内容，根据要求建立

处方主表(处方日期,处方号,处方总金额)

处方明细表(处方日期,处方号,序号,药品代码,药品名称,药品规格,数量,单价,使用天数,处方明细唯一号)

药品价表(药品代码,药品规格,单价,开始时间,结束时间)

药品名称字典(药品代码,药品名称)

这4张表并插入数据。

二、运行语句

BEGIN
INSERT INTO 处方主表(处方日期,处方号,处方总金额) VALUES (to_date('20220527','yyyymmdd'),10001,40);
INSERT INTO 处方主表(处方日期,处方号,处方总金额) VALUES (to_date('20220527','yyyymmdd'),10002,7);
INSERT INTO 处方主表(处方日期,处方号,处方总金额) VALUES (to_date('20220526','yyyymmdd'),10002,216);
INSERT INTO 处方明细表 VALUES(to_date('20220526','yyyymmdd'),10002,1,'D00003','注射用重组人Ⅱ型肿瘤坏死因子受体-抗体融合蛋白','12.5mg',2,216,2,'20220526100031');

COMMIT;
END;

三、对处方主表新增字段：平均金额

ALTER TABLE 处方主表 ADD 平均金额 NUMBER;

四、建立存储过程日志表

create table 存储过程日志表

(存储过程名 varchar2(100),

错误发生时间 date,

错误提示内容 varchar2(4000)

);

前置代码：

-----------

create table 药品名称字典
(药品代码 varchar2(100) primary key,
药品名称 varchar2(100)
); 

INSERT INTO 药品名称字典 VALUES ('D00001','酮替芬分散片');
INSERT INTO 药品名称字典 VALUES ('D00002','地诺孕素片');
INSERT INTO 药品名称字典 VALUES ('D00003','注射用重组人Ⅱ型肿瘤坏死因子受体-抗体融合蛋白');
INSERT INTO 药品名称字典 VALUES ('D00004','复方风湿宁片');

select * from 药品名称字典 for update;

------------

create table 药品价表
(药品代码 varchar2(100),
药品规格 varchar2(100),
单价 number(10,3),
开始时间 date not null,
结束时间 date,
primary key (药品代码，药品规格)
);

INSERT INTO 药品价表 VALUES ('D00001','1mg*24',18,to_date('2022-01-01 09:00:00','yyyy-mm-dd hh24:mi:ss'),NULL);
INSERT INTO 药品价表 VALUES ('D00002','2mg*28',36.5,to_date('2022-01-15 09:20:00','yyyy-mm-dd hh24:mi:ss'),to_date('2022-11-17 17:21:11','yyyy-mm-dd hh24:mi:ss'));
INSERT INTO 药品价表 VALUES ('D00003','12.5mg',216,to_date('2022-04-07 08:00:00','yyyy-mm-dd hh24:mi:ss'),NULL);
INSERT INTO 药品价表 VALUES ('D00004','0.21g*60',7.8,to_date('2022-06-19 08:00:00','yyyy-mm-dd hh24:mi:ss'),NULL); 

select * from 药品价表 for update;

----------

create table 处方明细表
(处方日期 date,
处方号 number(30),
序号 number(30),
药品代码 varchar2(100),
药品名称 varchar2(100),
药品规格 varchar2(100),
数量 number(30),
单价 number(10,2),
使用天数 number(30)check(使用天数 >=1 and 使用天数 <=30),
处方明细唯一号 varchar2(100) unique not null,
primary key(处方日期,处方号,序号),
foreign key(药品代码) references 药品名称字典(药品代码),
foreign key(药品代码,药品规格) references 药品价表(药品代码,药品规格)
);

INSERT INTO 处方明细表 VALUES (to_date('2022-05-26','yyyy-mm-dd'),10001,1,'D00001','酮替芬分散片','1mg*24',1,18,6,'20220526100011');
INSERT INTO 处方明细表 VALUES (to_date('2022-05-26','yyyy-mm-dd'),10001,2,'D00002','地诺孕素片','2mg*28',1,36.5,7,'20220526100012');

select * from 药品价表 for update;
select * from 处方明细表 for update;

--------

create table 处方主表
(处方日期 date,
处方号 number(30),
处方总金额 number(10,2),
primary key(处方日期,处方号)
);
INSERT INTO 处方主表 VALUES (to_date('2022-05-26','yyyy-mm-dd'),10001,54.5);

select * from 处方主表 for update;

---------

CREATE TABLE 操作表
(药品代码 VARCHAR2(20),
 数量 NUMBER,
 使用天数 NUMBER
);

select * from 操作表 for update;

---------
CREATE TABLE 存储过程日志表
(存储过程名 VARCHAR2(50),
 错误发生时间 date,
 错误提示 varchar2(4000)
);

BEGIN
INSERT INTO 处方主表(处方日期,处方号,处方总金额) VALUES (to_date('20220527','yyyymmdd'),10001,40);
INSERT INTO 处方主表(处方日期,处方号,处方总金额) VALUES (to_date('20220527','yyyymmdd'),10002,7);
INSERT INTO 处方主表(处方日期,处方号,处方总金额) VALUES (to_date('20220526','yyyymmdd'),10002,216);
INSERT INTO 处方明细表 VALUES(to_date('20220526','yyyymmdd'),10002,1,'D00003','注射用重组人Ⅱ型肿瘤坏死因子受体-抗体融合蛋白','12.5mg',2,216,2,'20220526100031');
COMMIT;
END;

-------

ALTER TABLE 处方主表 ADD 平均金额 NUMBER;

【实验内容】

一、建立存储过程,存储过程名称为“proc_平均金额计算”，实现输入日期(入参“输入日期”，字符串，格式yyyymmdd，内容为“录入日期 VARCHAR2”)，计算当天每张处方平均每条药品价格功能。

具体功能：

1、根据输入日期,查询输入日期当天所有的处方主表的记录。

2、在这些查询到的处方主表记录中，用处方主表的处方总金额除以对于处方明细表中对应的明细药品记录数，得到平均每条药品记录金额，更新到处方主记录新增的平均金额字段中。平均金额保留2位小数。

3、如果存储过程中发生错误，将存储过程名、错误发生时间和错误提示内容存放到存储过程日志表中。

4、存储过程结束时，提交更新。

个人答案：


--------yi-------
CREATE OR REPLACE PROCEDURE proc_平均金额计算(录入日期 varchar2) IS
  CURSOR c_temp IS
    SELECT * FROM 处方主表
    WHERE 处方日期 = to_date(录入日期,'yyyymmdd');
  ROW_NR  c_temp%ROWTYPE;
  i_count int;
  V_MSG VARCHAR2(4000);
BEGIN
    OPEN c_temp;
    FETCH c_temp
      INTO ROW_NR;
    WHILE c_temp%FOUND LOOP
    
--查询对应处方明细表药品条数
    SELECT count(1) INTO i_count FROM 处方明细表 a
    WHERE a.处方日期 = row_nr.处方日期
    and a.处方号 = row_nr.处方号;

--查询每条药品平均金额更新到处方主表
update 处方主表 a set 平均金额 = round(row_nr.处方总金额/i_count,2)
where a.处方日期 = row_nr.处方日期
and   a.处方号 = row_nr.处方号;

      FETCH c_temp
        INTO ROW_NR;
    END LOOP;
    CLOSE c_temp;
    
exception  when others THEN
V_MSG :=SQLERRM;
insert into 存储过程日志表(存储过程名,错误发生时间,错误提示) values ('proc_平均金额计算',sysdate,V_MSG);
  COMMIT;
END;

参考答案：

CREATE OR REPLACE PROCEDURE proc_平均金额计算(录入日期 VARCHAR2)
IS
  CURSOR c_temp IS
    SELECT * FROM 处方主表
    WHERE 处方日期 = to_date(录入日期,'yyyymmdd');
  ROW_NR  c_temp%ROWTYPE;
  i_count INT;
  V_MSG VARCHAR2(4000);
BEGIN
    OPEN c_temp;
    FETCH c_temp
      INTO ROW_NR;
    WHILE c_temp%FOUND LOOP

--查询对应处方明细表药品条数
SELECT COUNT(1) INTO i_count FROM 处方明细表 a
WHERE a.处方日期 = ROW_NR.处方日期
AND   a.处方号 = ROW_NR.处方号;
--计算每条药品平均金额更新到处方主表
update 处方主表 a set  平均金额 = round(ROW_NR.处方总金额/i_count,2)
WHERE a.处方日期 = ROW_NR.处方日期
AND   a.处方号 = ROW_NR.处方号;

      FETCH c_temp
        INTO ROW_NR;
    END LOOP;
    CLOSE c_temp;

exception  when others THEN
V_MSG :=SQLERRM;
INSERT INTO 存储过程日志表(存储过程名,错误发生时间,错误提示) VALUES ('proc_平均金额计算',SYSDATE,V_MSG);

COMMIT;
END;

二、测试存储过程1

执行语句：

CALL proc_平均金额计算('20220526');

(提供以下2条语句的查询结果：

Select * from 处方主表 where 处方日期=to_date('20220526','yyyymmdd');
SELECT * FROM 存储过程日志表;)

个人答案：