[Chapter 15] 15.5 Functions

15.5 Functions

A function is a module that returns a value. Unlike a procedure, which is a standalone executable statement, a call to a function can only be part of an executable statement.

Because a function returns a value, it can be said to have a datatype. A function can be used in place of an expression in a PL/SQL statement having the same datatype as the function.

Appendix C, Built-In Packages , describes the built-in functions that PL/SQL provides to help you write your programs. You can also write your own functions -- numeric functions, VARCHAR2 functions, and even functions that return a PL/SQL table or record. The programmer-defined function, a powerful addition to your toolchest, will aid greatly in making your code more flexible and easier to understand.

15.5.1 Structure of a Function

The structure of a function is the same as that of a procedure, except that the function also has a RETURN clause. The general format of a function follows:

FUNCTION name [ ( parameter [, parameter ... ] ) ]
   RETURN return_datatype
IS
   [ declaration statements ]

BEGIN
   executable statements

[ EXCEPTION
   exception handler statements ]

END [ name ];

where each component is used in the following ways:

name: The name of the procedure comes directly after the keyword FUNCTION.
parameters: An optional list of parameters that you define to both pass information into the procedure and send information out of the procedure, back to the calling program.
return_datatype: The datatype of the value returned by the function. This is required in the function header and is explained in more detail in the next section.
declaration statements: The declarations of local identifiers for that function. If you do not have any declarations, then there will not be any statements between the IS and BEGIN statements.
executable statements: The statements the function executes when it is called. You must have at least one executable statement after the BEGIN and before the END or EXCEPTION keywords.
exception handler statements: The optional exception handlers for the function. If you do not explicitly handle any exceptions, then you can leave out the EXCEPTION keyword and simply terminate the execution section with the END keyword.

Figure 15.10 illustrates the PL/SQL function and its different sections. Notice that the tot_sales function does not have an exception section.

Figure 15.10: The tot_sales function

15.5.2 The RETURN Datatype

The return_datatype is the datatype of the value returned by the function. This datatype can be any datatype (and many complex structures) supported by PL/SQL, including scalars like these:

VARCHAR2
NUMBER
BINARY_INTEGER
BOOLEAN

Functions can also return complex and composite datatypes, such as:

PL/SQL table (Oracle7)
Nested table or variable array (VARRAY) (PL/SQL8)
PL/SQL record
Object type (PL/SQL8)
Large objects (LOBs) such as BFILEs and CLOBs (PL/SQL8)

The datatype of a function cannot be either of the following:

Named exception: Once you declare an exception, you can reference that exception only in a RAISE statement and in the exception handler itself.
Cursor name: You cannot return a cursor from a function. (Note that PL/SQL Release 2.2 and beyond provides a REF CURSOR TYPE declaration that will allow you to return a cursor and even declare a parameter as a cursor.)

15.5.3 The END Label

You can append the name of the function directly after the END keyword when you complete your function, as shown below:

FUNCTION tot_sales (company_in IN INTEGER) RETURN NUMBER
IS
BEGIN
   ...
END tot_sales;

This name serves as a label that explicitly links up the end of the program with its beginning. You should as a matter of habit use an END label. It is especially important to do so when you have a function that spans more than a single page, or is one in a series of functions and procedures in a package body.

15.5.4 Calling a Function

A function is called as part of an executable PL/SQL statement, wherever an expression can be used. The following examples illustrate the different ways that the tot_sales function demonstrated in Figure 15.10 might be called:

Call tot_sales to assign a value to a local PL/SQL variable:
```
sales_for_1995 := tot_sales (1504, 'C');
```

Use a call to tot_sales to set a default value for a variable:

DECLARE
   sales_for_1995 NUMBER DEFAULT tot_sales (1504, 'C');
BEGIN

Use tot_sales directly in an expression:

IF tot_sales (275, 'O') > 10000
THEN
...

15.5.4.1 Functions without parameters

If a function has no parameters, then the function call must be written without parentheses, as the line below illustrates:

IF tot_sales THEN ...

Notice that you cannot tell just by looking at the above line of code whether tot_sales is a function or a variable. You would, in fact, have to check the declaration section of your PL/SQL block if you really needed to know. And that's the whole point. A function returns a value, as does a variable. The function just happens to execute some code to come up with that value, whereas a variable has that value as its very attribute, available for immediate return.

15.5.5 Function Header

The portion of the function definition that comes before the IS keyword is called the function header. The header provides all the information a programmer needs to call that function, namely:

The function name
The parameter list, if any
The RETURN datatype

A programmer does not need to know about the inside of the function in order to be able to call it properly from another program.

The header for the tot_sales function discussed above is:

FUNCTION tot_sales
   (company_id_in IN company.company_id%TYPE,
    status_in IN order.status_code%TYPE := NULL)
RETURN NUMBER

It consists of the module type, the name, a list of two parameters, and a RETURN datatype of NUMBER. This means that the PL/SQL statement containing a call to tot_sales must be able to use a numeric value.

15.5.6 Function Body

The body of the function is the code required to implement the function. It consists of the declaration, execution, and exception sections of the function. Everything after the IS keyword in the function makes up that function's body.

