虚拟列(virtual column)

虚拟列是oracle11g的新特性，列中的值并不存储在磁盘中，而是根据需要由定义的表带式或者

函数自动生成列值。

SQL> desc books

Name     Type         Nullable Default Comments

-------- ------------ -------- ------- --------

ISBN     VARCHAR2(10)

TITLE    VARCHAR2(30) Y

PUBDATE  DATE         Y

PUBID    NUMBER(2)    Y

COST     NUMBER(5,2)  Y

RETAIL   NUMBER(5,2)  Y

DISCOUNT NUMBER(4,2)  Y

CATEGORY VARCHAR2(12) Y

SQL> alter table books add(profit as (retail-cost));

Table altered //增加一个虚拟列profit

SQL> desc books

Name     Type         Nullable Default         Comments

-------- ------------ -------- --------------- --------

ISBN     VARCHAR2(10)

TITLE    VARCHAR2(30) Y

PUBDATE  DATE         Y

PUBID    NUMBER(2)    Y

COST     NUMBER(5,2)  Y

RETAIL   NUMBER(5,2)  Y

DISCOUNT NUMBER(4,2)  Y

CATEGORY VARCHAR2(12) Y

PROFIT   NUMBER       Y        "RETAIL"-"COST"

SQL> select title,cost,retail,profit

2  from books; //虚拟列的数据会在使用的时候自动生成。

TITLE                             COST  RETAIL     PROFIT

------------------------------ ------- ------- ----------

BODYBUILD IN 10 MINUTES A DAY    18.75   30.95       12.2

REVENGE OF MICKEY                14.20   22.00        7.8

BUILDING A CAR WITH TOOTHPICKS   37.80   59.95      22.15

DATABASE IMPLEMENTATION          31.40   55.95      24.55

COOKING WITH MUSHROOMS           12.50   19.95       7.45

HOLY GRAIL OF ORACLE             47.25   75.95       28.7

HANDCRANKED COMPUTERS            21.80   25.00        3.2

E-BUSINESS THE EASY WAY          37.90   54.50       16.6

PAINLESS CHILD-REARING           48.00   89.95      41.95

THE WOK WAY TO COOK              19.00   28.75       9.75

BIG BEAR AND LITTLE DOVE          5.32    8.95       3.63

HOW TO GET FASTER PIZZA          17.85   29.95       12.1

HOW TO MANAGE THE MANAGER        15.40   31.95      16.55

SHORTEST POEMS                   21.85   39.95       18.1

下面使用表结构简单一点的表来做虚拟列插入的演示

CREATE TABLE acctmanager

(amid CHAR(4),

amfirst VARCHAR2(12)  NOT NULL,

amlast VARCHAR2(12)  NOT NULL,

amedate DATE DEFAULT SYSDATE,

amsal NUMBER(8,2),

amcomm NUMBER(7,2) DEFAULT 0,

region CHAR(2),

CONSTRAINT acctmanager_amid_pk PRIMARY KEY (amid),

CONSTRAINT acctmanager_region_ck

CHECK (region IN ('N', 'NW', 'NE', 'S', 'SE', 'SW', 'W', 'E')));

SQL> desc acctmanager

Name    Type         Nullable Default Comments

------- ------------ -------- ------- --------

AMID    CHAR(4)

AMFIRST VARCHAR2(12)

AMLAST  VARCHAR2(12)

AMEDATE DATE         Y        SYSDATE

AMSAL   NUMBER(8,2)  Y

AMCOMM  NUMBER(7,2)  Y        0

REGION  CHAR(2)      Y

SQL> alter table acctmanager add(amearn as(amsal+amcomm));

Table altered  //一个客户经理的总收入等于固定工资+提出

增加一个虚拟列amearn

SQL> insert into acctmanager(amid,amfirst,amlast,amsal,amcomm,amearn)

2  values ('0001','sam','smith',5000,3000,8000);

insert into acctmanager(amid,amfirst,amlast,amsal,amcomm,amearn)

values ('0001','sam','smith',5000,3000,8000)

ORA-54013: 不允许对虚拟列执行 INSERT 操作

虚拟列不能出现在插入列列表中。

SQL> insert into acctmanager(amid,amfirst,amlast,amsal,amcomm)

2  values ('0001','sam','smith',5000,3000);

1 row inserted

SQL> select amid,amfirst,amlast,amsal,amcomm,amearn

2  from acctmanager;  //虚拟列的数据会在使用的时候自动生成。

AMID AMFIRST      AMLAST            AMSAL    AMCOMM     AMEARN

---- ------------ ------------ ---------- --------- ----------

0001 sam          smith           5000.00   3000.00       8000

SQL> update acctmanager set amearn=9000

2  where amid='0001';

update acctmanager set amearn=9000

where amid='0001'

ORA-54017: 不允许对虚拟列执行 UPDATE 操作

SQL> update acctmanager set amearn=8000

2  where amid='0001';

update acctmanager set amearn=8000

where amid='0001'

ORA-54017: 不允许对虚拟列执行 UPDATE 操作

不能对虚拟列进行更新，不管是否违反了虚拟列产生的定义。

SQL> alter table acctmanager drop column amearn;

Table altered  //删除虚拟列的方法与普通列相关

因为虚拟列不存储数据，所以即使在系统繁忙时，也不需要使用

alter table table_name set unused column_name;

来unused一个列，然后在删除，可以直接删除虚列，只是删除

虚拟列的定义。

SQL> desc acctmanager

Name    Type         Nullable Default Comments

------- ------------ -------- ------- --------

AMID    CHAR(4)

AMFIRST VARCHAR2(12)

AMLAST  VARCHAR2(12)

AMEDATE DATE         Y        SYSDATE

AMSAL   NUMBER(8,2)  Y

AMCOMM  NUMBER(7,2)  Y        0

REGION  CHAR(2)      Y