oracle generated always,ORACLE 11g新特性-虚拟列

建表时指定虚拟列，示例代码如下：

SQL> create table dbdream(id number,identifier varchar2(255),

keyword varchar2(20),fond_code varchar2(20) generated always as

(substr(identifier,1,2)));

Table created.

也可以在已有的表中增加虚拟列：

SQL> drop table dbdream purge;

Table dropped.

SQL> create table dbdream(id number,identifier varchar2(255),keyword

varchar2(20));

Table created.

SQL> alter table dbdream add fond_code varchar2(20) generated always as

(substr(identifier,1,2));

Table altered.

如果在已有表中增加虚拟列时，没有指定虚拟列的字段类型，ORACLE会根据generated always as后面的表达式计算的结果自动设置该字段的字段类型，示例代码如下：

SQL> alter table dbdream drop column FOND_CODE;

Table altered.

SQL> alter table dbdream add fond_code generated always as

(substr(identifier,1,2));

Table altered.

SQL> desc dbdream

NameNull? Type

----------------------------------------- -------- ----------------------------

IDNUMBER

IDENTIFIERVARCHAR2(255)

KEYWORDVARCHAR2(20)

FOND_CODEVARCHAR2(8)

虚拟列的值由ORACLE根据表达式自动计算得出，不可以手动去修改和指定虚拟列的值：

SQL> insert into dbdream values(1,'02-01-03-03296-001','奏折','02');

insert into dbdream values(1,'02-01-03-03296-001','奏折','02')

*

ERROR at line 1:

ORA-54013: INSERT operation disallowed on virtual columns

虚拟列的值并不是真实存在的，只有利用到虚拟列，ORACLE才会根据表达式计算出虚拟列的值，磁盘上并不存放虚拟列的值。

SQL> insert into dbdream(id,identifier,keyword) values

(1,'02-01-03-03296-001','奏折');

1 row created.

SQL> insert into dbdream(id,identifier,keyword) values

(2,'03-01-03-0001-001','手谕');

1 row created.

SQL> insert into dbdream(id,identifier,keyword) values

(3,'04-01-02-0075-010','奏折');

1 row created.

SQL> insert into dbdream(id,identifier,keyword) values

(4,'05-01-03-0001-001','遗诏');

1 row created.

SQL> commit;

Commit complete.

以上虽然没有插入虚拟列的值(也插入不了)，但是ORACLE会根据虚拟列的表达式自动计算出虚拟列的值：

SQL> select * from dbdream;

ID IDENTIFIERKEYWORDFOND_COD

-- ----------------- ---------------

102-01-03-0326-001奏折02

2 03-01-03-0001-001手谕03

3 04-01-02-0075-010奏折04

405-01-03-0001-001遗诏05

可以把虚拟列当做分区关键字建立分区表：

SQL> create table stream(ID NUMBER,IDENTIFIER VARCHAR2(255),

2KEYWORD VARCHAR2(20),FOND_CODE VARCHAR2(8)

3generated always as (substr(identifier,1,2)))

4partition by list (FOND_CODE)

5(partition par01 values('02'),

6partition par02 values('03'),

7partition par03 values('04'),

8partition par04 values('05'),

9partition par05 values(default));

Table created.

将dbdream表里的数据插入到stream表：

SQL> insert into stream(id,identifier,keyword) select id,

identifier,keyword from dbdream;

4 rows created.

SQL> commit;

Commit complete.

查询数据验证分区建立是否正确：

SQL> select * from dbdream;

ID IDENTIFIERKEYWORDFOND_COD

-- ----------------- ---------------

102-01-03-0326-001奏折02

2 03-01-03-0001-001手谕03

3 04-01-02-0075-010奏折04

405-01-03-0001-001遗诏05

SQL> select * from stream partition(par01);

ID IDENTIFIERKEYWORDFOND_COD

-- --------------------------------

1 02-01-03-0326-001奏折02

SQL> select * from stream partition(par02);

ID IDENTIFIERKEYWORDFOND_COD

----------------------------------

2 03-01-03-0001-001手谕03

SQL> select * from stream partition(par03);

ID IDENTIFIERKEYWORDFOND_COD

-- --------------------------------

3 04-01-02-0075-010奏折04

SQL> select * from stream partition(par04);

ID IDENTIFIERKEYWORDFOND_COD

----------------------------------

4 05-01-03-0001-001遗诏05

SQL> select * from stream partition(par05);

no rows selected

可见在虚拟列做分区表的分区键是可以的，这也是ORACLE 11g的新特性-虚拟列分区，下面是虚拟列的特点和限制(可能不全)：

1.虚拟列的值由ORACLE通过表达式计算得出，并不存放在表中。

2.不可以对虚拟列做UPDATE和INSERT操作。

3.可以在虚拟列上建立索引，可以建立虚拟列分区表。

SQL> create index ind_fond on dbdream(fond_code);

Index created.

4.只能在堆组织表(普通表)上创建虚拟列，不能在索引组织表、外部表、临时表上创建虚拟列。

5.虚拟列字段不能是LOB或RAW类型。

6.表达式中的所有列必须在同一张表。

7.表达式不能使用其他虚拟列。

有些资料上说不能对虚拟列做DELETE操作，但实验证明是可以的：

SQL> delete from dbdream where FOND_CODE='05';

1 row deleted.

SQL> rollback;

Rollback complete.