Oracle 23ai新特性:表值构造函数

随着 Oracle 数据库不断发展，新版本引入了许多增强功能和特性，以提高开发效率、简化 SQL 编写并优化性能。Oracle 23c 引入了表值构造器（Table Values Constructor），这一特性允许用户直接在 SQL 语句中定义和使用内联表数据，极大地增强了 SQL 的灵活性和表达能力。

本文将详细介绍 Oracle 23c 中的表值构造器特性，包括其语法、应用场景以及如何与现有 SQL 结构结合使用，旨在帮助数据库开发者更好地理解和利用这一强大的工具。

一、表构造器简介

表值构造器是一种 SQL 构造，它允许你在查询中创建一个临时的、匿名的表结构，并填充具体的数据行。这使得你可以像操作常规表一样对这些数据进行查询、连接和其他操作，而无需预先创建物理表或视图。

表值构造器特别适合用于以下场景：

测试数据生成：快速创建小规模的数据集用于调试或演示。

复杂查询构建：作为子查询的一部分，提供中间结果集。

参数化输入：为存储过程或函数传递表格形式的参数。

二、基本语法

Oracle 23c 中，表值构造器的基本语法如下：

WITH table_name AS (VALUES (value1, value2, ...),(value3, value4, ...)
)
SELECT * FROM table_name;

或者更简洁地，在 SELECT 语句中直接使用：

SELECT *
FROM VALUES (value1, value2),(value3, value4);

每个 VALUES 子句可以包含多个逗号分隔的值列表，每个列表代表一行数据。如果需要指定列名，可以在 VALUES 后面添加 AS 关键字和列名列表：

SELECT *
FROM (VALUES (1, 'Alice'),(2, 'Bob')) AS t(id, name);

三、应用示例

运行本文中的示例需要下表。

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

3.1 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 ONE2 TWO    Description for TWO3 THREE  Description for THREESQL>

这是一个事务，无需将所有插入语句组合到 PL/SQL 块中。如果任何值导致失败，则该语句的所有插入都会丢失。

在此示例中，我们再次插入 3 条记录，但确保中间值子句导致整个事务失败，尚未插入任何新行。

insert into t1
values (4, 'FOUR', 'Description for ONE'),(5, 'FIVE', 'This one will fail because it is too big'),(6, 'SIX', 'Description for THREE');insert into t1
*
ERROR at line 1:
ORA-12899: value too large for column "TESTUSER1"."T1"."DESCRIPTION" (actual: 40, maximum: 25)select * from t1;ID CODE   DESCRIPTION
---------- ------ -------------------------1 ONE    Description for ONE2 TWO    Description for TWO3 THREE  Description for THREESQL>

3.2 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 FOUR5 FIVE Description for FIVE6 SIX  Description for SIXSQL>

3.3 WITH语句

表值构造函数可以用作WITH 子句的一部分。

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 SEVEN8 EIGHT Description for EIGHT9 NINE  Description for NINESQL>

3.4 MERGE

表值构造函数可以用作 MERGE 语句的源数据。

merge into t1 ausing (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 thenupdate set a.code        = b.code,a.description = b.descriptionwhen not matched theninsert (a.id, a.code, a.description)values (b.id, b.code, b.description);3 rows merged.SQL>select * from t1;ID CODE   DESCRIPTION
---------- ------ -------------------------1 ONE    Description for ONE2 TWO    Description for TWO3 THREE  Description for THREE4 FOUR   Description for FOUR5 FIVE   Description for FIVE6 SIX    Description for SIX6 rows selected.SQL>

3.5 PROCEDURE

在 PL/SQL 中，表值构造器还可以用作存储过程或函数的输入参数，实现更加灵活的数据传递方式：

CREATE OR REPLACE PROCEDURE process_employee_data(p_employees SYS.ODCIVARCHAR2LIST
) IS
BEGINFOR i IN 1..p_employees.COUNT LOOPINSERT INTO employees (name, department, salary)VALUES (p_employees(i).name,p_employees(i).department,p_employees(i).salary);END LOOP;
END;
/

在这个例子中，SYS.ODCIVARCHAR2LIST 是一种集合类型，它可以用来接收来自表值构造器的数据。