8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

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
INSERT
SELECT
WITH Clause
MERGE

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;

For more information see:

Hope this helps. Regards Tim...

Back to the Top.