Oracle WITH operator

The Oracle PL/SQL operator WITH allows you to give a subquery block a name/allowable that can be referenced in several places in the main SQL query.

The name assigned to the subquery is processed as if it were a built-in view or table. The WITH SQL statement is essentially a replacement for a regular subquery.

Oracle PL/SQL WITH syntax with one subquery

WITH query_name AS (SELECT FROM table_A)
SELECT column_list
FROM query_name [,table_name]
[WHERE conditions]

or

Oracle PL/SQL WITH syntax with multiple subqueries

WITH query_name_A AS
(SELECT FROM table_A),
query_name_B AS
([SELECT FROM query_name_A] | [SELECT FROM table_B]).
SELECT column_list
FROM query_name_A, query_name_B [,table_X | Join table_Z].
[WHERE conditions]

• expressions – fields or calculations under request.
• column_list – fields or calculations of the main request.
• table_A, table_B, table_X, table_Z – tables or connections for subqueries.
• query_name_A, query_name_B – the alias of the subquery. If there are several subqueries, they are listed by a comma.
• WHERE conditions – conditions that must be met for main queries.

Note: 

• Formally, the WITH offer is called subquery factoring.
• The SQL WITH offer is used when a subquery is executed several times.
• WITH subqueries are listed by a comma.

Application

• SQL-proposal WITH was introduced by Oracle in Oracle 9i database release 2.
• Starting with Oracle Database 12c Release 1 (12.1), functions and procedures can be defined in the WITH operator.

Examples of Oracle PL/SQL WITH Operator

For an example of using a WITH operator, with one and two subqueries, let’s create several tables with data.

--Remove the tables from the database, if any.
--DROP TABLE EMP PURGE;
--DROP TABLE DEPT PURGE;

--Create two EMP and DEPT tables:
CREATE TABLE DEPT (
DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14),
CITY VARCHAR2(15)
) ;

CREATE TABLE EMP (
EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
);

-- And add the following data to the EMP and DEPT tables:
--DEPT .
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
--EMP
INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK',7902,to_date('17.12.1980', 'dd.mm.yyyy'),800,NULL,20);
INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN',7698,to_date('20.2.1981', 'dd.mm.yyyy'),1600,300,30);
INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN',7698,to_date('22.2.1981', 'dd.mm.yyyy'),1250,500,30);
INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER',7839,to_date('2.4.1981', 'dd.mm.yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN',7698,to_date('28.9.1981', 'dd.mm.yyyy'),1250,1400,30);
INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER',7839,to_date('1.5.1981', 'dd.mm.yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER',7839,to_date('9.6.1981','dd.mm.yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST',7566,to_date('13.7.87', 'dd.mm.rr').85,3000,NULL,20);
INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT',NULL,to_date('17.11.1981', 'dd.mm.yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN',7698,to_date('8.9.1981', 'dd.mm.yyyy'),1500,0,30);
INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK',7788,to_date('13.7.87', 'dd.mm.rr').51,1100,NULL,20);
INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK',7698,to_date('3.12.1981', 'dd.mm.yyyy'),950,NULL,30);
INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST',7566,to_date('3.12.1981', 'dd.mm.yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK',7782,to_date('23.1.1982', 'dd.mm.yyyy'),1300,NULL,10);
COMMIT;

WITH example with one subquery

Consider the example of the sql operator with a single subquery to understand how to use the with operator in Oracle PL/SQL. For example:

WITH employee AS (SELECT * FROM emp)
SELECT * FROM employee WHERE empno > 7900
UNION ALL
SELECT * FROM employee WHERE sal > 3000

In this example, we created a subquery with the nickname employee from the Employees table. Then, using two main queries and the UNION ALL operator, we requested data from all employees with empno > 7900 and all employees with sal > 3000.

In the following example, we want to know how many people are in the department for each employee.

