oracle如何查询虚拟列,Oracle 11G 表的虚拟列

SQL> CREATE TABLE

yu_test2 (c_vl_1

NUMBER, c_vl_2 AS

(c_vl_1+1) );

Table created

SQL> CREATE TABLE

yu_test2 (c_vl_1

NUMBER, c_vl_2 AS

(c_vl_1+1), c_vl_3 AS

(c_vl_2+1) );

CREATE TABLE yu_test2 (c_vl_1

NUMBER, c_vl_2 AS

(c_vl_1+1), c_vl_3 AS

(c_vl_2+1) )

ORA-54012: 在列表达式中引用了虚拟列

以上只是一个简单的虚拟列的例子，实际上虚拟列的完整写法应该包括列名、数据类型、GENERATED

ALWAYS关键字、AS加列表达式和VIRTUAL关键字。其中GENERATED

ALWAYS和VIRTUAL为可选关键字，主要用于描述虚拟列的特性，写与不写没有本质区别。而列的数据类型如果忽略，那么Oracle会根据AS后面的表达式最终结果的数据类型来确定虚拟列的数据类型：

SQL> CREATE TABLE yu_test1

( v_cl_1 VARCHAR2(30), v_cl_2 CHAR(50) GENERATED

ALWAYS AS (LOWER(v_cl_1)) VIRTUAL );

Table created

SQL> desc yu_test1;

Name Type Nullable

Default Comments ------ ------------ -------- ---------------

-------- V_CL_1 VARCHAR2(30)

Y V_CL_2

CHAR(50) Y LOWER("V_CL_1")

虚拟列可以使用Oracle自带的函数或用户定义的函数，不过对于用户定义的函数要求必须声明函数的确定性(DETERMINISTIC)，虚拟列必须是对实际列进行操作后的结果，不能使用没有实际列当做入参的函数，也就是说，虚拟列必须和表字段有关联：

SQL> CREATE OR REPLACE

FUNCTION FUN_TEST1 RETURN NUMBER AS

2 BEGIN

3 RETURN

1;

4 END;

5 /

Function created

SQL> drop table yu_test1

purge;

Table dropped

SQL> CREATE TABLE yu_test2 (c_vl_1 NUMBER, c_vl_2 AS

(FUN_TEST1) );

CREATE TABLE yu_test2 (c_vl_1

NUMBER, c_vl_2 AS (FUN_TEST1)

)

ORA-54016: 指定了无效的列表达式

SQL> CREATE OR REPLACE

FUNCTION FUN_TEST1 (c_in number) RETURN NUMBER AS

2 BEGIN

3 RETURN

1;

4 END;

5 /

Function created

SQL> CREATE TABLE

yu_test2 (c_vl_1

NUMBER, c_vl_2 AS

(FUN_TEST1(c_vl_1)) );

CREATE TABLE yu_test2 (c_vl_1

NUMBER, c_vl_2 AS

(FUN_TEST1(c_vl_1)) )

ORA-30553: 函数不能确定

SQL> SQL> CREATE OR REPLACE FUNCTION FUN_TEST1 (c_in number) RETURN

NUMBER DETERMINISTIC AS

2 BEGIN

3 RETURN

1;

4 END;

5 /

Function created

SQL> CREATE TABLE yu_test2 (c_vl_1 NUMBER, c_vl_2 AS

(FUN_TEST1(c_vl_1)) );

Table created

SQL>

DETERMINISTIC是必须的。

不过Oracle虽然在创建创建的时候会检查函数的确定性，在表建立之后，却可以将函数替换为非确定性函数：

SQL> insert into yu_test2

(c_vl_1) values(1);

1 row inserted

SQL> commit;

Commit complete

SQL> DROP FUNCTION

FUN_TEST1;

Function dropped

SQL> select * from yu_test2;

select * from yu_test2

ORA-00904: "YUZH"."FUN_TEST1": 标识符无效

SQL> CREATE OR REPLACE

FUNCTION FUN_TEST1 (c_in number) RETURN NUMBER AS

2 BEGIN

3 RETURN

2;

4 END;

5 /

Function created

SQL> select * from

yu_test2;

C_VL_1 C_VL_2

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

1 2 建立了虚拟列可以有效的减少数据的存储，简化查询语句中对列进行的处理，而且还可以利用虚拟列进行分区。不过虚拟列还会带来其他问题。首先包含了虚拟列的表在INSERT

INTO语句中不能省略COLUMN列表。由于虚拟列的值是由其他列的值计算得出的，且Oracle并不存储虚拟列的值，因此无论是INSERT还是UPDATE都不能对虚拟列进行修改：

SQL> insert into yu_test2

values(1);

insert into yu_test2 values(1)

ORA-00947: 没有足够的值

SQL> insert into yu_test2

values(1,1);

insert into yu_test2 values(1,1)

ORA-54013: 不允许对虚拟列执行 INSERT 操作

SQL> insert into yu_test2

(c_vl_1,c_vl_2) values(1,1);

insert into yu_test2 (c_vl_1,c_vl_2) values(1,1)

ORA-54013: 不允许对虚拟列执行 INSERT 操作

SQL> insert into yu_test2 (c_vl_1) values(1);

1 row inserted

SQL> commit;

Commit complete

SQL> update yu_test2 set

c_vl_1=2;

2 rows updated

SQL> update yu_test2 set c_vl_2=2;

update yu_test2 set c_vl_2=2

ORA-54017: 不允许对虚拟列执行 UPDATE 操作

SQL> commit

2 ;

Commit complete

如果程序选择使用了一些工具来自动生成表的INSERT、UPDATE语句，那么遇到包含虚拟列的表就会报错。出于同样的原因，无法使用CREATE

TABLE AS SELECT创建一个包含虚拟列的表。解决方法是CREATE TABLE AS SELECT结束后通过ALTER

TABLE添加虚拟列。虚拟列还存在一个问题，当虚拟列的值一旦被实体化，那么虚拟列表达式发生变化会造成实体化结果与虚拟列不一致。简单的说就是虚拟列的结果是在查询的时候确定的，如果修改了虚拟列的表达式，下次执行查询时，虚拟列的值就会发生变化。但是一旦对虚拟列建立了索引，或者对包含虚拟列的表建立了物化视图，那么虚拟列的数值就被实际的存储下来，当虚拟列的表达式发生修改后，会导致索引或物化视图中已有的数据与目前虚拟列结果不一致。这个问题的解决方法只有删除索引并重建，或者将物化视图完全刷新。

SQL> create index

YU_TEST2_IDX on YU_TEST2 (c_vl_2);

Index created

SQL> ALTER TABLE YU_TEST2

MODIFY c_vl_2 AS (UPPER(c_vl_1));

ALTER TABLE YU_TEST2 MODIFY c_vl_2 AS (UPPER(c_vl_1))

ORA-54022: 无法更改虚拟列表达式, 因为在列上定义了索引

虽然建立了索引后Oracle会禁止虚拟列发生修改，但是Oracle并不禁止虚拟列参考的函数的修改，修改方式见前面。