As you develop more complex stored procedures, you’ll notice that they can get hard to read and difficult to scale. The WITH clause simplifies complex queries so that you can use a single variable throughout your stored procedures without repeating complex queries. Instead of reusing queries, the Oracle WITH statement stores values to improve performance for your stored procedures.
How the Oracle WITH Clause Works
If you only have one query in a stored procedure, it’s fairly easy to read it and understand the developer’s intention. As the stored procedure changes, more queries are added, and additional tables are joined to various queries, it gets too complicated to read it and identify the developer’s intention.
The WITH clause takes complex SQL queries with several join statements and simplifies them for future use in the stored procedure. Developers can store a complex query result into a variable and use it throughout the entire stored procedure rather than continually using the same complex query multiple times.
When to Use the Oracle WITH Clause
Using the same complex query numerous times in a stored procedure exhausts database resources unnecessarily. Every time the query executes, the database must use server resources to run it. Instead of using resources for the same complex query, the WITH clause gives developers a way to store results as the stored procedure executes so that the database no longer needs to run the same query multiple times. Using the WITH clause saves resources and improves performance of your stored procedures.
How to Use Oracle WITH Clause: Code Example
You can use one or several WITH statements in your stored procedures. For every WITH statement, you can store a value in a variable to use it later in your code. For example, suppose that you want to store a customer’s total number of orders. You later use the total number of orders to determine a discount for the customer, or you might use it to get an average. Whatever you choose to do, using the WITH statement avoids calling the same query multiple times in your stored procedure.
The following WITH statement gets the total number of orders in the Customer table:
WITH orderCount
AS
(SELECT
COUNT(orders) as orderCount
FROM Customer
WHERE customerId = 44)
In the above query, Oracle counts the number of orders for a customer with an identifier of 44. The count is then stored in the orderCount variable, and it can be used at any time throughout the stored procedure. If you want to look at the value, use the following query:
SELECT orderCount;
