ORACLE添加基于同一表中其他记录的新列

嗨,所以我有一个称为产品的表,我那里有一些信息,如名称,netto价格,税等。我想添加名为brutto_price的新记录,该记录自动计算为(netto_price + netto_price * tax)或(netto_price * 1.22,如果含税null),不允许将null插入netto_price。 (税项可以为空)。 我正在做某事

alter table products add (brutto_price FLOAT DEFAULT netto_price*1.22 )

    UPDATE products
   SET brutto_price = netto_price + netto_price*tax
   where
   tax is not null;

但是第一个查询不起作用(可能是因为我在此使用记录名称)有什么想法?

评论
笑回首
笑回首

我认为您想要的逻辑是:

alter table products add brutto_price FLOAT DEFAULT netto_price*1.22;

update products
     set brutto_price = netto_price + netto_price * coalesce(tax, 0.22);

However, Oracle can simplify this logic by using virtual columns:

alter table products add brutto_price float as (netto_price + netto_price * coalesce(tax, 0.22));

无需更新。该值始终是最新的。

Note: float is a really bad choice for monetary values. You should be using fixed point representations -- number/numeric/decimal instead.

点赞
评论
求败
求败

You can use CASE for the "if tax is null" bit:

CREATE TABLE products (
  netto_price NUMBER,
  tax         NUMBER
);
INSERT INTO products (netto_price, tax) VALUES (10, 5.2);
INSERT INTO products (netto_price, tax) VALUES (20, NULL);

SELECT netto_price, tax,
       CASE WHEN tax IS NULL THEN netto_price * 1.22
            ELSE netto_price + netto_price * tax
            END as brutto_price
  FROM products;

NETTO_PRICE  TAX   BRUTTO_PRICE
   10        5,2           15,2
   20        NULL          24,4

Alternatively, there is a function called NVL that supplies a default value in case the argument is NULL:

SELECT netto_price, tax,
       netto_price * (1 + NVL(tax, 0.22)) AS brutto_price
  FROM products;

也许您不需要将brutto价格添加为真实列(因为这样一来,您有责任保持brutto价格为最新,以防netto_price或税费发生变化)。请查看“虚拟列”:

ALTER TABLE products ADD (
  brutto_price NUMBER GENERATED ALWAYS AS (
    netto_price * (1 + NVL(tax, 0.22)))
);

现在,您可以从表中进行选择,并始终获得正确的brutto_price计算值:

SELECT * FROM products;

NETTO_PRICE  TAX   BRUTTO_PRICE
   10        5,2           15,2
   20        NULL          24,4

即使您更改税金,brutto_price也会自动更改。

点赞
评论