8
PL/SQL 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 items 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 acct_no = acct_id;

   new_balance := old_balance - amount;

   IF new_balance < 0 THEN

      RAISE overdrawn;

   ELSE

      UPDATE accts SET bal = new_balance

         WHERE acct_no = 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 VARCHAR2, new_loc VARCHAR2) 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 modules. This supports top-down design and the stepwise refinement approach to problem solving.

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

Finally, subprograms aid abstraction, the mental process of deriving a universal 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.

Understanding PL/SQL Procedures

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

[CREATE [OR REPLACE]]

PROCEDURE procedure_name[(parameter[, parameter]...)]

   [AUTHID {DEFINER | CURRENT_USER}] {IS | AS}

   [PRAGMA AUTONOMOUS_TRANSACTION;]

   [local declarations]

BEGIN

   executable statements

[EXCEPTION

   exception handlers]

END [name];

where parameter stands for the following syntax:

parameter_name [IN | OUT [NOCOPY] | IN OUT [NOCOPY]] datatype

   [{:= | DEFAULT} expression]

The CREATE clause lets you create standalone procedures, which are stored in an Oracle database. You can execute the CREATE PROCEDURE statement interactively from SQL*Plus or from a program using native dynamic SQL (see Chapter 11).

The AUTHID clause determines whether a stored procedure executes with the privileges of its owner (the default) or current user and whether its unqualified references to schema objects are resolved in the schema of the owner or current user. You can override the default behavior by specifying CURRENT_USER. For more information, see "Invoker Rights Versus Definer Rights".

The pragma AUTONOMOUS_TRANSACTION instructs the PL/SQL compiler to mark a procedure as autonomous (independent). Autonomous transactions let you suspend the main transaction, do SQL operations, commit or roll back those operations, then resume the main transaction. For more information, see "Doing Independent Units of Work with Autonomous Transactions".

You cannot constrain the datatype of a parameter. For example, the following declaration of acct_id is illegal because the datatype CHAR is size-constrained:

PROCEDURE reconcile (acct_id CHAR(5)) IS ...  -- illegal

However, you can use the following workaround to size-constrain parameter types indirectly:

DECLARE

   SUBTYPE Char5 IS CHAR(5);

   PROCEDURE reconcile (acct_id Char5) IS ...

A procedure has two parts: the specification (spec for short) and the body. The procedure spec 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 (or AS) 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 by a given amount:

PROCEDURE raise_salary (emp_id INTEGER, amount 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 + amount

         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_id, amount);

Understanding PL/SQL 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 (local) functions using the syntax:

[CREATE  [OR REPLACE ] ]

FUNCTION function_name [ ( parameter [ , parameter ]... ) ] RETURN

datatype

   [ AUTHID { DEFINER  | CURRENT_USER } ]

   [ PARALLEL_ENABLE

    [ { [CLUSTER parameter BY (column_name [, column_name ]... ) ] |

     [ORDER parameter BY (column_name [ , column_name ]... ) ] } ]

     [ ( PARTITION parameter BY

       { [ {RANGE | HASH } (column_name [, column_name]...)] | ANY }

) ]

   [DETERMINISTIC]    [ PIPELINED  [ USING implementation_type ] ]

   [ AGGREGATE  [UPDATE VALUE]  [WITH EXTERNAL CONTEXT]

USING  implementation_type ]  {IS | AS}

   [ PRAGMA AUTONOMOUS_TRANSACTION; ]

   [ local declarations ]

BEGIN

   executable statements

[ EXCEPTION

   exception handlers ]

END [ name ];

The CREATE clause lets you create standalone functions, which are stored in an Oracle database. You can execute the CREATE FUNCTION statement interactively from SQL*Plus or from a program using native dynamic SQL.

The AUTHID clause determines whether a stored function executes with the privileges of its owner (the default) or current user and whether its unqualified references to schema objects are resolved in the schema of the owner or current user. You can override the default behavior by specifying CURRENT_USER.

The PARALLEL_ENABLE option declares that a stored function can be used safely in the slave sessions of parallel DML evaluations. The state of a main (logon) session is never shared with slave sessions. Each slave session has its own state, which is initialized when the session begins. The function result should not depend on the state of session (static) variables. Otherwise, results might vary across sessions.

The hint DETERMINISTIC helps the optimizer avoid redundant function calls. If a stored function was called previously with the same arguments, the optimizer can elect to use the previous result. The function result should not depend on the state of session variables or schema objects. Otherwise, results might vary across calls. Only DETERMINISTIC functions can be called from a function-based index or a materialized view that has query-rewrite enabled. For more information, see Oracle9i SQL Reference.

The pragma AUTONOMOUS_TRANSACTION instructs the PL/SQL compiler to mark a function as autonomous (independent). Autonomous transactions let you suspend the main transaction, do SQL operations, commit or roll back those operations, then resume the main transaction.

You cannot constrain (with NOT NULL for example) the datatype of a parameter or a function return value. However, you can use a workaround to size-constrain them indirectly. See "Understanding PL/SQL Procedures".

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

The function body begins with the keyword IS (or AS) 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 a salary is out of range:

FUNCTION sal_ok (salary REAL, title VARCHAR2) 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, as the example below shows. The function identifier sal_ok acts like a variable whose value depends on the parameters passed to it.

IF sal_ok(new_sal, new_title) THEN ...

Using the 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 in a function spec, which specifies the datatype of the return value.)

