Table Values Constructor in Oracle Database 23ai

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