Oracle 11G虚拟列Virtual Column介绍


Oracle 11G虚拟列Virtual Column介绍
 
[html] 
Oracle 11G 虚拟列 Virtual Column  
  
Oracle 11G 在表中引入了虚拟列，虚拟列是一个表达式，在运行时计算，不存储在数据库中，不能更新虚拟列的值。  
  
定义一个虚拟列的语法：  
  
    column_name [datatype] [GENERATED ALWAYS] AS [expression] [VIRTUAL]  
  
1.虚拟列可以用在select，update，delete语句的where条件中，但是不能用于DML语句  
2.可以基于虚拟列来做分区  
3. 可以在虚拟列上建索引，oracle的函数索引就类似。  
4. 可以在虚拟列上建约束  
  
创建一个带虚拟列的表：  
  
    CREATE TABLE EMPLOYEE  
    (  
        empl_id        NUMBER,  
        empl_nm        VARCHAR2(50),  
        monthly_sal    NUMBER(10,2),  
        bonus          NUMBER(10,2),  
        total_sal      NUMBER(10,2) GENERATED ALWAYS AS (monthly_sal*12 + bonus)  
    );  
      
 total_sal就是一个虚拟列  
   
查看下表定义内容：  
  
    SELECT column_name, data_type, data_length, data_default, virtual_column  
      FROM user_tab_cols  
     WHERE table_name = 'EMPLOYEE';  
       
    COLUMN_NAME | DATA_TYPE | DATA_LENGTH | DATA_DEFAULT             | VIRTUAL_COLUMN  
    EMPL_ID     | NUMBER    | 22          | null                     | NO             
    EMPL_NM     | VARCHAR2  | 50          | null                     | NO             
    MONTHLY_SAL | NUMBER    | 22          | null                     | NO             
    BONUS       | NUMBER    | 22          | null                     | NO             
    TOTAL_SAL   | NUMBER    | 22          | "MONTHLY_SAL"*12+"BONUS" | YES          
      
virtual_column是yes，表示是虚拟列，我们也可以在建表语句加上VIRTUAL，显示声明为虚拟列  
  
    DROP TABLE EMPLOYEE PURGE;  
       
    CREATE OR REPLACE FUNCTION get_empl_total_sal ( p_monthly_sal   NUMBER,  
                                                    p_bonus         NUMBER)  
       RETURN NUMBER  
    DETERMINISTIC  
    IS  
    BEGIN  
       RETURN p_monthly_sal * 12 + p_bonus;  
    END;  
       
    CREATE TABLE EMPLOYEE  
    (empl_id     NUMBER,  
     empl_nm     VARCHAR2(50),  
     monthly_sal NUMBER(10,2),  
     bonus       NUMBER(10,2),  
     total_sal   NUMBER(10,2) AS (get_empl_total_sal(monthly_sal, bonus)) VIRTUAL  
    );  
      
我们可以在虚拟列上创建索引  
  
    CREATE INDEX idx_total_sal ON employee(total_sal);  
       
    SELECT index_name, index_type   
      FROM user_indexes  
     WHERE table_name = 'EMPLOYEE';  
       
    INDEX_NAME     INDEX_TYPE                   
    IDX_TOTAL_SAL  FUNCTION-BASED NORMAL  
      
这个函数也是表定义内容  
  
    DROP FUNCTION get_empl_total_sal;  
       
    SELECT * FROM employee;  
    *  
    Error at line 0  
    ORA-00904: "schema"."GET_EMPL_TOTAL_SAL": invalid identifier  
      
可以添加一个虚拟列  
  
    DROP TABLE EMPLOYEE PURGE;  
       
    CREATE TABLE EMPLOYEE  
    (empl_id     NUMBER,  
     empl_nm     VARCHAR2(50),  
     monthly_sal NUMBER(10,2),  
     bonus       NUMBER(10,2)  
    );  
       
    ALTER TABLE EMPLOYEE ADD (total_sal AS (monthly_sal * 12 + bonus));  
  
这个新列的类型时不确定的，是根据表达式来推断的。  
  
    INSERT INTO employee (empl_id, empl_nm, monthly_sal, bonus)  
       WITH DATA AS  
            (SELECT 100 empl_id, 'AAA' empl_nm, 20000 monthly_sal, 3000 bonus  
               FROM DUAL  
             UNION  
             SELECT 200, 'BBB', 12000, 2000  
               FROM DUAL  
             UNION  
             SELECT 300, 'CCC', 32100, 1000  
               FROM DUAL  
             UNION  
             SELECT 400, 'DDD', 24300, 5000  
               FROM DUAL  
             UNION  
             SELECT 500, 'EEE', 12300, 8000  
               FROM DUAL)  
&n