Oracle 11g新特性之--虚拟列（Virtual Column)
admin
2023-05-12 09:22:03
0次
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计算时间，而节约了磁盘的存储空间。
相关内容
热门资讯
以军士兵集体丢掉武器抗命，大喊...
据凤凰卫视报道，以色列国防军一军事基地7月30日发生士兵抗命事件，约120名士兵抗议指挥官做法，将武...
蒋成华任商务部副部长
国务院任免国家工作人员。任命蒋成华为商务部副部长。免去蒋成华的商务部国际贸易谈判副代表职务。
伊朗驻华大使：在军事威胁下，不...
新华社北京7月31日电（记者刁慧琳） 伊朗驻华大使法兹里7月28日表示，伊美回到谈判桌的前提是美国必...
伊朗革命卫队在霍尔木兹海峡击中...
当地时间31日，伊朗伊斯兰革命卫队发布声明称，革命卫队海军当天在霍尔木兹海峡击中并扣留了两艘违反禁令...
美媒：特朗普，遇到了一个更强硬...
据《纽约时报》7月29日报道，就在特朗普总统看似放弃战事升级计划几天后，美国再次与伊朗交火。上周末，...
女子做气管镜时不幸身亡，丈夫称...
7月29日，西安刘先生反映妻子在当地医院做支气管镜检查时死亡，看监控时发现医生疑有违规操作。刘先生表...
全网“帮卖西瓜”，然后呢？
近日，河南部分地区西瓜滞销的消息在网上热度很高。很多地方也伸出援手：有景区收购千斤西瓜、免费赠予游客...
美媒：乌克兰袭击伊朗船只，险引...
据《纽约时报》7月28日报道，据伊朗和西方官员称，伊朗曾考虑攻击乌克兰的一个港口，以报复乌克兰对一艘...
20年里，他只画美女，用东方风...
迈进KIM在上海的工作室，迎面是一整墙的美女们。她们像是刚从一场时髦的沙龙里退场，或倚或立，眉宇间是...
Google在港推出AI代理G...
观点网讯：7月29日，Google在香港推出AI代理Gemini Spark，该代理可全天候在后台运...