Oracle 11g新特性之--虚拟列（Virtual Column)

Oracle 11G虚拟列Virtual Column介绍

Oracle 11G 在表中引入了虚拟列，虚拟列是一个表达式，在运行时计算，不存储在数据库中，不能更新虚拟列的值。  

定义一个虚拟列的语法：   

    column_name [datatype] [GENERATED ALWAYS] AS [expression] [VIRTUAL]  

1.虚拟列可以用在select，update，delete语句的where条件中，但是不能用于DML语句  

2.可以基于虚拟列来做分区  

3. 可以在虚拟列上建索引，oracle的函数索引就类似。  

4. 可以在虚拟列上建约束  

案例：

1、创建一个带虚拟列的表：   

14:51:28 SCOTT@ test1 >CREATE TABLE EMP3
14:51:51   2  (
14:51:51   3    EMPNO     NUMBER(6),
14:51:51   4    SAL       NUMBER(8,2),
14:51:51   5    COMM      NUMBER(8,2),
14:51:51   6    SAL_PACK  GENERATED ALWAYS AS ( SAL + NVL(COMM,0) ) VIRTUAL
14:51:51   7  )
Table created.

2、查看虚拟列属性

14:56:10 SCOTT@ test1 >COL TABLE_NAME FOR A10
14:56:19 SCOTT@ test1 >COL COLUMN_NAME FOR A20
14:56:27 SCOTT@ test1 >COL DATA_TYPE FOR A20
14:56:34 SCOTT@ test1 >COL DATA_DEFAULT FOR A20
14:56:48 SCOTT@ test1 >R
  1  select table_name,COLUMN_NAME,data_type,data_default,VIRTUAL_COLUMN from user_tab_cols
  2*  where table_name=‘EMP3‘
TABLE_NAME COLUMN_NAME          DATA_TYPE            DATA_DEFAULT         VIR
---------- -------------------- -------------------- -------------------- ---
EMP3       SAL_PACK             NUMBER               "SAL"+NVL("COMM",0)  YES
EMP3       COMM                 NUMBER                                    NO
EMP3       SAL                  NUMBER                                    NO
EMP3       EMPNO                NUMBER                                    NO

     上述建的虚拟列 SAL_PACK 是由一个简单的表达式创建的，使用的关键字有 VIRTUAL（不过这个关键字是可选的），该字段的值是由 COMM 这个字段通过表达式计算而来的。

在Table上添加虚拟列:

15:44:12 SCOTT@ test1 >alter table emp3 add (sal_total as (sal*12+comm) virtual);
Table altered.

15:49:11 SCOTT@ test1 >desc emp3;
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 EMPNO                                                                      NUMBER(6)
 SAL                                                                        NUMBER(8,2)
 COMM                                                                       NUMBER(8,2)
 SAL_PACK                                                          NOT NULL NUMBER
 SAL_TOTAL                                                                  NUMBER
 
15:49:16 SCOTT@ test1 >select * from emp3;
     EMPNO        SAL       COMM   SAL_PACK  SAL_TOTAL
---------- ---------- ---------- ---------- ----------
        10       1500        500       2000      18500
        20       3000        500       3500      36500
        30       4000        500       4500      48500
        40       6000        500       6500      72500
        
15:51:00 SCOTT@ test1 >select table_name,COLUMN_NAME,data_type,data_default,VIRTUAL_COLUMN from user_tab_cols
15:51:27   2  where table_name=‘EMP3‘;
TABLE_NAME COLUMN_NAME          DATA_TYPE            DATA_DEFAULT         VIR
---------- -------------------- -------------------- -------------------- ---
EMP3       SAL_TOTAL            NUMBER               "SAL"*12+"COMM"      YES
EMP3       SAL_PACK             NUMBER               "SAL"+NVL("COMM",0)  YES
EMP3       COMM                 NUMBER                                    NO
EMP3       SAL                  NUMBER                                    NO
EMP3       EMPNO                NUMBER                                    NO

在虚拟列中使用函数：

15:51:37 SCOTT@ test1 >CREATE OR REPLACE FUNCTION sum_sal (in_num1 NUMBER, in_num2 NUMBER)
15:57:17   2     RETURN NUMBER DETERMINISTIC
15:57:17   3  AS
15:57:17   4  BEGIN
15:57:17   5     RETURN in_num1 + in_num2;
15:57:18   6  END;
15:57:19   7  /
Function created.

15:57:21 SCOTT@ test1 >alter table emp3 add ( sal_comm as (sum_sal(sal,comm)) virtual);
Table altered.

16:00:03 SCOTT@ test1 >desc emp3
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 EMPNO                                                                      NUMBER(6)
 SAL                                                                        NUMBER(8,2)
 COMM                                                                       NUMBER(8,2)
 SAL_PACK                                                          NOT NULL NUMBER
 SAL_TOTAL                                                                  NUMBER
 SAL_COMM                                                                   NUMBER
 
