Oracle11g新特性之--虛擬列（VirtualColumn)

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

創新互聯建站致力于互聯網品牌建設與網絡營銷，包括成都做網站、網站設計、SEO優化、網絡推廣、整站優化營銷策劃推廣、電子商務、移動互聯網營銷等。創新互聯建站為不同類型的客戶提供良好的互聯網應用定制及解決方案，創新互聯建站核心團隊10余年專注互聯網開發，積累了豐富的網站經驗，為廣大企業客戶提供一站式企業網站建設服務，在網站建設行業內樹立了良好口碑。

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 在表中引入了虛擬列，虛擬列是一個表達式，在運行時計算，不存儲在數據庫中，不能更新虛擬列的值。

定義一個虛擬列的語法：

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

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計算時間，而節約了磁盤的存儲空間。

本文標題：Oracle11g新特性之--虛擬列（VirtualColumn)
本文網址：http://m.kartarina.com/article38/jeccsp.html

成都網站建設公司_創新互聯，為您提供電子商務、企業網站制作、、商城網站、網站排名、網站策劃

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源：創新互聯

猜你還喜歡下面的內容