PL/SQL User's Guide and Reference Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index



Go to previous file in sequence Go to next file in sequence

Subprograms


Civilization advances by extending the number of important operations that we can perform without thinking about them.

Alfred North Whitehead

This chapter shows you how to use subprograms, which let you name and encapsulate a sequence of statements. Subprograms aid application development by isolating operations. They are like building blocks, which you can use to construct modular, maintainable applications.


What Are Subprograms?

Subprograms are named PL/SQL blocks that can take parameters and be invoked. PL/SQL has two types of subprograms called procedures and functions. Generally, you use a procedure to perform an action and a function to compute a value.

Like unnamed or anonymous PL/SQL blocks, subprograms have a declarative part, an executable part, and an optional exception-handling part. The declarative part contains declarations of types, cursors, constants, variables, exceptions, and nested subprograms. These objects are local and cease to exist when you exit the subprogram. The executable part contains statements that assign values, control execution, and manipulate Oracle data. The exception-handling part contains exception handlers, which deal with exceptions raised during execution.

Consider the following procedure named debit_account, which debits a bank account:

PROCEDURE debit_account (acct_id INTEGER, amount REAL) IS
   old_balance REAL;
   new_balance REAL;
   overdrawn   EXCEPTION;
BEGIN
   SELECT bal INTO old_balance FROM accts
      WHERE acctno = acct_id;
   new_balance := old_balance - amount;
   IF new_balance < 0 THEN
      RAISE overdrawn;
   ELSE
      UPDATE accts SET bal = new_balance
         WHERE acctno = acct_id;
   END IF;
EXCEPTION
   WHEN overdrawn THEN
      ...
END debit_account;

When invoked or called, this procedure accepts an account number and a debit amount. It uses the account number to select the account balance from the accts database table. Then, it uses the debit amount to compute a new balance. If the new balance is less than zero, an exception is raised; otherwise, the bank account is updated.


Advantages of Subprograms

Subprograms provide extensibility; that is, they let you tailor the PL/SQL language to suit your needs. For example, if you need a procedure that creates new departments, you can easily write one, as follows:

PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS
BEGIN
   INSERT INTO dept
      VALUES (deptno_seq.NEXTVAL, new_dname, new_loc);
END create_dept;

Subprograms also provide modularity; that is, they let you break a program down into manageable, well-defined logic modules. This supports top-down design and the stepwise refinement approach to problem solving.

Also, subprograms promote reusability and maintainability. Once validated, a subprogram can be used with confidence in any number of applications. Furthermore, only the subprogram is affected if its definition changes. This simplifies maintenance and enhancement.

Finally, subprograms aid abstraction, the mental separation from particulars. To use subprograms, you must know what they do, not how they work. Therefore, you can design applications from the top down without worrying about implementation details. Dummy subprograms (stubs) allow you to defer the definition of procedures and functions until you test and debug the main program.


Procedures

A procedure is a subprogram that performs a specific action. You write procedures using the syntax

PROCEDURE name [(parameter[, parameter, ...])] IS
   [local declarations]
BEGIN
   executable statements
[EXCEPTION
   exception handlers]
END [name];

where parameter stands for the following syntax:

parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expr]

You cannot impose the NOT NULL constraint on a parameter.

Also, you cannot specify a constraint on the datatype. For example, the following declaration of emp_id is illegal:

PROCEDURE ... (emp_id NUMBER(4)) IS  -- illegal; should be NUMBER
BEGIN ... END;

A procedure has two parts: the specification and the body. The procedure specification begins with the keyword PROCEDURE and ends with the procedure name or a parameter list. Parameter declarations are optional. Procedures that take no parameters are written without parentheses.

The procedure body begins with the keyword IS and ends with the keyword END followed by an optional procedure name. The procedure body has three parts: a declarative part, an executable part, and an optional exception-handling part.

The declarative part contains local declarations, which are placed between the keywords IS and BEGIN. The keyword DECLARE, which introduces declarations in an anonymous PL/SQL block, is not used. The executable part contains statements, which are placed between the keywords BEGIN and EXCEPTION (or END). At least one statement must appear in the executable part of a procedure. The NULL statement meets this requirement. The exception-handling part contains exception handlers, which are placed between the keywords EXCEPTION and END.

Consider the procedure raise_salary, which increases the salary of an employee:

