Oracle 11g 虚拟列

最新推荐文章于 2024-04-17 16:43:51 发布

Jane Chiu

最新推荐文章于 2024-04-17 16:43:51 发布

阅读量1.8k

点赞数

分类专栏：数据库文章标签： oracle 虚拟列

本文链接：https://blog.csdn.net/zhao__fang/article/details/53494086

版权

数据库专栏收录该内容

23 篇文章 1 订阅

订阅专栏

创建虚拟列

建立一个简单的测试表：

SQL> CREATE TABLE EMP
  2  (
  3    EMPNO     NUMBER(6),
  4    SAL       NUMBER(8),
  5    COMM      NUMBER(8),
  6    SAL_PACK  NUMBER(8) GENERATED ALWAYS AS ( SAL + NVL(COMM,0) ) VIRTUAL
  7  );
Table created

其中， SAL_PCAK是虚拟列，虚拟列的数值是通过真实列中的数值计算而来的，虚拟列不能引用虚拟列。
虚拟列的值是不存储在磁盘中的，在查询的时候根据定义的表达式临时计算出来。
虚拟列的完整写法如SAL_PACK列，包括列名、数据类型、GENERATED ALWAYS关键字、AS加列表达式和VIRTUAL关键字。其中GENERATED ALWAYS和VIRTUAL为可选关键字，主要用于描述虚拟列的特性，写与不写没有本质区别。而列的数据类型如果忽略，那么Oracle会根据AS后面的表达式最终结果的数据类型来确定虚拟列的数据类型。即可简写为：

SQL> CREATE TABLE EMP
  2  (
  3    EMPNO     NUMBER(6),
  4    SAL       NUMBER(8),
  5    COMM      NUMBER(8),
  6    SAL_PACK  AS ( SAL + NVL(COMM,0) )
  7  );

虚拟列可以使用oracle自带的函数，也可以使用用户自定义的函数，但是函数必须是确定的，也就是带DETERMINISTIC关键字。

SQL> CREATE OR REPLACE FUNCTION f_test(p_empno IN NUMBER) RETURN NUMBER DETERMINISTIC AS
  2    BEGIN
  3      RETURN p_empno* 5;
  4    END;
  5  /
Function created
SQL> alter table emp add sal_num as (f_test(empno));
Table altered

Oracle虽然在创建创建的时候会检查函数的确定性，在表建立之后，却可以将函数替换为非确定性函数。
建立了虚拟列可以有效的减少数据的存储，简化查询语句中对列进行的处理，而且还可以利用虚拟列进行分区。不过虚拟列还会带来其他问题。
首先包含了虚拟列的表在INSERT INTO语句中不能省略COLUMN列表。由于虚拟列的值是由其他列的值计算得出的，且Oracle并不存储虚拟列的值，因此无论是INSERT还是UPDATE都不能对虚拟列进行修改

我们不能往虚拟列中直接插入数据：

SQL> insert into emp values(10,100,100,200);
insert into emp values(10,100,100,200)
ORA-54013: INSERT operation disallowed on virtual columns

也不能隐式的添加数据：

SQL> insert into emp values(10,100,100);
insert into emp values(10,100,100)
ORA-00947: not enough values

必须使用物理列来插入数据，不能省略COLUMN列名：

SQL> insert into emp(empno,sal,comm) values(10,100,100);
1 row inserted

插入成功后可以查询到虚拟列的值：

SQL> select * from emp;
  EMPNO       SAL      COMM   SAL_PACK
------- --------- --------- ----------
     10       100       100        200

更新comm的值为200，再重新查看虚拟列结果：

SQL> update emp set comm = 200 where empno = 10;
1 row updated
SQL> select * from emp;
  EMPNO       SAL      COMM   SAL_PACK
------- --------- --------- ----------
     10       100       200        300

由此可见，虚拟列中的值并不是固定的，而是根据公式在查询的时候即时计算的。

虚拟列的索引和约束

索引和约束同样可以应用在虚拟列中，创建索引如下：

SQL> create index sal_pack_idx on emp(sal_pack);
Index created

查看表中是否存在虚拟列，可以在plslq中，使用view查看emp表结构，如图：
这里写图片描述
如果程序选择一些工具来自动生成表的INSERT、UPDATE语句，那么遇到包含虚拟列的表就会报错。因为虚拟列不可以直接插入或者更新数据。
同样的原因，也无法使用Create table as select创建一个包含虚拟列的表。解决方法是Create table as select结束后通过alter table添加虚拟列。
虚拟列的结果是查询时确定，如果修改了虚拟列的表达式，下次执行查询的时候，虚拟列的值就会发生变化。
但是一旦对虚拟列建立了索引，或者对包含虚拟列的表建立了物化视图，那么虚拟列的值就被实际的存储下来，当虚拟列的表达式发生修改后，会导致索引或物化视图中已有的数据与目前虚拟列结果不一致。解决办法：删除索引并重建，或者将物化视图完全刷新。
如下sal_pack虚拟列已建立索引，修改其表达式，报错。

SQL> alter table emp modify sal_pack as (sal*10);
alter table emp modify sal_pack as (sal*10)
ORA-54022: Virtual column expression cannot be changed because an index is defined on column

一旦建立了索引，Oracle会禁止虚拟列发生更改，但是，Oracle并不禁止虚拟列参考函数的修改。