度量快速开发平台-专业、快速的软件定制快开平台

标题: Oracle11新特性——虚拟列 [打印本页]

作者: 陈晓龙    时间: 2020-3-15 21:11
标题: Oracle11新特性——虚拟列
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并不禁止虚拟列参考的函数的修改,修改方式见前面。




作者: 张兴康    时间: 2020-3-18 16:14
不错,好东西,值得收藏
作者: 陈晓龙    时间: 2020-3-18 16:18
张兴康 发表于 2016-4-18 16:14
不错,好东西,值得收藏

嗯,多看看!
作者: 万望    时间: 2020-3-18 17:43

作者: 张兴康    时间: 2020-3-19 14:04
陈晓龙 发表于 2016-4-18 16:18
嗯,多看看!






欢迎光临 度量快速开发平台-专业、快速的软件定制快开平台 (http://bbs.delit.cn/) Powered by Discuz! X3.2