如何在oracle中执行触发器和过程

Question

我正在研究我的项目'超市账单管理系统'，因为我是一个初学者，我在制作项目时面临很多问题。这里我已经创建了一个触发器和一个过程，但我不知道如何执行它，我已经为单个产品(即ProdTotal = ProdPrice * ProdQuantity;)的总价格创建了一个触发器。这意味着每当用户在Products表中输入一些数据时，必须执行此触发器，但我不知道如何执行它，类似地，我创建了一个过程来计算单个客户购买的所有产品的总价。就像你去超市或任何商店一样，买完东西后，你会收到账单，这是最终的总金额。我甚至不完全确定我的程序代码是对还是错，虽然它是成功创建的，我不确定它是否会给我我想要的确切输出，所以如果你能帮助我，请让我知道，我从不同的网站研究了很多，也从许多youtube视频，但认真地说，我没有得到如何解决它，所以请帮助我!

代码:

产品表

create table Products
( ProdId number primary key,
ProdNum number not null unique,
ProdName varchar2(15),
ProdPrice int,
ProdQuantity int,
ProdCustId int references Customers,
ProdOrdId int references Orders,
ProdStoreId int references Stores
);

支付表

create table Payments
( PayId int primary key,
PayDate date,
ProdTotal int,
FinalTotal int,
PayOrdId int references orders,
PayProdId int references Products,
PayCustId int references Customers
);

触发代码

create trigger PROD_TOTAL
AFTER INSERT ON Products
BEGIN
UPDATE Payments
SET ProdTotal = (SELECT Products.ProdPrice * Products.ProdQuantity FROM Products);
END;
/

程序代码

create procedure FINAL_TOTAL(C IN NUMBER, T OUT NUMBER)
IS
BEGIN
UPDATE Payments
SET FinalTotal = FinalTotal + ProdTotal
WHERE PayCustId = C;
Commit;
SELECT FinalTotal into T FROM Payments WHERE PayCustId = C;
END;
/

在Product表中插入语句:

insert into Products values(1,1001,'Syrup',30,4,1,1,1);

在Payments表中插入语句:

insert into Payments(PayId, PayDate, PayOrdID, PayProdId, PayCustId)
values(1,date'2020-10-07',1,1,1);

输出:

select * from products;
PRODID    PRODNUM PRODNAME         PRODPRICE PRODQUANTITY PRODCUSTID
---------- ---------- --------------- ---------- ------------ ----------
PRODORDID PRODSTOREID
---------- -----------
1       1001 Syrup                   30            4          1
1           1
select * from Payments;
PAYID PAYDATE    PRODTOTAL FINALTOTAL   PAYORDID  PAYPRODID  PAYCUSTID
---------- --------- ---------- ---------- ---------- ---------- ----------
1 07-OCT-20                                1          1          1

现在，您可以看到PRODTOTAL和FINALTOTAL列是空的，我知道为什么它是空的，因为我没有输入任何值。我没有在这两列中输入任何值的原因是我想，系统应该在触发器和过程的帮助下自动计算这个计算，我甚至不能删除触发器和过程因为在我们的项目中强制使用这两个概念。所以请帮帮我吧!!

Answer 1

如前所述，首先尝试根据您的需求进行正确的设计。只要设计正确的数据库模式，就可以实现许多约束。

尽量远离触发器和PL/SQL。它最终会迫使你更好地设计，并会得到回报。

在为业务逻辑使用触发器之前，请尝试为可选择的事物使用视图。这就是数据库的作用。

当您"完成"时，测试性能，如果它不是最优的，则改进您的模式。如果没有帮助，开始使用触发器来处理业务逻辑。

我已经把我正在谈论的视图的示例放在一起。我希望它能让你开始。

