Oracle 11g的新特性：虛擬列

今天我將為大家解釋 Oracle 11g 資料庫的新特性 —— 虛擬列

介紹

在老的 Oracle 版本，當我們需要使用運算式或者一些計算公式時，我們會建立資料庫檢視，如果我們需要在這個視圖上使用索引，我們會建立基於函數的索引。

現在 Oracle 11g 允許我們直接在表上使用虛擬列來儲存運算式。

來看一個簡單的例子：

 

  
CREATE TABLE EMP  
  
(  
  
  EMPNO     NUMBER(6),  
  
  SAL       NUMBER(8,2),  
  
  COMM      NUMBER(8,2),  
  
  SAL_PACK  GENERATED ALWAYS AS ( SAL + NVL(COMM,0) ) VIRTUAL  
  
); 
 

上述建的虛擬列 SAL_PACK 是由一個簡單的運算式建立的，使用的關鍵字有 VIRTUAL不過這個關鍵字是可選的），該欄位的值是由 COMM 這個欄位通過運算式計算而來的。

虛擬列的值是不儲存在磁碟的，它們是在查詢時根據定義的運算式臨時計算的。

我們不能往虛擬列中插入資料：

 

  
SQL> INSERT INTO emp VALUES (10, 1500, 500,2000);  
  
 
  
ERROR at line 1:  
  
ORA-54013: INSERT operation disallowed on virtual columns 
 

我們也不能隱式的添加資料到虛擬列：

 

  
SQL> INSERT INTO t VALUES (10, 1500, 500);  
  
            *  
  
ERROR at line 1:  
  
ORA-00947: not enough values  
 

我們只能使用物理列來插入資料。

 

  
SQL> INSERT INTO t (empno, sal,comm) VALUES (10, 1500 , 500);  
  
1 row created. 
 

然後可以查詢虛擬列的值：

 

  
SQL> select * from emp;  
  
EMPNO    SAL        COMM       SAl_PACK  
  
-----   ------      -----      --------  
  
10      1500        500        2000  
  
 
  
1 row selected. 
 

運算式是在查詢的時候即時計算的，然後輸出上述的結果。

虛擬列的索引和約束

索引和約束同樣可以應用在虛擬列上。我們可使用如下SQL語句來建立索引：

 

  
SQL> create index sal_pack_idx on emp(sal_pack);  
  
           Index Created. 
 

我們也可以為虛擬列建立外鍵。

使用 PLSQL 函數來處理虛擬列

虛擬列的定義可使用 PLSQL 函數，但要求該函數必須是確定的：

 

  
CREATE OR REPLACE FUNCTION sum_num (in_num1 NUMBER, in_num2 NUMBER)  
  
   RETURN NUMBER DETERMINISTIC  
  
AS 
  
BEGIN 
  
   RETURN in_num1 + in_num2;  
  
END; 
 

然後可以在虛擬列中使用上述函數：

 

  
SQL>ALTER TABLE emp ADD sal_pack_temp GENERATED ALWAYS AS ( sum_num(SAL,COMM) ):  
  
Table Altered 
 

虛擬列的注釋

為虛擬列建立注釋的方法：

 

  
SQL> COMMENT ON COLUMN emp.sal_pack IS 'Virtual column [sal+ comm]';  
  
Comment created. 
 

上述例子看來虛擬列的功能比視圖本身要簡單很多。

希望這對你也有用。