Functions
Similar to a stored procedure, a function is a named derivative of a PL/SQL block that is physically stored within the Oracle database schema.
The key features of stored functions are as follows:
- A function can accept parameters in all three modes (
IN,OUT, andINOUT) and mandatorily returns a value. - Functions can be called in SQL statements (
SELECTand DMLs). Such functions must accept onlyINparameters of valid SQL types. Alternatively, a function can also be invoked fromSELECTstatements if the function body obeys the database purity rules. - If the function is called from an SQL statement, its return type should be a valid SQL data type. If the function is invoked from PL/SQL, the return type should be a valid PL/SQL type.
Note
Starting from Oracle Database 12c, PL/SQL—only data types can cross the PL/SQL to SQL interface. A PL/SQL anonymous block can invoke a PL/SQL subprogram with parameters of
BOOLEANor a packaged collection type.
The syntax for a function is as follows:
CREATE [OR REPLACE] FUNCTION [Function Name] [Parameter List] RETURN [Data type] [AUTHID DEFINER | CURRENT_USER] [DETERMINISTIC | PARALLEL_ENABLED | PIPELINED] [RESULT_CACHE [RELIES_ON (table name)]] IS [Declaration Statements] BEGIN [Executable Statements] RETURN [Value] EXCEPTION [Exception handlers] END [Function Name];
Let us create a standalone function, F_GET_DOUBLE, which accepts a numeric parameter and returns its double:
/*Create the function F_GET_DOUBLE*/ CREATE OR REPLACE FUNCTION F_GET_DOUBLE (P_NUM NUMBER) RETURN NUMBER /*Specify the return data type*/ IS /*Declare the local variable*/ L_NUM NUMBER; BEGIN /*Calculate the double of the given number*/ L_NUM := P_NUM * 2; /*Return the calculated value*/ RETURN L_NUM; END; / Function created.
Functions – execution methods
Functions can either be called from a SQL*Plus environment or invoked from a PL/SQL program as a procedural statement.
The function F_GET_DOUBLE can be executed in the SQL* Plus command prompt as follows. As the function returns an output, you must declare a session variable and capture the function result in the variable.
/*Enable the SERVEROUTPUT parameter to print the results in the environment*/ SET SERVEROUTPUT ON /*Declare a session variable M_NUM to hold the function output*/ VARIABLE M_NUM NUMBER; /*Function is executed and output is assigned to the session variable*/ EXECUTE :M_NUM := F_GET_DOUBLE(10); PL/SQL procedure successfully completed. /*Print the session variable M_NUM*/ PRINT M_NUM M_NUM ---------- 20
The F_GET_DOUBLE function can be called from an anonymous block or a standalone subprogram.
/*Enable the SERVEROUTPUT parameter to print the results in the environment*/
SET SERVEROUTPUT ON
DECLARE
M_NUM NUMBER;
BEGIN
M_NUM := F_GET_DOUBLE(10);
DBMS_OUTPUT.PUT_LINE('Doubled the input value as : '||M_NUM);
END;
/
Doubled the input value as : 20
PL/SQL procedure successfully completed.Restrictions on calling functions from SQL expressions
Unlike procedures, a stored function can be called from a SELECT statement, provided it does not violate the database purity levels. The rules are as follows:
- A function called from a
SELECTstatement cannot contain DML statements - A function called from an
UPDATEorDELETEstatement on a table cannot query (SELECT) or perform transactions (DMLs) on the same table - A function called from an SQL expression cannot contain TCL (
COMMITorROLLBACK) commands or DDL (CREATEorALTER) commands
The F_GET_DOUBLE function can easily be embedded within a SELECT statement as it respects all the preceding rules:
/*Invoke the function F_GET_DOUBLE from SELECT statement*/
SQL> SELECT F_GET_DOUBLE(10) FROM DUAL;
F_GET_DOUBLE(10)
----------------
20Note
In the Oracle Database, DUAL is a table owned by the SYS user, which has a single row and a single column, DUMMY, of VARCHAR2 (1) type. It was first designed by Charles Weiss while working with internal views to duplicate a row. The DUAL table is created by default during the creation of the data dictionary with a single row whose value is X. All database users, other than SYS, use its public synonym to select the value of pseudo columns such as USER, SYSDATE, NEXTVAL, or CURRVAL. Oracle 10g considerably improved the performance implications of the DUAL table through a fast dual-access mechanism.
