This weeks tutorial explains about Oracle Packages, Developing Packages, Package Specification, Package Body; Overloading, Oracle Supplied Packages and Dbms_output.
Oracle Packages
A package is a group of procedures, functions, variables and SQL statements created as a single unit. It is used to store together related objects. A package has two parts, Package Specification or spec or package header and Package Body.
Package Specification acts as an interface to the package. Declaration of types, variables, constants, exceptions, cursors and subprograms is done in Package specifications. Package specification does not contain any code.
Package body is used to provide implementation for the subprograms, queries for the cursors declared in the package specification or spec.
Advantages:
- It allows you to group together related items, types and subprograms as a PL/SQL module.
- When a procedure in a package is called entire package is loaded, though it happens to be expensive first time the response is faster for subsequent calls.
- Package allows us to create types, variable and subprograms that are private or public
Package Syntax:
The package specification
The package body
If the specification of the package declares only types, constants, variables, exceptions, and call specs the package body is not required there. This type of packages only contains global variables that will be used by subprograms or cursors.
Example for a bodiless package
For accessing the types, subprograms etc from a package we write
Package_name.type_name
Package_name.subprogram_name
Executing Procedure and function from above package
The following package spec and package body declare and define 2 procedures. First the procedure is used to insert new records into the table DEPT, where the second procedure deletes a record from it.
Private and Public Items in packages
Items declared in package body are private. They can only be accessed within the package. Where as items declared in package specification is public and is available outside package. It is explained in the following example.
The package shown in Fig 1 contains a variable Age_limit which is accessed by the program given below.
Overloading Packaged Subprograms:
Similar to the overloading concept in other programming languages PL/SQL also allows you to overload its subprograms.
We can have more than one subprogram with the same name within a package
Following examples better explain this concept.
Writing Cursor spec and body separately using Packages
The cursor specification can be written separate from its body using packages. It allows you to change cursor body without changing the cursor specification. Cursor coded in the package spec need to have a return type.
CURSOR cursor_name [(parameter [, parameter]…)] RETURN return_type;
The following examples explain how cursor can be declared and defined using packages.
Below given is the example that explains how to access the cursor values.
