8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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.
Related articles.
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...