- Introduction
The database’s SQL engine now supports a VALUES clause for many types of statements. This new clause allows for materializing rows of data on the fly by specifying them using the new syntax without relying on existing tables.
Oracle supports the VALUES clause for the SELECT, INSERT, and MERGE statements.
The introduction of the new VALUES clause allows developers to write less code for ad-hoc SQL commands, leading to better readability with less effort.
- INSERT and VALUES keyword
Before this feature we used to insert the data’s like below
Create table Emp
(
Empno number,
Ename varchar2(100)
);
Insert into emp values (1,’xxx’);
Insert into emp values (2,’yyy’);
Insert into emp values (3,’zzz’);
Commit;
In Oracle 23C, added this new feature to overcome from the multiple insert statement to the following statement.
Insert into emp values (1,’xxx’), (2,’yyy’), (3,’zzz’);
- SELECT and VALUES keyword
We can see how the same feature must be used for the select statement.
Select * from (values (1,’Ravi’), (2,’Arun’), (3,’Krish’)) as emp_tab (empno, ename);
- MERGE and VALUES keyword
In the same way will see how to write the queries with the merge statement.
MERGE INTO emp
USING (VALUES(1,’RAVI’),(2,’ARUN’),(3,’KRISH’)) as emp_tab(empno,ename)
On (emp.empno = emp_tab.empno)
When MATCHED then
Update set emp.ename = emp_tab.ename
When NOT MATCHED then
Insert (emp.empno , emp.ename)
Values ( emp_tab.empno,emp_tab.ename);
- WITH and VALUES keyword
WITH T (id,ename) as
(
Values (1,’A’), (2,’B’)
)
Select * from T;
- CONCLUSION
The new VALUE constructor leverages a much simpler syntax, either for SELECT statements or for INSERT/CREATE as SELECT statements.
