Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle PL/SQL WITH Clause      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

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      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 204 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?