A subprogram can contain several RETURN statements. The last lexical statement does not need to be a RETURN statement. Executing any RETURN statement completes the subprogram immediately. However, to have multiple exit points in a subprogram is a poor programming practice.

In procedures, a RETURN statement cannot return a value, and therefore 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 acct_no = 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;

In a function, there must be at least one execution path that leads to a RETURN statement. Otherwise, you get a function returned without value error at run time.

Controlling Side Effects of PL/SQL Subprograms

To be callable from SQL statements, a stored function must obey the following "purity" rules, which are meant to control side effects:

When called from a SELECT statement or a parallelized INSERT, UPDATE, or DELETE statement, the function cannot modify any database tables.
When called from an INSERT, UPDATE, or DELETE statement, the function cannot query or modify any database tables modified by that statement.
When called from a SELECT, INSERT, UPDATE, or DELETE statement, the function cannot execute SQL transaction control statements (such as COMMIT), session control statements (such as SET ROLE), or system control statements (such as ALTER SYSTEM). Also, it cannot execute DDL statements (such as CREATE) because they are followed by an automatic commit.

If any SQL statement inside the function body violates a rule, you get an error at run time (when the statement is parsed).

To check for violations of the rules, you can use the pragma (compiler directive) RESTRICT_REFERENCES. The pragma asserts that a function does not read and/or write database tables and/or package variables. For example, the following pragma asserts that packaged function credit_ok writes no database state (WNDS) and reads no package state (RNPS):

CREATE PACKAGE loans AS

...

   FUNCTION credit_ok RETURN BOOLEAN;

   PRAGMA RESTRICT_REFERENCES (credit_ok, WNDS, RNPS);

END loans;

Note: A static INSERT, UPDATE, or DELETE statement always violates WNDS. It also violates RNDS (reads no database state) if it reads any columns. A dynamic INSERT, UPDATE, or DELETE statement always violates WNDS and RNDS.

For more information about the purity rules and pragma RESTRICT_REFERENCES, see Oracle9i Application Developer's Guide - Fundamentals.

Declaring PL/SQL Subprograms

You can declare subprograms in any PL/SQL block, subprogram, or package. But, you must declare subprograms at the end of a declarative section after all other program items.

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 logical or alphabetical order.

You can solve the problem by using a special subprogram declaration called a forward declaration, which consists of a subprogram spec 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

...

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.

Packaging PL/SQL Subprograms Together

You can group logically related subprograms in a package, which is stored in the database. That way, the subprograms can be shared by many applications. The subprogram specs go in the package spec, 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 spec

   PROCEDURE hire_employee (emp_id INTEGER, name VARCHAR2, ...);

   PROCEDURE fire_employee (emp_id INTEGER);

   PROCEDURE raise_salary (emp_id INTEGER, amount REAL);

...

END emp_actions;

CREATE PACKAGE BODY emp_actions AS  -- package body

   PROCEDURE hire_employee (emp_id INTEGER, name VARCHAR2, ...) IS

   BEGIN

