Hi Everyone, Today's topic is Packages.
In PL/SQL, a package is a schema object that contains definitions for a group of related functionalities.
A package includes variables, constants, cursors, exceptions, procedures, functions, and subprograms.
It is compiled and stored in the Oracle Database.
Typically, a package has
1. a specification
2. a body.
A package specification is mandatory while the package body can be required or optional, depending on the package specification.
Package specification
The package specification declares the public objects that are accessible from outside the package.
If a package specification whose public objects include cursors and subprograms, then it must have a body which defines queries for the cursors and code for the subprograms.
Package body
A package body contains the implementation of the cursors or subprograms declared in the package specification. In the package body, you can declare or define private variables, cursors, etc., used only by package body itself.
A package body can have an initialization part whose statements initialize variables or perform other one-time setups for the whole package.
A package body can also have an exception-handling part used to handle exceptions.
Advantages:
1. Make code more modular
Packages allow you to encapsulate logically related types, variables, constants, subprograms, cursors, and exceptions in named PL/SQL modules. By doing this, you make each package more reusable, manageable, readable and reliable.
2.Hide implementation details
Packages allow you to expose the functionality via their specifications and hide the detailed implementation in the package body.
3.Improve application performance
Oracle loads the package into memory at the first time you invoke a package subprogram. The subsequent calls of other subprograms in the same package do not require disk I/O. This mechanism helps improve performance.
4.Minimize unnecessary recompiling code
Packages help avoid the unnecessary recompiling process. For instance, if you change the body of a package function, Oracle does not recompile the subprograms that use the function, because the subprograms are only dependent on the package specification, not the package body.
5.Manage authorization easily
By encapsulate objects in a package, you grant role on the package, instead of granting roles on each object in the package.
Creating Package:
Syntax:
CREATE [ORREPLACE] PACKAGE [schema_name.]<package_name>
IS | AS
declarations;
END [<package_name>];
DROP Package:
Syntax:
DROP PACKAGE [BODY] schema_name.package_name
Thank you for reading.
Ask your Doubt or Query in Comment Sections.