Package

The PL/SQL package helps us to organize our code in a similar way to the header file for C/C++ code.

We create a package, containing the function declarations and, when required, subtype definitions. Then we create the package body, where the functions are defined.

Let's create a package for a couple of utility functions for the employees table, as usual located in the hr oracle test schema:

create or replace package pkg_employee as

subtype fullname_t is varchar2(200);
  
function fullname(
  in_first employees.first_name%type,
  in_last employees.last_name%type
) return fullname_t;

function fullname(
  in_id in employees.employee_id%type
) return fullname_t;

end;

We have a function, fullname(), with two overloads. One expects first name and last name - and just combine them together, the other requires an employee id as input parameter. Both of them return an object of the subtype, fullname_t, created in the same package.

Now we provide the implementation for the functions:

create or replace package body pkg_employee
as

function fullname(
  in_first employees.first_name%type,
  in_last employees.last_name%type
) return fullname_t
is
begin
  return in_first || ' ' || in_last;
end;

function fullname(
  in_id in employees.employee_id%type
) return fullname_t
is
  retval fullname_t;
begin
  select fullname(first_name, last_name)
  into retval
  from employees
  where employee_id = in_id;

  return retval;
exception
  when no_data_found then
    return 'Not found!';
  when others then
    return null;
end;

end pkg_employee;

In this case I added the pkg_employee specification to the finale end tag. It is not mandatory, but when things get complex it helps to let the code be clearer.

Now I can use the package in my code, in this way:

declare
  l_name pkg_employee.fullname_t;
  l_id employees.employee_id%TYPE := 100;
begin
  l_name := pkg_employee.fullname(l_id);
  dbms_output.put_line(l_name);
end;

Packages are thoroughly discussed in chapter 18 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein.