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, we use a procedure to perform an action and a function to compute a value.
Subprograms
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:
Sample Procedure
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.
Related
Srinimf 