...

      INSERT INTO emp VALUES (emp_id, name, ...);

   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, amount REAL) IS

   BEGIN

      UPDATE emp SET sal = sal + amount WHERE empno = emp_id;

   END raise_salary;

...

END emp_actions;

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

Actual Versus Formal Subprogram 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 expressions can be used as actual parameters:

raise_salary(emp_num, merit + cola);

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

PROCEDURE raise_salary (emp_id INTEGER, amount REAL) IS

BEGIN

   UPDATE emp SET sal = sal + amount WHERE empno = emp_id;

END raise_salary;

A good programming practice is to use different names for actual and formal parameters.

When you call procedure raise_salary, the actual parameters are evaluated and the results are assigned to the corresponding formal parameters. If necessary, before assigning the value of an actual parameter to a formal parameter, PL/SQL converts the datatype of the value. For example, the following call to raise_salary is valid:

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 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

Positional Versus Named Notation for Subprogram Parameters

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. So, given the declarations

DECLARE

   acct INTEGER;

   amt  REAL;

   PROCEDURE credit_acct (acct_no INTEGER, amount REAL) IS ...

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

BEGIN

   credit_acct(acct, amt);                  -- positional notation

   credit_acct(amount => amt, acct_no => acct);  -- named notation

   credit_acct(acct_no => acct, amount => amt);  -- named notation

   credit_acct(acct, amount => amt);             -- mixed notation

Using Positional Notation

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

Using Named Notation

The second procedure call uses named notation. An arrow (=>) serves as the association operator, which 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. So, you need not know the order in which the formal parameters are listed.

Using 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_acct(acct_no => acct, amt);  -- illegal

Specifying Subprogram 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. To have a function return multiple values is a poor programming practice. Also, functions should be free from side effects, which change the values of variables not local to the subprogram.

Using the 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 DEFAULT 10.0;

   service_charge   CONSTANT REAL DEFAULT 0.50;

BEGIN

   IF amount < minimum_purchase THEN

      amount := amount + service_charge; -- causes compilation error

   END IF;

...

END debit_account;

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 "Using Default Values for Subprogram Parameters".

Using the OUT Mode

An OUT parameter lets you return values to the caller of a subprogram. Inside the subprogram, an OUT parameter acts like a variable. That means you can use an OUT formal parameter as if it were a local 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 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 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 compilation error

An OUT actual parameter can have a value before the subprogram is called. However, when you call the subprogram, the value is lost unless you specify the compiler hint NOCOPY (see "Passing Large Data Structures with the NOCOPY Compiler Hint") or the subprogram exits with an unhandled exception.

Like variables, OUT formal parameters are initialized to NULL. So, the datatype of an OUT formal parameter cannot be a subtype defined as NOT NULL (that includes the built-in subtypes NATURALN and POSITIVEN). Otherwise, when you call the subprogram, PL/SQL raises VALUE_ERROR. An example follows:

DECLARE

   SUBTYPE Counter IS INTEGER NOT NULL;

   rows Counter := 0;

   PROCEDURE count_emps (n OUT Counter) IS

   BEGIN

      SELECT COUNT(*) INTO n FROM emp;

   END;

BEGIN

   count_emps(rows);  -- raises VALUE_ERROR

Before exiting a subprogram, explicitly assign values to all OUT formal parameters. Otherwise, the corresponding actual parameters will be null. 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.

Using the 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.

The actual parameter that corresponds to an IN OUT formal parameter must be a variable; it cannot be a constant or an expression.

If you exit a subprogram 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.

Summary of Subprogram Parameter Modes

Table 8-1 summarizes all you need to know about the parameter modes.

Table 8-1 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 a variable	formal parameter acts like an initialized variable
formal parameter cannot be assigned a value	formal parameter 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
actual parameter is passed by reference (a pointer to the value is passed in)	actual parameter is passed by value (a copy of the value is passed out) unless `NOCOPY` is specified	actual parameter is passed by value (a copy of the value is passed in and out) unless `NOCOPY` is specified

