如何在 oracle 10g 中计算列值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17946326/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to compute a column value in oracle 10g?
提问by user1334522_Bhavin
create table ord_tbl
(
ord_id number(10) primary key,
ord_name varchar2(20),
quantity number(20),
cost_per_item number(30),
total_cost number(30)--This colm shud be (quantity*cost_per_item),
ord_date date
)
So when I insert rows then the 'total_cost' should automatically get generated and inserted into a table
因此，当我插入行时，'total_cost' 应该自动生成并插入到表中
回答by Gordon Linoff
10g doesn't have this feature. Instead, use a view:
10g没有这个功能。相反，使用视图：
create table ord_tbl
(
ord_id number(10) primary key,
ord_name varchar2(20),
quantity number(20),
cost_per_item number(30),
ord_date date
);
create view vw_ord_tbl as
select ord_id, ord_name, quantity, cost_perId, (quantity*cost_per_item) as total_cost, ord_date
from ord_tbl;
The alternative would be to have the column in the table to maintain the value using a trigger -- for both updates and inserts. I would suggest using the view, because maintaining the triggers adds a lot of maintenance overhead.
另一种方法是让表中的列使用触发器来维护值——用于更新和插入。我建议使用视图，因为维护触发器会增加很多维护开销。
EDIT (by Jason):
编辑（由杰森）：
In 11g you can create a virtual column in the table definition.
在 11g 中，您可以在表定义中创建虚拟列。
create table ord_tbl (
ord_id number(10) primary key,
ord_name varchar2(20),
quantity number(20),
cost_per_item number(30),
total_cost as (quantity*cost_per_item),
ord_date date
)
回答by GolezTrol
Like Gordon Linoff answered, you can create a view. Alternatively you can create a trigger and store the actual value:
就像 Gordon Linoff 回答的那样，您可以创建一个视图。或者，您可以创建触发器并存储实际值：
create trigger tiua_ord_tbl
on ord_tbl after insert or update
for each row
begin
:new.total_cost := :new.quantity * :new.cost_per_item;
end;
The advantage of storing the data, is that you can access it faster and index it if you need. The disadvantage is that you store redundant data (it can be calculated at runtime) and it requires more storage space.
存储数据的优点是您可以更快地访问它并根据需要对其进行索引。缺点是你存储了冗余数据（可以在运行时计算），需要更多的存储空间。
I would advise to use the view at first, and only start storing the value in the table if you need this 'cached' version for performance.
我建议首先使用视图，如果您需要此“缓存”版本以提高性能，则仅开始将值存储在表中。