create table Products (
ProdId number generated always as identity primary key
, ProdName varchar2(20) not null
);
create table Stores (
StoreId number generated always as identity primary key
, StoreName varchar2(20) not null
);
create table Customers (
CustomerId number generated always as identity primary key
, CustomerName varchar2(20) not null
);
create table Prices (
PriceId number generated always as identity primary key
, ProdId number not null
, Price number
, ValidFrom date default on null sysdate
, constraint fk_Prices_Product foreign key (ProdId) references Products (ProdId)
);
create unique index uniq_prices_product_price on Prices (ProdId, ValidFrom);
create table Orders (
OrderId number generated always as identity primary key
, CustomerId number not null
, StoreId number not null
, OrderedAt date default on null sysdate
, constraint fk_Orders_Customer foreign key (CustomerId) references Customers (CustomerId)
, constraint fk_Orders_Store foreign key (StoreId) references Stores (StoreId)
);
create table OrderLines (
OrderLineId number generated always as identity primary key
, OrderId number not null
, ProdId number not null
, ProdQuantity number not null
, constraint fk_OrderLines_Order foreign key (OrderId) references Orders (OrderId)
, constraint fk_OrderLines_Prod foreign key (ProdId) references Products (ProdId)
);
create table Payments (
PaymentId number generated always as identity primary key
, OrderId number not null
, PaidAt date default on null sysdate
, PaidAmount number not null
, constraint fk_Payments_Order foreign key (OrderId) references Orders (OrderId)
);
create view Prices_V as
select
p.*
, coalesce(
lead(p.ValidFrom) over (partition by p.ProdId order by p.ValidFrom)
, to_date('9999', 'YYYY')
) ValidTo
from Prices p;
create view Orders_V as
select
o.*
, (
select sum(ol.ProdQuantity * p.Price)
from OrderLines ol
join Prices_V p on (p.ProdId = ol.ProdId and o.OrderedAt between p.ValidFrom and p.ValidTo)
where o.OrderId = ol.OrderId
) Total
, (
select sum(PaidAmount)
from Payments p
where p.OrderId = o.OrderId
) TotalPaid
from Orders o;
insert into Products(ProdName)
select 'Prod A' from dual union all
select 'Prod B' from dual;
insert into Stores(StoreName) values ('Store A');
insert into Customers(CustomerName) 
select 'Customer A' from dual union all
select 'Customer B' from dual;
insert into Prices(ProdId, Price, ValidFrom)
select 1, 10, sysdate - 10 from dual union all
select 1, 12, sysdate - 2 from dual union all
select 1, 14, sysdate + 3 from dual union all
select 2, 100, sysdate - 10 from dual union all
select 2,  90, sysdate - 2 from dual union all
select 2,  null, sysdate + 5 from dual;
insert into Orders(CustomerId, StoreId, OrderedAt)
select 1 cid, 1 stoid, sysdate - 5 from dual union all
select 2, 1, sysdate - 5 from dual union all
select 2, 1, sysdate - 1 from dual;
insert into OrderLines(OrderId, ProdId, ProdQuantity)
select 1 ordid, 1 prodid, 3 prodquant from dual union all
select 1, 2, 2 from dual union all
select 2, 2, 10 from dual union all
select 3, 2, 10 from dual;
insert into Payments(OrderId, PaidAmount) values (2, 500);

select * from Prices_V order by ProdId, ValidFrom;
select * from OrderLines order by OrderId, ProdId;
select * from Orders_v order by OrderId;

里面的一些想法:

1. 价格存储在单独的表中，参考产品并具有有效性，以便产品价格可以随时间变化。价格视图添加了ValidTo列，因此更容易使用
2. 有一个独特的价格指数，因此我们不能在同一时间对同一产品有两个价格
3. 您可以按顺序有许多项，因此Orders和OrderLines表是一对多的关系
4. 在Order_V中显示支付总额(使用Payments的子查询)，并显示总订单值(使用OrderLines和Prices的子查询，使用订单日期从正确的时间段获得价格)

基于模式，您将看到哪些东西可以表示，哪些不能表示。让它符合你的要求是你的工作:)

现在我已经到了你说触发器和程序在你的项目中是强制性的这一点。因此，我有一个建议:

1. 创建允许用户为产品创建新价格的过程。它一定要检查有效性不是从过去开始的。然后实现另一个允许将有效日期更改为日期(也不能在过去结束)。然后，您可以撤销Products表上的任何插入/更新权限，并强制用户使用包含此业务逻辑的过程。
2. 创建表PricesLog并在Prices上触发，将插入PriceId, old。价格,新。在任何插入/更新价格表时，将Price, sysdate和User写入日志。