Oracle11g增加了表的虚拟列,这个列的数据并没有存储在数据文件中,而是Oracle通过列数据的生成放到了数据字典中。虚拟列的数值是通过真实列中的数据计算而来的。虚拟列的位置可以放在它参考的列的前面,也可以包括多个实际列的值,但是不能引用其他的虚拟列: 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并不禁止虚拟列参考的函数的修改,修改方式见前面。
|