PROCEDURE raise_salary (emp_id INTEGER, increase REAL) IS
   current_salary REAL;
   salary_missing EXCEPTION;
BEGIN
   SELECT sal INTO current_salary FROM emp
      WHERE empno = emp_id;
   IF current_salary IS NULL THEN
      RAISE salary_missing;
   ELSE
      UPDATE emp SET sal = sal + increase
         WHERE empno = emp_id;
   END IF;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      INSERT INTO emp_audit VALUES (emp_id, 'No such number');
   WHEN salary_missing THEN
      INSERT INTO emp_audit VALUES (emp_id, 'Salary is null');
END raise_salary;

When called, this procedure accepts an employee number and a salary increase amount. It uses the employee number to select the current salary from the emp database table. If the employee number is not found or if the current salary is null, an exception is raised. Otherwise, the salary is updated.

A procedure is called as a PL/SQL statement. For example, you might call the procedure raise_salary as follows:

raise_salary(emp_num, amount);


Functions

A function is a subprogram that computes a value. Functions and procedures are structured alike, except that functions have a RETURN clause. You write functions using the syntax

FUNCTION name [(parameter[, parameter, ...])] RETURN datatype IS
   [local declarations]
BEGIN
   executable statements
[EXCEPTION
   exception handlers]
END [name];

where parameter stands for the following syntax:

parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expr]

Remember, you cannot impose the NOT NULL constraint on a parameter, and you cannot specify a constraint on the datatype.

Like a procedure, a function has two parts: the specification and the body. The function specification begins with the keyword FUNCTION and ends with the RETURN clause, which specifies the datatype of the result value. Parameter declarations are optional. Functions that take no parameters are written without parentheses.

The function body begins with the keyword IS and ends with the keyword END followed by an optional function name. The function body has three parts: a declarative part, an executable part, and an optional exception-handling part.

The declarative part contains local declarations, which are placed between the keywords IS and BEGIN. The keyword DECLARE is not used. The executable part contains statements, which are placed between the keywords BEGIN and EXCEPTION (or END). One or more RETURN statements must appear in the executable part of a function. The exception-handling part contains exception handlers, which are placed between the keywords EXCEPTION and END.

Consider the function sal_ok, which determines if an employee salary is out of range:

FUNCTION sal_ok (salary REAL, title REAL) RETURN BOOLEAN IS
   min_sal REAL;
   max_sal REAL;
BEGIN
   SELECT losal, hisal INTO min_sal, max_sal 
      FROM sals
      WHERE job = title;
   RETURN (salary >= min_sal) AND (salary <= max_sal);
END sal_ok;

When called, this function accepts an employee salary and job title. It uses the job title to select range limits from the sals database table. The function identifier, sal_ok, is set to a Boolean value by the RETURN statement. If the salary is out of range, sal_ok is set to FALSE; otherwise, sal_ok is set to TRUE.

A function is called as part of an expression. For example, the function sal_ok might be called as follows:

IF sal_ok(new_sal, new_title) THEN ...

The function identifier acts like a variable whose value depends on the parameters passed to it.

Restriction

To be callable from SQL expressions, a stored function must obey certain rules meant to control side effects. For standalone functions, Oracle can enforce these rules by checking the function body. However, the body of a packaged function is hidden. So, for packaged functions, you must use the pragma RESTRICT_REFERENCES to enforce the rules. For more information, see "Calling Stored Functions from SQL Expressions" in Oracle7 Server Application Developer's Guide.


RETURN Statement

The RETURN statement immediately completes the execution of a subprogram and returns control to the caller. Execution then resumes with the statement following the subprogram call. (Do not confuse the RETURN statement with the RETURN clause, which specifies the datatype of the result value in a function specification.)

A subprogram can contain several RETURN statements, none of which need be the last lexical statement. Executing any of them completes the subprogram immediately. However, it is poor programming practice to have multiple exit points in a subprogram.

In procedures, a RETURN statement cannot contain an expression. The statement simply returns control to the caller before the normal end of the procedure is reached.

However, in functions, a RETURN statement must contain an expression, which is evaluated when the RETURN statement is executed. The resulting value is assigned to the function identifier, which acts like a variable of the type specified in the RETURN clause. Observe how the function balance returns the balance of a specified bank account:

FUNCTION balance (acct_id INTEGER) RETURN REAL IS
   acct_bal REAL;
