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.
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 Subprograms
Subprograms provide extensibility; that is, they let you tailor the PL/SQL language to suit your needs. For example, if you need a procedure that creates new departments, you can easily write one, as follows:
PROCEDURE create_dept [new_dname CHAR, new_loc CHAR] IS
BEGIN
INSERT INTO dept
VALUES [deptno_seq.NEXTVAL, new_dname, new_loc];
END create_dept;
Subprograms also provide modularity; that is, they let you break a program down into manageable, well-defined logic modules. This supports top-down design and the stepwise refinement approach to problem solving.
Also, subprograms promote reusability and maintainability. Once validated, a subprogram can be used with confidence in any number of applications. Furthermore, only the subprogram is affected if its definition changes. This simplifies maintenance and enhancement.
Finally, subprograms aid abstraction, the mental separation from particulars. To use subprograms, you must know what they do, not how they work. Therefore, you can design applications from the top down without worrying about implementation details. Dummy subprograms [stubs] allow you to defer the definition of procedures and functions until you test and debug the main program.
Procedures
A procedure is a subprogram that performs a specific action. You write procedures using the syntax
PROCEDURE name [[parameter[, parameter, ...]]] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];
where
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];
Functions
A 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 syntaxFUNCTION 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