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.

No comments:

Post a Comment