11g_Virtual_Columns

Oracle 11g的虚拟列功能，有点类似于excel的函数列功能--不存实际值，而是根据其他列的值计算得出。[@more@]

==准备环境
SQL> create table trans (
2 trans_id number,
3 cust_name varchar2(20),
4 trans_dt date,
5 trans_amt number(12,2),
6 store_id number(2)
7 )
8 /

Table created.

SQL> create sequence trans_id_seq
2 minvalue 1
3 maxvalue 999999999999
4 start with 1
5 increment by 1
6 cache 20;

Sequence created.

SQL> declare
2 l_stmt varchar2(2000);
3 begin
4 for ctr in 1..5 loop
5 l_stmt := 'insert into trans values ('||
6 trans_id_seq.nextval||','||
7 ''''||dbms_random.string('U',20)||''','||
8 'sysdate - '||
9 round(dbms_random.value(1,365))||','||
10 round(dbms_random.value(1,99999999),2)||','||
11 round(dbms_random.value(1,99))||')';
12 dbms_output.put_line(l_stmt);
13 execute immediate l_stmt;
14 commit;
15 end loop;
16 end;
17 /

PL/SQL procedure successfully completed.

SQL> select * from trans;

TRANS_ID CUST_NAME TRANS_DT TRANS_AMT STORE_ID
---------- -------------------- --------- ---------- ----------
101 NLVULXSEUEGDDYEMJCSQ 06-JAN-08 28429555.7 23
102 WFIZTGOYWVDTQQHHODAL 31-JUL-08 88919063.1 19
103 VSFVUEHCNSTVOSMRAPIA 08-FEB-08 76991822.7 24
104 MANCEYTMBOKDUYORMPOY 11-JUL-08 33868554.9 12
105 ZIEMAFBDRWFIZSXTDBDX 30-MAR-08 52322834.2 27

==增加一列虚拟列odd_even，如果store_id的值是奇数，odd_even列的值就是odd；如果store_id是偶数，odd_even列就存even。
SQL> alter table trans add odd_even varchar(4)
2 generated always as
3 (case
4 when mod(store_id,2)=0 then 'EVEN'
5 when mod(store_id,2)=1 then 'ODD'
6 else 'WHAT'
7 end
8 ) virtual;

Table altered.

SQL> select * from trans;

TRANS_ID CUST_NAME TRANS_DT TRANS_AMT STORE_ID ODD_
---------- -------------------- --------- ---------- ---------- ----
101 NLVULXSEUEGDDYEMJCSQ 06-JAN-08 28429555.7 23 ODD
102 WFIZTGOYWVDTQQHHODAL 31-JUL-08 88919063.1 19 ODD
103 VSFVUEHCNSTVOSMRAPIA 08-FEB-08 76991822.7 24 EVEN
104 MANCEYTMBOKDUYORMPOY 11-JUL-08 33868554.9 12 EVEN
105 ZIEMAFBDRWFIZSXTDBDX 30-MAR-08 52322834.2 27 ODD

插入新纪录的时候，似乎得指定每个插入列，同时虚拟列不能插入值。
SQL> insert into trans(trans_id,cust_name,trans_dt,trans_amt,store_id)
2 values(110,'cust_110',to_date('20081110','yyyymmdd'), 3543.12, 23);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into trans values(110,'cust_110',to_date('20081110','yyyymmdd'), 3543.12, 23);
insert into trans values(110,'cust_110',to_date('20081110','yyyymmdd'), 3543.12, 23)
*
ERROR at line 1:
ORA-00947: not enough values （五个列的值都已经提供，还说not enough values）

SQL> insert into trans values(110,'cust_110',to_date('20081110','yyyymmdd'), 3543.12, 23, 'ODD');
insert into trans values(110,'cust_110',to_date('20081110','yyyymmdd'), 3543.12, 23, 'ODD')
*
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns

虚拟列上还可以创建索引。
SQL> create index trans_idx1 on trans(odd_even);
Index created.

SQL> select index_name,index_type from user_indexes where index_name='TRANS_IDX1';
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
TRANS_IDX1 FUNCTION-BASED NORMAL

SQL> select column_expression from user_ind_expressions
2 where index_name ='TRANS_IDX1';
COLUMN_EXPRESSION
-------------------------------------------------------------------------------
CASE MOD("STORE_ID",2) WHEN 0 THEN 'EVEN' WHEN 1 THEN 'ODD' ELSE 'WHAT' END

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/207/viewspace-1027391/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/207/viewspace-1027391/