虚拟列(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
添加新评论0 条评论