How to Write Procedure and Function in PLSQL

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

  1. PLSQL Subprogram
  2. PLSQL Procedure /or Function

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

Author: Srini

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