Release 8.1.5
A67842-01
 Library |
 Product |
 Contents |
 Index |
| PL/SQL User's Guide and Reference Release 8.1.5 A67842-01 |
|
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.
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.
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 logic 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 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.
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 [NOCOPY] | IN OUT [NOCOPY]] datatype_name [{:= | DEFAULT} expression]
You cannot constrain (with NOT NULL for instance) 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 temp CHAR(5); SUBTYPE Char5 IS temp%TYPE; 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 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:
DECLARE emp_id NUMBER; amount REAL; BEGIN ... raise_salary(emp_id, amount);
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 [NOCOPY] | IN OUT [NOCOPY]] datatype_name [{:= | DEFAULT} expression]
You cannot constrain (with NOT NULL for exampe) the datatype of a parameter or function result. However, you can use a workaround to size-constrain them indirectly. See "Functions".
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 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:
DECLARE new_sal REAL; new_title VARCHAR2(15); BEGIN ... IF sal_ok(new_sal, new_title) THEN ...
The function identifier sal_ok acts like a variable whose value depends on the parameters passed to it.
To be callable from SQL statements, a function must obey the following "purity" rules, which are meant to control side effects:
SELECT statement or a parallelized INSERT, UPDATE, or DELETE statement, the function cannot modify any database tables.
INSERT, UPDATE, or DELETE statement, the function cannot query or modify any database tables modified by that statement.
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).
You can use the pragma (compiler directive) RESTRICT_REFERENCES to check for violations of the rules. 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 Oracle8i Application Developer's Guide - Fundamentals.
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 spec.)
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, to have multiple exit points in a subprogram is a poor programming practice.
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 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;
A function must contain at least one RETURN statement. Otherwise, PL/SQL raises the predefined exception PROGRAM_ERROR at run time.
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. 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;
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 the problem by providing a special subprogram declaration called a forward declaration. You can use forward declarations to
A forward declaration 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 ... /* 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.
Forward declarations also let you group logically related subprograms in a package. 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 INTGER, 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 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, amount 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 specs in the package spec. However, such subprograms can be called only from inside the package. For more information about packages, see Chapter 8.
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, subprograms must be stored in an Oracle database.
To create subprograms and store them permanently in an Oracle database, use the CREATE PROCEDURE and CREATE FUNCTION statements, which you can execute interactively from SQL*Plus. For example, in SQL*Plus, you might create the procedure fire_employee, as follows:
SQL> list 1 CREATE PROCEDURE fire_employee (emp_id NUMBER) AS 2 BEGIN 3 DELETE FROM emp WHERE empno = emp_id; 4* END; SQL> / Procedure created.
For readability, when creating subprograms, you can use the keyword AS instead of IS in the spec. For more information about creating and using stored subprograms, see Oracle8i Application Developer's Guide - Fundamentals.
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 current_salary REAL; ... BEGIN SELECT sal INTO current_salary FROM emp WHERE empno = emp_id; ... 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 result values 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 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".
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
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.
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.
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
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.
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 "Parameter Default Values".
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, the value is lost when you call the subprogram (unless it 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.
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.
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 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 |
actual parameter is passed by value (a copy of the value is passed in and out) unless |
|
Note: The |
||
Suppose a subprogram declares an IN parameter, an OUT parameter, and an IN OUT parameter. When you call the subprogram, the IN parameter is passed by reference. That is, a pointer to the IN actual parameter is passed to the corresponding formal parameter. So, both parameters reference the same memory location, which holds the value of the actual parameter.
By default, the OUT and IN OUT parameters are passed by value. That is, the value of the IN OUT actual parameter is copied into the corresponding formal parameter. Then, if the subprogram exits normally, the values assigned to the OUT and IN OUT formal parameters are copied into the corresponding actual parameters.
When the parameters hold large data structures such as collections, records, and instances of object types, all this copying slows down execution and uses up memory. To prevent that, you can specify the NOCOPY hint, which allows the PL/SQL compiler to pass OUT and IN OUT parameters by reference.
In the following example, you ask the compiler to pass IN OUT parameter my_staff by reference instead of by value:
DECLARE TYPE Staff IS VARRAY(200) OF Employee; PROCEDURE reorganize (my_staff IN OUT NOCOPY Staff) IS ...
Remember, NOCOPY is a hint, not a directive. So, the compiler might pass my_staff by value despite your request. Usually, however, NOCOPY succeeds. So, it can benefit any PL/SQL application that passes around large data structures.
In the example below, 5000 records are loaded into a local nested table, which is passed to two local procedures that do nothing but execute NULL statements. However, a call to one procedure takes 26 seconds because of all the copying. With NOCOPY, a call to the other procedure takes much less than 1 second.
SQL> SET SERVEROUTPUT ON SQL> GET test.sql 1 DECLARE 2 TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE; 3 emp_tab EmpTabTyp := EmpTabTyp(NULL); -- initialize 4 t1 CHAR(5); 5 t2 CHAR(5); 6 t3 CHAR(5); 7 PROCEDURE get_time (t OUT NUMBER) IS 8 BEGIN SELECT TO_CHAR(SYSDATE,'SSSSS') INTO t FROM dual; END; 9 PROCEDURE do_nothing1 (tab IN OUT EmpTabTyp) IS 10 BEGIN NULL; END; 11 PROCEDURE do_nothing2 (tab IN OUT NOCOPY EmpTabTyp) IS 12 BEGIN NULL; END; 13 BEGIN 14 SELECT * INTO emp_tab(1) FROM emp WHERE empno = 7788; 15 emp_tab.EXTEND(4999, 1); -- copy element 1 into 2..5000 16 get_time(t1); 17 do_nothing1(emp_tab); -- pass IN OUT parameter 18 get_time(t2); 19 do_nothing2(emp_tab); -- pass IN OUT NOCOPY parameter 20 get_time(t3); 21 DBMS_OUTPUT.PUT_LINE('Call Duration (secs)'); 22 DBMS_OUTPUT.PUT_LINE('--------------------'); 23 DBMS_OUTPUT.PUT_LINE('Just IN OUT: ' || TO_CHAR(t2 - t1)); 24 DBMS_OUTPUT.PUT_LINE('With NOCOPY: ' || TO_CHAR(t3 - t2)); 25* END; SQL> / Call Duration (secs) -------------------- Just IN OUT: 26 With NOCOPY: 0
NOCOPY lets you trade well-defined exception semantics for better performance. Its use affects exception handling in the following ways:
NOCOPY is a hint, not a directive, the compiler can pass NOCOPY parameters to a subprogram by value or by reference. So, if the subprogram exits with an unhandled exception, you cannot rely on the values of the NOCOPY actual parameters.
OUT and IN OUT formal parameters are not copied into the corresponding actual parameters, and changes appear to roll back. However, when you specify NOCOPY, assignments to the formal parameters immediately affect the actual parameters as well. So, if the subprogram exits with an unhandled exception, the (possibly unfinished) changes are not "rolled back."
NOCOPY parameters to a remote site, those parameters will no longer be passed by reference.
Also, the use of NOCOPY increases the likelihood of parameter aliasing. For more information, see "Parameter Aliasing".
In the following cases, the PL/SQL compiler ignores the NOCOPY hint and uses the by-value parameter-passing method (no error is generated):
NOT NULL for example). This restriction does not extend to constrained elements or attributes. Also, it does not apply to size-constrained character strings.
%ROWTYPE or %TYPE, and constraints on corresponding fields in the records differ.
FOR loop, and constraints on corresponding fields in the records differ.
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;
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 host variables (bind variables) in the DEFAULT clause. The following SQL*Plus example causes a bad bind variable error 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, ...
To optimize a subprogram call, the PL/SQL compiler can choose between two methods of parameter passing. With the by-value method, the value of an actual parameter is passed to the subprogram. With the by-reference method, only a pointer to the value is passed, in which case the actual and formal parameters reference the same item.
The NOCOPY compiler hint increases the possibility of aliasing (that is, having two different names refer to the same memory location). This can occur 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.
In the example below, procedure add_entry refers to varray lexicon in two different ways: as a parameter and as a global variable. So, when add_entry is called, the identifiers word_list and lexicon name the same varray.
DECLARE TYPE Definition IS RECORD ( word VARCHAR2(20), meaning VARCHAR2(200)); TYPE Dictionary IS VARRAY(2000) OF Definition; lexicon Dictionary := Dictionary(); PROCEDURE add_entry (word_list IN OUT NOCOPY Dictionary) IS BEGIN word_list(1).word := 'aardvark'; lexicon(1).word := 'aardwolf'; END; BEGIN lexicon.EXTEND; add_entry(lexicon); DBMS_OUTPUT.PUT_LINE(lexicon(1).word); -- prints 'aardvark' if parameter was passed by value -- prints 'aardwolf' if parameter was passed by reference END;
The result depends on the method of parameter passing chosen by the compiler. If the compiler chooses the by-value method, word_list and lexicon are separate copies of the same varray. So, changing one does not affect the other. But, if the compiler chooses the by-reference method, word_list and lexicon are just different names for the same varray. (Hence, the term "aliasing.") So, changing the value of lexicon(1) also changes the value of word_list(1).
Aliasing can also occur when the same actual parameter appears more than once in a subprogram call. In the example below, n2 is an IN OUT parameter, so the value of the actual parameter is not updated until the procedure exits. That is why the first PUT_LINE prints 10 (the initial value of n) and the third PUT_LINE prints 20. However, n3 is a NOCOPY parameter, so the value of the actual parameter is updated immediately. That is why the second PUT_LINE prints 30.
DECLARE n NUMBER := 10; PROCEDURE do_something ( n1 IN NUMBER, n2 IN OUT NUMBER, n3 IN OUT NOCOPY NUMBER) IS BEGIN n2 := 20; DBMS_OUTPUT.PUT_LINE(n1); -- prints 10 n3 := 30; DBMS_OUTPUT.PUT_LINE(n1); -- prints 30 END; BEGIN do_something(n, n, n); DBMS_OUTPUT.PUT_LINE(n); -- prints 20 END;
Because they are pointers, cursor variables also increase the possibility of aliasing. Consider the example below. After the assignment, emp_cv2 is an alias of emp_cv1 because both point to the same query work area. So, both can alter its state. That is why the first fetch from emp_cv2 fetches the third row (not the first) and why the second fetch from emp_cv2 fails after you close emp_cv1.
PROCEDURE get_emp_data ( emp_cv1 IN OUT EmpCurTyp, emp_cv2 IN OUT EmpCurTyp) IS emp_rec emp%ROWTYPE; BEGIN OPEN emp_cv1 FOR SELECT * FROM emp; emp_cv2 := emp_cv1; FETCH emp_cv1 INTO emp_rec; -- fetches first row FETCH emp_cv1 INTO emp_rec; -- fetches second row FETCH emp_cv2 INTO emp_rec; -- fetches third row CLOSE emp_cv1; FETCH emp_cv2 INTO emp_rec; -- raises INVALID_CURSOR ... END;
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.
Suppose you want to initialize the first n rows in two index-by 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 index-by 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;
And, you might write the next procedure to initialize the index-by 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
Only local or packaged subprograms can be overloaded. Therefore, you cannot overload stand-alone 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 (acct_no IN INTEGER) IS BEGIN ... END; PROCEDURE reconcile (acct_no 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;
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.
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; PROCEDURE swap (n1 REAL, n2 REAL) IS BEGIN ... END; BEGIN ... swap(date1, date2); END valid; BEGIN ... END;
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
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 routines (that is, routines written in other languages). This makes the strengths and capabilities of those languages available to you. No longer are you restricted to one language with its inherent limitations.
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 routine
For more information about Java stored procedures, see Oracle8i Java Stored Procedures Developer's Guide.
Typically, external C routines are used to interface with embedded systems, solve engineering problems, analyze data, or control real-time devices and processes. Moreover, external C routines enable you to extend the functionality of the database server, and to move computation-bound programs from client to server, where they will execute faster.
For more information about external C routines, see Oracle8i Application Developer's Guide - Fundamentals.
By default, stored procedures and SQL methods execute with the privileges of their definer, not their invoker. Such definer-rights routines are bound to the schema in which they reside. For example, assume that duplicates of table dept reside in schema scott and schema blake, and that the following stand-alone procedure resides in schema scott:
CREATE PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) AS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END;
Also assume that user scott has granted the EXECUTE privilege on this procedure to user blake. When user blake calls the procedure, the INSERT statement executes with the privileges of user scott. Also, the unqualified reference to table dept is resolved in schema scott. So, the procedure updates the dept table in schema scott, not schema blake.
How can routines in one schema manipulate objects in another schema? One way is to fully qualify references to the objects, as in
INSERT INTO blake.dept ...
However, that hampers portability. Another way is to copy the routines into the other schema. However, that hampers maintenance. A better way is to use the AUTHID clause, which enables stored procedures and SQL methods to execute with the privileges of their invoker (current user). Such invoker-rights routines are not bound to a particular schema. They can be run by a variety of users, and their definer need not know who those users will be.
For example, the following version of procedure create_dept executes with the privileges of its invoker:
CREATE PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) AUTHID CURRENT_USER AS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END;
Also, the unqualified reference to table dept is resolved in the schema of the invoker, not the definer.
Invoker-rights routines let you centralize data retrieval. They are especially useful in applications that store data in different schemas. In such cases, multiple users can manage their own data using a single code base.
Consider a company that uses a definer-rights procedure to analyze sales. To provide local sales statistics, procedure analyze must access sales tables that reside at each regional site. So, as Figure 7-2 shows, the procedure must also reside at each regional site. This causes a maintenance problem.
To solve the problem, the company installs an invoker-rights version of procedure analyze at headquarters. Now, as Figure 7-3 shows, all regional sites can use the same procedure to query their own sales tables.
Invoker-rights routines also let you restrict access to sensitive data. Suppose headquarters would like procedure analyze to calculate sales commissions and update a central payroll table.
That presents a problem because invokers of analyze should not have direct access to the payroll table, which stores employee salaries and other sensitive data. As Figure 7-4 shows, the solution is to have procedure analyze call definer-rights procedure calc_comm, which in turn updates the payroll table.
To implement invoker rights, use the AUTHID clause, which specifies whether a routine executes with the privileges of its definer or its invoker. It also specifies whether external references (that is, references to objects outside the routine) are resolved in the schema of the definer or the invoker.
The AUTHID clause is allowed only in the header of a stand-alone subprogram, a package spec, or an object type spec. The header syntax follows:
-- stand-alone function CREATE [OR REPLACE] FUNCTION [schema_name.]function_name [(parameter_list)] RETURN datatype [AUTHID {CURRENT_USER | DEFINER}] {IS | AS} -- stand-alone procedure CREATE [OR REPLACE] PROCEDURE [schema_name.]procedure_name [(parameter_list)] [AUTHID {CURRENT_USER | DEFINER}] {IS | AS} -- package spec CREATE [OR REPLACE] PACKAGE [schema_name.]package_name [AUTHID {CURRENT_USER | DEFINER}] {IS | AS} -- object type spec CREATE [OR REPLACE] TYPE [schema_name.]object_type_name [AUTHID {CURRENT_USER | DEFINER}] {IS | AS} OBJECT
where DEFINER is the default option. In a package or object type, the AUTHID clause applies to all routines.
If you specify AUTHID CURRENT_USER, the privileges of the invoker are checked at run time, and external references are resolved in the schema of the invoker. However, this applies only to external references in
SELECT, INSERT, UPDATE, and DELETE data manipulation statements
LOCK TABLE transaction control statement
OPEN and OPEN-FOR cursor control statements
EXECUTE IMMEDIATE and OPEN-FOR-USING dynamic SQL statements
DBMS_SQL.PARSE()
For all other statements, the privileges of the definer are checked at compile time, and external references are resolved in the schema of the definer. For example, the assignment statement below refers to a packaged function. This external reference is resolved in the schema of the definer of procedure reconcile.
CREATE PROCEDURE reconcile (acc_id IN INTEGER) AUTHID CURRENT_USER AS bal NUMBER; BEGIN bal := bank_stuff.balance(acct_id); ... END;
External references in an invoker-rights routine are resolved in the schema of the invoker at run time. However, the PL/SQL compiler must resolve all references at compile time. So, the definer must create template objects in his or her schema beforehand. At run time, the template objects and the actual objects must match. Otherwise, you get an error or unexpected results.
For example, suppose user scott creates the following database table and stand-alone procedure:
CREATE TABLE emp ( empno NUMBER(4), ename VARCHAR2(15)); / CREATE PROCEDURE evaluate (my_empno NUMBER) AUTHID CURRENT_USER AS my_ename VARCHAR2(15); BEGIN SELECT ename INTO my_ename FROM emp WHERE empno = my_empno; ... END; /
Now, suppose user blake creates a similar database table, then calls procedure evaluate, as follows:
CREATE TABLE emp ( empno NUMBER(4), ename VARCHAR2(15), my_empno NUMBER(4)); -- note extra column / DECLARE ... BEGIN ... scott.evaluate(7788); END; /
The procedure call executes without error but ignores column my_empno in the table created by user blake. That is because the actual table in the schema of the invoker does not match the template table used at compile time.
Occasionally, you might want to override the default invoker-rights behavior. Suppose user scott defines the stand-alone procedure below. Notice that the SELECT statement calls an external function. Normally, this external reference would be resolved in the schema of the invoker.
CREATE PROCEDURE calc_bonus (emp_id INTEGER) AUTHID CURRENT_USER AS mid_sal REAL; BEGIN SELECT median(sal) INTO mid_sal FROM emp; ... END;
To have the reference resolved in the schema of the definer, create a public synonym for the function using the CREATE SYNONYM statement, as follows:
CREATE PUBLIC SYNONYM median FOR scott.median;
This works unless the invoker has defined a function or private synonym named median.
Alternatively, you can fully qualify the reference, as follows:
BEGIN SELECT scott.median(sal) INTO mid_sal FROM emp; ... END;
This works unless the invoker has defined a package named scott that contains a function named median.
To call an invoker-rights routine directly, users must have the EXECUTE privilege on that routine. By granting the privilege, you allow a user to
Suppose user util grants the EXECUTE privilege on invoker-rights routine fft to user app, as follows:
GRANT EXECUTE ON util.fft TO app;
Now, user app can compile functions and procedures that call routine fft. At run time, no privilege checks on the calls are done. So, as Figure 7-5 shows, user util need not grant the EXECUTE privilege to every user who might call fft indirectly.
Notice that routine util.fft is called directly only from definer-rights routine app.entry. So, user util must grant the EXECUTE privilege only to user app. When util.fft is executed, its invoker might be app, scott, or blake.
For invoker-rights routines executed within a view expression, the view owner, not the view user, is considered to be the invoker. For example, suppose user scott creates a view, as follows:
CREATE VIEW payroll AS SELECT layout(3) FROM dual;
The function layout always executes with the privileges of user scott, who is the view owner.
This rule also applies to database triggers.
External references to database links are resolved in the usual way. However, the link determines which user is connected to the remote database.
With named links, the username specified in the link is used. Suppose a routine owned by user blake references the database link below. No matter who calls the routine, it connects to the Chicago database as user scott.
CREATE DATABASE LINK chicago CONNECT TO scott IDENTIFIED BY tiger USING connect_string;
With anonymous links, the session username is used. Suppose a routine owned by user blake references the database link below. If user scott logs on, and then calls the routine, it connects to the Atlanta database as user scott.
CREATE DATABASE LINK atlanta USING connect_string;
With privileged links, the username of the invoker is used. Suppose an invoker-rights routine owned by user blake references the database link below. If global user scott calls the routine, it connects to the Dallas database as user scott, who is the current user.
CREATE DATABASE LINK dallas CONNECT TO CURRENT_USER USING connect_string;
If it were a definer-rights routine, the current user would be blake. So, the routine would connect to the Dallas database as global user blake.
An invoker-rights instance method executes with the privileges of the invoker, not the creator of the instance. Suppose that Person is an invoker-rights object type, and that user scott creates p1, an object of type Person. If user blake calls instance method change_job to operate on object p1, the invoker of the method is blake, not scott. Consider the following example:
-- user blake creates a definer-rights procedure CREATE PROCEDURE reassign (p Person, new_job VARCHAR2) AS BEGIN -- user blake calls method change_job, so the -- method executes with the privileges of blake p.change_job(new_job); END; / -- user scott passes a Person object to the procedure DECLARE p1 Person; BEGIN p1 := Person(...); blake.reassign(p1, 'CLERK'); END; /
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)!
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.
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.
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 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;
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.
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.
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 this sequence of calls:
even(4) odd(3) even(2) odd(1) even(0) -- returns TRUE
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.