WITH dept_count AS (
SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno)
SELECT e.ename AS employee_name,
dc.dept_count AS emp_dept_count
FROM emp e
JOIN dept_count dc ON e.deptno = dc.deptno;

Example of WITH with two subqueries

For an example of using a WITH operator, let’s create some tables with data.

For example, we may need to select departments with salaries above average. To do this, first define the amount of salaries by department in the first subquery dept_costs.

Then in the second subquery avg_cost let’s define the average salary by department. For example:

WITH
dept_costs AS (
SELECT dname, SUM(sal) dept_total
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY dname
),
avg_cost AS (
SELECT SUM(dept_total)/COUNT(*) avg
FROM dept_costs
)
SELECT *
FROM dept_costs
WHERE dept_total > (SELECT avg FROM avg_cost)
ORDER BY dname;

Example of WITH with the function

The following test table must be created for the WITH operator examples.

DROP TABLE t1 PURGE;

CREATE TABLE t1 AS
SELECT 1 AS id
FROM dual
CONNECT BY level <= 1000000;

--Statistics collection on CTAS is no longer required in 12c,
--provided that the request is issued by a non-SYS user,
--EXEC DBMS_STATS.gather_table_stats (USER, 't1');

In this WITH statement, the declaration section can be used to define PL/SQL functions as shown below.

WITH 
FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN p_id;
END;
SELECT with_function(id)
FROM t1
WHERE rownum = 1

--Result of
WITH_FUNCTION(ID)
-----------------
1

In terms of function name resolution, the PL/SQL functions defined in the WITH declaration section have priority over objects with the same name defined at the schema level.

An example of WITH with the procedure

We can also define procedures in the WITH operator announcement section, even if they are not used.

WITH
PROCEDURE with_procedure(p_id IN NUMBER) IS
BEGIN
DBMS_OUTPUT.put_line('p_id=' || p_id);
END;
SELECT id
FROM t1
WHERE rownum = 1

--Result of
ID
----------
1

In fact, you would only put the procedure in a WITH statement if you planned to call the procedure from a function in the announcements section.

WITH
PROCEDURE with_procedure(p_id IN NUMBER) IS
BEGIN
DBMS_OUTPUT.put_line('p_id=' || p_id);
END;

FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
BEGIN
with_procedure(p_id);
RETURN p_id;
END;
SELECT with_function(id)
FROM t1
WHERE rownum = 1

--Result of
WITH_FUNCTION(ID)
-----------------
1

p_id=1

It seems that this function is not supported by PL/SQL. Any attempt to use it will result in compilation errors, as shown below.

BEGIN
FOR cur_rec IN (WITH
FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN p_id;
END;
SELECT with_function(id)
FROM t1
WHERE rownum = 1)
LOOP
NULL;
END LOOP;
END;

FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
*
ERROR at line 3:
ORA-06550: line 3, column 30:
PL/SQL: ORA-00905: missing keyword
ORA-06550: line 2, column 19:
PL/SQL: SQL Statement ignored
ORA-06550: line 5, column 34:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
loop

Using dynamic SQL allows you to bypass this restriction.

SET SERVEROUTPUT ON
DECLARE
l_sql VARCHAR2(32767);
l_cursor SYS_REFCURSOR;
l_value NUMBER;
BEGIN .
l_sql := 'WITH
FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN p_id;
END;
SELECT with_function(id)
FROM t1
WHERE rownum = 1';

OPEN l_cursor FOR l_sql;
FETCH l_cursor INTO l_value;
DBMS_OUTPUT.put_line('l_value=' || l_value);
CLOSE l_cursor;
END;

l_value=1

PL/SQL procedure successfully completed.

Support for this function using static SQL within PL/SQL is expected in the next Oracle release.

PRODUCTIVITY BENEFITS

The whole reason to define embedded PL/SQL code is to improve performance.
Create a normal function to use as a comparison.

