实验五：触发器

最新推荐文章于 2023-03-09 17:30:42 发布

hellenionia

最新推荐文章于 2023-03-09 17:30:42 发布

阅读量328

点赞数 1

分类专栏：【数据库原理】数据库系统概论（第五版）文章标签：数据库 java 服务器

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

版权

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

6 篇文章 2 订阅

订阅专栏

【实验目的】

掌握数据库触发器的设计和使用方法

【实验准备】

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

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

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

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

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

这4张表并插入数据。

二、创建操作表

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

【实验内容】

前置代码：

-----------

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;

一、建立触发器，触发器名称为“T_模拟操作”，实现在操作表中录入药品代码、数量和使用天数后，自动生成一条处方主记录和处方明细表的数据。

功能1：操作表插入数据后，通过触发器操作，处方明细表中新增一条记录，新增记录的属性值为：

药品代码、数量和使用天数等于操作表中触发元组的药品代码、数量和使用天数

处方日期：为当前时间所在的日期，格式为yyyy-mm-dd

处方号：如果是今天的处方主表中的第一张处方，则处方号为10001；如果不是今天的第一张处方，则处方号为今天的最大处方号加1

序号：为了简化内容，假设每张处方只有一条明细，序号的值都为1

药品名称：根据药品代码，查询药品名称字典中的药品名称

药品规格、单价：根据药品代码，查询药品价表中的药品规格和单价。

处方明细唯一号：是一个拼接的字符串，为当前元组的"处方日期转换为字符串,格式为yyyymmdd"+"处方号转换为字符串"+"1"，是这内容的拼接数据。例如：‘20221130100021’

功能2：在新增的处方明细表记录之前，先新增处方主表

新增处方主记录的属性值为：

处方日期：为新增的处方明细表记录的处方日期

处方号：为新增的处方明细表记录的处方号

处方总金额：为新增的处方明细表记录中的单价乘以数量的结果。

个人答案：

create  trigger T_模拟操作 
after insert on 操作表
for each row
declare
n_max_处方号 number;
n_处方号 number;
v_药品名称 varchar2(50);
v_药品规格 varchar2(20);
n_单价 number;
v_处方唯一号 varchar2(20);

begin
--处方号
select max(处方号) into n_max_处方号 from 处方主表 where 处方日期 = truncate(sysdate);
if n_max_处方号 is null then 
n_处方号 := 10001;
else
n_处方号 := n_max_处方号 + 1;
end if;

--药品名称
select 药品名称 into v_药品名称 from 药品名称字典 where 药品代码 = :new.药品代码;

--药品规格
select 药品规格 into v_药品规格 from 药品价表 where 药品代码 = :new.药品代码;

--单价
select 单价 into n_单价 from 药品价表 where 药品代码 = :new.药品代码;

--处方唯一号
v_处方唯一号 := to_char(sysdate,'yyyymmdd')||n_处方号||'1';

insert into 处方主表(处方日期,处方号,处方金额)
values
(truncate(sysdate),n_处方号,n_单价);

insert into 处方明细表(处方日期,处方号,序号,药品代码,药品名称,药品规格,数量,单价,使用天数,处方唯一号)
values
(truncate(sysdate),n_处方号,1,:new.药品代码,v_药品名称,v_药品规格,:new.数量,n_单价,:new.使用天数,v_处方唯一号);

end;

参考答案：

CREATE OR REPLACE TRIGGER T_模拟操作
AFTER INSERT ON 操作表
FOR EACH ROW
DECLARE
n_max_处方号 NUMBER;
n_处方号 NUMBER;
v_药品名称 VARCHAR2(50);
v_药品规格 VARCHAR2(20);
n_单价 NUMBER;
v_处方唯一号 VARCHAR2(20);
BEGIN
--处方号
SELECT MAX(处方号) INTO n_max_处方号 FROM 处方主表 WHERE 处方日期 = trunc(SYSDATE);
IF n_max_处方号 IS NULL THEN
 n_处方号 := 10001;
ELSE
 n_处方号 := n_max_处方号 + 1;
END IF;

--药品名称
SELECT 药品名称 INTO v_药品名称 FROM 药品名称字典 WHERE 药品代码 = :NEW.药品代码;

--药品规格
SELECT 药品规格 INTO v_药品规格 FROM 药品价表 WHERE 药品代码 = :NEW.药品代码;

--单价
SELECT 单价 INTO n_单价 FROM 药品价表 WHERE 药品代码 = :NEW.药品代码;

--处方唯一号
v_处方唯一号 := to_char(SYSDATE,'yyyymmdd')||n_处方号||'1';

INSERT INTO 处方主表(处方日期,处方号,处方总金额)
VALUES
(trunc(SYSDATE),n_处方号,n_单价);

INSERT INTO 处方明细表(处方日期,处方号,序号,药品代码,药品名称,药品规格,数量,单价,使用天数,处方明细唯一号)
VALUES
(trunc(SYSDATE),n_处方号,1,:NEW.药品代码,v_药品名称,v_药品规格,:NEW.数量,n_单价,:NEW.使用天数,v_处方唯一号);

END;