Packages are unique to Oracle relational database management systems. Packages in the simplest terms are a group of related objects all bundled together. It is common to see that these objects such as procedures, functions, data types, etc. all have related business functionality. For instance, a business can have different functions and procedures to get or update customer names, emails, addresses, and basically other details. Since they are all related to customer information, we can basically group them into a package as they all have similar business functionalities.

There are several reasons to use packages in a database, some of them are:

1. Packages provide an easy way to grant privileges. That is, a role can be granted on a package instead of having to grant the necessary privileges on each object in the package.
2. Packages can foster application design: Developers can have the specifications and use that as a guide to develop applications while the package body is still being written. Bear in mind that the package will have to be completed before the application can go live.
3. Packages provide modularity: Similar database objects (cursors, custom types, data types, procedures etc) are encapsulated into a single package. This makes it easier to understand and consume by developers.
4. It minimizes the need to recompile code on the database.
5. The database loads the package into memory when it is first invoked. Subsequent calls to the package will be from the memory leading to better performance.

The above reasons are not exhaustive but are a good starting point.

Let's get quite technical

Oracle docs define a package as a schema object that groups logically related PL/SQL types, variables, constants, subprograms, cursors, and exceptions. A package is compiled and stored in the database, where many applications can share its contents.

A PL/SQL package has two (2) parts:

1. The Package Specification
2. The Package Body

The package specification contains the declaration of all public items. The public items' scope is the schema of the package and can be accessed everywhere within the schema.

Appropriate public items are types, variables, constants, subprograms, cursors, and exceptions used by multiple subprograms. Other examples of public items are overloaded subprograms and subprograms that call each other. Let's create a simple package with the functions and procedures created in this series. The below code snippet shows the package specification.

-- Package specification example
CREATE OR REPLACE PACKAGE HR_EMPLOYEE_PKG AS

  PROCEDURE CALC_BONUS (date_hired hr.employees.hire_date%TYPE);

  PROCEDURE GET_EMP_NAME (v_id IN NUMBER, v_emp_name OUT VARCHAR2);

  -- overloaded procedure (takes in varchar as the input)
  -- returns the first employee with a similar first name sorted by employee_id
  PROCEDURE GET_EMP_NAME(v_first_name IN VARCHAR2, v_emp_name OUT VARCHAR2);

  FUNCTION GET_EMP_EMAIL(v_id IN NUMBER) RETURN VARCHAR2;

END HR_EMPLOYEE_PKG;

The package body is only required when subprograms and cursors are declared in the package specification. Subprograms are basically blocks of code that can be executed repeatedly like functions and procedures. For each subprogram or cursor in the package specification, there must be a corresponding definition in the package body with the headings matching exactly both in the body and the specification. Hence, the package body for HR_EMPLOYEE_PKG we are creating will be:

CREATE OR REPLACE PACKAGE BODY HR_EMPLOYEE_PKG AS

    PROCEDURE calc_bonus (date_hired hr.employees.hire_date%TYPE) IS
      BEGIN
        DBMS_OUTPUT.PUT_LINE('Employees hired on ' || date_hired || ' get bonus.');
      END;

    PROCEDURE GET_EMP_NAME(v_id IN NUMBER, v_emp_name OUT VARCHAR2)
    IS  
        l_name VARCHAR2(20);
    BEGIN
        SELECT first_name || ' ' || last_name
        INTO l_name
        FROM hr.employees
        WHERE employee_id = v_id;

        v_emp_name := l_name;

        EXCEPTION WHEN OTHERS THEN
        v_emp_name := 'exception block, employee does not exist';
    END GET_EMP_NAME;

    -- overloaded procedure
    PROCEDURE GET_EMP_NAME(v_first_name IN VARCHAR2, v_emp_name OUT VARCHAR2)
        IS  
            l_name VARCHAR2(20);
        BEGIN
            SELECT full_name INTO l_name 
            FROM (
                SELECT employee_id, first_name || ' ' || last_name full_name
                --INTO l_name
                FROM hr.employees
                WHERE lower(first_name) like '%lex%'-- '%'||lower(v_name)||'%'
                ORDER BY employee_id
                )
            WHERE ROWNUM < 2;

            v_emp_name := l_name;

        EXCEPTION WHEN OTHERS THEN
            v_emp_name := 'exception block, employee does not exist';
    END GET_EMP_NAME;


    FUNCTION get_emp_email(v_id IN NUMBER)
        RETURN VARCHAR2
    IS 
        v_email VARCHAR2(150);
    BEGIN
        SELECT LOWER(replace(first_name, ' ', '-')  || 
        '.' || replace(last_name, ' ', '-') 
        || '@learnplsql.com')
        INTO v_email
        FROM hr.employees
        WHERE employee_id = v_id;

        RETURN v_email;
    END get_emp_email;

END HR_EMPLOYEE_PKG;

Subprograms in the package can now be invoked in anonymous blocks or basically how they are normally called. Let's use some of the subprograms in our newly created package.

-- Invoking the subprograms

-- using the procedure
DECLARE
    emp_full_name varchar2(50);
BEGIN
    HR_EMPLOYEE_PKG.GET_EMP_NAME(112, emp_full_name);
    IF emp_full_name = 'exception block, employee does not exist' THEN
        DBMS_OUTPUT.PUT_LINE('The employee does not exist');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Employee full name is: ' || emp_full_name);
    END IF;
END;

The result is:

Employee full name is: Jose Manuel Urman

-- using the overloaded procedure
DECLARE
    emp_full_name varchar2(50);
BEGIN
    HR_EMPLOYEE_PKG.GET_EMP_NAME('Lex', emp_full_name);
    IF emp_full_name = 'exception block, employee does not exist' THEN
        DBMS_OUTPUT.PUT_LINE('The employee does not exist');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Employee full name is: ' || emp_full_name);
    END IF;
END;

The result is:

Employee full name is: Lex De Haan

-- using the function
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HR_EMPLOYEE_PKG.GET_EMP_EMAIL(EMPLOYEE_ID) EMAIL 
FROM HR.EMPLOYEES;

The output looks like:

Wrapping up

Packages are a very neat way to group related business logic together. A good example can be a package containing subprograms for employee management. It can contain subprograms that keep track of the number of employees, update salaries, increase bonuses, hire new employees and create employee IDs and so on.

Don't forget to practice what you have learnt on Oracle's live SQL platform