Oracle 11g新特性之--虛擬列(Virtual Column) Oracle 11G虛擬列Virtual Column介紹 在老的 Oracle 版本，當(dāng)我們需要使用表達(dá)式或者一些計(jì)算公式時(shí)，我們會(huì)創(chuàng)建數(shù)據(jù)庫(kù)視圖，如果我們需要在這個(gè)視圖上使用索引，我們會(huì)創(chuàng)建基于函數(shù)的索引。 我們從 Oracle 官

Oracle 11g新特性之--虛擬列(Virtual Column)

Oracle 11G虛擬列Virtual Column介紹

在老的 Oracle 版本，當(dāng)我們需要使用表達(dá)式或者一些計(jì)算公式時(shí)，我們會(huì)創(chuàng)建數(shù)據(jù)庫(kù)視圖，如果我們需要在這個(gè)視圖上使用索引，我們會(huì)創(chuàng)建基于函數(shù)的索引。

我們從Oracle官方文檔中，找到下面對(duì)于虛擬列技術(shù)的描述。

“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 在表中引入了虛擬列，虛擬列是一個(gè)表達(dá)式，在運(yùn)行時(shí)計(jì)算，不存儲(chǔ)在數(shù)據(jù)庫(kù)中，不能更新虛擬列的值。

定義一個(gè)虛擬列的語(yǔ)法：

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

1.虛擬列可以用在select，update，delete語(yǔ)句的where條件中，但是不能用于DML語(yǔ)句

2.可以基于虛擬列來(lái)做分區(qū)

3. 可以在虛擬列上建索引，oracle的函數(shù)索引就類(lèi)似。

4. 可以在虛擬列上建約束

案例：

1、創(chuàng)建一個(gè)帶虛擬列的表：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 是由一個(gè)簡(jiǎn)單的表達(dá)式創(chuàng)建的，使用的關(guān)鍵字有 VIRTUAL(不過(guò)這個(gè)關(guān)鍵字是可選的)，該字段的值是由 COMM 這個(gè)字段通過(guò)表達(dá)式計(jì)算而來(lái)的。

在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

在虛擬列中使用函數(shù)：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

虛擬列的值是不存儲(chǔ)在磁盤(pán)的，它們是在查詢(xún)時(shí)根據(jù)定義的表達(dá)式臨時(shí)計(jì)算的。

3、對(duì)虛擬列的操作

Insert 操作：我們不能往虛擬列中插入數(shù)據(jù)：

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

也不能隱式的添加數(shù)據(jù)到虛擬列：

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

虛擬列的數(shù)據(jù)會(huì)自動(dòng)計(jì)算生成

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

對(duì)虛擬列不能做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

在虛擬列上創(chuàng)建索引和約束：

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

在虛擬列上建立分區(qū)表: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

--通過(guò)以上對(duì)虛擬列的特性可以看出，Oracle采用虛擬列是占用了CPU計(jì)算時(shí)間，而節(jié)約了磁盤(pán)的存儲(chǔ)空間。

本文原創(chuàng)發(fā)布php中文網(wǎng)，轉(zhuǎn)載請(qǐng)注明出處，感謝您的尊重！

總結(jié)

以上是生活随笔為你收集整理的oracle如何查询虚拟列,Oracle11g新特性之--虚拟列（VirtualColumn)的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。