Table of Contents
WITH Clause in Oracle
WHAT IS THE USE OF WITH CLAUSE IN ORACLE:
WITH clause was introduced in Oracle 9i Release 2. Inside WITH clause we can define multiple query block and provide a name to each query block which is also called sub-query refactoring. Then in the main query we can use each query block as a data source. WITH clause helps us to write complex queries in a simple manner. This method is often used to improve query speed for complex sub-queries.
Syntax of SQL queries having WITH clause is little bit confusing as the query starts with WITH clause rather than SELECT clause. We have to define the each query block first and finally at the end the main query is constructed where the above query blocks are used as a data source or temp table.
EXAMPLES OF WITH CLAUSE IN ORACLE:
EXAMPLE 1:
--WITH Clause in Oracle: Example
WITH
LOCATION_DTLS
AS
(SELECT A.LOCATION_ID,
A.STREET_ADDRESS,
A.POSTAL_CODE,
A.CITY,
A.STATE_PROVINCE,
A.COUNTRY_ID,
B.COUNTRY_NAME,
C.REGION_NAME
FROM LOCATIONS A,
COUNTRIES B,
REGIONS C
WHERE A.COUNTRY_ID = B.COUNTRY_ID
AND B.REGION_ID = C.REGION_ID
),
DEPARTMENT_DTLS
AS
(SELECT A.DEPARTMENT_ID,
A.DEPARTMENT_NAME,
A.MANAGER_ID,
A.LOCATION_ID,
COUNT(B.EMPLOYEE_ID) NO_OF_EMPLOYEES,
MAX(B.SALARY) MAX_SALARY,
MIN(B.SALARY) MIN_SALARY,
SUM(B.SALARY) TOTAL_SALARY,
MAX(B.HIRE_DATE) MAX_HIRE_DATE,
MIN(B.HIRE_DATE) MIN_HIRE_DATE
FROM DEPARTMENTS A,
EMPLOYEES B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
GROUP BY A.DEPARTMENT_ID,
A.DEPARTMENT_NAME,
A.MANAGER_ID,
A.LOCATION_ID
),
EMPLOYEE_DTLS
AS
(SELECT A.EMPLOYEE_ID,
A.FIRST_NAME,
A.LAST_NAME,
A.EMAIL,
A.PHONE_NUMBER,
A.HIRE_DATE,
A.JOB_ID,
A.SALARY,
A.DEPARTMENT_ID,
(SELECT FIRST_NAME||' '||LAST_NAME
FROM EMPLOYEES
WHERE EMPLOYEE_ID = A.MANAGER_ID
AND ROWNUM = 1
) MANAGER_NAME
FROM EMPLOYEES A
ORDER BY EMPLOYEE_ID
)
SELECT P.EMPLOYEE_ID EMPID,
P.FIRST_NAME||' '||P.LAST_NAME EMP_NAME,
P.EMAIL,
P.PHONE_NUMBER PHONE,
P.HIRE_DATE,
P.JOB_ID,
P.SALARY,
Q.DEPARTMENT_NAME DEPARTMENT,
R.STREET_ADDRESS STREET,
R.POSTAL_CODE ZIPCODE,
R.CITY,
R.STATE_PROVINCE STATE,
R.COUNTRY_NAME COUNTRY,
R.REGION_NAME REGION,
P.MANAGER_NAME MANAGER,
Q.NO_OF_EMPLOYEES NO_OF_EMP,
Q.MAX_SALARY MAX_SAL,
Q.MIN_SALARY MIN_SAL,
Q.TOTAL_SALARY DEPT_TOTAL,
Q.MAX_HIRE_DATE,
Q.MIN_HIRE_DATE
FROM EMPLOYEE_DTLS P,
DEPARTMENT_DTLS Q,
LOCATION_DTLS R
WHERE P.DEPARTMENT_ID = Q.DEPARTMENT_ID
AND Q.LOCATION_ID = R.LOCATION_ID
ORDER BY P.EMPLOYEE_ID;
In the above example, we have defined 3 query blocks named LOCATION_DTLS, DEPARTMENT_DTLS and EMPLOYEE_DTLS. Then in the main query we have used those 3 query blocks as data source to build the query.
EXAMPLE 2:
We can use one query block in other query blocks before using them in the main query.
--WITH Clause in Oracle: Example
WITH
LOCATION_DTLS
AS
(SELECT A.LOCATION_ID,
A.STREET_ADDRESS,
A.POSTAL_CODE,
A.CITY,
A.STATE_PROVINCE,
A.COUNTRY_ID,
B.COUNTRY_NAME,
C.REGION_NAME
FROM LOCATIONS A,
COUNTRIES B,
REGIONS C
WHERE A.COUNTRY_ID = B.COUNTRY_ID
AND B.REGION_ID = C.REGION_ID
),
DEPARTMENT_DTLS
AS
(SELECT A.DEPARTMENT_ID,
A.DEPARTMENT_NAME,
A.MANAGER_ID,
A.LOCATION_ID,
P.STREET_ADDRESS,
P.POSTAL_CODE,
P.CITY,
P.STATE_PROVINCE,
P.COUNTRY_NAME,
P.REGION_NAME,
COUNT(B.EMPLOYEE_ID) NO_OF_EMPLOYEES,
MAX(B.SALARY) MAX_SALARY,
MIN(B.SALARY) MIN_SALARY,
SUM(B.SALARY) TOTAL_SALARY,
MAX(B.HIRE_DATE) MAX_HIRE_DATE,
MIN(B.HIRE_DATE) MIN_HIRE_DATE
FROM DEPARTMENTS A,
EMPLOYEES B,
LOCATION_DTLS P
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
AND A.LOCATION_ID = P.LOCATION_ID
GROUP BY A.DEPARTMENT_ID,
A.DEPARTMENT_NAME,
A.MANAGER_ID,
A.LOCATION_ID,
P.STREET_ADDRESS,
P.POSTAL_CODE,
P.CITY,
P.STATE_PROVINCE,
P.COUNTRY_NAME,
P.REGION_NAME
),
EMPLOYEE_DTLS
AS
(SELECT A.EMPLOYEE_ID,
A.FIRST_NAME,
A.LAST_NAME,
A.EMAIL,
A.PHONE_NUMBER,
A.HIRE_DATE,
A.JOB_ID,
A.SALARY,
A.DEPARTMENT_ID,
(SELECT FIRST_NAME||' '||LAST_NAME
FROM EMPLOYEES
WHERE EMPLOYEE_ID = A.MANAGER_ID
AND ROWNUM = 1
) MANAGER_NAME
FROM EMPLOYEES A
ORDER BY EMPLOYEE_ID
)
SELECT P.EMPLOYEE_ID EMPID,
P.FIRST_NAME||' '||P.LAST_NAME EMP_NAME,
P.EMAIL,
P.PHONE_NUMBER PHONE,
P.HIRE_DATE,
P.JOB_ID,
P.SALARY,
Q.DEPARTMENT_NAME DEPARTMENT,
Q.STREET_ADDRESS STREET,
Q.POSTAL_CODE ZIPCODE,
Q.CITY,
Q.STATE_PROVINCE STATE,
Q.COUNTRY_NAME COUNTRY,
Q.REGION_NAME REGION,
P.MANAGER_NAME MANAGER,
Q.NO_OF_EMPLOYEES NO_OF_EMP,
Q.MAX_SALARY MAX_SAL,
Q.MIN_SALARY MIN_SAL,
Q.TOTAL_SALARY DEPT_TOTAL,
Q.MAX_HIRE_DATE,
Q.MIN_HIRE_DATE
FROM EMPLOYEE_DTLS P,
DEPARTMENT_DTLS Q
WHERE P.DEPARTMENT_ID = Q.DEPARTMENT_ID
ORDER BY P.EMPLOYEE_ID;
In the above example, LOCATION_DTLS query block is used in DEPARTMENT_DTLS block.
EXAMPLE 3:
WITH CLAUSE ENHANCEMENT IN ORACLE 12c:
In oracle 12c the declarative function of the WITH clause can be used to define a PLSQL functions and procedures.
USING FUNCTION INSIDE WITH CLAUSE:
EXAMPLE 1: SINGLE FUNCTION IN WITH CLAUSE
--WITH Clause in Oracle: Example : SINGLE FUNCTION IN WITH CLAUSE
WITH
FUNCTION FUN_GET_MAX_SAL(P_DEPTID DEPARTMENTS.DEPARTMENT_ID%TYPE)
RETURN NUMBER
IS
V_MAX_SAL EMPLOYEES.SALARY%TYPE := 0;
BEGIN
SELECT MAX(SALARY)
INTO V_MAX_SAL
FROM EMPLOYEES
WHERE DEPARTMENT_ID = P_DEPTID;
--
RETURN V_MAX_SAL;
EXCEPTION
WHEN OTHERS THEN
V_MAX_SAL := 0;
END;
--
SELECT A.EMPLOYEE_ID,
A.FIRST_NAME,
A.LAST_NAME,
A.EMAIL,
A.PHONE_NUMBER,
A.HIRE_DATE,
A.JOB_ID,
A.SALARY,
A.DEPARTMENT_ID,
(SELECT FIRST_NAME||' '||LAST_NAME
FROM EMPLOYEES
WHERE EMPLOYEE_ID = A.MANAGER_ID
AND ROWNUM = 1
) MANAGER_NAME,
FUN_GET_MAX_SAL(DEPARTMENT_ID) MAX_DEPT_SAL
FROM EMPLOYEES A
WHERE A.DEPARTMENT_ID IN (20, 30);
EXAMPLE 2: MULTIPLE FUNCTIONS IN WITH CLAUSE
--WITH Clause in Oracle: Example : MULTIPLE FUNCTIONS IN WITH CLAUSE
WITH
FUNCTION FUN_GET_MAX_SAL(P_DEPTID DEPARTMENTS.DEPARTMENT_ID%TYPE)
RETURN NUMBER
IS
V_MAX_SAL EMPLOYEES.SALARY%TYPE := 0;
BEGIN
SELECT MAX(SALARY)
INTO V_MAX_SAL
FROM EMPLOYEES
WHERE DEPARTMENT_ID = P_DEPTID;
--
RETURN V_MAX_SAL;
EXCEPTION
WHEN OTHERS THEN
V_MAX_SAL := 0;
END;
--
FUNCTION FUN_GET_AVG_SAL(P_DEPTID DEPARTMENTS.DEPARTMENT_ID%TYPE)
RETURN NUMBER
IS
V_AVG_SAL EMPLOYEES.SALARY%TYPE := 0;
BEGIN
SELECT AVG(SALARY)
INTO V_AVG_SAL
FROM EMPLOYEES
WHERE DEPARTMENT_ID = P_DEPTID;
--
RETURN V_AVG_SAL;
EXCEPTION
WHEN OTHERS THEN
V_AVG_SAL := 0;
END;
--
SELECT A.EMPLOYEE_ID,
A.FIRST_NAME,
A.LAST_NAME,
A.EMAIL,
A.PHONE_NUMBER,
A.HIRE_DATE,
A.JOB_ID,
A.SALARY,
A.DEPARTMENT_ID,
(SELECT FIRST_NAME||' '||LAST_NAME
FROM EMPLOYEES
WHERE EMPLOYEE_ID = A.MANAGER_ID
AND ROWNUM = 1
) MANAGER_NAME,
FUN_GET_MAX_SAL(DEPARTMENT_ID) MAX_DEPT_SAL,
FUN_GET_AVG_SAL(DEPARTMENT_ID) AVG_DEPT_SAL
FROM EMPLOYEES A
WHERE A.DEPARTMENT_ID IN (20, 30);
USING PROCEDURE INSIDE WITH CLAUSE:
You can only define a procedure in the declaration section of WITH clause:
EXAMPLE1:
--WITH Clause in Oracle: Example: USING PROCEDURE INSIDE WITH CLAUSE
WITH
PROCEDURE SP_GET_MAX_SAL(P_DEPTID DEPARTMENTS.DEPARTMENT_ID%TYPE)
IS
V_MAX_SAL EMPLOYEES.SALARY%TYPE := 0;
BEGIN
SELECT MAX(SALARY)
INTO V_MAX_SAL
FROM EMPLOYEES
WHERE DEPARTMENT_ID = P_DEPTID;
--
DBMS_OUTPUT.PUT_LINE('MAXIMUM SALARY OF DEPARTMENT '||P_DEPTID||' IS '||V_MAX_SAL);
EXCEPTION
WHEN OTHERS THEN
V_MAX_SAL := 0;
END;
--
SELECT A.EMPLOYEE_ID,
A.FIRST_NAME,
A.LAST_NAME,
A.EMAIL,
A.PHONE_NUMBER,
A.HIRE_DATE,
A.JOB_ID,
A.SALARY,
A.DEPARTMENT_ID,
(SELECT FIRST_NAME||' '||LAST_NAME
FROM EMPLOYEES
WHERE EMPLOYEE_ID = A.MANAGER_ID
AND ROWNUM = 1
) MANAGER_NAME
FROM EMPLOYEES A
WHERE A.DEPARTMENT_ID IN (20, 30);
But if we want to use procedure inside the WITH clause then we have to create a function as well and the procedure will be called from that function.
EXAMPLE:
--WITH Clause in Oracle: Example
WITH
PROCEDURE SP_GET_MAX_SAL(P_DEPTID IN DEPARTMENTS.DEPARTMENT_ID%TYPE,
P_MAX_SAL OUT EMPLOYEES.SALARY%TYPE)
IS
V_MAX_SAL EMPLOYEES.SALARY%TYPE := 0;
BEGIN
SELECT MAX(SALARY)
INTO V_MAX_SAL
FROM EMPLOYEES
WHERE DEPARTMENT_ID = P_DEPTID;
--
P_MAX_SAL := V_MAX_SAL;
DBMS_OUTPUT.PUT_LINE('MAXIMUM SALARY OF DEPARTMENT '||P_DEPTID||' IS '||V_MAX_SAL);
EXCEPTION
WHEN OTHERS THEN
V_MAX_SAL := 0;
END;
--
FUNCTION FUN_GET_AVG_SAL(P_DEPTID DEPARTMENTS.DEPARTMENT_ID%TYPE)
RETURN VARCHAR2
IS
V_AVG_SAL EMPLOYEES.SALARY%TYPE := 0;
V_MAX_SAL EMPLOYEES.SALARY%TYPE := 0;
BEGIN
SELECT AVG(SALARY)
INTO V_AVG_SAL
FROM EMPLOYEES
WHERE DEPARTMENT_ID = P_DEPTID;
--
SP_GET_MAX_SAL(P_DEPTID, V_MAX_SAL);
RETURN 'MAXIMUM SALARY -> '||V_MAX_SAL||' AND AVERAGE SALARY -> '||V_AVG_SAL;
EXCEPTION
WHEN OTHERS THEN
V_AVG_SAL := 0;
END;
--
SELECT A.EMPLOYEE_ID,
A.FIRST_NAME,
A.LAST_NAME,
A.EMAIL,
A.PHONE_NUMBER,
A.HIRE_DATE,
A.JOB_ID,
A.SALARY,
A.DEPARTMENT_ID,
(SELECT FIRST_NAME||' '||LAST_NAME
FROM EMPLOYEES
WHERE EMPLOYEE_ID = A.MANAGER_ID
AND ROWNUM = 1
) MANAGER_NAME,
FUN_GET_AVG_SAL(DEPARTMENT_ID) SALARY_INFO
FROM EMPLOYEES A
WHERE A.DEPARTMENT_ID IN (20, 30);
PLSQL SUPPORT FOR WITH CLAUSE:
PLSQL does not support WITH clause with function or procedure. Attempting to do that will cause compilation error.
EXAMPLE:
--WITH Clause in Oracle: Example
SET SERVEROUTPUT ON;
BEGIN
FOR I IN (WITH
FUNCTION FUN_GET_MAX_SAL(P_DEPTID DEPARTMENTS.DEPARTMENT_ID%TYPE)
RETURN NUMBER
IS
V_MAX_SAL EMPLOYEES.SALARY%TYPE := 0;
BEGIN
SELECT MAX(SALARY)
INTO V_MAX_SAL
FROM EMPLOYEES
WHERE DEPARTMENT_ID = P_DEPTID;
--
RETURN V_MAX_SAL;
EXCEPTION
WHEN OTHERS THEN
V_MAX_SAL := 0;
END;
--
SELECT A.EMPLOYEE_ID,
A.FIRST_NAME,
A.LAST_NAME,
A.EMAIL,
A.PHONE_NUMBER,
A.HIRE_DATE,
A.JOB_ID,
A.SALARY,
A.DEPARTMENT_ID,
(SELECT FIRST_NAME||' '||LAST_NAME
FROM EMPLOYEES
WHERE EMPLOYEE_ID = A.MANAGER_ID
AND ROWNUM = 1
) MANAGER_NAME,
FUN_GET_MAX_SAL(DEPARTMENT_ID) MAX_DEPT_SAL
FROM EMPLOYEES A
WHERE A.DEPARTMENT_ID IN (20, 30)
)
LOOP
DBMS_OUTPUT.PUT_LINE('MAX SALARY INFO -> '||I.MAX_DEPT_SAL);
END LOOP;
END;
/
WITH Clause in Oracle: Example Output Error report - ORA-06550: line 3, column 22: PL/SQL: ORA-00905: missing keyword ORA-06550: line 2, column 13: PL/SQL: SQL Statement ignored ORA-06550: line 6, column 51: PLS-00103: Encountered the symbol ";" when expecting one of the following: loop 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:
Dynamic sql can provide a work around solution.
EXAMPLE:
--WITH Clause in Oracle: Example
SET SERVEROUTPUT ON;
DECLARE
V_SQL VARCHAR2(32767);
V_CURSOR SYS_REFCURSOR;
TYPE T_VALUE IS TABLE OF VARCHAR2(32767) INDEX BY PLS_INTEGER;
V_VALUE T_VALUE;
BEGIN
V_SQL := 'WITH
FUNCTION FUN_GET_MAX_SAL(P_DEPTID DEPARTMENTS.DEPARTMENT_ID%TYPE)
RETURN NUMBER
IS
V_MAX_SAL EMPLOYEES.SALARY%TYPE := 0;
BEGIN
SELECT MAX(SALARY)
INTO V_MAX_SAL
FROM EMPLOYEES
WHERE DEPARTMENT_ID = P_DEPTID;
--
RETURN V_MAX_SAL;
EXCEPTION
WHEN OTHERS THEN
V_MAX_SAL := 0;
END;
--
SELECT ''EMPLOYEE_ID -> ''||A.EMPLOYEE_ID||'''||' NAME -> ''||A.FIRST_NAME||'' ''||A.LAST_NAME||'' -> DEPARTMENTID -> ''||A.DEPARTMENT_ID||'' -> MAX_DEPT_SAL -> '||'''||FUN_GET_MAX_SAL(A.DEPARTMENT_ID) MAX_DEPT_SAL
FROM EMPLOYEES A
WHERE A.DEPARTMENT_ID IN (20, 30)';
OPEN V_CURSOR FOR V_SQL;
FETCH V_CURSOR BULK COLLECT INTO V_VALUE;
CLOSE V_CURSOR;
FOR I IN 1..V_VALUE.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE('CURSOR VALUE -> '||V_VALUE(I));
END LOOP;
END;
/
RELATED TOPICS:
