PL/SQL User's Guide and Reference

Contents Index Home Previous Next

Some Examples

Consider the package below named emp_actions. The package specification declares the following types, objects, and subprograms:

After writing the package, you can develop applications that reference its types, call its subprograms, use its cursor, and raise its exception. When you create the package, it is stored in an Oracle database for general use.

CREATE PACKAGE emp_actions AS
   /* Declare externally visible types, cursor, exception. */
   TYPE EmpRecTyp IS RECORD (emp_id INTEGER, salary REAL);
   TYPE DeptRecTyp IS RECORD (dept_id INTEGER, location VARCHAR2);
   CURSOR desc_salary RETURN EmpRecTyp;
   salary_missing EXCEPTION;
 
   /* Declare externally callable subprograms. */
   FUNCTION hire_employee (
      ename  VARCHAR2,
      job    VARCHAR2,
      mgr    NUMBER,
      sal    NUMBER,
      comm   NUMBER,
      deptno NUMBER) RETURN INTEGER;
   PROCEDURE fire_employee (emp_id INTEGER);
   PROCEDURE raise_salary (emp_id INTEGER, increase NUMBER);
   FUNCTION nth_highest_salary (n INTEGER) RETURN EmpRecTyp;
END emp_actions;
 
CREATE PACKAGE BODY emp_actions AS
   number_hired  INTEGER;  -- visible only in this package
 
   /* Fully define cursor specified in package. */
   CURSOR desc_salary RETURN EmpRecTyp IS
      SELECT empno, sal FROM emp ORDER BY sal DESC;
 
   /* Fully define subprograms specified in package. */
   FUNCTION hire_employee (
      ename  VARCHAR2,
      job    VARCHAR2,
      mgr    NUMBER,
      sal    NUMBER,
      comm   NUMBER,
      deptno NUMBER) RETURN INTEGER IS
      new_empno  INTEGER;
   BEGIN
      SELECT empno_seq.NEXTVAL INTO new_empno FROM dual;
      INSERT INTO emp VALUES (new_empno, ename, job,
         mgr, SYSDATE, sal, comm, deptno);
      number_hired := number_hired + 1;
      RETURN new_empno;
   END hire_employee;
 
   PROCEDURE fire_employee (emp_id INTEGER) IS
   BEGIN
      DELETE FROM emp WHERE empno = emp_id;
   END fire_employee;

   PROCEDURE raise_salary (emp_id INTEGER, increase NUMBER) IS
      current_salary NUMBER;
   BEGIN
      SELECT sal INTO current_salary FROM emp
         WHERE empno = emp_id;
      IF current_salary IS NULL THEN
         RAISE salary_missing;
      ELSE
         UPDATE emp SET sal = sal + increase 
            WHERE empno = emp_id;
      END IF;
   END raise_salary;
 
   FUNCTION nth_highest_salary (n INTEGER) RETURN EmpRecTyp IS
      emp_rec  EmpRecTyp;
   BEGIN
      OPEN desc_salary;
      FOR i IN 1..n LOOP
         FETCH desc_salary INTO emp_rec;
      END LOOP;
      CLOSE desc_salary;
      RETURN emp_rec;
   END nth_highest_salary;
 
   /* Define local function, available only in package. */
   FUNCTION rank (emp_id INTEGER, job_title VARCHAR2)
      RETURN INTEGER IS
   /* Return rank (highest = 1) of employee in a given 
      job classification based on performance rating. */
      head_count INTEGER;
      score      NUMBER;
   BEGIN
      SELECT COUNT(*) INTO head_count FROM emp
         WHERE job = job_title;
      SELECT rating INTO score FROM reviews
         WHERE empno = emp_id;
      score := score / 100;  -- maximum score is 100
      RETURN (head_count + 1) - ROUND(head_count * score);
   END rank;

BEGIN  -- initialization part starts here
   INSERT INTO emp_audit VALUES (SYSDATE, USER, 'EMP_ACTIONS');
   number_hired := 0;
END emp_actions;

Remember, the initialization part of a package is run just once, the first time you reference the package. So, in the last example, only one row is inserted into the database table emp_audit. Likewise, the variable number_hired is initialized only once.

Every time the procedure hire_employee is called, the variable number_hired is updated. However, the count kept by number_hired is session specific. That is, the count reflects the number of new employees processed by one user, not the number processed by all users.

In the next example, you package some typical bank transactions. Assume that debit and credit transactions are entered after business hours via automatic teller machines, then applied to accounts the next morning.

CREATE PACKAGE bank_transactions AS
   /* Declare externally visible constant. */
   minimum_balance  CONSTANT NUMBER := 100.00;
 
   /* Declare externally callable procedures. */
   PROCEDURE apply_transactions;
   PROCEDURE enter_transaction (
      acct   NUMBER,
      kind   CHAR,
      amount NUMBER);
END bank_transactions;

CREATE PACKAGE BODY bank_transactions AS
   /* Declare global variable to hold transaction status. */
   new_status  VARCHAR2(70) := 'Unknown';

   /* Use forward declarations because apply_transactions 
      calls credit_account and debit_account, which are not 
      yet declared when the calls are made. */
   PROCEDURE credit_account (acct NUMBER, credit REAL);
   PROCEDURE debit_account (acct NUMBER, debit REAL);
   /* Fully define procedures specified in package. */
   PROCEDURE apply_transactions IS
   /* Apply pending transactions in transactions table 
      to accounts table. Use cursor to fetch rows. */
      CURSOR trans_cursor IS
         SELECT acct_id, kind, amount FROM transactions
            WHERE status = 'Pending'
            ORDER BY time_tag
            FOR UPDATE OF status;  -- to lock rows
   BEGIN
      FOR trans IN trans_cursor LOOP
         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 SET status = new_status
            WHERE CURRENT OF trans_cursor;
      END LOOP;
   END apply_transactions;

   PROCEDURE enter_transaction (
   /* Add a transaction to transactions table. */
      acct   NUMBER,
      kind   CHAR,
      amount NUMBER) IS
   BEGIN
      INSERT INTO transactions
         VALUES (acct, kind, amount, 'Pending', SYSDATE);
   END enter_transaction;

   /* Define local procedures, available only in package. */
   PROCEDURE do_journal_entry (
   /* Record transaction in journal. */
      acct    NUMBER,
      kind    CHAR,
      new_bal NUMBER) IS
   BEGIN
      INSERT INTO journal
         VALUES (acct, kind, new_bal, sysdate);
      IF kind = 'D' THEN
         new_status := 'Debit applied';
      ELSE
         new_status := 'Credit applied';
      END IF;
   END do_journal_entry;
   PROCEDURE credit_account (acct NUMBER, credit REAL) IS
   /* Credit account unless account number is bad. */
      old_balance NUMBER;
      new_balance NUMBER;
   BEGIN
      SELECT balance INTO old_balance FROM accounts
         WHERE acct_id = acct
         FOR UPDATE OF balance;  -- to lock the row
      new_balance := old_balance + credit;
      UPDATE accounts SET balance = new_balance
         WHERE acct_id = acct;
      do_journal_entry(acct, 'C', new_balance);
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         new_status := 'Bad account number';
      WHEN OTHERS THEN
         new_status := SUBSTR(SQLERRM,1,70);
   END credit_account;

   PROCEDURE debit_account (acct NUMBER, debit REAL) IS
   /* Debit account unless account number is bad or 
      account has insufficient funds. */
      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;  -- to lock the row
      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', new_balance);
      ELSE
         RAISE insufficient_funds;
      END IF;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         new_status := 'Bad account number';
      WHEN insufficient_funds THEN
         new_status := 'Insufficient funds';
      WHEN OTHERS THEN
         new_status := SUBSTR(SQLERRM,1,70);
   END debit_account;
END bank_transactions;

In this package, the initialization part is not used.


Contents Index Home Previous Next