Understanding and Using 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 (0, 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 as simpler versions of itself. Consider the definition of n factorial (n!), the product of all integers from 1 to n:

n! = n * (n - 1)!

What Is a Recursive Subprogram?

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 items 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.

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.

There must be at least two paths through a recursive subprogram: one that leads to the recursive call and one that does not. 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.

Recursion Example: Computing the Factorial

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. Use recursion when the problem can be broken down into simpler versions of itself. For example, you can evaluate 3! as follows:

0! = 1  -- by definition

1! = 1 * 0! = 1

2! = 2 * 1! = 2

3! = 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.

Recursion Example: Traversing Tree-Structured Data

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.

PROCEDURE find_staff (mgr_no NUMBER, tier NUMBER := 1) IS

   boss_name VARCHAR2(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;

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. Unlike the initial call, each recursive call passes a second actual parameter (the next tier) to the procedure.

Tip: Perform Recursive Queries with the CONNECT BY Clause

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.

Using 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.

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 this 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;

   accumulator  INTEGER;

BEGIN

   IF (n = 1) OR (n = 2) THEN

      RETURN 1;

   ELSE

      accumulator := pos1 + pos2;

      FOR i IN 3..n LOOP

         pos2 := pos1;

         pos1 := accumulator;

         accumulator := pos1 + pos2;

      END LOOP;

      RETURN accumulator;

   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.

Calling External Subprograms

PL/SQL is a powerful development tool; you can use it for almost any purpose. However, it is specialized for SQL transaction processing. So, some tasks are more quickly done in a lower-level language such as C, which is very efficient at machine-precision calculations. Other tasks are more easily done in a fully object-oriented, standardized language such as Java.

To support such special-purpose processing, you can use PL/SQL call specs to invoke external subprograms (that is, subprograms written in other languages). This makes the strengths and capabilities of those languages available to you from PL/SQL.

For example, you can call Java stored procedures from any PL/SQL block, subprogram, or package. Suppose you store the following Java class in the database:

import java.sql.*;

import oracle.jdbc.driver.*;

public class Adjuster {

  public static void raiseSalary (int empNo, float percent)

  throws SQLException {

    Connection conn = new OracleDriver().defaultConnection();

    String sql = "UPDATE emp SET sal = sal * ? WHERE empno = ?";

    try {

      PreparedStatement pstmt = conn.prepareStatement(sql);

      pstmt.setFloat(1, (1 + percent / 100));

      pstmt.setInt(2, empNo);

      pstmt.executeUpdate();

      pstmt.close();

    } catch (SQLException e) {System.err.println(e.getMessage());}

The class Adjuster has one method, which raises the salary of an employee by a given percentage. Because raiseSalary is a void method, you publish it as a procedure using this call spec:

CREATE PROCEDURE raise_salary (empno NUMBER, pct NUMBER)

AS LANGUAGE JAVA

NAME 'Adjuster.raiseSalary(int, float)';

Later, you might call procedure raise_salary from an anonymous PL/SQL block, as follows:

DECLARE

   emp_id  NUMBER;

   percent NUMBER;

BEGIN

   -- get values for emp_id and percent

   raise_salary(emp_id, percent);  -- call external subprogram

Typically, external C subprograms are used to interface with embedded systems, solve engineering problems, analyze data, or control real-time devices and processes. External C subprograms let you extend the functionality of the database server, and move computation-bound programs from client to server, where they execute faster.

For more information about Java stored procedures, see Oracle9i Java Stored Procedures Developer's Guide. For more information about external C subprograms, see Oracle9i Application Developer's Guide - Fundamentals.

Creating Dynamic Web Pages with PL/SQL Server Pages

PL/SQL Server Pages (PSPs) enable you to develop Web pages with dynamic content. They are an alternative to coding a stored procedure that writes out the HTML code for a web page, one line at a time.

Using special tags, you can embed PL/SQL scripts into HTML source code. The scripts are executed when the pages are requested by Web clients such as browsers. A script can accept parameters, query or update the database, then display a customized page showing the results.

During development, PSPs can act like templates with a static part for page layout and a dynamic part for content. You can design the layouts using your favorite HTML authoring tools, leaving placeholders for the dynamic content. Then, you can write the PL/SQL scripts that generate the content. When finished, you simply load the resulting PSP files into the database as stored procedures.

For more information about creating and using PSPs, see Oracle9i Application Developer's Guide - Fundamentals.

8 PL/SQL Subprograms