Here is an example for PLSQL Subprogram. The subprograms in Oracle are named-PLSQL block (procedures and functions).
The differences are the procedure performs an action, and a function computes a value.
IN THIS PAGE
PLSQL Subprogram
Like unnamed (anonymous) PL/SQL blocks, subprograms have three parts. These are 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 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.
PLSQL Procedure /or Function
Below you’ll find 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;How it works
When invoked, it accepts an account number and a debit amount. It uses the account number to select the account balance from the accts 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 