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

我会带着你远行 2023-01-18 04:28 392阅读 0赞

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

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

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

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

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并不禁止虚拟列参考的函数的修改，修改方式见前面。