Amazing Facts Subprograms in PLSQL

Subprograms are named PL/SQL blocks. PLSQL has two types of subprograms called procedures and functions. Procedure performs an action and a function computes 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 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;


SELECT bal INTO old_balance FROM accts

WHERE acct_no = acct_id;

new_balance := old_balance – amount;

IF new_balance < 0 THEN

RAISE overdrawn;


UPDATE accts SET bal = new_balance WHERE acct_no = acct_id;



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.


Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.