BEGIN
   SELECT bal INTO acct_bal FROM accts 
      WHERE acctno = acct_id;
   RETURN acct_bal;
END balance;

The following example shows that the expression in a function RETURN statement can be arbitrarily complex:

FUNCTION compound (years  NUMBER,
                   amount NUMBER,
                   rate   NUMBER) RETURN NUMBER IS
BEGIN 
   RETURN amount * POWER((rate / 100) + 1, years); 
END compound; 

A function must contain at least one RETURN statement. Otherwise, PL/SQL raises the predefined exception PROGRAM_ERROR at run time.


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

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.


Actual versus Formal Parameters

Subprograms pass information using parameters. The variables or expressions referenced in the parameter list of a subprogram call are actual parameters. For example, the following procedure call lists two actual parameters named emp_num and amount:

raise_salary(emp_num, amount);

The next procedure call shows that in some cases, expressions can be used as actual parameters:

raise_salary(emp_num, merit + cola);

The variables declared in a subprogram specification and referenced in the subprogram body are formal parameters. For example, the following procedure declares two formal parameters named emp_id and increase:

PROCEDURE raise_salary (emp_id INTEGER, increase REAL) IS
   current_salary  REAL;
   ...
BEGIN
   SELECT sal INTO current_salary FROM emp WHERE empno = emp_id;
   ...
   UPDATE emp SET sal = sal + increase WHERE empno = emp_id;
END raise_salary;

Though not necessary, it is good programming practice to use different names for actual and formal parameters.

When you call procedure raise_salary, the actual parameters are evaluated and the result values are assigned to the corresponding formal parameters. Before assigning the value of an actual parameter to a formal parameter, PL/SQL converts the datatype of the value if necessary. For example, the following call to raise_salary is legal:

raise_salary(emp_num, '2500');

The actual parameter and its corresponding formal parameter must have compatible datatypes. For instance, PL/SQL cannot convert between the DATE and REAL datatypes. Also, the result value must be convertible to the new datatype. The following procedure call raises the predefined exception VALUE_ERROR because PL/SQL cannot convert the second actual parameter to a number:

raise_salary(emp_num, '$2500');  -- note the dollar sign

For more information, see "Datatype Conversion" [*].


Positional and Named Notation

When calling a subprogram, you can write the actual parameters using either positional or named notation. That is, you can indicate the association between an actual and formal parameter by position or name. For example, given the declarations

DECLARE
   acct INTEGER;
   amt  REAL;
   PROCEDURE credit (acctno INTEGER, amount REAL) IS 
   BEGIN ... END;

you can call the procedure credit in four logically equivalent ways:

BEGIN
   ...
   credit(acct, amt);                      -- positional notation
   credit(amount => amt, acctno => acct);  -- named notation
   credit(acctno => acct, amount => amt);  -- named notation
   credit(acct, amount => amt);            -- mixed notation
END;

Positional Notation

The first procedure call uses positional notation. The PL/SQL compiler associates the first actual parameter, acct, with the first formal parameter, acctno. And, the compiler associates the second actual parameter, amt, with the second formal parameter, amount.

Named Notation

The second procedure call uses named notation. The arrow (called an association operator) associates the formal parameter to the left of the arrow with the actual parameter to the right of the arrow.

The third procedure call also uses named notation and shows that you can list the parameter pairs in any order. Therefore, you need not know the order in which the formal parameters are listed.

Mixed Notation

The fourth procedure call shows that you can mix positional and named notation. In this case, the first parameter uses positional notation, and the second parameter uses named notation. Positional notation must precede named notation. The reverse is not allowed. For example, the following procedure call is illegal:

credit(acctno => acct, amt);  -- illegal


Parameter Modes

You use parameter modes to define the behavior of formal parameters. The three parameter modes, IN (the default), OUT, and IN OUT, can be used with any subprogram. However, avoid using the OUT and IN OUT modes with functions. The purpose of a function is to take zero or more arguments (actual parameters) and return a single value. It is poor programming practice to have a function return multiple values. Also, functions should be free from side effects, which change the values of variables not local to the subprogram.

IN Mode

An IN parameter lets you pass values to the subprogram being called. Inside the subprogram, an IN parameter acts like a constant. Therefore, it cannot be assigned a value. For example, the following assignment statement causes a compilation error:

