當(dāng)前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

oracle+字段+virtual,Oracle 11g新特性之--虚拟列（Virtual Column)

發(fā)布時(shí)間：2025/4/17 编程问答 63 豆豆

生活随笔收集整理的這篇文章主要介紹了 oracle+字段+virtual,Oracle 11g新特性之--虚拟列（Virtual Column) 小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

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

Oracle 11G虛擬列Virtual Column介紹

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

我們從Oracle官方文檔中，找到下面對于虛擬列技術(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ù)庫中，不能更新虛擬列的值。

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

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

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

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

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

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

在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ǔ)在磁盤的，它們是在查詢時(shí)根據(jù)定義的表達(dá)式臨時(shí)計(jì)算的。

3、對虛擬列的操作

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

對虛擬列不能做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

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

總結(jié)

以上是生活随笔為你收集整理的oracle+字段+virtual,Oracle 11g新特性之--虚拟列（Virtual Column)的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

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

上一篇： oracle 迁移用户信息,Oracle
下一篇： ora00600内部错误代码oracle