【TAB】虚拟列 virtual columns

目录

定义

特性

创建带虚拟列的表

创建基于函数的虚拟列

在虚拟列上创建索引

添加虚拟列

基于虚拟列分区

---

定义

使用 virtual_column_definition子句可以定义虚拟列，它不存在磁盘中，相反它是一个函数或是一组表达式，按照需求派生出来的一列值
column_name [datatype] [GENERATED ALWAYS] AS [expression] [VIRTUAL]

特性

虚拟列可以用在select，update，delete语句的where条件中，但是不能用于DML语句
可以基于虚拟列来做分区
可以在虚拟列上建索引，类似于oracle的函数索引。
可以在虚拟列上建约束

创建带虚拟列的表

创建一个带虚拟列的表

SQL> create table tb_vc01 ( empno number(2), ename varchar2(10), monthly_sal number(10,3), bonus number(10,3), yearly_sal number(10,3) generated always as (monthly_sal*12+bonus) );   Table created.

其中yearly_sal为虚拟列

查看tb_vc01列的定义

SQL> select column_name,data_type,data_length,data_default,virtual_column from user_tab_cols where table_name='TB_VC01';   COLUMN_NAME DATA_TYP DATA_LENGTH DATA_DEFAULT             VIR ----------- -------- ----------- ------------------------ --- EMPNO       NUMBER            22                          NO ENAME       VARCHAR2          10                          NO MONTHLY_SAL NUMBER            22                          NO BONUS       NUMBER            22                          NO YEARLY_SAL  NUMBER            22 "MONTHLY_SAL"*12+"BONUS" YES

可以在virtual_column中看出值为YES

创建基于函数的虚拟列

在建表的过程也可以加上virtual显示声明虚拟列，并且在该列引用一个自建的函数

SQL> CREATE OR REPLACE FUNCTION get_emp_yearly_sal (p_monthly_sal NUMBER,p_bonus NUMBER) RETURN NUMBER DETERMINISTIC IS BEGIN RETURN p_monthly_sal * 12 + p_bonus; END; /   Function created.

 

SQL> create table tb_vc02 ( empno number(2), ename varchar2(10), monthly_sal number(10,3), bonus number(10,3), yearly_sal number(10,3) as (get_emp_yearly_sal(monthly_sal,bonus))VIRTUAL );   Table created.

查看tb_vc02上列的定义

SQL> select column_name,data_type,data_length,data_default,virtual_column from user_tab_cols where table_name='TB_VC02';   COLUMN_NAME DATA_TYP DATA_LENGTH DATA_DEFAULT             VIR ----------- -------- ----------- ------------------------ --- EMPNO       NUMBER            22                          NO ENAME       VARCHAR2          10                          NO MONTHLY_SAL NUMBER            22                          NO BONUS       NUMBER            22                          NO YEARLY_SAL  NUMBER            22 "SCOTT"."GET_EMP_YEARLY_ YES                                  SAL"("MONTHLY_SAL","BONU                                  S")

分别向tb_vc01和tb_vc02插入测试数据并查看

SQL> insert into tb_vc01 (empno,ename,monthly_sal,bonus) values(01,'aaa',1000,10);   1 row created.   SQL> insert into tb_vc02 (empno,ename,monthly_sal,bonus) values(01,'aaa',1000,10);   1 row created.   SQL> select * from tb_vc01;        EMPNO ENAME MONTHLY_SAL      BONUS YEARLY_SAL ---------- ----- ----------- ---------- ----------          1 aaa          1000         10      12010   SQL> select * from tb_vc02;        EMPNO ENAME MONTHLY_SAL      BONUS YEARLY_SAL ---------- ----- ----------- ---------- ----------          1 aaa          1000         10      12010

可以看出yearly_sal列中的数值被计算出来

 

insert中不可以向虚拟列添加数据

SQL> insert into tb_vc01 values(02,'bbb',2000,20,9999); insert into tb_vc01 values(02,'bbb',2000,20,9999) * ERROR at line 1: ORA-54013: INSERT operation disallowed on virtual columns

也不可以对其update

SQL> update tb_vc01 set yearly_sal=99999 where empno=10; update tb_vc01 set yearly_sal=99999 where empno=10                    * ERROR at line 1: ORA-54017: UPDATE operation disallowed on virtual columns

 

在虚拟列上创建索引

SQL> create index ind_vc01 on tb_vc01(yearly_sal);   Index created.

索引定义为函数索引

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

由于函数是表定义的一部分，如果删除以后，表也无法查看了

SQL> drop function get_emp_yearly_sal;   Function dropped.   SQL> select * from tb_vc02; select * from tb_vc02 * ERROR at line 1: ORA-00904: "SCOTT"."GET_EMP_YEARLY_SAL": invalid identifier

 

