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.
Applications and Databases
Simplify management, boost performance, and dramatically cut costs
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;
Recursive Subquery Factoring
Introduced in Oracle 11g Release 2, recursive subquery factoring allows the WITH clause to perform recursive queries, enabling operations like hierarchical data retrieval without relying on older methods such as CONNECT BY. This feature simplifies the process of querying hierarchical data structures.oracle-base.com+3oracle-base.com+3complexsql.com+3
Example:
|
1 2 3 4 5 6 7 |
sqlCopyEdit<code>WITH RECURSIVE temp (n, fact) AS ( SELECT 0, 1 — Initial Subquery UNION ALL SELECT n+1, (n+1)*fact FROM temp WHERE n < 9 — Recursive Subquery ) SELECT * FROM temp; |
In this example, the recursive common table expression calculates factorials from 0 to 9. en.wikipedia.org
PL/SQL Declarations within SQL
Oracle Database 12c Release 1 introduced the ability to declare PL/SQL functions and procedures directly within the WITHclause of a SQL statement. This enhancement allows for more modular and readable code by embedding procedural logic within SQL queries.asktom.oracle.com+5stackoverflow.com+5oracle-base.com+5
Example:
|
1 2 3 4 5 6 7 8 |
sqlCopyEdit<code>WITH FUNCTION get_discount(p_total NUMBER) RETURN NUMBER IS BEGIN RETURN p_total * 0.1; END; SELECT customer_id, get_discount(total_spent) AS discount FROM customers; |
Here, a function get_discount is defined within the WITH clause and then applied in the subsequent SELECT statement.
These advancements in the WITH clause enhance its utility, allowing for more concise and efficient query formulations. By leveraging recursive subquery factoring and inline PL/SQL declarations, developers can write more powerful and maintainable SQL code.
