oracle 虚拟表更新,Oracle虚拟列 - yafeishi的个人页面 - OSCHINA - 中文开源技术交流社区...

创建带虚拟列的表

create table t_vir_col

(

empno number,

name varchar2(100),

birth date,

birthmonth varchar2(3) GENERATED ALWAYS AS (to_char(birth,'mm')) VIRTUAL,

age number GENERATED ALWAYS AS (floor(months_between(sysdate,birth)/12)) VIRTUAL

);

报错,因为使用了sysdate，这个值返回的数据不确定，所以没法使用。

ERROR at line 7:

ORA-54002: only pure functions can be specified in a virtual column expression

改用确定值：

create table t_vir_col_2

(

empno number,

name varchar2(100),

birth date,

birthmonth varchar2(3) GENERATED ALWAYS AS (to_char(birth,'mm')) VIRTUAL,

age number GENERATED ALWAYS AS (floor(months_between(to_date('2018-12-31','yyyy-mm-dd'),birth)/12)) VIRTUAL

);

创建成功。

去掉 age 列：

create table t_vir_col

(

empno number,

name varchar2(100),

birth date,

birthmonth varchar2(3) GENERATED ALWAYS AS (to_char(birth,'mm')) VIRTUAL

);

Table created.

SQL>

插入数据

insert into t_vir_col (empno,name,birth) values(1,'a',to_date('1999-10-10','yyyy-mm-dd'));

查询数据：

SQL> select * from t_vir_col;

EMPNO NAME BIRTH BIRTHMONTH

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

1 a 10-OCT-99 10

SQL>

虚拟列分区

drop table t_vir_col;

create table t_vir_col

(

empno number,

name varchar2(100),

birth date,

birthmonth number GENERATED ALWAYS AS (to_number(to_char(birth,'mm'))) VIRTUAL

)

partition by range (birthmonth)

(

partition month_01 values less than (2),

partition month_02 values less than (3),

partition month_03 values less than (4),

partition month_04 values less than (5),

partition month_05 values less than (6),

partition month_06 values less than (7),

partition month_07 values less than (8),

partition month_08 values less than (9),

partition month_09 values less than (10),

partition month_10 values less than (11),

partition month_11 values less than (12),

partition month_12 values less than (13)

);

创建成功。

分区列不能更新：

SQL> update t_vir_col set birth=to_date('1999-11-10','yyyy-mm-dd') where empno=1;

update t_vir_col set birth=to_date('1999-11-10','yyyy-mm-dd') where empno=1

*

ERROR at line 1:

ORA-14402: updating partition key column would cause a partition change

SQL> update t_vir_col set birth=to_date('1998-10-10','yyyy-mm-dd') where empno=1;

1 row updated.

虚拟列也不能更新：

SQL> update t_vir_col_2 set age=20 where empno=1;

update t_vir_col_2 set age=20 where empno=1

*

ERROR at line 1:

ORA-54017: UPDATE operation disallowed on virtual columns