PL/SQL packages have two parts:
- the specification and
- the body,
although sometimes the body is unnecessary.
The specification is the interface to your application; it declares the
- cursors, and
available for use. The body fully defines cursors and subprograms, and so implements the specification.
Unlike subprograms, packages cannot be called, parameterized, or nested. However, the formats of a package and a subprogram are similar:
CREATE PACKAGE name AS -- specification (visible part)
-- public type and item declarations
-- subprogram specifications
CREATE PACKAGE BODY name AS -- body (hidden part)
-- private type and item declarations
-- subprogram bodies
-- initialization statements]
The specification holds public declarations that are visible to your application.
The body holds implementation details and private declarations that are hidden from your application.
You can debug, enhance, or replace a package body without changing the specification.
You can change a package body without recompiling calling programs because the implementation details in the body are hidden from your application.
A package can contain any combination of procedures and functions. In addition to procedures and functions, a package specification may include declared variables, cursors, types, and exceptions.
The procedure specification contains the procedure name and all its parameters.
The function specification includes the function name, its parameters, and its return specification.
The package body contains blocks that define each procedure and function within the package.
A private procedure is a procedure that is called by other procedures or functions inside this package. It cannot be referenced outside the package. A private procedure is also called a local procedure.
A private function is a function that is called by other procedures or functions inside this package. It cannot be referenced outside the package. A private function is also called a local function.
A function that was defined in the package specification must be defined in the package body.
The name, parameters, and return specifications must all match exactly with those in the package specification.
The complete function is defined here in the same syntax as that used when defining a stand-alone function.
A procedure that was defined in the package specification must be defined in the package body.
The name and parameters must all match exactly with those in the package specification. The complete procedure is defined here in the same syntax as that used when defining a stand-alone procedure.