将ORACLE表字段的默认值设置为公式 [英] Set ORACLE table fields default value to a formular

问题描述

我有一个这样的oracle表：

I have an oracle table like this:

create table tms_transaction_tbl
(
trans_id number primary key,
location_id number,
trans_date date,
resource_id number,
ts_id number,
max_value number,
booked_units number default 0,
remaining number default (select max_value-booked_units),
booked number not null ,
user_id number,
trans_time timestamp
);

如您所见，我尝试将剩余的默认值设置为（max_value-booked_units）

as you can see I tried to set default value of remaining to (max_value-booked_units)

remainging number default (select max_value-booked_units),

但是它给我这个错误这个错误

but it gives me this error this error

ora-22818:subquery expression not allowed here

推荐答案

您不能使用SELECT作为默认值，它必须是一个常量。

You can't use a SELECT as a default value, it must be a constant.

如果你使用Oracle 11g这是什么虚拟列。您不能插入或更新它们，但为您提供数据库中的预先计算的列。

If you're using Oracle 11g this is what virtual columns are for. You can't insert into or update them but the provide a pre-calculated column in the database for you.

create table tms_transaction_tbl
 ( trans_id number primary key,
   location_id number,
   trans_date date,
   resource_id number,
   ts_id number,
   max_value number,
   booked_units number default 0,
   remaining number generated always as ( max_value - booked_units ) virtual,
   booked number not null ,
   user_id number,
   trans_time timestamp
   );

语法在 CREATE TABLE语句。如果您不使用11g，您可以通过桌面上的视图实现相同的逻辑。

The syntax is further described in the documentation for the CREATE TABLE statement. If you're not using 11g you can achieve the same logic with a view on top of your table.

如果您不想使用视图或虚拟列然后我只能推荐您不存储数据。

If you don't want to use a view or a virtual column then I can only recommend that you do not store this data at all.

这篇关于将ORACLE表字段的默认值设置为公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！