PL/SQL is the foundation of Oracle application development. Banking systems use PL/SQL procedures for transaction processing (100M+ transactions/day). Oracle ERP systems have 50K+ PL/SQL procedures for business logic. E-commerce platforms use functions for pricing calculations. Insurance companies use exception handling for claim validation. Understanding PL/SQL basics is essential for any Oracle developer role.

Banking Transaction Processing with PL/SQL

**Company**: Major Bank

**Challenge**: Process 100M+ transactions daily with ACID guarantees. Each transaction requires: validate accounts, check balance, deduct from source, add to destination, log transaction. Doing this with separate SQL statements requires 5+ round trips (500ms). Need sub-100ms response time.

**Solution**: PL/SQL procedure encapsulates all logic in single call. Exception handling ensures atomic operations (all or nothing). FOR UPDATE locks rows to prevent race conditions. RETURNING clause gets generated transaction ID. Procedure compiled and cached (3x faster than ad-hoc SQL).

**Results**: Transaction processing time reduced from 500ms to 150ms (3.3x faster). 100% ACID compliance with exception handling. Zero data corruption in 3 years. Procedures handle 100M transactions/day. Network round trips reduced from 5 to 1 (80% reduction).

-- Banking transfer procedure

CREATE OR REPLACE 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

) AS

v_from_balance NUMBER;

insufficient_funds EXCEPTION;

BEGIN

-- Lock and check source account

SELECT balance INTO v_from_balance

FROM accounts

WHERE account_id = p_from_account

FOR UPDATE;

IF v_from_balance < p_amount THEN

RAISE insufficient_funds;

END IF;

-- Deduct from source

UPDATE accounts SET balance = balance - p_amount

WHERE account_id = p_from_account;

-- Add to destination

UPDATE accounts SET balance = balance + p_amount

WHERE account_id = p_to_account;

-- Log transaction

INSERT INTO transactions (from_account, to_account, amount, status)

VALUES (p_from_account, p_to_account, p_amount, 'COMPLETED')

RETURNING transaction_id INTO p_transaction_id;

COMMIT;

p_status := 'SUCCESS';

EXCEPTION

WHEN insufficient_funds THEN

ROLLBACK;