如何在Oracle中执行触发器和过程 [英] How to execute trigger and procedure in oracle

问题描述

由于我是一名初学者，因此我正在开发超市帐单管理系统"项目，在制作项目时遇到很多问题.在这里，我已经创建了一个触发器和一个过程，但是我不知道如何执行它，我已经为单个产品的总价格创建了一个触发器，即 ProdTotal = ProdPrice * ProdQ​​uantity; .这意味着每当用户在产品"表中输入一些数据时，都必须执行此触发器，但是我不知道如何执行该触发器，类似地，我创建了一个过程来计算由以下用户购买的所有产品的总价一个客户.就像您去超市或任何商店然后在购买商品之后一样，您会收到一张账单，并且有最终的总金额.我什至不完全确定我的过程代码是对还是错，尽管它是成功创建的，我也不知道它是否会提供我想要的确切输出，所以如果您可以帮助我，请告诉我，我已经从不同的网站和许多youtube视频中进行了很多研究，但认真的说我还没有解决方法，所以请帮助我！

I am working on my Project 'Supermarket Billing Management System' since I am a beginner I m facing a lot of issues while making project. Here I've already created a trigger and a procedure but I don't know how I can execute it, I've created a trigger for a total price of a single Product i.e ProdTotal = ProdPrice * ProdQuantity;. That means whenever a user enters some data in the Products table, then this trigger must get executed, but I don't know how to execute it, similarly, I've created a procedure to calculate the total price of all the products purchased by a single customer. Just like when you go to the supermarket or any store then after purchasing the items, you get a bill and there is the final total amount. I m not even exactly sure whether my procedure code is right or wrong, though it was created successfully, I m not sure whether it will give me the exact output which I want, So if you can help me then, please let me know, I've researched a lot from different websites and also from many youtube videos but seriously I am not getting how to solve it, so please help me!

代码:

产品表

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;
    /

在产品"表中插入语句:

Insert statement in Product table:

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

在付款"表中插入对帐单:

Insert statements in Payments table:

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列为空，我知道为什么它为空，因为我没有输入任何值.我之所以没有在这两列中输入任何值，是因为我想要，系统应该在触发器和过程的帮助下自动计算该计算，而且，我什至无法删除触发器和过程，因为它在我们的设计中是强制性的计划同时使用这两个概念.所以请帮助我！

Now here, as you can see PRODTOTAL and FINALTOTAL column is blank, I know why it is blank because I didn't enter any value. And the reason why I didn't enter any value in these two columns is that I want, the system should automatically calculate that calculation with the help of trigger and procedure and, I can't even remove trigger and procedure because it's mandatory in our project to use both of these concepts. So please help me!!!

推荐答案

如已经提出的，首先尝试根据您的要求正确设计.您只需正确设计数据库架构即可实现许多约束.

As already proposed, first try to get the design right with respect to your requirements. You can implement many constraints just by designing correctly your database schema.

尽可能远离触发器和PL/SQL.最终将迫使您进行更好的设计并获得回报.

Stay away from triggers and PL/SQL for as long as possible. It will force you to better design in the end and will pay off.

在将触发器用于业务逻辑之前，请尝试将视图用于可以选择的事物.这就是数据库的用途.

Before using triggers for business logic, try to use views for things that can be selected. That's what the database is for.

当您完成"时，请测试性能，如果性能欠佳，请改进您的架构.如果没有帮助，请开始将触发器用于业务逻辑.

When you are "done", test for the performance and if it's suboptimal, improve your schema. If nothing helps, start using triggers for business logic.

我已将示例与我在说的观点放在一起.我希望它可以帮助您入门.

I've put together a sample with views I am talking about. I hope it can get you started.

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. 价格上有一个唯一的索引，因此我们不能同时为同一产品获得2个价格
3. 您可以有很多订单，这就是为什么存在一对多关系的 Orders 和 OrderLines 表
4. 在 Order_V 中显示已支付的总额(使用 Payments 上的子查询)和显示总订单值(使用在 OrderLines 上的子查询>和 Prices ，订单日期用于获取正确期间内的价格)

1. Prices are stored in separate table, reference the product and have validity so that product price can change over time. Price view have ValidTo column added so it's easier to work with
2. There is a unique index on Prices so that we cannot have 2 prices for the same product at the same time
3. You can have many items in order, so that's why there is Orders and OrderLines tables in 1-to-many relationship
4. In Order_V the total paid is shown (using a subquery on Payments) and the total order values is shown (using a subquery on OrderLines and Prices, date of order is used to get prices form the correct period)

基于模式，您将确定可以代表什么以及不能代表什么.使它符合您的要求是您的工作:)

Based on the schema you will se what things you can represent and which you cannot. It's your job to make it match your requirements :)

现在我要说的是，触发器和过程在您的项目中是必不可少的.因此，我有一个建议:

And now I've come to the point you say triggers and procedures are mandatory in your project. Hence I have a proposal:

1. 创建一个程序，使用户可以为产品创建新价格.它绝对应该检查有效性是否从过去开始.然后实施另一个允许更改有效日期(也不能过去结束)的方法.然后，您可以撤消产品"表上的所有插入/更新特权，并强制用户使用将包含此业务逻辑的过程.
2. 创建一个表 PricesLog 并在 Prices 上触发，这会将PriceId，old.Price，new.Price，sysdate和 User 插入在价格表上进行任何插入/更新的日志.

1. Create a procedure that will allow users to create new price for a product. It should definitely check that the validity does not start in the past. Then implement another one that allows for changing the valid to date (also cannot end in the past). You can than revoke any insert/update privileges on Products table and force users to use your procedures that will contain this business logic.
2. Create a table PricesLog and trigger on Prices that will insert the PriceId, old.Price, new.Price, sysdate and User to the log on any inserts/updates to the prices table.

这篇关于如何在Oracle中执行触发器和过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！