Functions

Like a procedure, a function is also a derivative of a PL/SQL block structure which is physically stored within a database. Unlike procedures, they are the "workforce" in PL/SQL and meant for calculative and computational activities in the applications.

The characteristics of functions are as follows:

• Functions can be called from 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.

• Functions can accept parameters in all three modes (IN, OUT, and IN OUT) and mandatorily return a value. The type of the return value must be a valid SQL data type (not be of BOOLEAN, RECORD, TABLE, or any other PL/SQL data 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 | PIPELINES]
[RESULT_CACHE [RELIES_ON (table name)]]
IS
  [Declaration Statements]
BEGIN
 [Executable Statements] 
  RETURN [Value]
EXCEPTION
 [Exception handlers]
END [Function Name];

The standalone function, F_GET_DOUBLE, accepts a single argument 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.

Function—execution methods

As a common feature shared among the stored subprograms, functions can be invoked from a SQL*Plus environment and called from a PL/SQL as a procedural statement.

The following code snippet demonstrates the execution of a function from a SQL*Plus environment and its return value have been captured in a session bind variable:

/*Enable the SERVEROUTPUT parameter to print the results in the environment*/
SQL> SET SERVEROUTPUT ON

/*Declare a session variable M_NUM to hold the function output*/
SQL> VARIABLE M_NUM NUMBER;

/*Function is executed and output is assigned to the session variable*/
SQL> EXEC :M_NUM := F_GET_DOUBLE(10);

PL/SQL procedure successfully completed.

/*Print the session variable M_NUM*/
SQL> PRINT M_NUM

M_NUM
----------
20

Now, we will see the function execution from an anonymous PL/SQL block as a procedural statement:

/*Enable the SERVEROUTPUT parameter to print the results in the environment*/
SQL> SET SERVEROUTPUT ON

SQL>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 SELECT statements; provided it must not violate the database purity levels. These rules are as follows:

• A function called from a SELECT statement cannot contain DML statements

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

• A function called from SQL expressions cannot contain the TCL (COMMIT or ROLLBACK) command or the DDL (CREATE or ALTER) command

Besides these rules, a standalone user-defined function must qualify the following conditions:

• The parameters to the stored function, if any, should be passed in "pass by reference" mode that is, IN parameter only. The data type of the parameter must be a valid SQL data type. Also, the parameters must follow positional notation in the list.

• The return type of the function must be a valid SQL data type.

The F_GET_DOUBLE function can easily be embedded within a SELECT statement as it perfectly 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

In Oracle, DUAL is a table owned by the SYS user, which has 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 data dictionaries with a single row, whose value is X. The users other than SYS, use its public synonym, to select the value of pseudo columns, such as USER, SYSDATE, NEXTVAL, or CURRVAL. Oracle 10g has considerably improved the performance implications of the DUAL table through a "fast dual" access mechanism.