PROCEDURE debit_account (acct_id IN INTEGER, amount IN REAL) IS
   minimum_purchase CONSTANT REAL := 10.0;
   service_charge   CONSTANT REAL := 0.50;
BEGIN
   ...
   IF amount < minimum_purchase THEN
      amount := amount + service_charge;  -- causes syntax error
   END IF;

The actual parameter that corresponds to an IN formal parameter can be a constant, literal, initialized variable, or expression.

Unlike OUT and IN OUT parameters, IN parameters can be initialized to default values. For more information, see "Parameter Default Values" [*].

OUT Mode

An OUT parameter lets you return values to the caller of a subprogram. Inside the subprogram, an OUT parameter acts like an uninitialized variable. Therefore, its value cannot be assigned to another variable or reassigned to itself. For instance, the following assignment statement causes a compilation error:

PROCEDURE calc_bonus (emp_id IN INTEGER, bonus OUT REAL) IS
   hire_date DATE;
BEGIN
   SELECT sal * 0.10, hiredate INTO bonus, hire_date FROM emp
      WHERE empno = emp_id;
   IF MONTHS_BETWEEN(SYSDATE, hire_date) > 60 THEN
      bonus := bonus + 500;  -- causes syntax error
   END IF;

The actual parameter that corresponds to an OUT formal parameter must be a variable; it cannot be a constant or an expression. For example, the following procedure call is illegal:

calc_bonus(7499, salary + commission);  -- causes syntax error

An OUT actual parameter can have a value before the subprogram is called. However, the value is lost when you call the subprogram. Inside the subprogram, an OUT formal parameter cannot be used in an expression; the only operation allowed on the parameter is to assign it a value.

Before exiting a subprogram, explicitly assign values to all OUT formal parameters. Otherwise, the values of corresponding actual parameters are indeterminate. If you exit successfully, PL/SQL assigns values to the actual parameters. However, if you exit with an unhandled exception, PL/SQL does not assign values to the actual parameters.

IN OUT Mode

An IN OUT parameter lets you pass initial values to the subprogram being called and return updated values to the caller. Inside the subprogram, an IN OUT parameter acts like an initialized variable. Therefore, it can be assigned a value and its value can be assigned to another variable. That means you can use an IN OUT formal parameter as if it were a normal variable. You can change its value or reference the value in any way, as the following example shows:

PROCEDURE calc_bonus (emp_id IN INTEGER, bonus IN OUT REAL) IS
   hire_date     DATE;
   bonus_missing EXCEPTION;
BEGIN
   SELECT sal * 0.10, hiredate INTO bonus, hire_date FROM emp
      WHERE empno = emp_id;
   IF bonus IS NULL THEN
      RAISE bonus_missing;
   END IF;
   IF MONTHS_BETWEEN(SYSDATE, hire_date) > 60 THEN
      bonus := bonus + 500;
   END IF;
   ...
EXCEPTION
   WHEN bonus_missing THEN
      ...
END calc_bonus;

The actual parameter that corresponds to an IN OUT formal parameter must be a variable; it cannot be a constant or an expression. Table 7 - 1 summarizes all you need to know about the parameter modes.

IN OUT IN OUT
the default must be specified must be specified
passes values to a subprogram returns values to the caller passes initial values to a subprogram and returns updated values to the caller
formal parameter acts like a constant formal parameter acts like an uninitialized variable formal parameter acts like an initialized variable
formal parameter cannot be assigned a value formal parameter cannot be used in an expression and must be assigned a value formal parameter should be assigned a value
actual parameter can be a constant, initialized variable, literal, or expression actual parameter must be a variable actual parameter must be a variable
Table 7 - 1. Parameter Modes


Parameter Default Values

As the example below shows, you can initialize IN parameters to default values. That way, you can pass different numbers of actual parameters to a subprogram, accepting or overriding the default values as you please. Moreover, you can add new formal parameters without having to change every call to the subprogram.

PROCEDURE create_dept (
    new_dname CHAR DEFAULT 'TEMP',
    new_loc   CHAR DEFAULT 'TEMP') IS
BEGIN
   INSERT INTO dept
      VALUES (deptno_seq.NEXTVAL, new_dname, new_loc);
END create_dept;

If an actual parameter is not passed, the default value of its corresponding formal parameter is used. Consider the following calls to create_dept:

