ORACLE - 计算两个值并在视图中显示结果

2021-09-17 00:00:00 oracle view subtotal

我从 Dave Costa 和 Justin Cave这里(再次感谢)获得了很棒的帮助从另外两个属性 Quantity 和 Price 计算小计值(因此 Quantity * Price = Subtotal).在其中一个答案中指出，从规范化的角度来看，这样做并不好，因为小计值可以从其他两个属性中得出，我可能应该考虑使用视图.我已经阅读了 Views 并且正在了解这个概念(无论如何我都会在其他地方使用它们)但我仍然不确定如何实际计算这两个值并在自定义视图中显示结果.如果有人能指出我正确的方向，我将不胜感激.

I received fantastic help from Dave Costa and Justin Cave here (thanks again) in how to calculate a subtotal value from two other attributes, Quantity and Price (so Quantity * Price = Subtotal). In one of the answers it was stated that from a normalisation point of view it's not good to do this as the subtotal value can be derived from the other two attributes and that I should probably look at using a View. I've read up on Views and am getting the concept (I'll be using them elsewhere regardless) but I'm still not sure how to go about actually calculating the two values and show the result in a custom view. If anyone could point me in the right direction I'd appreciate it.

当前触发器(归功于戴夫和贾斯汀):

The current trigger (credit to Dave and Justin):

CREATE VIEW show_subtotal 
AS SELECT price 
FROM products
WHERE product_no =:new.product_no;

:new.subtotal := currentPrice * :new.quantity;

:new.subtotal := currentPrice * :new.quantity;

推荐答案

例如，这样的事情将通过将 order_line 和 product 表连接在一起来计算小计正如前一个触发器所做的那样.据推测，您希望包含一些额外的属性(即订单号、订单行号等)

Something like this, for example, will compute the subtotal by joining the order_line and product tables together just as the previous trigger was doing. Presumably, you'd want to include some additional attributes (i.e. the order number, the order line number, etc.)

CREATE OR REPLACE VIEW order_line_with_subtotal( product_no,
                                                 quantity, 
                                                 price,
                                                 subtotal )
AS
SELECT ol.product_no,
       ol.quantity,
       p.price,
       (ol.quantity * p.price) subtotal
  FROM order_line ol
       JOIN product p ON (ol.product_no = p.product_no)

这与基于触发器的解决方案存在许多相同的数据一致性问题，因为当前价格是从 product 表中引用的，而不是存储在 表中的当前价格>order_line 表.如果您更改数据模型以便 order_line 表存储数量和当前价格，则视图将变得更简单，因为它不再需要连接到 product 表

This has many of the same data consistency issues that the trigger-based solution had since the current price is being referenced from the product table rather than the then-current price being stored in the order_line table. If you changed the data model so that the order_line table stored the quantity and the current price, the view would get simpler because it would no longer need to join to the product table

CREATE OR REPLACE VIEW order_line_with_subtotal( product_no,
                                                 quantity, 
                                                 price,
                                                 subtotal )
AS
SELECT ol.product_no,
       ol.quantity,
       ol.price,
       (ol.quantity * ol.price) subtotal
  FROM order_line ol

如果您使用的是 11g，您还可以在您的表定义中创建一个虚拟列，

If you are on 11g, you can also create a virtual column in your table definition, 

CREATE TABLE order_line (
  order_line_no NUMBER PRIMARY KEY,
  order_no      NUMBER REFERENCES order( order_no ),
  price         NUMBER,
  quantity      NUMBER,
  subtotal      NUMBER GENERATED ALWAYS AS (price*quantity) VIRTUAL 
);

相关文章