Table Values Constructor in Oracle Database 23c

The table values constructor allows us to define multiple rows using a single constructor for use in SQL statements.

Setup

The following table is required to run the examples in this article.

drop table if exists t1;

create table t1 (

  id number,

  code varchar2(6),

  description varchar(25),

  constraint t1_pk primary key (id)

);

INSERT

The table values constructor allows us to insert multiple rows into a table in a single step.

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>

That's a single network round trip without having to combine all the insert statements into a PL/SQL block.

SELECT

The same type of table values constructor can be used in the FROM clause of a SELECT statement. Notice we have to alias the column names so they are presented correctly.

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

SQL>

WITH Clause

The table values constructor can be used as part of a WITH clause.

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

SQL>

MERGE

The table values constructor can be used as the source data for a MERGE statement.

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);

3 rows merged.

SQL>

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

6 rows selected.

SQL>

rollback;

Source: oracle-base.com