Oracle Features Deep Dive: Examples

Module: Database-Specific Features

Banking Transaction Processing with PL/SQL Package

advanced

Banks process millions of transactions daily. Each transaction requires: validate accounts, check balance, deduct from source, add to destination, log transaction, handle errors. PL/SQL packages encapsulate this logic with error handling, providing 3-5x better performance than ad-hoc SQL.

-- Banking transaction package

CREATE OR REPLACE PACKAGE banking_transactions AS

-- Public procedures

PROCEDURE transfer_money(

p_from_account IN NUMBER,

p_to_account IN NUMBER,

p_amount IN NUMBER,

p_transaction_id OUT NUMBER,

p_status OUT VARCHAR2

);

FUNCTION get_account_balance(p_account_id IN NUMBER) RETURN NUMBER;

-- Public constants

c_min_balance CONSTANT NUMBER := 100;

c_max_transfer CONSTANT NUMBER := 100000;

END banking_transactions;

CREATE OR REPLACE PACKAGE BODY banking_transactions AS

-- Private procedure (not visible outside)

PROCEDURE log_transaction(

p_transaction_id IN NUMBER,

p_status IN VARCHAR2,

p_message IN VARCHAR2

) AS

BEGIN

INSERT INTO transaction_log (transaction_id, status, message, log_date)

VALUES (p_transaction_id, p_status, p_message, SYSDATE);

END log_transaction;