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
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.