Quick Search:
 
 The Oracle PL/SQL WITH Clause      [Return To Index] Jump to:  

Term: WITH

Definition:
In Oracle PL/SQL, the WITH clause is a subquery factoring clause which is used to create a named subquery block. This block acts as a virtual table or an inline view for a SQL statement. It was first introduced in Oracle 9.2. It reduces the overhead of multiple references of a table alias in a query. The scope of the WITH clause subquery block is the SELECT query with which is is associated.

Example Syntax:

WITH [NAME] as
(
SELECT statements
)
SELECT [column list]
FROM [NAME]


Example Usage:

The SQL statement below uses WITH clause to declare a virtual table C and has a SELECT query on it.


SQL> WITH C AS
(SELECT 1 N FROM DUAL UNION ALL
SELECT 2 FROM DUAL UNION ALL
SELECT 5 FROM DUAL UNION ALL
SELECT 20 FROM DUAL UNION ALL
SELECT 29 FROM DUAL UNION ALL
SELECT 40 FROM DUAL)
SELECT * FROM C;

N
----------
1
2
5
20
29
40

6 rows selected.



Related Links:

Related Code Snippets:
 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us © 2003 - 2024 psoug.org