The WITH clause or subquery factoring is a clause in SQL that allows you to define a query before your SELECT statement and use that query definition inside your main SELECT statement. It is like temporary table, where you store the data once and read it multiple times in your SQL query.

You can define custom PL/SQL functions and Procedures which you can use in your main SELECT query. They don’t take up permanent storage space in your database and you don’t need to apply security to them to ensure that only your query accesses it.

It is helpful in several reason.

•    WITH clause is used when a subquery is executed multiple times.

•    It is used to simplify complex SQL.

•    To improve the performance of the query.

•    Best use when the result of the WITH is required more than one time such as when one  

   aggregate value needs to be compared against two or three times.

Let’s create a PL/SQL function and Procedure using WITH clause and use them in main SELECT query.

In this example we will create a PL/SQL Procedure to get all the Payment Numbers which are paid against the invoice, and we will create a PL/SQL Function to call the above Procedure and return the all the Payment Numbers in a single column.