暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / Oracle 数据库 23ai 中的表值构造函数

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

山东Oracle用户组 2025-03-13
89
表值构造函数（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 的这一新特性使得跨平台开发时能更好地借鉴和整合各系统优势，减少迁移过程中需要改写的代码量。



                    👇👇关注不迷路，技术有深度👇👇

                    数据库者，乃数据之仓廪，业务之根基也。其管控者，号曰DBA，承安稳之责，护存储之安。

                    昼则巡检索引，夜则优化流程，一有警讯，则彻夜排查，焚膏继晷而不辞劳。

                    纵遇崩溃之时，亦能沉毅应对，重整归档，复归平定。故有云：DBA者，数据之柱石，系统之长城也。能担此任，必怀坚韧与明察。守护千行万业，乃彰己之价值也。

                    oracle构造函数大数据数据库select
                    文章转载自山东Oracle用户组，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

                    评论