There are a number of reasons why a package might be used rather than using separate individual subprograms.
Packages organize groups of related subprograms into logical groupings. As you design an application, packages provide a collective entity that provides a clear interface to the underlying PL/SQL code.
Packages provide a separate namespace for subprograms with the same identifier. For instance, you might have two "FORMAT_NAME" functions. One could be associated with a PERSON package that returns the last name concatenated to the first name, and a separate FORMAT_NAME function in a COMPANY package that capitalizes the name of the company and removes any trailing designation such as Inc., LLC, etc.
When Oracle loads an object from a package into memory, other contiguous objects in that package might also be loaded into memory. Performance might be better, and some objects might be shared among subprograms. These advantages are generally secondary to the intent to define well organized logical groupings of PL/SQL functionality.
In versions 7.4 and earlier Oracle attempted to load the entire package in memory. Now Oracle loads package objects on an on-demand basis, in 4k chunks or smaller.
Wrapping is the process of hiding PL/SQL code in order to protect source code from being viewed by unauthorized resources. Since a discussion of this utility is outside the scope of this course, see the Oracle documentation for details of this process. If you are using this feature to wrap packages, the package body (PL/SQL implementation) can be obscured while leaving the package specification visible to the outside world.
create or replace package test_package is function f1 return varchar2; function f2(p_x in varchar2) return varchar2; -- no function f3 in the package! function f4 return varchar2; end test_package;
Package declarations contain types, exceptions as well as procedure and function declarations.
create or replace package body test_package is function f1 return varchar2 as begin return 'In f1'; end f1; function f2(p_x in varchar2) return varchar2 as begin return 'in f2'; end f2; function f3 return varchar2 as begin return 'in f3'; end f3; function f4 return varchar2 as begin return f3; end f4; end test_package;
Package bodies contain subprogram implementations. The subprogram declaration signature must match in the package specification and body. This is to say the name, parameters and types must match.
select test_package.f1() from dual;
This demonstrates a call of function F1 from SQL. The package name is used to qualify the call to the function named f1. This allows for functions with the same name to exist in different packages.
select test_package.f2('x') from dual;
Call uses positional parameters. The order that the values are passed in is used to associate each value with a parameter.
select test_package.f2(p_x =>'x') from dual;
Named parameters explicitly reference each parameter by name to associate it with a value. They cannot be used in SQL prior to Oracle 11g.
begin dbms_output.put_line(test_package.f2(p_x =>'x')); end;
Named parameters can be used in the context of PL/SQL.