With Function Introduced with Oracle 12C onwards.

With Functions

With Function Introduced with Oracle 12C onwards. it gives you the flexibility to write PL/SQL inside a SQL. We need approval from the DBA/Client/DB Owner/Product owner to write a function.

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 want to avoid joining this table, suppose the table has millions of records, why put an extra burden on the database. I want to check in table hr.countries only for those records that satisfy the condition.

 

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.