Oracle 23c introduces a powerful extension to the VALUES keyword, enabling you to materialize rows of data directly within various SQL statements. This streamlines code, improves readability, and offers flexibility in data manipulation.
Supported Statements:
- INSERT: Insert multiple rows simultaneously.
- SELECT: Generate inline datasets for queries.
- WITH Clause: Create inline views for complex queries.
- MERGE: Provide data for both MATCHED and NOT MATCHED clauses.
Examples:
INSERT Statement:
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (1, 'John', 'Doe'), (2, 'Jane', 'Smith'), (3, 'Bob', 'Johnson');SELECT Statement:
SELECT *
FROM (VALUES (100, 'Product A', 25), (200, 'Product B', 30)) AS products (id, name, price);WITH Clause:
WITH temp_data (employee_id, first_name, last_name) AS (
VALUES (1, 'John', 'Doe'), (2, 'Jane', 'Smith'), (3, 'Bob', 'Johnson')
)
SELECT * FROM temp_data;MERGE Statement:
MERGE INTO employees e
USING (VALUES (4, 'Alice', 'Parker'), (5, 'David', 'Lee')) AS new_emps (id, first_name, last_name)
ON (e.employee_id = new_emps.id)
WHEN MATCHED THEN
UPDATE SET first_name = new_emps.first_name, last_name = new_emps.last_name
WHEN NOT MATCHED THEN
INSERT (employee_id, first_name, last_name) VALUES (new_emps.id, new_emps.first_name, new_emps.last_name);Conclusion:
The enhanced VALUES keyword in Oracle 23c simplifies SQL syntax, improves code readability, and offers a more flexible approach to data manipulation. Embrace this feature to write more concise and efficient SQL statements in your Oracle database applications.