10.2 Functions

Some languages have a single subprogram type. A Java class has methods and a method may or may not return data. The C language is the same. The only subprogram in C is a function. A C function can return a value or modify the contents of an address value passed as an argument.

PL/SQL includes two types of subprograms: procedures and functions. Consider a package to be the encapsulation of operations on some object. That object can be a table, database pipe, host file, tables in a remote database, or many other options. Procedures take actions on the object and modify them. Functions provide a means to acquire status or state information about the object.

The package typically behaves as an API that hides an object and provides operations on the object. If the user needs to know, for example, how large the object is, a method must be available. Functions play this role. Functions frequently act as selectors on an object.

Consider the function to be the evaluation of an attribute of an object. Functions are not actors, but rather evaluations of a state. You should use nouns to name functions.

Figure 10-2 illustrates the components of a function. The key components of a function, to which you write code, are the following:

Declarative Part	This is where you declare variables , for example: local_counter NUMBER := 0; You may also have type definitions. Type definitions would be required for composite structures such as records and index-by tables. You may code procedures and functions within the declarative part. For example, you might write a local function that returns a substring with certain characteristics. You could use this local function to make the body easier to read. You can declare exceptions here. If you declare an exception within a procedure you should handle it locally. No program that calls your procedure can capture exceptions that are local to your procedure. You frequently declare a variable that is the variable to be returned. You must return something in a function. If the function returns a NUMBER, the NUMBER variable could be declared. That variable would be used in the RETURN statement.
Subprogram Body	The subprogram body contains the logical algorithm implemented with PL/SQL control constructs. PL/SQL supports loops , if-then-else structures, case statements, and declare-block structures. The body must include a RETURN statement, otherwise it will not compile.
Exception Handler	The exception handler is optional. The exception handler is similar to the try-catch model in other languages. You can code an exception handler for a specific type of error or write a general-purpose exception handler. Make sure the exception handler includes a RETURN statement.

Figure 10-2. Function.

Parameters are optional, but the RETURN part is not. The FUNCTION statement must include a RETURN and a type.

The RETURN statement, in the subprogram body, is a key component. A function compilation fails if there is no RETURN statement in the body of the code. A function that follows an execution path that ends before executing a RETURN statement causes a run-time error. A function must have a RETURN statement to compile; it must execute a RETURN during run-time. A frequent oversight is to write an exception handler and not include a RETURN clause. A function that enters an exception handler without a RETURN statement generates a run-time error.

The following example is a function that returns a DATE type. The declarative part creates the variable NEXT_DAY part. The program body assigns a value and returns that value.

 CREATE OR REPLACE FUNCTION tomorrow RETURN DATE IS     next_day DATE; BEGIN     next_day := SYSDATE + 1;     RETURN next_day; END tomorrow; 

Functions can include an expression in the RETURN statement. This often means less code in the function body. The following function performs the same logic as the preceding function. In this example, the local variable is not necessary.

 FUNCTION tomorrow RETURN DATE IS BEGIN     RETURN SYSDATE + 1; END tomorrow; 

If there are no parameters, do not use empty parentheses in the function definition. This rule applies to procedures as well. The user of the function can use the function as a single expression that evaluates to some type. The following code uses the TOMORROW function in an assignment statement. The same procedure uses the function as an argument to DBMS_OUTPUT.

 CREATE OR REPLACE PROCEDURE tomorrow_user IS     my_date DATE; BEGIN     my_date := tomorrow();     my_date := tomorrow;     dbms_output.put_line('Tomorrow is 'tomorrow); END tomorrow_user; 

Use functions to increase the readability of the code. The following uses the TOMORROW function in a loop. The code is clear and identifies when the loop stops.

 WHILE local_date_variable < tomorrow LOOP     Loop body END LOOP; 

Variables and function calls are always interchangeable provided the function returns the same type as the variable. For example, the TOMORROW function returns a DATE type. Any PL/SQL statement, in which a DATE variable is acceptable, can use the function TOMORROW.

 PROCEDURE sample IS     today DATE; BEGIN     today := tomorrow - 1;     dbms_output.put_line(tomorrow - 1); END sample;