create_dept;
create_dept('MARKETING');
create_dept('MARKETING', 'NEW YORK');

The first call passes no actual parameters, so both default values are used. The second call passes one actual parameter, so the default value for new_loc is used. The third call passes two actual parameters, so neither default value is used.

Usually, you can use positional notation to override the default values of formal parameters. However, you cannot skip a formal parameter by leaving out its actual parameter. For example, the following call incorrectly associates the actual parameter 'NEW YORK' with the formal parameter new_dname:

create_dept('NEW YORK');  -- incorrect

You cannot solve the problem by leaving a placeholder for the actual parameter. For example, the following call is illegal:

create_dept( , 'NEW YORK');  -- illegal

In such cases, you must use named notation, as follows:

create_dept(new_loc => 'NEW YORK');

Also, you cannot assign a null to an uninitialized formal parameter by leaving out its actual parameter. For example, given the declaration

DECLARE
   FUNCTION gross_pay (
      emp_id   IN NUMBER, 
      st_hours IN NUMBER DEFAULT 40, 
      ot_hours IN NUMBER) RETURN REAL IS
   BEGIN
      ...
   END;

the following function call does not assign a null to ot_hours:

IF gross_pay(emp_num) > max_pay THEN ...  -- illegal

Instead, you must pass the null explicitly, as in

IF gross_pay(emp_num, ot_hour => NULL) > max_pay THEN ...

or you can initialize ot_hours to NULL, as follows:

ot_hours IN NUMBER DEFAULT NULL;

Finally, when creating a stored subprogram, you cannot use bind variables in the DEFAULT clause. The following SQL*Plus example raises a bad bind variable exception because at the time of creation, num is just a placeholder whose value might change:

