16.2.3 The Procedure Body

The body of the procedure is the code required to implement that procedure, and consists of the declaration, execution, and exception sections of the function. Everything after the IS keyword in the procedure makes up that procedure's body. The exception and declaration sections are optional. If you have no exception handlers, leave off the EXCEPTION keyword and simply enter the END statement to terminate the procedure. If you have no declarations, the BEGIN statement simply follows immediately after the IS keyword.

You must supply at least one executable statement in a procedure. That is generally not a problem; instead, watch out for execution sections that become extremely long and hard to manage. You should work hard to keep the execution section compact and readable. See later sections in this chapter for more specific guidance on this topic, expecially Section 16.5.1.2.

16.2.4 The end Descriptor

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

PROCEDURE display_stores (region_in IN VARCHAR2) IS

BEGIN

...

END display_stores;

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

16.2.5 The return Statement

The RETURN statement is generally associated with a function, as it is required to RETURN a value from a function (or else raise an exception). Interestingly, PL/SQL also allows you to use a RETURN statement in a procedure. The procedure version of the RETURN does not take an expression; it therefore cannot 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 see this usage of RETURN very often, and for good reason. Use of the RETURN in a procedure usually leads to very unstructured code, because there would then be at least two paths out of the procedure, making execution flow hard to understand and maintain. Avoid using both RETURN and GOTO to bypass proper control structures and process flow in your program units.

16.3 Functions

A function is a module that returns a value. Unlike a procedure call, which is a standalone executable statement, a call to a function can exist only as part of an executable statement, such as an element in an expression or the value assigned as the default in a declaration of a variable.

Because a function returns a value, it is 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.

Functions are particularly important constructs for building modular code. For example, every single business rule or formula in your application should be placed inside a function. Every single-row query should also be defined within a function, so that it can be easily and reliably reused.

Some programmers prefer to rely less on functions, and more on procedures that return status information through the parameter list. If you are one of these programmers, make sure that your business rules, formulas, and single-row queries are tucked away into your procedures!

An application short on function definition and usage is likely to be difficult to maintain and enhance over time.