Oracle With Clouse, With Function Details with examples and interviews related to all topics.

With Function

With Function Introduced with Oracle 12C onwards. it gives you the flexibility that you can write PL/SQL inside a SQL. For writing a function, we need approval from the DBA/Client/DB Owner/Product owner. We can write a function/procedure included in SQL itself and the scope of that function/procedure is limited to SQL only.

SELECT E.FIRST_NAME, E.LAST_NAME, E.SALARY

FROM HR.EMPLOYEES E, HR.DEPARTMENTS D, HR.LOCATIONS L

WHERE E.DEPARTMENT_ID = 90

AND E.DEPARTMENT_ID = D.DEPARTMENT_ID

AND D.LOCATION_ID = L.LOCATION_ID;

See this above query. Consider a simple query, which brings all employee names and salary and I also want to print in which country these employees belong to. Country_name will be in hr.countries table. I do not want to join this table, suppose the table has millions of records, why put extra burden on the database. I want to check only those records that satisfy the condition in Table HR countries.

WITH
FUNCTION GET_COUNTRY_NAME1 (P_COUNTRY_ID VARCHAR2)
RETURN VARCHAR2
AS
L_COUNTRY_NAME HR.COUNTRIES.COUNTRY_NAME%TYPE;
BEGIN
SELECT COUNTRY_NAME
INTO L_COUNTRY_NAME
FROM HR.COUNTRIES
WHERE COUNTRY_ID = P_COUNTRY_ID;
RETURN L_COUNTRY_NAME;
END
GET_COUNTRY_NAME1;
N1 AS ( SELECT E.FIRST_NAME, E.LAST_NAME, E.SALARY, L.COUNTRY_ID FROM HR.EMPLOYEES E, HR.DEPARTMENTS D, HR.LOCATIONS L
WHERE E.DEPARTMENT_ID = 90
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.LOCATION_ID = L.LOCATION_ID )
SELECT N1.*, GET_COUNTRY_NAME1(N1.COUNTRY_ID) COUNTRY_NAME FROM N1;

In the above script, we create a function that takes input as a country_id and returns the country_name. Benefit 1: This function is not stored in the database, so no issue with manageability Benefit 2: This is good for performance because table hr.countries will only be called for final records to return country_name.

Conitinue...