CREATE OR REPLACE FUNCTION normal_function(p_id IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN p_id;
END;

Run the next test, which measures the time spent and CPU load of the query using the definition of the built-in function.

DECLARE
l_time PLS_INTEGER;
l_cpu PLS_INTEGER;

l_sql VARCHAR2(32767);
l_cursor SYS_REFCURSOR;

TYPE t_tab IS TABLE OF NUMBER;
l_tab t_tab;
BEGIN
l_time := DBMS_UTILITY.get_time;
l_cpu := DBMS_UTILITY.get_cpu_time;

l_sql := 'WITH
FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN p_id;
END;
SELECT with_function(id)
FROM t1';

OPEN l_cursor FOR l_sql;
FETCH l_cursor
BULK COLLECT INTO l_tab;
CLOSE l_cursor;

DBMS_OUTPUT.put_line('WITH_FUNCTION : ' ||.
'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||
'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs');

l_time := DBMS_UTILITY.get_time;
l_cpu := DBMS_UTILITY.get_cpu_time;

l_sql := 'SELECT normal_function(id)
FROM t1';

OPEN l_cursor FOR l_sql;
FETCH l_cursor
BULK COLLECT INTO l_tab;
CLOSE l_cursor;

DBMS_OUTPUT.put_line('NORMAL_FUNCTION: ' ||.
'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||
'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs');

END;

WITH_FUNCTION : Time=45 hsecs CPU Time=39 hsecs
NORMAL_FUNCTION: Time=129 hsecs CPU Time=113 hsecs

PL/SQL procedure successfully completed.

From this we see that the definition of the built-in function takes approximately one third of the time spent and the time of the processor for completion.

Example of PRAGMA UDF

In a number of presentations prior to the official release of 12c, speakers mentioned PRAGMA UDF (User Defined Function), which supposedly gives you the performance advantages of built-in PL/SQL, while allowing you to define a PL/SQL object outside of the SQL statement. The following code overrides the previous common function to use this pragma.

CREATE OR REPLACE FUNCTION normal_function(p_id IN NUMBER) RETURN NUMBER IS
PRAGMA UDF;
BEGIN .
RETURN p_id;
END;

Once the function is compiled, the execution of the test from the previous section for this function gives quite interesting results.

SET SERVEROUTPUT ON
DECLARE
l_time PLS_INTEGER;
l_cpu PLS_INTEGER;

l_sql VARCHAR2(32767);
l_cursor SYS_REFCURSOR;

TYPE t_tab IS TABLE OF NUMBER;
l_tab t_tab;
BEGIN
l_time := DBMS_UTILITY.get_time;
l_cpu := DBMS_UTILITY.get_cpu_time;

l_sql := 'WITH
FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN p_id;
END;
SELECT with_function(id)
FROM t1';

OPEN l_cursor FOR l_sql;
FETCH l_cursor
BULK COLLECT INTO l_tab;
CLOSE l_cursor;

DBMS_OUTPUT.put_line('WITH_FUNCTION : ' ||.
'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||
'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs');

l_time := DBMS_UTILITY.get_time;
l_cpu := DBMS_UTILITY.get_cpu_time;

l_sql := 'SELECT normal_function(id)
FROM t1';

OPEN l_cursor FOR l_sql;
FETCH l_cursor
BULK COLLECT INTO l_tab;
CLOSE l_cursor;

DBMS_OUTPUT.put_line('NORMAL_FUNCTION: ' ||.
'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||
'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs');

END;

WITH_FUNCTION : Time=44 hsecs CPU Time=40 hsecs
NORMAL_FUNCTION: Time=33 hsecs CPU Time=29 hsecs

PL/SQL procedure successfully completed.

It seems that a stand-alone function using the PRAGMA UDF consistently performs the built-in function.

I have the impression that calling a function defined with PRAGMA UDF directly from PL / SQL will not succeed. It doesn’t look like an accident.

DECLARE
l_number NUMBER;
BEGIN
l_number := normal_function(1);
END;

PL/SQL procedure successfully completed.

SQL: WITH Clause