oracle 11g中的虚拟列

在oracle 11g中,支持虚拟列,注意虚拟列是可以根据其他列动态计算出来的,
语法:
column_name [datatype] [GENERATED ALWAYS] AS [expression] [VIRTUAL]

例子:
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) );

再看下数据字典:
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 | NOEMPL_NM | VARCHAR2 | 50 | null | NOMONTHLY_SAL | NUMBER | 22 | null | NOBONUS | NUMBER | 22 | null | NOTOTAL_SAL | NUMBER | 22 | "MONTHLY_SAL"*12+"BONUS" | YES


可以对表进行的列进行增加:
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) SELECT * FROM DATA;


--可以为虚拟列建立索引，索引类型为函数索引
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


虚拟列还可以作为分区表的分区键

比如:
create table test_part(i1 int, i2 as (i1+100))
partition by range(i2)
(partition part1 values less than(100),
partition part2 values less than(200),
partition part3 values less than(maxvalue));