DBA Hub

📋Steps in this guide1/5

Table Values Constructor in Oracle Database 23ai/26ai

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

oracle 23configurationintermediate
by OracleDba
15 views
1

Setup

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
drop table if exists t1;

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

INSERT

The table values constructor allows us to insert multiple rows into a table in a single step. That's a single network round trip without having to combine all the insert statements into a PL/SQL block.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
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>

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 ONE
         2 TWO    Description for TWO
         3 THREE  Description for THREE

SQL>
3

SELECT

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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>
4

WITH Clause

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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>
5

MERGE

The table values constructor can be used as the source data for a statement. For more information see: - IN Condition - VALUES Clause - Multitable Inserts Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
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;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!