Packages in Oracle with examples

In Oracle, a package is a schema object that groups logically related PL/SQL types, variables, constants, subprograms (procedures and functions), cursors, and exceptions. Packages have two parts: the specification and the body.

• Package Specification: Declares the public items that can be referenced from outside the package. These include types, variables, constants, exceptions, cursors, and subprograms.
• Package Body: Defines the public subprograms declared in the specification and can also contain private elements that are not accessible outside the package.

Benefits of Using Packages

1. Modularity: Packages help organize related procedures and functions together.
2. Encapsulation: Packages can hide implementation details and expose only necessary components.
3. Reusability: Commonly used procedures and functions can be reused across different applications.
4. Performance: Packages can improve performance by loading all objects in the package into memory at once.

Example of Creating a Package

Package Specification

Let's create a simple package for managing employee records. The specification declares two procedures: add_employee and remove_employee.

CREATE OR REPLACE PACKAGE emp_pkg AS
  PROCEDURE add_employee(p_emp_id NUMBER, p_emp_name VARCHAR2);
  PROCEDURE remove_employee(p_emp_id NUMBER);
END emp_pkg;
/
Package Body

Now, let's define the body of the package, where the logic for add_employee and remove_employee is implemented.

CREATE OR REPLACE PACKAGE BODY emp_pkg AS

  PROCEDURE add_employee(p_emp_id NUMBER, p_emp_name VARCHAR2) IS
  BEGIN
    INSERT INTO employees (emp_id, emp_name)
    VALUES (p_emp_id, p_emp_name);
  END add_employee;

  PROCEDURE remove_employee(p_emp_id NUMBER) IS
  BEGIN
    DELETE FROM employees
    WHERE emp_id = p_emp_id;
  END remove_employee;

END emp_pkg;
/

Using the Package

After creating the package, you can use its procedures like this:

BEGIN
  emp_pkg.add_employee(1, 'John Doe');
  emp_pkg.remove_employee(1);
END;
/

More Advanced Example

Let's create a more advanced package that includes a function to get an employee's name by their ID and a cursor to list all employees.

Package Specification

CREATE OR REPLACE PACKAGE advanced_emp_pkg AS
  PROCEDURE add_employee(p_emp_id NUMBER, p_emp_name VARCHAR2);
  PROCEDURE remove_employee(p_emp_id NUMBER);
  FUNCTION get_employee_name(p_emp_id NUMBER) RETURN VARCHAR2;
  CURSOR all_employees IS SELECT emp_id, emp_name FROM employees;
END advanced_emp_pkg;
/

Package Body

CREATE OR REPLACE PACKAGE BODY advanced_emp_pkg AS

  PROCEDURE add_employee(p_emp_id NUMBER, p_emp_name VARCHAR2) IS
  BEGIN
    INSERT INTO employees (emp_id, emp_name)
    VALUES (p_emp_id, p_emp_name);
  END add_employee;

  PROCEDURE remove_employee(p_emp_id NUMBER) IS
  BEGIN
    DELETE FROM employees
    WHERE emp_id = p_emp_id;
  END remove_employee;

  FUNCTION get_employee_name(p_emp_id NUMBER) RETURN VARCHAR2 IS
    v_emp_name VARCHAR2(100);
  BEGIN
    SELECT emp_name INTO v_emp_name
    FROM employees
    WHERE emp_id = p_emp_id;
    RETURN v_emp_name;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RETURN NULL;
  END get_employee_name;

END advanced_emp_pkg;
/

Using the Advanced Package

You can use the advanced package like this:

-- Add an employee
BEGIN
  advanced_emp_pkg.add_employee(2, 'Jane Smith');
END;
/

-- Get employee name
DECLARE
  v_name VARCHAR2(100);
BEGIN
  v_name := advanced_emp_pkg.get_employee_name(2);
  DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_name);
END;
/

-- List all employees
DECLARE
  v_emp_id employees.emp_id%TYPE;
  v_emp_name employees.emp_name%TYPE;
BEGIN
  FOR r IN advanced_emp_pkg.all_employees LOOP
    v_emp_id := r.emp_id;
    v_emp_name := r.emp_name;
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_emp_id || ', Employee Name: ' || v_emp_name);
  END LOOP;
END;
/

Summary

• Package Specification: Declares the public elements (procedures, functions, cursors, etc.).
• Package Body: Defines the logic of the public elements declared in the specification and can contain private elements.
• Usage: Invoke package procedures and functions using dot notation (package_name.procedure_name).

Packages help modularize code, encapsulate logic, and improve performance by grouping related PL/SQL elements together.