sql - 向现有表添加一列，其值依赖于其他列

Question

我想在现有表中添加一列，其值依赖于另一列 STORE。 STORE 有两个值，T 或 D。如果存储值为 T，则我希望添加列中的值为 1，否则为零。有什么办法吗？

Answer 1

虚拟列可以在 11g 中完成:

SQL> create table yourtab (store varchar2(1));

Table created.

SQL> alter table yourtab add col as (case store when 'T' then 1 else 0 end);

Table altered.

SQL> insert into yourtab (store) values ('T');

1 row created.

SQL> insert into yourtab (store) values ('D');

1 row created.

SQL> select * from yourtab;

S        COL
- ----------
T          1
D          0

如果您使用的是 10g 或更早版本，则使用触发方法:

SQL> create table yourtab (store varchar2(1), col number);

Table created.

SQL> create trigger yourtab_biu
  2  before insert or update on yourtab
  3  for each row
  4  declare
  5  begin
  6    :new.col := case :new.store when 'T' then 1 else 0 end;
  7  end;
  8  /

Trigger created.

SQL> insert into yourtab (store) values ('T');

1 row created.

SQL> insert into yourtab (store) values ('D');

1 row created.

SQL> select * from yourtab;

S        COL
- ----------
T          1
D          0