Friday, August 18, 2023

Table Values Constructor in Oracle Database 23c

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

Related Posts

0 comments:

Post a Comment