在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));
oracle 11g中的虚拟列
原创
©著作权归作者所有:来自51CTO博客作者mb5c80f4c73b73a的原创作品,请联系作者获取转载授权,否则将追究法律责任
上一篇:php中常用的技巧及注意的地方1
下一篇:RAID小结
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章