16:00:07 SCOTT@ test1 >select * from emp3;
     EMPNO        SAL       COMM   SAL_PACK  SAL_TOTAL   SAL_COMM
---------- ---------- ---------- ---------- ---------- ----------
        10       1500        500       2000      18500       2000
        20       3000        500       3500      36500       3500
        30       4000        500       4500      48500       4500
        40       6000        500       6500      72500       6500

虚拟列的值是不存储在磁盘的，它们是在查询时根据定义的表达式临时计算的。

3、对虚拟列的操作

Insert 操作：

我们不能往虚拟列中插入数据：
15:01:52 SCOTT@ test1 >insert into emp3 values (10,1500,500,2000);
insert into emp3 values (10,1500,500,2000)
            *
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns

也不能隐式的添加数据到虚拟列：
15:02:16 SCOTT@ test1 >insert into emp3 values (10,1500,500);
insert into emp3 values (10,1500,500)
            *
ERROR at line 1:
ORA-00947: not enough values

虚拟列的数据会自动计算生成
15:07:16 SCOTT@ test1 >insert into emp3(empno,sal,comm) values (10,1500,500);
1 row created.

15:07:29 SCOTT@ test1 >select * from emp3;
     EMPNO        SAL       COMM   SAL_PACK
---------- ---------- ---------- ----------
        10       1500        500       2000

对虚拟列不能做update操作：

15:18:45 SCOTT@ test1 >update emp3 set sal_pack=3000;
update emp3 set sal_pack=3000
       *
ERROR at line 1:
ORA-54017: UPDATE operation disallowed on virtual columns

在虚拟列上创建索引和约束：

15:19:07 SCOTT@ test1 >create index emp3_val_ind on emp3(sal_pack) tablespace indx;
Index created.

15:21:20 SCOTT@ test1 >select table_name,index_name,INDEX_TYPE from user_indexes
15:22:11   2   where table_name=‘EMP3‘;
TABLE_NAME INDEX_NAME                     INDEX_TYPE
---------- ------------------------------ ---------------------------
EMP3       EMP3_VAL_IND                   FUNCTION-BASED NORMAL

15:22:18 SCOTT@ test1 >drop index EMP3_VAL_IND;
Index dropped.

15:24:37 SCOTT@ test1 >alter table emp3 add constraint pk_emp3 primary key (sal_pack);
Table altered.

15:25:22 SCOTT@ test1 >select table_name,index_name,INDEX_TYPE from user_indexes
15:25:34   2   where table_name=‘EMP3‘;
TABLE_NAME INDEX_NAME                     INDEX_TYPE
---------- ------------------------------ ---------------------------
EMP3       PK_EMP3                        FUNCTION-BASED NORMAL

在虚拟列上建立分区表:

15:41:43 SCOTT@ test1 >CREATE TABLE EMP3_part
15:41:46   2   (
15:41:46   3     EMPNO     NUMBER(6),
15:41:46   4     SAL       NUMBER(8,2),
15:41:46   5     COMM      NUMBER(8,2),
15:41:46   6     SAL_PACK  GENERATED ALWAYS AS ( SAL + NVL(COMM,0) ) VIRTUAL
15:41:46   7   )
15:41:46   8  PARTITION BY range (sal_pack)
15:41:46   9          (PARTITION sal_2000 VALUES LESS THAN (2000),
15:41:46  10           PARTITION sal_4000 VALUES LESS THAN (4000),
15:41:46  11           PARTITION sal_6000 VALUES LESS THAN (6000),
15:41:46  12           PARTITION sal_8000 VALUES LESS THAN (8000),
15:41:46  13           PARTITION sal_default VALUES LESS THAN (MAXVALUE));
Table created.

15:42:33 SCOTT@ test1 >insert into emp3_part(empno,sal,comm) select empno,sal,comm from emp3;
4 rows created.

15:43:33 SCOTT@ test1 >commit;
Commit complete.

15:43:36 SCOTT@ test1 >select * from emp3_part;
     EMPNO        SAL       COMM   SAL_PACK
---------- ---------- ---------- ----------
        10       1500        500       2000
        20       3000        500       3500
        30       4000        500       4500
        40       6000        500       6500

15:43:44 SCOTT@ test1 >select * from emp3_part partition(sal_2000);
no rows selected

15:44:01 SCOTT@ test1 >select * from emp3_part partition(sal_4000);
     EMPNO        SAL       COMM   SAL_PACK
---------- ---------- ---------- ----------
        10       1500        500       2000
        20       3000        500       3500

本文出自 “天涯客的blog” 博客，请务必保留此出处http://tiany.blog.51cto.com/513694/1570419

Oracle 11g新特性之--虚拟列（Virtual Column)