Once again, the declaration and exception sections are optional. If you have no exception handlers, you will simply leave off the EXCEPTION keyword and enter the END statement to terminate the function.

If you do not have any declarations, the BEGIN statement simply follows immediately after the IS keyword (see the does_nothing function below for an example of this structure).

15.5.7 A Tiny Function

You must supply at least one executable statement in a function. Here is my candidate for the Boolean function with the smallest possible body in PL/SQL:

FUNCTION does_nothing RETURN BOOLEAN IS
BEGIN
   RETURN TRUE;
END;

You would call does_nothing as follows:

IF does_nothing
THEN
   NULL;
END IF;

15.5.8 The RETURN Statement

A function must have at least one RETURN statement in its execution section of statements. It can have more than one RETURN, but only one of those statements is executed each time the function is called. The RETURN statement that is executed by the function determines the value that is returned by that function. When a RETURN statement is processed, the function terminates immediately and returns control to the calling PL/SQL block.

The RETURN clause in the header of the function is different from the RETURN statement in the execution section of the body of the function. While the RETURN clause indicates the datatype of the return or result value of the function, the RETURN statement specifies the actual value that is returned. You have to specify the RETURN datatype in the header, but then also include at least one RETURN statement in the function.

15.5.8.1 Multiple RETURNs

In the tot_sales function shown in Figure 15.10 , I used two different RETURN statements to handle different situations in the function, as follows:

IF sales_cur%NOTFOUND
THEN
   CLOSE sales_cur;
   RETURN NULL;
ELSE
   CLOSE sales_cur;
   RETURN return_value;
END IF;

In other words, if I could not obtain sales information from the cursor, I will return NULL (which is different from zero). If I do get a value from the cursor, I return it to the calling program. In both of these cases the RETURN statement passes back a value; in one case the NULL value, and in the other the return_value variable.

15.5.8.2 RETURN any valid expression

The RETURN statement can accept any expression for evaluation and return. This expression can be composed of calls to other functions, complex calculations, and even data conversions. All of the following usages of RETURN are valid:

RETURN 'buy me lunch';
RETURN POWER (max_salary, 5);
RETURN (100 - pct_of_total_salary (employee_id));
RETURN TO_DATE ('01' || earliest_month || initial_year, 'DDMMYY');

An expression in the RETURN statement is evaluated when the RETURN is executed. When control is passed back to the calling form, the result of the evaluated expression is passed along, too.

15.5.8.3 No RETURN is executed

What happens when you include one or any number of RETURN statements in your functions but none of them is executed? PL/SQL raises an error.

The following function:

FUNCTION company_type (type_code_in IN VARCHAR2)
   RETURN VARCHAR2 IS
BEGIN
   IF type_code_in = 'S'
   THEN
      RETURN 'SUBSIDIARY';
   ELSIF type_code_in = 'P'
   THEN
      RETURN 'PARTNER';
   END IF;
END;

is then called in this executable statement:

type_description := company_type ('R');

Because the RETURN statements are executed only when the type code is `S' or `P', the function never hits a RETURN. It does, however, execute to the end of the function and then raise an error, as follows:

ORA-6503: PL/SQL: Function returned without value

You can avoid this kind of problem (which you may never encounter in testing since you always pass a sensible value to the function) by restructuring your use of the RETURN statement.

15.5.8.4 RETURN as last executable statement

Generally, the best way to make sure that your function always returns a value is to make the last executable statement in the function your RETURN statement. Declare a variable named return_value, which clearly indicates that it will contain the return value for the function, write all the code to come up with that value, and then at the very end of the function RETURN the return_value:

FUNCTION do_it_all (parameter_list) RETURN NUMBER
IS
   return_value NUMBER;
BEGIN

   ... lots of executable statements ...

   RETURN return_value;
END;

The company_type function, for example, can be converted easily to this structure:

FUNCTION company_type (type_code_in IN VARCHAR2)
   RETURN VARCHAR2
IS
   return_value VARCHAR2 (25) := NULL;
BEGIN
   IF type_code_in = 'S'
   THEN
      return_value := 'SUBSIDIARY';

   ELSIF type_code_in = 'P'
   THEN
      return_value := 'PARTNER';
   END IF;

   RETURN return_value;
END;

Notice that, because I provided the return_value variable with a default value of NULL, I didn't have to code an ELSE clause in the IF statement to explicitly make that assignment (though doing so would probably make the code more readable). If the type_code_in does not match any of the values in the IF statement, there is no problem because each IF and ELSIF no longer performs its own RETURN. Instead, they just assign a value and then leave the RETURNing to the little RETURN section at the end of the function.

15.5.8.5 RETURN statement in a procedure

Believe it or not, RETURN statements can also be used in procedures. The procedure version of the RETURN does not take an expression; it cannot, therefore, pass a value back to the calling program unit. The RETURN simply halts execution of the procedure and returns control to the calling code.

You do not (should not, in any case) see this usage of RETURN very often, and for good reason. Use of the RETURN in a procedure usually leads to very unstructured code that is hard to understand and maintain. Avoid using both RETURN and GOTO to bypass proper control structures and process flow in your program units.