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

创新互联坚持“要么做到，要么别承诺”的工作理念，服务领域包括：成都网站制作、网站设计、企业官网、英文网站、手机端网站、网站推广等服务，满足客户于互联网时代的剑阁网站设计、移动媒体设计的需求，帮助企业找到有效的互联网解决方案。努力成为您成熟可靠的网络建设合作伙伴！

Oracle 11G虚拟列Virtual Column介绍

   在老的 Oracle 版本，当我们需要使用表达式或者一些计算公式时，我们会创建数据库视图，如果我们需要在这个视图上使用索引，我们会创建基于函数的索引。

我们从Oracle官方文档中，找到下面对于虚拟列技术的描述。

    “Tables can also include virtual columns. A virtual column is like any other table column, except that its value is derived by evaluating an expression. The expression can include columns from the same table, constants, SQL functions, and user-defined PL/SQL functions. You cannot explicitly write to a 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          --通过以上对虚拟列的特性可以看出，Oracle采用虚拟列是占用了CPU计算时间，而节约了磁盘的存储空间。

另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。

网页标题：Oracle11g新特性之--虚拟列（VirtualColumn)-创新互联
网页地址：http://scyanting.com/article/ddjepe.html