添加虚拟列

虚拟列可以在建表以后添加

创建表TB_VC03

SQL> create table tb_vc03 (empno number(2),ename varchar2(10),monthly_sal number(10,3),bonus number(10,3));   Table created.

像TB_VC03中添加虚拟列，可以不指定数据类型

SQL> alter table tb_vc03 add(yearly_sal as(monthly_sal*12+bonus));   Table altered.

 

可以在有虚拟列的表上收集统计信息

SQL> exec dbms_stats.gather_table_stats('scott','tb_vc03');   PL/SQL procedure successfully completed.

 

基于虚拟列分区

创建分区表tb_vc04

SQL> create table tb_vc04(empno number(2), ename varchar2(10), monthly_sal number(10,3), bonus number(10,3), yearly_sal number(10,3) as(monthly_sal*12+bonus) ) partition by range(yearly_sal)( partition sal_10000 values less than(10000), partition sal_30000 values less than(30000), partition sal_50000 values less than(50000), partition sal_70000 values less than(70000), partition sal_default values less than(maxvalue) );   Table created.

添加一些数据并查看

SQL>  insert into tb_vc04 (empno,ename,monthly_sal,bonus） select 10 empno,'aaa' ename,1000 monthly_sal,100 bonus from dual union select 20,'bbb',2000,200 from dual union select 30,'ccc',3000,300 from dual union select 40,'ddd',4000,400 from dual union select 50,'eee',5000,500 from dual union select 60,'fff',6000,600 from dual ;   6 rows created.   SQL> select * from tb_vc04;        EMPNO ENAME MONTHLY_SAL      BONUS YEARLY_SAL ---------- ----- ----------- ---------- ----------         10 aaa          1000        100      12100         20 bbb          2000        200      24200         30 ccc          3000        300      36300         40 ddd          4000        400      48400         50 eee          5000        500      60500         60 fff          6000        600      72600   6 rows selected.

查看分区情况

SQL> select table_name,partition_name,num_rows from user_tab_partitions where table_name='TB_VC04' order by partition_name;   TABLE_N PARTITION_N   NUM_ROWS ------- ----------- ---------- TB_VC04 SAL_10000 TB_VC04 SAL_30000 TB_VC04 SAL_50000 TB_VC04 SAL_70000 TB_VC04 SAL_DEFAULT

收集一下统计信息

SQL> exec dbms_stats.gather_table_stats('scott','tb_vc04');   PL/SQL procedure successfully completed.   SQL> select table_name,partition_name,num_rows from user_tab_partitions where table_name='TB_VC04' order by partition_name;   TABLE_N PARTITION_N   NUM_ROWS ------- ----------- ---------- TB_VC04 SAL_10000            0 TB_VC04 SAL_30000            2 TB_VC04 SAL_50000            2 TB_VC04 SAL_70000            1 TB_VC04 SAL_DEFAULT          1

 

在分区的情况下，不能对虚拟列的引用列更新

SQL> update tb_vc04 set monthly_sal=8000 where empno=10; update tb_vc04 set monthly_sal=8000 where empno=10        * ERROR at line 1: ORA-14402: updating partition key column would cause a partition change

如果需要更新则要设置enable row movement

SQL> alter table tb_vc04 enable row movement;   Table altered.   SQL> update tb_vc04 set monthly_sal=8000 where empno=10;   1 row updated.

 

不可以创建基于函数的虚拟列的分区

SQL> CREATE OR REPLACE FUNCTION get_emp_yearly_sal (p_monthly_sal NUMBER,p_bonus NUMBER) RETURN NUMBER DETERMINISTIC IS BEGIN RETURN p_monthly_sal * 12 + p_bonus; END; /   Function created.

 

SQL> create table tb_vc05( empno number(2), ename varchar2(10), monthly_sal number(10,3), bonus number(10,3), yearly_sal number(10,3) as(get_emp_yearly_sal(monthly_sal,bonus)) ) partition by range(yearly_sal)( partition sal_10000 values less than(10000), partition sal_30000 values less than(30000), partition sal_50000 values less than(50000), partition sal_70000 values less than(70000), partition sal_default values less than(maxvalue) ); create table tb_vc05(empno number(2),ename varchar2(10),monthly_sal number(10,3),bonus number(10,3),yearly_sal number(10,3) as(get_emp_yearly_sal(monthly_sal,bonus)))partition by range(yearly_sal)(partition sal_10000 values less than(10000),partition sal_30000 values less than(30000),partition sal_50000 values less than(50000),partition sal_70000 values less than(70000),partition sal_default values less than(maxvalue))                                                                                                                                * ERROR at line 1: ORA-54021: Cannot use PL/SQL expressions in partitioning or subpartitioning columns