南大通用GBase8s虚拟列：提升数据操作灵活性的新利器

原文链接：

https://www.gbase.cn/community/post/3980

更多精彩内容尽在南大通用GBase技术社区，南大通用致力于成为用户最信赖的数据库产品供应商。

在数据库管理中，我们经常需要根据已有数据动态生成新的数据列。GBase8s数据库的虚拟列功能提供了一种高效的方式来实现这一需求。本文将详细介绍GBase8s虚拟列的概念、定义方式、使用场景以及相关的限制。

一、虚拟列定义

虚拟列是指使用表达式或函数进行定义的数据列。逻辑上，表的虚拟列与普通列具有相同的语法含义，但虚拟列的值并不保存在任何物理存储介质上，而是在SQL的执行过程中，根据定义虚拟列的表达式或函数进行计算而获得。

要点：

1. 与普通列类似，一般在使用上没有过多区别，只是通过表达式计算；

2. 在虚拟列的表达式中，可以包括同表的其他列、常量、SQL函数，甚至可以包括一些用户自定义的函数；

3. 只有当查询虚拟列时才能看到该列的值,但是,这个值不像普通列的值那样永久存储在磁盘上, 只有当通过动态地一个或一个列表达式被查询时,虚拟列的值才被计算。

例如：

create table t1 (id int, month_sal decimal(10,2,total_sal as(month_sal*12));

total_sal 就是一个虚拟列。它返回 month_sal*12 的值。

二、语法说明

       

• column：虚拟列的列名，命名规则和相关约束与GBase 8s当前版本的普通列保持一致，不可与该表其它列重名，不可省略；
• datatype：虚拟列数据类型，支持当前8s版本的内置数据类型，可省略；如省略，虚拟列的数据类型，与定义该虚拟列的表达式或函数的返回值数据类型保持一致。（不支持大对象、ROW、集合、SERIAL自增类型）；
• GENERTATED ALWAYS：显式声明虚拟列关键字，可省略；
• AS：显式声明虚拟列关键字，不可省略；
• column_expression：用于定义虚拟列的列表达式或常量表达式，表达式只能引用当前表中的列，且该表达式具有唯一的返回值；不可省略；虚拟列表达式中不可引用其它虚拟列；
• VIRTUAL：显式声明虚拟列关键字，可省略；

例如：

--创建 sc 表， v_source 为虚拟列。
>  CREATE TABLE sc (stu_id INT primary key,
stu_nm VARCHAR2(50),
course_id INT,
source decimal(10,2),
v_source varchar(30)
AS (case when source < 60 then '不合格'
         when source >60  then '合格' end ) VIRTUAL )；
--插入数据，然后再查询，可以看到虚拟列的值会根据定义动态生成。
> insert into sc(stu_id,stu_nm,course_id,source)values(1,'张三',9001,56);
1 row(s) inserted.
>insert into sc(stu_id,stu_nm,course_id,source)values(2,'李思思',9001,80);
1 row(s) inserted.

> select * from sc;
stu_id     1
stu_nm     张三
course_id  9001
source     56.00
v_source   不合格

stu_id     2
stu_nm     李思思
course_id  9001
source     80.00
v_source   合格

2 row(s) retrieved.

 数据类型使用限制

1. 不支持大对象、ROW、自定义类型、集合类型。
2. 不支持 SERIAL、SERIAL8 、BIGSERIAL。

列表达式使用范围

1.  只能引用当前表的列，且表达式必须具有唯一返回值。
2.  支持单列、常量表达式、条件表达式、函数表达式……
3.  支持用户自定函数，PACKAGE 中定义的函数。
4.  不能引用虚拟列。
5.  不支持伪列。
6.  不支持聚集函数、LISTAGG()、列转行函数。

三、虚拟列的使用

在 DDL 中应用

• 可在CREATE TABLE 中定义虚拟列。

• 可通过 ALTER TABLE ADD Col 新增虚拟列。

• 可通过ALTER TABLE Modify Col 修改虚拟列。

  •  可以修改虚拟列数据类型、列表达式。

  •  不支持修改虚拟列表达式引用的列。

  •  不支持将虚拟列修改为普通列。

  •  不支持将普通列修改为虚拟列。

• 可通过ALTER TABLE DROP Col 删除虚拟列。

  • 不能直接删除虚拟列引用的列，需先删除虚拟列，再删除指定列。

• 支持 COMMNET 给虚拟列添加注释。

• 虚拟列不支持 DEFAULT 表达式。

• 不支持 CREATE AS SELECT 。

基于虚拟列创建约束和索引，GBase 8s 和 Oracle 的支持情况如下表所示：

	GBase 8s	ORACLE
主键	N	Y
外键	N	Y
NOT NULL/NULL	Y	Y
CHECK	Y	Y
UNIQUE/DISTINCT	N	Y
索引	只支持函数索引。 其它索引创建成功，但不生效。	Y

注：NOT NULL 约束只能在create table 定义虚拟列时指定，不支持alter modify not null。

不同类型对象的使用

• 支持在普通表、视图、RAW 表、分片表、ROW TYPE 表使用虚拟列；

• 不支持在临时表以及外部表中使用虚拟列；

• 不支持作为哈希分区表的分区字段；

• 不允许对虚拟列执行 INSERT 操作；

• 支持在存储过程、包、触发器中使用虚拟列。

在 DML 中应用

1.  不允许在虚拟列上执行UPDATE语句；
2.  支持在 UPDATE/DELETE 的 WHERE 语句中使用虚拟列；
3.  支持 insert into t1 select  * from  t2 。

在 DQL 中应用

不支持在 group by 子句后使用。其余查询语法都支持。

四、虚拟列属性查询

系统表

• SYSCOLUMNS  

COLATTR 字段，新增值 256 或  768 表示：该列为虚拟列，显式指定虚拟列数据类型时，COLATTR 值为 768。 不显式指定时，COLATTR 值为 256。

      示例：查询SC 表的列属性

>  select distinct t.tabname,sysc.colname,sysc.colattr
 from  systables t, syscolumns  sysc
 where sysc.tabid=t.tabid
  and t.tabname='sc';
tabname  sc
colname   course_id
colattr  0
tabname  sc
colname  source
colattr  0
tabname  sc
colname  stu_id
colattr  128
tabname  sc
colname  stu_nm
colattr  0
tabname  sc
colname   v_source
colattr  768
5 row(s) retrieved.

• SYSDEFAULTSEXPR

新增 VTCOL 字段，作为虚拟列标识：

               值为 1 ：定义为虚拟列表达式；

               值为 0： DEFAULT 表达式。

DEFAULT 字段，可显式虚拟列列表达式。

示例：查询 SC 表上虚拟列 v_source 的表达式。

> select   t.tabname,d.colno,d.vtcol,d.default
from sysdefaultsexpr d, systables t
 where d.tabid=t.tabid
  and t.tabname='sc'
  and d.type='T';> > > >
tabname  sc
colno    5
vtcol    1
default  CASE WHEN (source < 60.00 )  TH

tabname  sc
colno    5
vtcol    1
default  EN '不合格'   WHEN (source >

tabname  sc
colno    5
vtcol    1
default  60.00 )  THEN '合格'  END

3 row(s) retrieved.

虚拟列作为GBase8s数据库的一项高级功能，为数据操作提供了更大的灵活性。通过本文的介绍，我们了解到虚拟列的定义、创建、使用以及相关限制。希望这些信息能够帮助您更有效地利用虚拟列功能，提升数据库管理的效率。

原文链接：

https://www.gbase.cn/community/post/3980

更多精彩内容尽在南大通用GBase技术社区，南大通用致力于成为用户最信赖的数据库产品供应商。