Oracle 数据库 23ai 中的表值构造函数

Yunjie Ge

发布于 2025-03-14 17:19:36

2600

代码可运行

运行总次数：0

代码可运行

表值构造函数（Table Values Constructor）是 Oracle 数据库 23ai 引入的一项新特性，它允许我们在 SQL 语句中通过单一构造表达式一次性定义多行数据。其实这种功能在其它数据库系统中（例如 MySQL）早就支持，MySQL 多行插入语法已经存在多年。

Oracle 通过在 23ai 中引入这一特性，它不仅能够减少网络往返次数、提高事务处理效率，而且还能使 SQL 语句更加直观、易于维护。无论是在数据导入、临时数据处理还是复杂数据同步场景中，这种简洁而强大的构造方式都能带来显著优势，帮助开发人员构建更高效、可靠的数据库应用。

1. 环境准备（Setup）

在开始示例操作前，需要准备一张用于演示的表。本文中使用的表包含三个字段：

drop table if exists t1;

create table t1 (
  id number,
  code varchar2(6),
  description varchar(25),
  constraint t1_pk primary key (id)
);

2. 多行数据插入（INSERT）

使用表值构造函数，我们可以在单个 INSERT 语句中插入多条记录，而无需将多个独立的 INSERT 语句组合在一起。这样不仅可以减少网络往返的次数，还能在事务中一次性提交所有数据。

insert into t1
values (1, 'ONE', 'Description for ONE'),
       (2, 'TWO', 'Description for TWO'),
       (3, 'THREE', 'Description for THREE');

commit;

select * from t1;

运行结果：

        ID CODE   DESCRIPTION
---------- ------ -------------------------
         1 ONE    Description for ONE
         2 TWO    Description for TWO
         3 THREE  Description for THREE

单一网络往返在一个语句中发送所有数据，减少了网络通信的延迟，这在分布式系统或网络延迟较高的环境中尤其重要。
事务控制如果插入过程中出现任何错误（如字段数据长度超出限制），整个操作会回滚，从而保证数据一致性。
应用场景非常适合批量数据导入、日志记录和临时数据存储等场景，既提高了操作效率，也使 SQL 代码更为简洁明了。

3. 在 SELECT 中构造数据（SELECT）

表值构造函数同样可以用于 SELECT 语句的 FROM 子句中，直接构造一个临时数据集合用于查询或调试，这种方式为数据分析和快速测试提供了极大便利。

select *
from   (values
          (4, 'FOUR', 'Description for FOUR'),
          (5, 'FIVE', 'Description for FIVE'),
          (6, 'SIX', 'Description for SIX')
       ) a (id, code, description);

查询结果：

        ID CODE DESCRIPTION
---------- ---- --------------------
         4 FOUR Description for FOUR
         5 FIVE Description for FIVE
         6 SIX  Description for SIX

内联表构造直接在查询语句中定义数据，无需依赖物理表，这样做对于快速调试和验证数据逻辑十分有用。
列别名的必要性构造的临时表必须指定列名，这确保了查询结果的准确性和可读性。
开发实践在实际开发中，内联构造的数据集可以用来模拟复杂查询中的子查询或联结操作，减少依赖外部数据表带来的维护成本。

4. 使用 WITH 子句（WITH Clause）

将表值构造函数与 WITH 子句结合使用，可以创建公用表表达式（CTE），在后续的 SQL 查询中重复引用这一临时数据集，提高代码的模块化和可读性。

with a (id, code, description) AS (
  values (7, 'SEVEN', 'Description for SEVEN'),
         (8, 'EIGHT', 'Description for EIGHT'),
         (9, 'NINE', 'Description for NINE')
)
select * from a;

查询结果：

        ID CODE  DESCRIPTION
---------- ----- ---------------------
         7 SEVEN Description for SEVEN
         8 EIGHT Description for EIGHT
         9 NINE  Description for NINE

公用表表达式（CTE）的优势通过 WITH 子句构建临时数据集，使得复杂查询逻辑分层清晰，便于调试和维护。
数据预处理CTE 常用于在主查询前对数据进行预处理或聚合，结合表值构造函数，可以灵活构建测试数据和模拟环境。
实际应用在数据仓库建设和报表系统中，这种方式能够大大简化 SQL 语句，提高开发效率和代码可读性。

5. 使用 MERGE 语句（MERGE）

表值构造函数也可以作为 MERGE 语句的数据源，实现数据的更新和插入（合并）操作。当源数据中的记录在目标表中存在时，执行更新操作；如果不存在，则执行插入操作，从而简化了数据同步与批处理任务。

merge into t1 a
  using (values
          (4, 'FOUR', 'Description for FOUR'),
          (5, 'FIVE', 'Description for FIVE'),
          (6, 'SIX', 'Description for SIX')
        ) b (id, code, description)
  on (a.id = b.id)
  when matched then
    update set a.code        = b.code,
               a.description = b.description
  when not matched then
    insert (a.id, a.code, a.description)
    values (b.id, b.code, b.description);

select * from t1;

查询结果：

        ID CODE   DESCRIPTION
---------- ------ -------------------------
         1 ONE    Description for ONE
         2 TWO    Description for TWO
         3 THREE  Description for THREE
         4 FOUR   Description for FOUR
         5 FIVE   Description for FIVE
         6 SIX    Description for SIX

MERGE 操作的强大功能在一次 SQL 语句中同时处理插入和更新逻辑，大大简化了传统的分步操作。
事务一致性MERGE 保证操作的原子性，出现错误时能整体回滚，确保数据状态的一致性。
数据同步应用在数据仓库和实时数据集成场景中，MERGE 是进行数据合并、同步和去重的常用工具，能够有效减少代码冗余和维护成本。

7. 表值构造函数的优势

采用表值构造函数带来了许多明显的好处，包括但不限于以下几点：

减少网络往返将多行数据一次性提交，显著减少了 SQL 语句的执行次数，尤其适用于网络延迟较高的分布式环境。
简化代码结构用一条语句实现多行插入、构造临时数据集等操作，使得 SQL 代码更加清晰、易于维护，降低了出错率。
提升性能批量操作通常比逐行插入具有更好的性能表现，对于大数据量操作和实时数据处理尤为重要。
事务管理在事务中一次性提交所有操作，确保数据一致性，并能在出现错误时整体回滚，降低了数据异常的风险。
跨数据库兼容性随着其他数据库系统长期支持类似功能，Oracle 的这一新特性使得跨平台开发时能更好地借鉴和整合各系统优势，减少迁移过程中需要改写的代码量。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2025-03-13，如有侵权请联系 cloudcommunity@tencent.com 删除

网络