Forward Declarations

PL/SQL is rather fussy about its requirement that you declare elements before using them in your code. Otherwise, how can PL/SQL be sure that the way you are using the construct is appropriate? Because modules can call other modules, however, you may encounter situations where it is completely impossible to define all modules before any references to those modules are made. What if program A calls program B and program B calls program A? PL/SQL supports recursion, including mutual recursion, in which two or more programs directly or indirectly call each other.

If you find yourself committed to mutual recursion, you will be very glad to hear that PL/SQL supports the forward declaration of local modules, which means that modules are declared in advance of the actual definition of that program. This declaration makes that program available to be called by other programs even before the program definition.

Remember that both procedures and functions have a header and a body. A forward declaration consists simply of the program header followed by a semicolon (;). This construction is called the module header. This header, which must include the parameter list (and a RETURN clause if it's a function), is all the information PL/SQL needs about a module in order to declare it and resolve any references to it.

The following example illustrates the technique of forward declaration. I define two mutually recursive functions within a procedure. Consequently, I have to declare just the header of my second function, total_cost, before the full declaration of net_profit:

PROCEDURE perform_calcs (year_in IN INTEGER)IS /* Header only for total_cost function. */ FUNCTION total_cost (...) RETURN NUMBER; /* The net_profit function uses total_cost. */ FUNCTION net_profit (...) RETURN NUMBER IS BEGIN RETURN tot_sales (...) - total_cost (. . .); END; /* The total_cost function uses net_profit. */ FUNCTION total_cost (...) RETURN NUMBER IS BEGIN IF <condition based on parameters> THEN RETURN net_profit (...) * .10; ELSE RETURN <parameter value>; END IF; END;BEGIN . . .END;

Here are some rules to remember concerning forward declarations:

· You cannot make forward declarations of a variable or cursor. This technique works only with modules (procedures and functions).

· The definition for a forwardly declared program must be contained in the declaration section of the same PL/SQL block (anonymous block, procedure, function, or package body) in which you code the forward declaration.

In some situations, forward declarations are absolutely required; in most situations, they just help make your code more readable and presentable. As with every other advanced or unusual feature of the PL/SQL language, use forward declarations only when you really need the functionality. Otherwise, the declarations simply add to the clutter of your program, which is the last thing you want.

16.8 Advanced Topics

The following sections are most appropriate for experienced PL/SQL programmers. Here, I'll touch on a number of advanced modularization topics, including calling functions in SQL, using table functions, and using deterministic functions.