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, and IN OUT) and mandatorily returns a value.

• Functions can be called in SQL statements (SELECT and DMLs). Such functions must accept only IN parameters of valid SQL types. Alternatively, a function can also be invoked from SELECT statements 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 BOOLEAN or 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 SELECT statement cannot contain DML statements

• A function called from an UPDATE or DELETE statement on a table cannot query (SELECT) or perform transactions (DMLs) on the same table

• A function called from an SQL expression cannot contain TCL (COMMIT or ROLLBACK) commands or DDL (CREATE or ALTER) 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)
----------------
              20

Note

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.