实现效果:向销量表插入数据后,通过触发器调用存储过程同步更新至销售额表(工具为PL/SQL)
1、创建相关表
-- Create table
create table R_TEST1
(
stdate VARCHAR2(20),
productid VARCHAR2(200),
saleamount VARCHAR2(20)
);
comment on table R_TEST1 is '产品销量表';
comment on column R_TEST1.stdate is '日期';
comment on column R_TEST1.productid is '产品ID';
comment on column R_TEST1.saleamount is '销售数量';
CREATE TABLE R_TEST2
(
PRODUCTID VARCHAR2(200),
PRODUCTNAME VARCHAR2(20)
);
COMMENT ON TABLE R_TEST2 IS '产品表';
COMMENT ON COLUMN R_TEST2.PRODUCTID IS '产品ID';
COMMENT ON COLUMN R_TEST2.PRODUCTNAME IS '产品名称';
insert into R_TEST2 values('1','苹果');
insert into R_TEST2 values('2','牛奶');
insert into R_TEST2 values('3','薯片');
insert into R_TEST2 values('4','马克杯');
insert into R_TEST2 values('5','鼠标');
insert into R_TEST2 values('6','耳机');
insert into R_TEST2 values('7','键盘');
insert into R_TEST2 values('8','土豆');
insert into R_TEST2 values('9','腊肠');
commit;
CREATE TABLE R_TEST3
(
STDATE VARCHAR2(20),
PRODUCTNAME VARCHAR2(20),
AMOUNT VARCHAR2(20)
);
COMMENT ON TABLE R_TEST3 IS '销售额表';
COMMENT ON COLUMN R_TEST3.STDATE IS '日期';
COMMENT ON COLUMN R_TEST3.PRODUCTNAME IS '产品名称';
COMMENT ON COLUMN R_TEST3.AMOUNT IS '销售额';
2、创建存储过程
create or replace procedure SCOTT.TESTPRODUCT is
begin
--清空表数据
delete from SCOTT.R_TEST3;
--因为此存储过程是触发器中调用的,所以不能增加提交命令
--COMMIT;
--插入数据
insert into SCOTT.R_TEST3(
STDATE,
PRODUCTNAME,
AMOUNT)
WITH A AS(
SELECT T1.STDATE AS 日期,T2.PRODUCTNAME AS 产品名称,T1.SALEAMOUNT AS 销量,T2.PRODUCTSALE AS 单价
FROM SCOTT.R_TEST1 T1 LEFT JOIN SCOTT.R_TEST2 T2 ON T1.PRODUCTID=T2.PRODUCTID
)
SELECT 日期,产品名称,销量*单价 AS 销售额
FROM A;
--因为此存储过程是触发器中调用的,所以不能增加提交命令
--COMMIT;
END;
3、创建触发器
create or replace trigger SCOTT.TESTPRODUCT_TRIGGER
--插入表之前执行
after insert on SCOTT.R_TEST1
--for each row 表示该触发器为行级触发器
begin
--调用存储过程
SCOTT.TESTPRODUCT();
end;
4、插入数据测试
INSERT INTO SCOTT.R_TEST1 VALUES('2021-12-10','2','50');
COMMIT;