When you use a n ____ parameter any passed variable must have an assigned value?
Civilization advances by extending the number of important operations that we can perform without thinking about them. Show
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. Major TopicsWhat Are Subprograms?Advantages of SubprogramsProceduresFunctionsRETURN StatementDeclaring SubprogramsActual versus Formal ParametersPositional and Named NotationParameter ModesParameter Default ValuesParameter AliasingOverloadingRecursionWhat 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 raise_salary(emp_num, amount);2, 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 raise_salary(emp_num, amount);3 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 SubprogramsSubprograms provide extensibility; that is, they let you tailor the PL/SQL language to suit your needs. For example, if you need a procedure that creates new departments, you can easily write one, as follows: PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept; Subprograms also provide modularity; that is, they let you break a program down into manageable, well-defined logic modules. This supports top-down design and the stepwise refinement approach to problem solving. Also, subprograms promote reusability and maintainability. Once validated, a subprogram can be used with confidence in any number of applications. Furthermore, only the subprogram is affected if its definition changes. This simplifies maintenance and enhancement. Finally, subprograms aid abstraction, the mental separation from particulars. To use subprograms, you must know what they do, not how they work. Therefore, you can design applications from the top down without worrying about implementation details. Dummy subprograms (stubs) allow you to defer the definition of procedures and functions until you test and debug the main program. ProceduresA 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 raise_salary(emp_num, amount);4 stands for the following syntax: parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expression] You cannot impose the raise_salary(emp_num, amount);5 raise_salary(emp_num, amount);6 constraint on a parameter. Also, you cannot specify a constraint on the datatype. For example, the following declaration of raise_salary(emp_num, amount);7 is illegal because it imposes a size constraint: PROCEDURE raise_salary (emp_id NUMBER(4)) IS ... -- illegal; should be NUMBER A procedure has two parts: the specification and the body. The procedure specification begins with the keyword raise_salary(emp_num, amount);8 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 raise_salary(emp_num, amount);9 and ends with the keyword FUNCTION name [(parameter[, parameter, ...])] RETURN datatype IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];0 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 raise_salary(emp_num, amount);9 and FUNCTION name [(parameter[, parameter, ...])] RETURN datatype IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];2. The keyword FUNCTION name [(parameter[, parameter, ...])] RETURN datatype IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];3, which introduces declarations in an anonymous PL/SQL block, is not used. The executable part contains statements, which are placed between the keywords FUNCTION name [(parameter[, parameter, ...])] RETURN datatype IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];2 and FUNCTION name [(parameter[, parameter, ...])] RETURN datatype IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];5 (or FUNCTION name [(parameter[, parameter, ...])] RETURN datatype IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];0). At least one statement must appear in the executable part of a procedure. The raise_salary(emp_num, amount);6 statement meets this requirement. The exception-handling part contains exception handlers, which are placed between the keywords FUNCTION name [(parameter[, parameter, ...])] RETURN datatype IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];5 and FUNCTION name [(parameter[, parameter, ...])] RETURN datatype IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];0. Consider the procedure parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expression]0, which increases the salary of an employee: PROCEDURE raise_salary (emp_id INTEGER, increase REAL) IS current_salary REAL; salary_missing EXCEPTION; BEGIN SELECT sal INTO current_salary FROM emp WHERE empno = emp_id; IF current_salary IS NULL THEN RAISE salary_missing; ELSE UPDATE emp SET sal = sal + increase WHERE empno = emp_id; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO emp_audit VALUES (emp_id, 'No such number'); WHEN salary_missing THEN INSERT INTO emp_audit VALUES (emp_id, 'Salary is null'); END raise_salary; When called, this procedure accepts an employee number and a salary increase amount. It uses the employee number to select the current salary from the parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expression]1 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 parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expression]0 as follows: raise_salary(emp_num, amount); FunctionsA function is a subprogram that computes a value. Functions and procedures are structured alike, except that functions have a parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expression]3 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 raise_salary(emp_num, amount);4 stands for the following syntax: parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expression] Remember, you cannot impose the raise_salary(emp_num, amount);5 raise_salary(emp_num, amount);6 constraint on a parameter, and you cannot specify a constraint on the datatype. Like a procedure, a function has two parts: the specification and the body. The function specification begins with the keyword parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expression]7 and ends with the parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expression]3 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 raise_salary(emp_num, amount);9 and ends with the keyword FUNCTION name [(parameter[, parameter, ...])] RETURN datatype IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];0 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 raise_salary(emp_num, amount);9 and FUNCTION name [(parameter[, parameter, ...])] RETURN datatype IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];2. The keyword FUNCTION name [(parameter[, parameter, ...])] RETURN datatype IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];3 is not used. The executable part contains statements, which are placed between the keywords FUNCTION name [(parameter[, parameter, ...])] RETURN datatype IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];2 and FUNCTION name [(parameter[, parameter, ...])] RETURN datatype IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];5 (or FUNCTION name [(parameter[, parameter, ...])] RETURN datatype IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];0). One or more parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expression]3 statements must appear in the executable part of a function. The exception-handling part contains exception handlers, which are placed between the keywords FUNCTION name [(parameter[, parameter, ...])] RETURN datatype IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];5 and FUNCTION name [(parameter[, parameter, ...])] RETURN datatype IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];0. Consider the function PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;00, 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 PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;01 database table. The function identifier, PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;00, is set to a Boolean value by the parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expression]3 statement. If the salary is out of range, PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;00 is set to PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;05; otherwise, PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;00 is set to PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;07. A function is called as part of an expression. For example, the function PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;00 might be called as follows: PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;0 The function identifier acts like a variable whose value depends on the parameters passed to it. RestrictionTo be callable from SQL expressions, a stored function must obey certain rules meant to control side effects. For stand-alone functions, Oracle can enforce these rules by checking the function body. However, the body of a packaged function is hidden. So, for packaged functions, you must use the pragma PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;09 to enforce the rules. For more information, see Oracle8 Application Developer's Guide. RETURN StatementThe parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expression]3 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 parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expression]3 statement with the parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expression]3 clause, which specifies the datatype of the result value in a function specification.) A subprogram can contain several parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expression]3 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 parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expression]3 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 parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expression]3 statement must contain an expression, which is evaluated when the parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expression]3 statement is executed. The resulting value is assigned to the function identifier, which acts like a variable of the type specified in the parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expression]3 clause. Observe how the function PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;18 returns the balance of a specified bank account: PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;1 The following example shows that the expression in a function parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expression]3 statement can be arbitrarily complex: PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;2 A function must contain at least one parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expression]3 statement. Otherwise, PL/SQL raises the predefined exception PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;21 at run time. Declaring SubprogramsYou can declare subprograms in any PL/SQL block, subprogram, or package. However, you must declare subprograms at the end of a declarative section after all other program items. For example, the following procedure declaration is misplaced: PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;3 Forward DeclarationsPL/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 PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;22 is illegal because PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;22 calls procedure PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;24, which is not yet declared when the call is made: PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;4 In this case, you can solve the problem easily by placing procedure PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;24 before procedure PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;22. However, the easy solution does not always work. For example, suppose the procedures are mutually recursive (call each other) or you want to define them in alphabetical order. PL/SQL solves this problem by providing a special subprogram declaration called a forward declaration. You can use forward declarations to
A forward declaration consists of a subprogram specification terminated by a semicolon. In the following example, the forward declaration advises PL/SQL that the body of procedure PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;24 can be found later in the block: PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;5 Although the formal parameter list appears in the forward declaration, it must also appear in the subprogram body. You can place the subprogram body anywhere after the forward declaration, but they must appear in the same program unit. In PackagesForward declarations also let you group logically related subprograms in a package. The subprogram specifications go in the package specification, and the subprogram bodies go in the package body, where they are invisible to applications. Thus, packages allow you to hide implementation details. An example follows: PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;6 You can define subprograms in a package body without declaring their specifications in the package specification. However, such subprograms can be called only from inside the package. For more information about packages, see Chapter 8. Stored SubprogramsGenerally, tools (such as Oracle Forms) that incorporate the PL/SQL engine can store subprograms locally for later, strictly local execution. However, to become available for general use by all tools, subprograms must be stored in an Oracle database. To create subprograms and store them permanently in an Oracle database, you use the PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;28 raise_salary(emp_num, amount);8 and PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;28 parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expression]7 statements, which you can execute interactively from SQL*Plus or Enterprise Manager. For example, you might create the procedure PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;32, as follows: PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;7 When creating subprograms, you can use the keyword PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;33 instead of raise_salary(emp_num, amount);9 in the specification for readability. For more information about creating and using stored subprograms, see Oracle8 Application Developer's Guide. Actual versus Formal ParametersSubprograms 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 PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;35 and PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;36: PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;8 The next procedure call shows that expressions can be used as actual parameters: PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;9 The variables declared in a subprogram specification and referenced in the subprogram body are formal parameters. For example, the following procedure declares two formal parameters named raise_salary(emp_num, amount);7 and PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;38: PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];0 A good programming practice is to use different names for actual and formal parameters. When you call procedure parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expression]0, the actual parameters are evaluated and the result values are assigned to the corresponding formal parameters. Before assigning the value of an actual parameter to a formal parameter, PL/SQL converts the datatype of the value if necessary. For example, the following call to parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expression]0 is legal: PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];1 The actual parameter and its corresponding formal parameter must have compatible datatypes. For instance, PL/SQL cannot convert between the PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;41 and PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;42 datatypes. Also, the result value must be convertible to the new datatype. The following procedure call raises the predefined exception PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;43 because PL/SQL cannot convert the second actual parameter to a number: PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];2 For more information, see "Datatype Conversion". Positional and Named NotationWhen calling a subprogram, you can write the actual parameters using either positional or named notation. That is, you can indicate the association between an actual and formal parameter by position or name. For example, given the declarations PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];3 you can call the procedure PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;44 in four logically equivalent ways: PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];4 Positional NotationThe first procedure call uses positional notation. The PL/SQL compiler associates the first actual parameter, PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;45, with the first formal parameter, PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;46. And, the compiler associates the second actual parameter, PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;47, with the second formal parameter, PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;36. Named NotationThe second procedure call uses named notation. An arrow ( PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;49) 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. Therefore, you need not know the order in which the formal parameters are listed. Mixed NotationThe 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: PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];5 Parameter ModesYou use parameter modes to define the behavior of formal parameters. The three parameter modes, PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;50 (the default), PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;51, and PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;50 PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;51, can be used with any subprogram. However, avoid using the PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;51 and PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;50 PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;51 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. IN ModeAn PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;50 parameter lets you pass values to the subprogram being called. Inside the subprogram, an PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;50 parameter acts like a constant. Therefore, it cannot be assigned a value. For example, the following assignment statement causes a compilation error: PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];6 The actual parameter that corresponds to an PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;50 formal parameter can be a constant, literal, initialized variable, or expression. Unlike PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;51 and PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;50 PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;51 parameters, PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;50 parameters can be initialized to default values. For more information, see "Parameter Default Values". OUT ModeAn PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;51 parameter lets you return values to the caller of a subprogram. Inside the subprogram, an PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;51 parameter acts like an uninitialized variable. Therefore, its value cannot be assigned to another variable or reassigned to itself. For instance, the following assignment statement causes a compilation error: PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];7 The actual parameter that corresponds to an PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;51 formal parameter must be a variable; it cannot be a constant or an expression. For example, the following procedure call is illegal: PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];8 An PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;51 actual parameter can have a value before the subprogram is called. However, the value is lost when you call the subprogram. Inside the subprogram, an PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;51 formal parameter cannot be used in an expression; the only operation allowed on the parameter is to assign it a value. Like variables, PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;51 formal parameters are initialized to raise_salary(emp_num, amount);6. So, before exiting a subprogram, explicitly assign values to all PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;51 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. IN OUT ModeAn PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;50 PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;51 parameter lets you pass initial values to the subprogram being called and return updated values to the caller. Inside the subprogram, an PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;50 PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;51 parameter acts like an initialized variable. Therefore, it can be assigned a value and its value can be assigned to another variable. That means you can use an PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;50 PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;51 formal parameter as if it were a normal variable. You can change its value or reference the value in any way, as the following example shows: PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];9 The actual parameter that corresponds to an PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;50 PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;51 formal parameter must be a variable; it cannot be a constant or an expression. Table 7-1 summarizes all you need to know about the parameter modes. Table 7-1 Parameter ModesIN OUT IN OUTthe default must be specified must be specified passes values to a subprogram returns values to the caller passes initial values to a subprogram and returns updated values to the caller formal parameter acts like a constant formal parameter acts like an uninitialized variable formal parameter acts like an initialized variable formal parameter cannot be assigned a value formal parameter cannot be used in an expression and must be assigned a value formal parameter should be assigned a value actual parameter can be a constant, initialized variable, literal, or expression actual parameter must be a variable actual parameter must be a variable 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) actual parameter is passed by value (a copy of the value is passed in and out) Parameter Default ValuesAs the example below shows, you can initialize PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;50 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. parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expression]0 If an actual parameter is not passed, the default value of its corresponding formal parameter is used. Consider the following calls to PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;81: parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expression]1 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 PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;82 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 PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;83 with the formal parameter PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;84: parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expression]2 You cannot solve the problem by leaving a placeholder for the actual parameter. For example, the following call is illegal: parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expression]3 In such cases, you must use named notation, as follows: parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expression]4 Also, you cannot assign a null to an uninitialized formal parameter by leaving out its actual parameter. For example, given the declaration parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expression]5 the following function call does not assign a null to PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;85: parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expression]6 Instead, you must pass the null explicitly, as in parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expression]7 or you can initialize PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;85 to raise_salary(emp_num, amount);6, as follows: parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expression]8 Finally, when creating a stored subprogram, you cannot use bind variables in the PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;88 clause. The following SQL*Plus example raises a bad bind variable exception because at the time of creation, PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;89 is just a placeholder whose value might change: parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expression]9 Parameter AliasingTo 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. Passing large composite types by value is inefficient. So, in most cases-but never across client/server boundaries-PL/SQL passes composite types by reference, which saves time. The easy-to-avoid problem of aliasing occurs when a global variable appears as an actual parameter in a subprogram call and then is referenced within the subprogram. The result is indeterminate because it depends on the method of parameter passing chosen by the compiler. In the example below, procedure PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;90 refers to varray PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;91 in two different ways: as a parameter and as a global variable. So, when PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;90 is called, the identifiers PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;93 and PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;91 name the same varray. PROCEDURE raise_salary (emp_id NUMBER(4)) IS ... -- illegal; should be NUMBER0 The result depends on the method of parameter passing chosen by the compiler. If the compiler chooses the by-value method, PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;93 and PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;91 are separate copies of the same varray. So, changing one does not affect the other. But, if the compiler chooses the by-reference method, PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;93 and PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;91 are just different names for the same varray. So, changing the value of PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;99 also changes the value of PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];00. OverloadingPL/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: PROCEDURE raise_salary (emp_id NUMBER(4)) IS ... -- illegal; should be NUMBER1 You might write the following procedure to initialize the index-by table named PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];01: PROCEDURE raise_salary (emp_id NUMBER(4)) IS ... -- illegal; should be NUMBER2 And, you might write the next procedure to initialize the index-by table named PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];02: PROCEDURE raise_salary (emp_id NUMBER(4)) IS ... -- illegal; should be NUMBER3 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 PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];03 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 PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];03 with a PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];05 parameter, PL/SQL uses the first version of PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];03. But, if you call PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];03 with a PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];08 parameter, PL/SQL uses the second version. PROCEDURE raise_salary (emp_id NUMBER(4)) IS ... -- illegal; should be NUMBER4 RestrictionsOnly 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 raise_salary (emp_id NUMBER(4)) IS ... -- illegal; should be NUMBER5 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 PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];09 and PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;42 are in the same family: PROCEDURE raise_salary (emp_id NUMBER(4)) IS ... -- illegal; should be NUMBER6 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 PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];11 and PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];12 are in the same family: PROCEDURE raise_salary (emp_id NUMBER(4)) IS ... -- illegal; should be NUMBER7 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: PROCEDURE raise_salary (emp_id NUMBER(4)) IS ... -- illegal; should be NUMBER8 How Calls Are ResolvedFigure 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 PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];13 from within the function PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];14. However, the compiler generates an error because neither declaration of PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];13 within the current scope matches the procedure call: PROCEDURE raise_salary (emp_id NUMBER(4)) IS ... -- illegal; should be NUMBER9 Avoiding ErrorsPL/SQL declares built-in functions globally in package PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];16. 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 PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];17, then within the scope of that declaration, try to call the built-in function PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];18: PROCEDURE raise_salary (emp_id INTEGER, increase REAL) IS current_salary REAL; salary_missing EXCEPTION; BEGIN SELECT sal INTO current_salary FROM emp WHERE empno = emp_id; IF current_salary IS NULL THEN RAISE salary_missing; ELSE UPDATE emp SET sal = sal + increase WHERE empno = emp_id; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO emp_audit VALUES (emp_id, 'No such number'); WHEN salary_missing THEN INSERT INTO emp_audit VALUES (emp_id, 'Salary is null'); END raise_salary;0 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: PROCEDURE raise_salary (emp_id INTEGER, increase REAL) IS current_salary REAL; salary_missing EXCEPTION; BEGIN SELECT sal INTO current_salary FROM emp WHERE empno = emp_id; IF current_salary IS NULL THEN RAISE salary_missing; ELSE UPDATE emp SET sal = sal + increase WHERE empno = emp_id; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO emp_audit VALUES (emp_id, 'No such number'); WHEN salary_missing THEN INSERT INTO emp_audit VALUES (emp_id, 'Salary is null'); END raise_salary;1 RecursionRecursion 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: PROCEDURE raise_salary (emp_id INTEGER, increase REAL) IS current_salary REAL; salary_missing EXCEPTION; BEGIN SELECT sal INTO current_salary FROM emp WHERE empno = emp_id; IF current_salary IS NULL THEN RAISE salary_missing; ELSE UPDATE emp SET sal = sal + increase WHERE empno = emp_id; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO emp_audit VALUES (emp_id, 'No such number'); WHEN salary_missing THEN INSERT INTO emp_audit VALUES (emp_id, 'Salary is null'); END raise_salary;2 Recursive SubprogramsA 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 PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];19 loop or between PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];20 and PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];21 statements, another cursor is opened at each call. As a result, your program might exceed the limit set by the Oracle initialization parameter PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];22. There must be at least two paths through a recursive subprogram: one that leads to the recursive call and one that does not. That is, at least one path must lead to a terminating condition. Otherwise, the recursion would (theoretically) go on forever. In practice, if a recursive subprogram strays into infinite regress, PL/SQL eventually runs out of memory and raises PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];23. Example 1To 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: PROCEDURE raise_salary (emp_id INTEGER, increase REAL) IS current_salary REAL; salary_missing EXCEPTION; BEGIN SELECT sal INTO current_salary FROM emp WHERE empno = emp_id; IF current_salary IS NULL THEN RAISE salary_missing; ELSE UPDATE emp SET sal = sal + increase WHERE empno = emp_id; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO emp_audit VALUES (emp_id, 'No such number'); WHEN salary_missing THEN INSERT INTO emp_audit VALUES (emp_id, 'Salary is null'); END raise_salary;3 To implement this algorithm, you might write the following recursive function, which returns the factorial of a positive integer: PROCEDURE raise_salary (emp_id INTEGER, increase REAL) IS current_salary REAL; salary_missing EXCEPTION; BEGIN SELECT sal INTO current_salary FROM emp WHERE empno = emp_id; IF current_salary IS NULL THEN RAISE salary_missing; ELSE UPDATE emp SET sal = sal + increase WHERE empno = emp_id; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO emp_audit VALUES (emp_id, 'No such number'); WHEN salary_missing THEN INSERT INTO emp_audit VALUES (emp_id, 'Salary is null'); END raise_salary;4 At each recursive call, PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];24 is decremented. Eventually, PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];24 becomes 1 and the recursion stops. Example 2Consider the procedure below, which finds the staff of a given manager. The procedure declares two formal parameters, PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];26 and PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];27, which represent the manager's employee number and a tier in his or her departmental organization. Staff members reporting directly to the manager occupy the first tier. When called, the procedure accepts a value for PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];26 but uses the default value of PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];27. For example, you might call the procedure as follows: PROCEDURE raise_salary (emp_id INTEGER, increase REAL) IS current_salary REAL; salary_missing EXCEPTION; BEGIN SELECT sal INTO current_salary FROM emp WHERE empno = emp_id; IF current_salary IS NULL THEN RAISE salary_missing; ELSE UPDATE emp SET sal = sal + increase WHERE empno = emp_id; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO emp_audit VALUES (emp_id, 'No such number'); WHEN salary_missing THEN INSERT INTO emp_audit VALUES (emp_id, 'Salary is null'); END raise_salary;5 The procedure passes PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];26 to a cursor in a cursor PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];19 loop, which finds staff members at successively lower tiers in the organization. At each recursive call, a new instance of the PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];19 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 PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];19 loop is exited. Since the recursive call is inside the PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];19 loop, the recursion stops. PROCEDURE raise_salary (emp_id INTEGER, increase REAL) IS current_salary REAL; salary_missing EXCEPTION; BEGIN SELECT sal INTO current_salary FROM emp WHERE empno = emp_id; IF current_salary IS NULL THEN RAISE salary_missing; ELSE UPDATE emp SET sal = sal + increase WHERE empno = emp_id; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO emp_audit VALUES (emp_id, 'No such number'); WHEN salary_missing THEN INSERT INTO emp_audit VALUES (emp_id, 'Salary is null'); END raise_salary;6 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: PROCEDURE raise_salary (emp_id INTEGER, increase REAL) IS current_salary REAL; salary_missing EXCEPTION; BEGIN SELECT sal INTO current_salary FROM emp WHERE empno = emp_id; IF current_salary IS NULL THEN RAISE salary_missing; ELSE UPDATE emp SET sal = sal + increase WHERE empno = emp_id; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO emp_audit VALUES (emp_id, 'No such number'); WHEN salary_missing THEN INSERT INTO emp_audit VALUES (emp_id, 'Salary is null'); END raise_salary;7 The SQL statement is appreciably faster. However, the procedure is more flexible. For example, a multi-table query cannot contain the PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];35 PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];36 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. Mutual RecursionSubprograms are mutually recursive if they directly or indirectly call each other. In the example below, the Boolean functions PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];37 and PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];38, which determine whether a number is odd or even, call each other directly. The forward declaration of PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];37 is necessary because PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];38 calls PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];37, which is not yet declared when the call is made. (See "Forward Declarations".) PROCEDURE raise_salary (emp_id INTEGER, increase REAL) IS current_salary REAL; salary_missing EXCEPTION; BEGIN SELECT sal INTO current_salary FROM emp WHERE empno = emp_id; IF current_salary IS NULL THEN RAISE salary_missing; ELSE UPDATE emp SET sal = sal + increase WHERE empno = emp_id; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO emp_audit VALUES (emp_id, 'No such number'); WHEN salary_missing THEN INSERT INTO emp_audit VALUES (emp_id, 'Salary is null'); END raise_salary;8 When a positive integer PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];24 is passed to PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];37 or PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];38, the functions call each other by turns. At each call, PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];24 is decremented. Ultimately, PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];24 becomes zero and the final call returns PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;07 or PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;05. For instance, passing the number 4 to PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];37 results in this sequence of calls: PROCEDURE raise_salary (emp_id INTEGER, increase REAL) IS current_salary REAL; salary_missing EXCEPTION; BEGIN SELECT sal INTO current_salary FROM emp WHERE empno = emp_id; IF current_salary IS NULL THEN RAISE salary_missing; ELSE UPDATE emp SET sal = sal + increase WHERE empno = emp_id; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO emp_audit VALUES (emp_id, 'No such number'); WHEN salary_missing THEN INSERT INTO emp_audit VALUES (emp_id, 'Salary is null'); END raise_salary;9 On the other hand, passing the number 4 to PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];38 results in the following sequence of calls: raise_salary(emp_num, amount);0 Recursion versus IterationUnlike 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: raise_salary(emp_num, amount);1 The recursive version of PROCEDURE name [(parameter[, parameter, ...])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];51 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. When you use a n ____ parameter The argument used to call the method must have an assigned value?Terms in this set (12) When you use a reference parameter, any passed variable must have an assigned value.
What type of parameter to a method will have a value automatically?What type of parameter to a method will have a value automatically supplied if you do not explicitly send one as an argument? To ensure that the original value of an argument passed to a method remains unchanged after returning from the method, you should use a reference parameter or an output parameter.
What is the name for a variable that holds data passed to a method when it is called?What is the name for a variable that holds data passed to a method when it is called? parameter.
When an argument is passed by value quizlet?When an argument is passed by value, the method has a copy of the argument, but does not have access to the original.
|