PL/SQL - Forward Declarations

1 . What are the Forward Declarations?

PL/SQL is rather fussy about its requirement that you declare a variable, cursor, or module before you use it in your code. Otherwise, how can PL/SQL be sure that the way you are using the object is appropriate? Since modules can call other modules, however, you can 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 mutual recursion, where two or more programs directly or indirectly call each other.

If you do find yourself committed to mutual recursion, you will be very glad to hear that PL/SQL supports the forward declaration of local modules, which declares a module 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 RETURN clause if a function) is all the information PL/SQL needs about a module in order to declare it and resolve any references to that module; a module should, after all, be a little black box.

The following example will illustrate the technique. 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 net_profit (. . .) < 0

      THEN

         RETURN 0;

      ELSE

         RETURN . . .;

      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) in which you code the forward declaration.

In some situations, you absolutely require forward declarations; 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.