What is a PL/SQL package?
A package is a collection of related PL/SQL objects. The package contains a body and a specification. The package specification has the declaration which is public and can be used in the PL/SQL sub programs inside the package.
The package body holds the implementation of all the PL/SQL objects declared in the specification.
Example of a PL/SQL Package CREATE OR REPLACE PACKAGE emp_data AS
PROCEDURE add_employee (ename VARCHAR2,
job VARCHAR2,
mgr NUMBER,
sal NUMBER,
deptno NUMBER);
END emp_actions;
CREATE OR REPLACE PACKAGE BODY emp_data AS
PROCEDURE add_employee (ename VARCHAR2,
job VARCHAR2,
mgr NUMBER,
sal NUMBER,
deptno NUMBER) IS
BEGIN
INSERT INTO emp VALUES (empno_seq.NEXTVAL, ename, job, mgr, SYSDATE, comm, deptno);
END add_employee;
END emp_data;
Advantages of PL/SQL Packages
Packages are easier for application designing, encapsulating data, additional functionality and better performance. An application has various modules which can be placed in packages and handled easier.
What are the types and layers of Packages? Explain them
The different types of packages are Built – in , Pre-build and new packages build new based on requirements.
The following are the layers of PL/SQL from the bottom – up approach
- SQL language layer is the lowest layer of the package.
- A PL/SQL standard package is the next layer of the package.
- Pre-built code and library packages.
PL/SQL Packages features
The PL/SQL packages allow you to declare Types, Cursors, exceptions, functions and procedures. All the initialization can be done in the package Specification and variables are initialized once.
Private vs. Public items in Packages.- The objects declared in the package specification are visible outside the package.
- The objects declared in the body of the implementation of the objects those are private variables.
Guidelines for writing packages
1. Packages need to be grouped with general PL/SQL subprograms so it can be reused in the entire application.
2. Package specifications are not mandatory but its always a good practice to have them declared.
3. Do not write more than 10,000 lines of code in package body. This may decrease the performance when a program is called from the package.
4. Changes in package specifications need a recompiling of all the supported objects. But that is not required when changes are done to the body of the package. So keep changes to the specification at the least.
