WITH FUNCTION (Oracle 12c+)

WITH FUNCTION (Oracle 12c+) is a SQL statement in the Oracle Specific category. Oracle 12c+: defines a PL/SQL function inline within a SQL statement (no CREATE FUNCTION needed). The syntax is WITH FUNCTION func_name (params) RETURN type IS ... BEGIN ... END; SELECT func_name(...) FROM DUAL;. It returns inline function result. A typical example: WITH FUNCTION double_salary(p_sal NUMBER) RETURN NUMBER IS BEGIN RETURN p_sal * 2; END; SELECT name, salary, double_salary(salary) AS doubled FROM employees WHERE double_salary(salary) > 100000; -- Inline function, valid only for this query A close relative is SEQUENCE (Oracle), which oracle: generates sequential numbers. Independent of tables. Accessed via NEXTVAL and CURRVAL. A close relative is ROWNUM / FETCH FIRST, which oracle: ROWNUM assigns sequential numbers to rows. Used for limiting results (pre-12c). A close relative is SYSDATE / CURRENT_DATE, which oracle: returns the current date and time of the database server.