1. Overview of Stored Functions A Function is a named PL/SQL subprogram, which when called from SQL or PL/SQL statement, mandatorily returns one and only one value. Functions are used to implement computation based logic. They are stored as schema objects in database. They promote reusability of logic and modularity. 2. Syntax Code (Text): CREATE [OR REPLACE] FUNCTION [NAME][PARAMETER LIST] RETURN [DATA TYPE] [IS | AS] … BEGIN STATEMENT 1; STATEMENT 2; … RETURN [VALUE] STATEMENT N; END; REPLACE preserves the object privileges, during change in definition. Parameters are passed into Function in any of three modes IN, OUT, IN OUT. RETURN clause specifies the data type of the value returned by the function. A Function may contain multiple RETURN statements in the body, but must logically return a single value 3. Procedures vs Functions [TABLE]Procedure Function Part of PL/SQL statement Part of an expression No RETURN in header RETURN keyword specified in header with the data type May or may not return values to host Must return single value to the host Executable section may or may not contain RETURN statement Executable section compulsorily contains one logical RETURN statement Objective is to store series of logical actions for serial execution Objective is to perform computational tasks and return result to the calling environment[/TABLE] Stored Function: Example Code (SQL): CREATE OR REPLACE FUNCTION F_AVG_SALARY RETURN NUMBER IS L_AVG NUMBER; BEGIN SELECT AVG(SALARY) INTO L_AVG FROM EMPLOYEE; RETURN L_AVG; END; 4. Function Executing Methods 1. Using DBMS_OUTPUT.PUT_LINE Code (SQL): SQL> EXECUTE DBMS_OUTPUT.PUT_LINE(F_AVG_SALARY); 2. Capturing function output in a variable Code (SQL): SQL> VARIABLE G_AVG NUMBER; SQL> EXEC :G_AVG := F_AVG_SALARY; PL/SQL PROCEDURE successfully completed. SQL> PRINT :G_AVG 4500 3. Calling within a PL/SQL block Code (SQL): DECLARE L_AVG NUMBER; BEGIN L_AVG := F_AVG_SALARY; DBMS_OUTPUT.PUT_LINE('Avg Salary:'||L_AVG); END; 4. Calling from SQL statement Code (SQL): SQL> SELECT F_AVG_SALARY FROM DUAL; 4500 5. Calling User-Defined Functions in SQL Statements A user defined function can be called from WHERE, HAVING, ORDER BY and GROUP BY clause and in hierarchical query clauses i.e. START WITH and CONNECT BY clauses in a SELECT statement. Restrictions when calling functions from SQL Expressions: • Stored function must be in valid state • All parameter must be of IN mode and should be of SQL supported data type and not a BOOLEAN or COLLECTION type • Function must return value of SQL supported data type Below SELECT query lists the employee details whose salary is same as the average salary of the company. Code (SQL): SELECT EMPLOYEE_ID, ENAME, DEPARTMENT_ID, JOB_ID FROM EMPLOYEE WHERE SAL = F_AVG_SALARY; 6. Calling functions from SQL A function called from a SQL must ensure the purity of database state. Below are the database states, which must be respected to ensure the purity level. • A function cannot make changes in tables, if called from SELECT query • A function cannot query a table, if called from INSERT, UPDATE or DELETE Example, Code (SQL): CREATE OR REPLACE FUNCTION F_CALC_SAL(P_RAISE_SAL NUMBER) RETURN NUMBER IS L_SAL NUMBER; BEGIN SELECT SALARY + P_RAISE_SAL INTO L_SAL FROM EMPLOYEE WHERE EMPLOYEE_ID = 170; RETURN (L_SAL); END; Below UPDATE statement calls above function F_CALC_SAL to update the incremented salary. It violates the second point as it queries EMPLOYEE table which is getting updated in below statement. Code (SQL): UPDATE EMPLOYEE SET SALARY = F_CALC_SAL(100) WHERE EMPLOYEE_ID = 170; UPDATE EMPLOYEE * ERROR at line 1: ORA-04091: TABLE SCOTT.EMPLOYEE IS mutating, TRIGGER/FUNCTION may NOT see it ORA-06512: at "SCOTT.F_CALC_SAL", line 5 7. Removing Functions using the DROP statement A function can be removed from the schema using DROP command. Syntax: Code (Text): DROP FUNCTION [function name] Example, Code (SQL): SQL> DROP FUNCTION F_AVG_SALARY; FUNCTION dropped.