PL/SQL packages

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
PROCEDURE add_employee (ename VARCHAR2,
   job VARCHAR2,
   mgr NUMBER,
   sal NUMBER,
   deptno NUMBER);
END emp_actions;

PROCEDURE add_employee (ename VARCHAR2,
   job VARCHAR2,
   mgr NUMBER,
   sal NUMBER,
   deptno NUMBER) IS
   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.
PL/SQL triggers
PL/SQL triggers - What is a Trigger? Syntax of Triggers, Types of PL/SQL Triggers, PL/SQL trigger execution hierarchy, How to obtain information about any trigger?...
PL/SQL sequences
PL/SQL sequences - Define PL/SQL sequences. syntax for a sequence, What does cache and nocache options mean while creating a sequence?, How do we set the LASTVALUE value in an Oracle Sequence?...
Oracle/PLSQL indexes
Oracle/PLSQL indexes - What is an Index? How to create an Index, What is Function-Based Index? Create a Function-Based Index, How do we drop an Index?...
Post your comment