A package is a schema object that groups logically related PL/SQL types, variables, and subprograms. A package is compiled and stored in the database that can be used later.
PL/SQL package basically has two components.
- Package Specification
- Package Body
Package Specification :
A specification is an interface to the package. It consists of a declaration of all the variables, constants, cursors, types, procedures, functions, and exceptions that can be referenced from outside the package. The elements which are all declared in the specification are called public elements. Any subprogram not in the package specification but coded in the package body is called a private element.
Syntax :
CREATE [OR REPLACE] PACKAGE
< package name >
IS
< sub programs and
element declaration>
END < package name >;
Example :
Package Body
The body holds implementation details and private declarations, which are hidden from code outside the package (can be called only from inside the package).
It should contain implementation for all the subprograms/cursors that have been declared in the specification.
It can also have more subprograms or other elements that are not declared in a specification. These are called private elements.
It is a dependable object, and it depends on package specification.
The state of the package body becomes ‘Invalid’ every time when the specification is compiled. Therefore, it needs to be recompiled each time after the compilation of specification.
Syntax :
CREATE [OR REPLACE]
PACKAGE BODY < package name >
IS
< global_declaration part >
< private element definition >
< sub programs and element declaration>
< Package Initialization >
END < package name >;
Example :
Referencing Package Contents To reference the types, items, subprograms, and call specs declared within a package spec, use dot notation:
package_name.type_name
package_name.item_name
package_name.subprogram_name
Example :
Creating the emp_admin Package