For example, the following two statements create the specification and body for a package that contains several procedures and functions that process banking transactions.
CREATE PACKAGE bank_transactions AS
minimum_balance CONSTANT NUMBER := 100.00;
PROCEDURE apply_transactions;
PROCEDURE enter_transaction (acct NUMBER,
kind CHAR,
amount NUMBER);
END bank_transactions;
CREATE PACKAGE BODY bank_transactions AS
/* Package to input bank transactions */
new_status CHAR(20); /* Global variable to record status
of transaction being applied. Used
for update in APPLY_TRANSACTIONS. */
PROCEDURE do_journal_entry (acct NUMBER,
kind CHAR) IS
/* Records a journal entry for each bank transaction applied
by the APPLY_TRANSACTIONS procedure. */
BEGIN
INSERT INTO journal
VALUES (acct, kind, sysdate);
IF kind = 'D' THEN
new_status := 'Debit applied';
ELSIF kind = 'C' THEN
new_status := 'Credit applied';
ELSE
new_status := 'New account';
END IF;
END do_journal_entry;
PROCEDURE credit_account (acct NUMBER, credit NUMBER) IS
/* Credits a bank account the specified amount. If the account
does not exist, the procedure creates a new account first. */
old_balance NUMBER;
new_balance NUMBER;
BEGIN
SELECT balance INTO old_balance FROM accounts
WHERE acct_id = acct
FOR UPDATE OF balance; /* Locks account for credit update */
new_balance := old_balance + credit;
UPDATE accounts SET balance = new_balance
WHERE acct_id = acct;
do_journal_entry(acct, 'C');
EXCEPTION
WHEN NO_DATA_FOUND THEN /* Create new account if not found */
INSERT INTO accounts (acct_id, balance)
VALUES(acct, credit);
do_journal_entry(acct, 'N');
WHEN OTHERS THEN /* Return other errors to application */
new_status := 'Error: ' || SQLERRM(SQLCODE);
END credit_account;
PROCEDURE debit_account (acct NUMBER, debit NUMBER) IS
/* Debits an existing account if result is greater than the
allowed minimum balance. */
old_balance NUMBER;
new_balance NUMBER;
insufficient_funds EXCEPTION;
BEGIN
SELECT balance INTO old_balance FROM accounts
WHERE acct_id = acct
FOR UPDATE OF balance;
new_balance := old_balance - debit;
IF new_balance >= minimum_balance THEN
UPDATE accounts SET balance = new_balance
WHERE acct_id = acct;
do_journal_entry(acct, 'D');
ELSE
RAISE insufficient_funds;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
new_status := 'Nonexistent account';
WHEN insufficient_funds THEN
new_status := 'Insufficient funds';
WHEN OTHERS THEN /* Returns other errors to application */
new_status := 'Error: ' || SQLERRM(SQLCODE);
END debit_account;
PROCEDURE apply_transactions IS
/* Applies pending transactions in the table TRANSACTIONS to the
ACCOUNTS table. Used at regular intervals to update bank
accounts without interfering with input of new transactions. */
/* Cursor fetches and locks all rows from the TRANSACTIONS
table with a status of 'Pending'. Locks released after all pending transactions have been applied. */
CURSOR trans_cursor IS
SELECT acct_id, kind, amount FROM transactions
WHERE status = 'Pending'
ORDER BY time_tag
FOR UPDATE OF status;
BEGIN
FOR trans IN trans_cursor LOOP /* implicit open and fetch */
IF trans.kind = 'D' THEN
debit_account(trans.acct_id, trans.amount);
ELSIF trans.kind = 'C' THEN
credit_account(trans.acct_id, trans.amount);
ELSE
new_status := 'Rejected';
END IF;
/* Update TRANSACTIONS table to return result of applying
this transaction. */
UPDATE transactions SET status = new_status
WHERE CURRENT OF trans_cursor;
END LOOP;
COMMIT; /* Release row locks in TRANSACTIONS table. */
END apply_transactions;
PROCEDURE enter_transaction (acct NUMBER,
kind CHAR,
amount NUMBER) IS
/* Enters a bank transaction into the TRANSACTIONS table. A new
transaction is always input into this 'queue' before being
applied to the specified account by the APPLY_TRANSACTIONS
procedure. Therefore, many transactions can be simultaneously
input without interference. */
BEGIN
INSERT INTO transactions
VALUES (acct, kind, amount, 'Pending', sysdate);
COMMIT;
END enter_transaction;
END bank_transactions;
While packages allow the database administrator or application developer to organize similar routines, they also offer increased functionality and database performance.
Encapsulation of procedural constructs in a package also makes privilege management easier. Granting the privilege to use a package makes all constructs of the package accessible to the grantee.
| public | Directly accessible to the user of a package. |
| private | Hidden from the user of a package. |
Do not confuse public and private package variables with grants to PUBLIC, which are described in Chapter 17, "Database Access".