PL/SQL User's Guide and Reference

Declaring Subprograms

You can declare subprograms in any PL/SQL block, subprogram, or package. However, you must declare subprograms at the end of a declarative section after all other program objects. For example, the following procedure declaration is misplaced:

DECLARE
   PROCEDURE award_bonus (...) IS  -- misplaced; must come last
   BEGIN 
      ... 
   END; 
   rating NUMBER; 
   CURSOR c1 IS SELECT * FROM emp;

Forward Declarations

PL/SQL requires that you declare an identifier before using it. Therefore, you must declare a subprogram before calling it. For example, the following declaration of procedure award_bonus is illegal because award_bonus calls procedure calc_rating, which is not yet declared when the call is made:

DECLARE
   ...
   PROCEDURE award_bonus ( ... ) IS
   BEGIN
      calc_rating( ... );  -- undeclared identifier
      ...
   END;
   PROCEDURE calc_rating ( ... ) IS 
   BEGIN 
      ... 
   END;

In this case, you can solve the problem easily by placing procedure calc_rating before procedure award_bonus. However, the easy solution does not always work. For example, suppose the procedures are mutually recursive (call each other) or you want to define them in alphabetical order.

PL/SQL solves this problem by providing a special subprogram declaration called a forward declaration. You can use forward declarations to

define subprograms in logical or alphabetical order

define mutually recursive subprograms (see "Recursion" )

group subprograms in a package

A forward declaration consists of a subprogram specification terminated by a semicolon. In the following example, the forward declaration advises PL/SQL that the body of procedure calc_rating can be found later in the block:

DECLARE
   PROCEDURE calc_rating ( ... );  -- forward declaration
   ...
   /* Define subprograms in alphabetical order. */
   PROCEDURE award_bonus ( ... ) IS
   BEGIN
      calc_rating( ... );
      ...
   END;
   PROCEDURE calc_rating ( ... ) IS 
   BEGIN 
      ... 
   END;

Although the formal parameter list appears in the forward declaration, it must also appear in the subprogram body. You can place the subprogram body anywhere after the forward declaration, but they must appear in the same program unit.

In Packages

Forward declarations also let you group logically related subprograms in a package. The subprogram specifications go in the package specification, and the subprogram bodies go in the package body, where they are invisible to applications. Thus, packages allow you to hide implementation details. An example follows:

CREATE PACKAGE emp_actions AS  -- package specification
   PROCEDURE hire_employee (emp_id INTGER, name VARCHAR2, ...);
   PROCEDURE fire_employee (emp_id INTEGER);
   PROCEDURE raise_salary (emp_id INTEGER, increase REAL);
   ...
END emp_actions;

CREATE PACKAGE BODY emp_actions AS  -- package body
   PROCEDURE hire_employee (emp_id INTGER, name VARCHAR2, ...) IS
   BEGIN
      INSERT INTO emp VALUES (empno, ename, ...);
   END hire_employee;

   PROCEDURE fire_employee (emp_id INTEGER) IS
   BEGIN
      DELETE FROM emp 
         WHERE empno = emp_id;
   END fire_employee;

   PROCEDURE raise_salary (emp_id INTEGER, increase REAL) IS
      salary REAL;
   BEGIN
      SELECT sal INTO salary FROM emp 
         WHERE empno = emp_id;
      ...
   END raise_salary;
   ...
END emp_actions;

You can define subprograms in a package body without declaring their specifications in the package specification. However, such subprograms can be called only from inside the package. For more information about packages, see Chapter 8.

Stored Subprograms

Generally, tools (such as Oracle Forms) that incorporate the PL/SQL engine can store subprograms locally for later, strictly local execution. However, to become available for general use by all tools, subprograms must be stored in an Oracle database.

To create subprograms and store them permanently in an Oracle database, you use the CREATE PROCEDURE and CREATE FUNCTION statements, which you can execute interactively from SQL*Plus or Server Manager. For example, you might create the procedure fire_employee, as follows:

CREATE PROCEDURE fire_employee (emp_id NUMBER) AS
BEGIN
   DELETE FROM emp WHERE empno = emp_id;
END;

When creating subprograms, you can use the keyword AS instead of IS in the specification for readability. For more information about creating and using stored subprograms, see Oracle7 Server Application Developer's Guide.