Understanding PL/SQL Functions

A function is a subprogram that computes a value. Functions and procedures are structured alike, except that functions have a RETURN clause. You write (local) functions using the syntax:

[CREATE  [OR REPLACE ] ] 

FUNCTION function_name [ ( parameter [ , parameter ]... ) ] RETURN 
datatype 


   [ AUTHID { DEFINER  | CURRENT_USER } ] 

   [ PARALLEL_ENABLE 

    [ { [CLUSTER parameter BY (column_name [, column_name ]... ) ] | 

     [ORDER parameter BY (column_name [ , column_name ]... ) ] } ] 

     [ ( PARTITION parameter BY

       { [ {RANGE | HASH } (column_name [, column_name]...)] | ANY } 
) ] 


   ] 

   [DETERMINISTIC]    [ PIPELINED  [ USING implementation_type ] ] 

   [ AGGREGATE  [UPDATE VALUE]  [WITH EXTERNAL CONTEXT] 

USING  implementation_type ]  {IS | AS} 

   [ PRAGMA AUTONOMOUS_TRANSACTION; ] 

   [ local declarations ] 

BEGIN 

   executable statements 

[ EXCEPTION 

   exception handlers ] 

END [ name ]; 

The CREATE clause lets you create standalone functions, which are stored in an Oracle database. You can execute the CREATE FUNCTION statement interactively from SQL*Plus or from a program using native dynamic SQL.

The AUTHID clause determines whether a stored function executes with the privileges of its owner (the default) or current user and whether its unqualified references to schema objects are resolved in the schema of the owner or current user. You can override the default behavior by specifying CURRENT_USER.

The PARALLEL_ENABLE option declares that a stored function can be used safely in the slave sessions of parallel DML evaluations. The state of a main (logon) session is never shared with slave sessions. Each slave session has its own state, which is initialized when the session begins. The function result should not depend on the state of session (static) variables. Otherwise, results might vary across sessions.

The hint DETERMINISTIC helps the optimizer avoid redundant function calls. If a stored function was called previously with the same arguments, the optimizer can elect to use the previous result. The function result should not depend on the state of session variables or schema objects. Otherwise, results might vary across calls. Only DETERMINISTIC functions can be called from a function-based index or a materialized view that has query-rewrite enabled. For more information, see Oracle9i SQL Reference.

The pragma AUTONOMOUS_TRANSACTION instructs the PL/SQL compiler to mark a function as autonomous (independent). Autonomous transactions let you suspend the main transaction, do SQL operations, commit or roll back those operations, then resume the main transaction.

You cannot constrain (with NOT NULL for example) the datatype of a parameter or a function return value. However, you can use a workaround to size-constrain them indirectly. See "Understanding PL/SQL Procedures".

Like a procedure, a function has two parts: the spec and the body. The function spec begins with the keyword FUNCTION and ends with the RETURN clause, which specifies the datatype of the return value. Parameter declarations are optional. Functions that take no parameters are written without parentheses.

The function body begins with the keyword IS (or AS) and ends with the keyword END followed by an optional function name. The function body has three parts: a declarative part, an executable part, and an optional exception-handling part.

The declarative part contains local declarations, which are placed between the keywords IS and BEGIN. The keyword DECLARE is not used. The executable part contains statements, which are placed between the keywords BEGIN and EXCEPTION (or END). One or more RETURN statements must appear in the executable part of a function. The exception-handling part contains exception handlers, which are placed between the keywords EXCEPTION and END.

Consider the function sal_ok, which determines if a salary is out of range:

FUNCTION sal_ok (salary REAL, title VARCHAR2) RETURN BOOLEAN IS

   min_sal REAL;

   max_sal REAL;

BEGIN

   SELECT losal, hisal INTO min_sal, max_sal FROM sals

      WHERE job = title;

   RETURN (salary >= min_sal) AND (salary <= max_sal);

END sal_ok;

When called, this function accepts an employee salary and job title. It uses the job title to select range limits from the sals database table. The function identifier, sal_ok, is set to a Boolean value by theRETURN statement. If the salary is out of range, sal_ok is set to FALSE; otherwise, sal_ok is set to TRUE.

A function is called as part of an expression, as the example below shows. The function identifier sal_ok acts like a variable whose value depends on the parameters passed to it.

IF sal_ok(new_sal, new_title) THEN ...