SQL> VARIABLE num NUMBER
SQL> CREATE FUNCTION gross_pay (emp_id IN NUMBER DEFAULT :num, ...


Parameter Aliasing

To optimize execution, the PL/SQL compiler can choose different methods of parameter passing (copy or reference) for different parameters in the same subprogram call. When the compiler chooses the copy method, the value of an actual parameter is copied into the subprogram. When the compiler chooses the reference method, the address of an actual parameter is passed to the subprogram.

The easy-to-avoid problem of aliasing occurs when a global variable appears as an actual parameter in a subprogram call and then is referenced within the subprogram. The result is indeterminate because it depends on the method of parameter passing chosen by the compiler. Consider the following example:

DECLARE
   rent REAL;
   PROCEDURE raise_rent (increase IN OUT REAL) IS
   BEGIN
      rent := rent + increase;
      /* At this point, if the compiler passed the address
         of the actual parameter to the subprogram, the same 
         variable has two names. Thus, the term 'aliasing'. */
      ...
   END raise_rent;
   ...
BEGIN
   ...
   raise_rent(rent);  -- indeterminate

Aliasing also occurs when the same actual parameter appears twice in a subprogram call. Unless both formal parameters are IN parameters, the result is indeterminate, as the following example shows:

DECLARE
   str VARCHAR2(10);
   PROCEDURE reverse (in_str VARCHAR2, out_str OUT VARCHAR2) IS
   BEGIN
      /* Reverse order of characters in string here. */
      ...
      /* At this point, whether the value of in_str 
         is 'abcd' or 'dcba' depends on the methods of 
         parameter passing chosen by the compiler. */
   END reverse;
   ...
BEGIN
   str := 'abcd';
   reverse(str, str);  -- indeterminate


Overloading

PL/SQL lets you overload subprogram names. That is, you can use the same name for several different subprograms as long as their formal parameters differ in number, order, or datatype family. (Figure 2 - 1 [*] shows the datatype families.)

Suppose you want to initialize the first n rows in two PL/SQL tables that were declared as follows:

DECLARE
   TYPE DateTabTyp IS TABLE OF DATE
      INDEX BY BINARY_INTEGER;
   TYPE RealTabTyp IS TABLE OF REAL
      INDEX BY BINARY_INTEGER;
   hiredate_tab DateTabTyp;
   sal_tab      RealTabTyp;

You might write the following procedure to initialize the PL/SQL table named hiredate_tab:

PROCEDURE initialize (tab OUT DateTabTyp, n INTEGER) IS
BEGIN
   FOR i IN 1..n LOOP
      tab(i) := SYSDATE;
   END LOOP;
END initialize;

Also, you might write the next procedure to initialize the PL/SQL table named sal_tab:

PROCEDURE initialize (tab OUT RealTabTyp, n INTEGER) IS
BEGIN
   FOR i IN 1..n LOOP
      tab(i) := 0.0;
   END LOOP;
END initialize;

Because the processing in these two procedures is the same, it is logical to give them the same name.

You can place the two overloaded initialize procedures in the same block, subprogram, or package. PL/SQL determines which of the two procedures is being called by checking their formal parameters.

Consider the example below. If you call initialize with a DateTabTyp parameter, PL/SQL uses the first version of initialize. But, if you call initialize with a RealTabTyp parameter, PL/SQL uses the second version.

DECLARE
   TYPE DateTabTyp IS TABLE OF DATE
      INDEX BY BINARY_INTEGER;
   TYPE RealTabTyp IS TABLE OF REAL
      INDEX BY BINARY_INTEGER;
   hiredate_tab  DateTabTyp;
   comm_tab      RealTabTyp;
   indx          BINARY_INTEGER;
   ...
BEGIN
   indx := 50;
   initialize(hiredate_tab, indx);  -- calls first version
   initialize(comm_tab, indx);      -- calls second version
   ...
END;

Restrictions

Only local or packaged subprograms can be overloaded. Therefore, you cannot overload standalone subprograms. Also, you cannot overload two subprograms if their formal parameters differ only in name or parameter mode. For example, you cannot overload the following two procedures:

PROCEDURE reconcile (acctno IN INTEGER) IS
BEGIN 
   ... 
END;

PROCEDURE reconcile (acctno OUT INTEGER) IS
BEGIN 
   ... 
END;

Furthermore, you cannot overload two subprograms if their formal parameters differ only in datatype and the different datatypes are in the same family. For instance, you cannot overload the following procedures because the datatypes INTEGER and REAL are in the same family:

PROCEDURE charge_back (amount INTEGER) IS
BEGIN
   ... 
END;

PROCEDURE charge_back (amount REAL) IS
BEGIN 
   ... 
END;

Likewise, you cannot overload two subprograms if their formal parameters differ only in subtype and the different subtypes are based on types in the same family. For example, you cannot overload the following procedures because the base types CHAR and LONG are in the same family:

DECLARE 
   SUBTYPE Delimiter IS CHAR; 
   SUBTYPE Text IS LONG; 
   ... 
   PROCEDURE scan (x Delimiter) IS 
   BEGIN ... END; 
 
   PROCEDURE scan (x Text) IS 
   BEGIN ... END; 

Finally, you cannot overload two functions that differ only in return type (the datatype of the result value) even if the types are in different families. For example, you cannot overload the following functions:

FUNCTION acct_ok (acct_id INTEGER) RETURN BOOLEAN IS
BEGIN ... END; 

FUNCTION acct_ok (acct_id INTEGER) RETURN INTEGER IS
BEGIN ... END; 

How Calls Are Resolved

Figure 7 - 1 shows how the PL/SQL compiler resolves subprogram calls. When the compiler encounters a procedure or function call, it tries to find a declaration that matches the call. The compiler searches first in the current scope and then, if necessary, in successive enclosing scopes. The compiler stops searching if it finds one or more subprogram declarations in which the subprogram name matches the name of the called subprogram.

To resolve a call among possibly like-named subprograms at the same level of scope, the compiler must find an exact match between the actual and formal parameters. That is, they must match in number, order, and datatype (unless some formal parameters were assigned default values). If no match is found or if multiple matches are found, the compiler generates a syntax error.

Figure 7 - 1. How the PL/SQL Compiler Resolves Calls

In the following example, you call the enclosing procedure swap from within the function valid. However, the compiler generates an error because neither declaration of swap within the current scope matches the procedure call:

PROCEDURE swap (d1 DATE, d2 DATE) IS
   date1 DATE;
   date2 DATE;
   FUNCTION valid (d DATE) RETURN BOOLEAN IS
      PROCEDURE swap (n1 INTEGER, n2 INTEGER) IS
      BEGIN ... END swap;
      PROCEDURE swap (n1 REAL, n2 REAL) IS
      BEGIN ... END swap;
   BEGIN
      ...
      swap(date1, date2);
   END valid;
BEGIN
   ...
END swap; 

Avoiding Errors

PL/SQL declares built-in functions globally in package STANDARD. Redeclaring them locally is error prone because your local declaration overrides the global declaration. Consider the following example, in which you declare a function named sign, then within the scope of that declaration, try to call the built-in function SIGN:

DECLARE
   x NUMBER;
   ...
BEGIN
   DECLARE
      FUNCTION sign (n NUMBER) RETURN NUMBER IS
      BEGIN
         IF n < 0 THEN RETURN -1; ELSE RETURN 1; END IF;
      END;
   BEGIN
      ...
      x := SIGN(0);  -- assigns 1 to x
   END;
   ...
   x := SIGN(0);  -- assigns 0 to x
END;

Inside the sub-block, PL/SQL uses your function definition, not the built-in definition. To call the built-in function from inside the sub-block, you must use dot notation, as follows:

x := STANDARD.SIGN(0);  -- assigns 0 to x


Recursion

Recursion is a powerful technique for simplifying the design of algorithms. Basically, recursion means self-reference. In a recursive mathematical sequence, each term is derived by applying a formula to preceding terms. The Fibonacci sequence (1, 1, 2, 3, 5, 8, 13, 21, ...), which was first used to model the growth of a rabbit colony, is an example. Each term in the sequence (after the second) is the sum of the two terms that immediately precede it.

In a recursive definition, something is defined in terms of simpler versions of itself. Consider the definition of n factorial (n!), the product of all integers from 1 to n:

n! = n * (n - 1)!

Recursive Subprograms

A recursive subprogram is one that calls itself. Think of a recursive call as a call to some other subprogram that does the same task as your subprogram. Each recursive call creates a new instance of any objects declared in the subprogram, including parameters, variables, cursors, and exceptions. Likewise, new instances of SQL statements are created at each level in the recursive descent.

There must be at least two paths through a recursive subprogram: one that leads to the recursive call and one that does not. That is, at least one path must lead to a terminating condition. Otherwise, the recursion would (theoretically) go on forever. In practice, if a recursive subprogram strays into infinite regress, PL/SQL eventually runs out of memory and raises the predefined exception STORAGE_ERROR.

An Example

To solve some programming problems, you must repeat a sequence of statements until a condition is met. You can use iteration or recursion to solve such problems. Recursion is appropriate when the problem can be broken down into simpler versions of itself. For example, you can evaluate 3! as follows:

0! = 1
1! = 1 * 0! = 1 * 1 = 1
2! = 2 * 1! = 2 * 1 = 2
3! = 3 * 2! = 3 * 2 = 6

To implement this algorithm, you might write the following recursive function, which returns the factorial of a positive integer:

FUNCTION fac (n POSITIVE) RETURN INTEGER IS  -- returns n!
BEGIN
   IF n = 1 THEN  -- terminating condition
      RETURN 1;
   ELSE
      RETURN n * fac(n - 1);  -- recursive call
   END IF;
END fac;

At each recursive call, n is decremented. Eventually, n becomes 1 and the recursion stops.

Another Example

Consider the procedure below, which finds the staff of a given manager. The procedure declares two formal parameters, mgr_no and tier, which represent the manager's employee number and a tier in his or her departmental organization. Staff members reporting directly to the manager occupy the first tier. When called, the procedure accepts a value for mgr_no but uses the default value of tier. For example, you might call the procedure as follows:

find_staff(7839);

The procedure passes mgr_no to a cursor in a cursor FOR loop, which finds staff members at successively lower tiers in the organization. At each recursive call, a new instance of the FOR loop is created and another cursor is opened, but prior cursors stay positioned on the next row in their result sets. When a fetch fails to return a row, the cursor is closed automatically and the FOR loop is exited. Since the recursive call is inside the FOR loop, the recursion stops.

PROCEDURE find_staff (mgr_no NUMBER, tier NUMBER := 1) IS
   boss_name  CHAR(10);
   CURSOR c1 (boss_no NUMBER) IS
      SELECT empno, ename FROM emp WHERE mgr = boss_no;
BEGIN
   /* Get manager's name. */
   SELECT ename INTO boss_name FROM emp WHERE empno = mgr_no;
   IF tier = 1 THEN
      INSERT INTO staff  -- single-column output table
         VALUES (boss_name || ' manages the staff');
   END IF;
   /* Find staff members who report directly to manager. */
   FOR ee IN c1 (mgr_no) LOOP
      INSERT INTO staff
         VALUES (boss_name || ' manages ' || ee.ename
            || ' on tier ' || to_char(tier));
      /* Drop to next tier in organization. */
      find_staff(ee.empno, tier + 1);  -- recursive call
   END LOOP;
   COMMIT;
END;

Unlike the initial call, each recursive call passes a second actual parameter (the next tier) to the procedure.

The last example illustrates recursion, not the efficient use of set-oriented SQL statements. You might want to compare the performance of the recursive procedure to that of the following SQL statement, which does the same task:

INSERT INTO staff
   SELECT PRIOR ename || ' manages ' || ename
      || ' on tier ' || to_char(LEVEL - 1)
   FROM emp
   START WITH empno = 7839
   CONNECT BY PRIOR empno = mgr;

The SQL statement is appreciably faster. However, the procedure is more flexible. For example, a multi-table query cannot contain the CONNECT BY clause. So, unlike the procedure, the SQL statement cannot be modified to do joins. (A join combines rows from two or more database tables.) In addition, a procedure can process data in ways that a single SQL statement cannot.

Caution

Be careful where you place a recursive call. If you place it inside a cursor FOR loop or between OPEN and CLOSE statements, another cursor is opened at each call. As a result, your program might exceed the limit set by the Oracle initialization parameter OPEN_CURSORS.

Mutual Recursion

Subprograms are mutually recursive if they directly or indirectly call each other. In the example below, the Boolean functions odd and even, which determine whether a number is odd or even, call each other directly. The forward declaration of odd is necessary because even calls odd, which is not yet declared when the call is made. (See "Forward Declarations" [*].)

FUNCTION odd (n NATURAL) RETURN BOOLEAN;  -- forward declaration

FUNCTION even (n NATURAL) RETURN BOOLEAN IS
BEGIN
   IF n = 0 THEN
      RETURN TRUE;
   ELSE
      RETURN odd(n - 1);  -- mutually recursive call
   END IF;
END even;

FUNCTION odd (n NATURAL) RETURN BOOLEAN IS
BEGIN
   IF n = 0 THEN
      RETURN FALSE;
   ELSE
      RETURN even(n - 1);  -- mutually recursive call
   END IF;
END odd;

When a positive integer n is passed to odd or even, the functions call each other by turns. At each call, n is decremented. Ultimately, n becomes zero and the final call returns TRUE or FALSE. For instance, passing the number 4 to odd results in this sequence of calls:

odd(4)
even(3)
odd(2)
even(1)
odd(0)  -- returns FALSE

On the other hand, passing the number 4 to even results in the following sequence of calls:

even(4)
odd(3)
even(2)
odd(1)
even(0)  -- returns TRUE

Recursion versus Iteration

Unlike iteration, recursion is not essential to PL/SQL programming. Any problem that can be solved using recursion can be solved using iteration. Also, the iterative version of a subprogram is usually easier to design than the recursive version. However, the recursive version is usually simpler, smaller, and therefore easier to debug. Compare the following functions, which compute the nth Fibonacci number:

-- recursive version
FUNCTION fib (n POSITIVE) RETURN INTEGER IS
BEGIN
   IF (n = 1) OR (n = 2) THEN
      RETURN 1;
   ELSE
      RETURN fib(n - 1) + fib(n - 2);
   END IF;
END fib;

-- iterative version
FUNCTION fib (n POSITIVE) RETURN INTEGER IS
   pos1 INTEGER := 1;
   pos2 INTEGER := 0;
   cum  INTEGER;
BEGIN
   IF (n = 1) OR (n = 2) THEN
      RETURN 1;
   ELSE
      cum := pos1 + pos2;
      FOR i IN 3..n LOOP
         pos2 := pos1;
         pos1 := cum;
         cum := pos1 + pos2;
      END LOOP;
      RETURN cum;
   END IF;
END fib;

The recursive version of fib is more elegant than the iterative version. However, the iterative version is more efficient; it runs faster and uses less storage. That is because each recursive call requires additional time and memory. As the number of recursive calls gets larger, so does the difference in efficiency. Still, if you expect the number of recursive calls to be small, you might choose the recursive version for its readability.




Go to previous file in sequence Go to next file in sequence
Prev Next
Oracle
Copyright © 1996 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index