Here’s a way to write procedures/functions in PL/SQL. In Oracle, named PLSQL procedures/functions are called sub-programs. The difference between these procedures do an action, whereas functions compute a value.
IN THIS PAGE
What’s Sub-program?
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
