問(wèn)題描述

我從 Dave Costa 和 Justin Cave這里(再次感謝)獲得了很棒的幫助從另外兩個(gè)屬性 Quantity 和 Price 計(jì)算小計(jì)值(因此 Quantity * Price = Subtotal).在其中一個(gè)答案中指出，從規(guī)范化的角度來(lái)看，這樣做并不好，因?yàn)樾∮?jì)值可以從其他兩個(gè)屬性中得出，我可能應(yīng)該考慮使用視圖.我已經(jīng)閱讀了 Views 并且正在了解這個(gè)概念(無(wú)論如何我都會(huì)在其他地方使用它們)但我仍然不確定如何實(shí)際計(jì)算這兩個(gè)值并在自定義視圖中顯示結(jié)果.如果有人能指出我正確的方向，我將不勝感激.

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.

當(dāng)前觸發(fā)器(歸功于戴夫和賈斯汀):

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;

推薦答案

例如，這樣的事情將通過(guò)將 order_line 和 product 表連接在一起來(lái)計(jì)算小計(jì)正如前一個(gè)觸發(fā)器所做的那樣.據(jù)推測(cè)，您希望包含一些額外的屬性(即訂單號(hào)、訂單行號(hào)等)

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)

這與基于觸發(fā)器的解決方案存在許多相同的數(shù)據(jù)一致性問(wèn)題，因?yàn)楫?dāng)前價(jià)格是從 product 表中引用的，而不是存儲(chǔ)在 表中的當(dāng)前價(jià)格>order_line 表.如果您更改數(shù)據(jù)模型以便 order_line 表存儲(chǔ)數(shù)量和當(dāng)前價(jià)格，則視圖將變得更簡(jiǎn)單，因?yàn)樗辉傩枰B接到 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，您還可以在您的表定義中創(chuàng)建一個(gè)虛擬列，

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

這篇關(guān)于ORACLE - 計(jì)算兩個(gè)值并在視圖中顯示結(jié)果的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！