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

2025-04-16 技术教程

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:28SCOTT@test1>CREATETABLEEMP314:51:512(14:51:513EMPNONUMBER(6),14:51:514SALNUMBER(8,2),14:51:515COMMNUMBER(8,2),14:51:516SAL_PACKGENERATEDALWAYSAS(SAL+NVL(COMM,0))VIRTUAL14:51:517)Tablecreated.

2、查看虚拟列属性

14:56:10SCOTT@test1>COLTABLE_NAMEFORA1014:56:19SCOTT@test1>COLCOLUMN_NAMEFORA2014:56:27SCOTT@test1>COLDATA_TYPEFORA2014:56:34SCOTT@test1>COLDATA_DEFAULTFORA2014:56:48SCOTT@test1>R1selecttable_name,COLUMN_NAME,data_type,data_default,VIRTUAL_COLUMNfromuser_tab_cols2*wheretable_name='EMP3'TABLE_NAMECOLUMN_NAMEDATA_TYPEDATA_DEFAULTVIR-------------------------------------------------------------------------EMP3SAL_PACKNUMBER"SAL"+NVL("COMM",0)YESEMP3COMMNUMBERNOEMP3SALNUMBERNOEMP3EMPNONUMBERNO

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

在Table上添加虚拟列:

15:44:12SCOTT@test1>altertableemp3add(sal_totalas(sal*12+comm)virtual);Tablealtered.15:49:11SCOTT@test1>descemp3;NameNull?Type---------------------------------------------------------------------------------------------------------------------EMPNONUMBER(6)SALNUMBER(8,2)COMMNUMBER(8,2)SAL_PACKNOTNULLNUMBERSAL_TOTALNUMBER15:49:16SCOTT@test1>select*fromemp3;EMPNOSALCOMMSAL_PACKSAL_TOTAL--------------------------------------------------10150050020001850020300050035003650030400050045004850040600050065007250015:51:00SCOTT@test1>selecttable_name,COLUMN_NAME,data_type,data_default,VIRTUAL_COLUMNfromuser_tab_cols15:51:272wheretable_name='EMP3';TABLE_NAMECOLUMN_NAMEDATA_TYPEDATA_DEFAULTVIR-------------------------------------------------------------------------EMP3SAL_TOTALNUMBER"SAL"*12+"COMM"YESEMP3SAL_PACKNUMBER"SAL"+NVL("COMM",0)YESEMP3COMMNUMBERNOEMP3SALNUMBERNOEMP3EMPNONUMBERNO

在虚拟列中使用函数：

15:51:37SCOTT@test1>CREATEORREPLACEFUNCTIONsum_sal(in_num1NUMBER,in_num2NUMBER)15:57:172RETURNNUMBERDETERMINISTIC15:57:173AS15:57:174BEGIN15:57:175RETURNin_num1+in_num2;15:57:186END;15:57:197/Functioncreated.15:57:21SCOTT@test1>altertableemp3add(sal_commas(sum_sal(sal,comm))virtual);Tablealtered.16:00:03SCOTT@test1>descemp3NameNull?Type---------------------------------------------------------------------------------------------------------------------EMPNONUMBER(6)SALNUMBER(8,2)COMMNUMBER(8,2)SAL_PACKNOTNULLNUMBERSAL_TOTALNUMBERSAL_COMMNUMBER16:00:07SCOTT@test1>select*fromemp3;EMPNOSALCOMMSAL_PACKSAL_TOTALSAL_COMM------------------------------------------------------------1015005002000185002000203000500350036500350030400050045004850045004060005006500725006500

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

3、对虚拟列的操作

Insert 操作：

我们不能往虚拟列中插入数据：15:01:52SCOTT@test1>insertintoemp3values(10,1500,500,2000);insertintoemp3values(10,1500,500,2000)*ERRORatline1:ORA-54013:INSERToperationdisallowedonvirtualcolumns也不能隐式的添加数据到虚拟列：15:02:16SCOTT@test1>insertintoemp3values(10,1500,500);insertintoemp3values(10,1500,500)*ERRORatline1:ORA-00947:notenoughvalues虚拟列的数据会自动计算生成15:07:16SCOTT@test1>insertintoemp3(empno,sal,comm)values(10,1500,500);1rowcreated.15:07:29SCOTT@test1>select*fromemp3;EMPNOSALCOMMSAL_PACK----------------------------------------1015005002000

对虚拟列不能做update操作：

15:18:45SCOTT@test1>updateemp3setsal_pack=3000;updateemp3setsal_pack=3000*ERRORatline1:ORA-54017:UPDATEoperationdisallowedonvirtualcolumns

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

15:19:07SCOTT@test1>createindexemp3_val_indonemp3(sal_pack)tablespaceindx;Indexcreated.15:21:20SCOTT@test1>selecttable_name,index_name,INDEX_TYPEfromuser_indexes15:22:112wheretable_name='EMP3';TABLE_NAMEINDEX_NAMEINDEX_TYPE-------------------------------------------------------------------EMP3EMP3_VAL_INDFUNCTION-BASEDNORMAL15:22:18SCOTT@test1>dropindexEMP3_VAL_IND;Indexdropped.15:24:37SCOTT@test1>altertableemp3addconstraintpk_emp3primarykey(sal_pack);Tablealtered.15:25:22SCOTT@test1>selecttable_name,index_name,INDEX_TYPEfromuser_indexes15:25:342wheretable_name='EMP3';TABLE_NAMEINDEX_NAMEINDEX_TYPE-------------------------------------------------------------------EMP3PK_EMP3FUNCTION-BASEDNORMAL

在虚拟列上建立分区表:

15:41:43SCOTT@test1>CREATETABLEEMP3_part15:41:462(15:41:463EMPNONUMBER(6),15:41:464SALNUMBER(8,2),15:41:465COMMNUMBER(8,2),15:41:466SAL_PACKGENERATEDALWAYSAS(SAL+NVL(COMM,0))VIRTUAL15:41:467)15:41:468PARTITIONBYrange(sal_pack)15:41:469(PARTITIONsal_2000VALUESLESSTHAN(2000),15:41:4610PARTITIONsal_4000VALUESLESSTHAN(4000),15:41:4611PARTITIONsal_6000VALUESLESSTHAN(6000),15:41:4612PARTITIONsal_8000VALUESLESSTHAN(8000),15:41:4613PARTITIONsal_defaultVALUESLESSTHAN(MAXVALUE));Tablecreated.15:42:33SCOTT@test1>insertintoemp3_part(empno,sal,comm)selectempno,sal,commfromemp3;4rowscreated.15:43:33SCOTT@test1>commit;Commitcomplete.15:43:36SCOTT@test1>select*fromemp3_part;EMPNOSALCOMMSAL_PACK----------------------------------------101500500200020300050035003040005004500406000500650015:43:44SCOTT@test1>select*fromemp3_partpartition(sal_2000);norowsselected15:44:01SCOTT@test1>select*fromemp3_partpartition(sal_4000);EMPNOSALCOMMSAL_PACK----------------------------------------10150050020002030005003500--通过以上对虚拟列的特性可以看出，Oracle采用虚拟列是占用了CPU计算时间，而节约了磁盘的存储空间。