Oracle 12C With Clause Enhancements

Functions can be defined in the "WITH" clause and can be used in select statement from 12.1 onwards.

       

WITH
    FUNCTION fn_cube (p_no NUMBER) RETURN NUMBER
        AS
    BEGIN
        RETURN power(p_no,3);
    END;

SELECT
    100 AS num,
    fn_cube(10)
FROM
    dual;
/

       
 

Another Example of "WITH" clause function

       

WITH
    FUNCTION avg_sal (p_deptno NUMBER) RETURN NUMBER AS
        v_avg_sal   NUMBER;
    BEGIN
        SELECT
            AVG(sal)
        INTO v_avg_sal
        FROM
            emp
        WHERE
            deptno = p_deptno;

        RETURN v_avg_sal;
    END avg_sal;

SELECT
    deptno,
    dname,
    loc,
    round(avg_sal(deptno) ) avg_sal -- Calling the function
FROM
    dept;
/

       
 

No DMLs are allowed in function while using in select statements. However DMLs can be used with autonomous_transaction functions.

       

CREATE TABLE temp_1 (
    col1   NUMBER
);

WITH
    FUNCTION avg_sal (p_deptno NUMBER) RETURN NUMBER AS
        PRAGMA autonomous_transaction;
        v_avg_sal   NUMBER;
    BEGIN
        SELECT
            AVG(sal)
        INTO v_avg_sal
        FROM
            emp
        WHERE
            deptno = p_deptno;

        INSERT INTO temp_1 VALUES ( 1 );

        COMMIT;
        RETURN v_avg_sal;
    END avg_sal;

SELECT
    deptno,
    dname,
    loc,
    round(avg_sal(deptno) ) avg_sal -- Calling the function
FROM
    dept;
/

       
 

Example of using procedure and function in WITH clause, however procedures cannot be called directly in select statements. Procedure can be accessed via the calling functions as show below.

       

WITH
    PROCEDURE pr_cube (p_no IN OUT NUMBER)
        AS
    BEGIN
        p_no := power(p_no,3);
    END;

    FUNCTION fn_cube (p_no NUMBER) RETURN NUMBER AS
        lv_num   NUMBER := p_no;
    BEGIN
        pr_cube(lv_num);
        RETURN lv_num;
    END;

SELECT
    100 AS num,
    fn_cube(10)
FROM
    dual;
/

       
 

       

WITH
    PROCEDURE pr_cube (p_no IN OUT NUMBER) AS
        PRAGMA autonomous_transaction;
    BEGIN
        p_no := power(p_no,3);
        INSERT INTO temp_1 VALUES ( 1 );

        COMMIT;
    END;

    FUNCTION fn_cube (p_no NUMBER) RETURN NUMBER AS
        lv_num   NUMBER := p_no;
    BEGIN
        pr_cube(lv_num);
        RETURN lv_num;
    END;

SELECT
    100 AS num,
    fn_cube(10)
FROM
    dual;
/