PL/SQL Sub-program Vs. Stored Procedure: Best Examples

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

  1. What’s Sub-program?
  2. PLSQL Procedure /or Function

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

Author: Srini

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