What are PL/SQL Packages?
- Packages are named PL/SQL Blocks which mean they are permanently stored into the database schema and can be referenced or reused by your program.
- Packages are logical groups of related PL/SQL objects.
Benefits Of Using Packages
Modularity
A huge application can be built by simply developing modules (or subprograms) that are well-defined and simple to use.
REUSABILITY
A PL package is compiled and stored in the database whenever it is produced. As a result, you only have to develop the code once, and other apps may utilize it.
Easier Application Design
All you need to start building an application is the interface information from the package specifications. Without their bodies, you can code and compile specifications. After that, you can construct independent subprograms that use the packages as a reference. You don’t have to define the package bodies in their whole until you’re ready to finish the application.
Hiding Information
Packages allow you to disclose your interface details in the package specification while keeping the implementation details hidden in the package body. Some of the benefits of hiding the implementation details in the body:
- The implementation details can be changed without affecting the application interface.
- Users of the application are unable to write code that is dependent on implementation specifics that you may choose to alter.
Additional Functionality
Public variables and cursors in a package can last for the duration of a session. They can be shared by all of the environment’s subprograms. They allow you to keep track of data across transactions without having to save it in the database.
Better Performance
During the first call, the package code is loaded into Oracle’s SGA (system global area), allowing subsequent calls to run quickly. • When objects (procedures, functions, variables, constants, exceptions, cursors, and so on) are defined globally in a package, they can be quickly used.
OVERLOADING
In a package with the same name, two or more procedures or functions can be constructed.
Package Elements
A package can hold multiple database objects such as
- Variables (scalars, records, tables, etc.) and constants
- Stored Procedures
- PL/SQL Functions
- Database Cursors
- Type declarations
Components In Packages
There are basically two components in a package:
Specification:
It’s a Package’s declaration section. The package specification is the interface to the package. It simply DECLARES the types, variables, constants, exceptions, cursors, and subprograms that can be used from outside of the package.
Public items are declared in a package specification.
- The scope of a public item is defined by the package’s schema.
- A public item is visible across the schema.
- All objects placed in the specification are called “public objects.” Whereas, a “private object” is any subprogram not in the package specification but coded in the package body.
- A package specification is a self-contained object that may exist in the absence of a package body.
CREATE [OR REPLACE] PACKAGE <package_name>
IS/AS
<subprogram and public_element declaration>
.
.
END <package name>
/
Body: It is a Package definition section.
The package body contains the implementation of the subprograms and cursors declared in the package specification. It should be noted that the subprograms implemented in the package body can be accessed outside the package if they are declared in the package specification.
The subprogram declared in the package specification and body must the same. This comparison is done token-by-token of their headers. In case they do not match, PL/SQL throws an exception.
The package body may contain other subprograms that are not declared in the package specification. In that case, those are private to that package body only. CREATE PACKAGE BODY is the keyword used for creating a package body.
CREATE [OR REPLACE] PACKAGE BODY <package_name>
IS
<globalDeclaration part>
<PrivateElement definition>
<subProgram and public element definition>
END <package_name>
/