Package in Oracle and it’s Advantage in PL/SQL
Program.
Hello Everyone,
In my Previous Blogs I Discussed about
Stored Procedures, Stored Functions, Cursor etc. Today we will discuss about
Oracle Packages. Oracle Package plays very important role in PL/SQL program .
Package we can say collection of Procedures , Functions , Cursor etc . In
another word we can say A package is a schema object that groups logically
related PL/SQL types, variables, and subprograms.
Packages usually have two parts:
Package Specification and Package
Body, Sometimes the body is not required .The specification is the main
interface to the package. It declares the types, variables, constants,
exceptions, cursors, and subprograms that can be referenced from outside the
package. The body mainly defines the queries for the cursors and the code for
the subprograms.
PL/SQL Package is very important in
Database and as mentioned above it contains multiple procedures , Functions,
variables , cursors etc so before creating Oracle Package and see the benefits
of Package please see my previous below blog again for Stored procedures ,
Stored Functions , Cursor etc for your reference so that it’s easy for you to understand Packages in
PL/SQL.
How to Create Package in PL/SQL:
Create Package : CREATE PACKAGE statement to create the
specification for a stored package, which is an encapsulated collection of
related procedures, functions, and other program objects stored together in the
database. The package specification declares these objects. The package body,
specified subsequently, defines these objects. To create a package in your own
schema, you must have the CREATE PROCEDURE system privilege. To create a
package in another user's schema, we must have the CREATE ANY PROCEDURE system privilege.
To embed a CREATE PACKAGE statement inside an Oracle Database precompiled
program, you must terminate the statement with the keyword END-EXEC followed by
the embedded SQL statement terminator for the specific language.
Syntax for creating Package
specification :
CREATE [OR REPLACE] PACKAGE
<package_name>
IS
<sub_program and public element
declaration>
.END <package name>
Here in subprogram and public element
declaration we can have multiple procedures, functions, variables, cursor etc .Few
characteristics of the Package specification is as below.
The elements which are all declared in
the specification can be accessed from outside of the package. Such elements
are known as a public element. Package specification is a standalone element
means that it can exist alone without package body. Whenever a package has
referred an instance of the package is created for that particular session.
Syntax for creating Package Body:
CREATE [OR REPLACE] PACKAGE BODY
<package_name>
IS
<global_declaration part>
<Private element definition>
<sub_program and public element
definition>
.
<Package Initialization>
END <package_name>
Package Body consists of the
definition of all the elements that are present in the package specification.
It can also have a definition of elements that are not declared in the specification
these elements are called private elements and can be called only from inside
the package.
Note: Package Body should contain
definitions for all the subprograms/cursors that have been declared in the
Package specification. It can also have more subprograms or other elements that
are not declared in specification. These are called private elements which can
hide information. Package Body depends on package specification.
So simply we can say we use package
body as a Private which used all the subprograms/variable declared in Package
Specification. Also use to hide information from the user, but Package body
can’t exist without Package Specification but Package Specification can exist
without Package Body.
Example of creating one package which
contains Procedures in package Specification and manipulating the
data of emp of table in package body:
CREATE OR REPLACE PACKAGE DOTC
AS
PROCEDURE NEW_EMP (V_EMPNO IN NUMBER , V_ENAME IN VARCHAR2 , V_JOB IN VARCHAR2 , V_SAL IN NUMBER , V_DEPTNO IN NUMBER);
PROCEDURE EMP_RESIG (V_EMPNO IN NUMBER);
END DOTC;
/
The specification for the DOTC package declares the public program objects .
Note: All of these objects are
available to users who have access to the package. After creating the package,
we can develop applications that call any of these public procedures or
functions or raise any of the public exceptions of the package. But before you
can call this package's procedures , you must define procedures in the package body.
Creation of Package Body which uses
procedures in Package Specification:
CREATE OR REPLACE PACKAGE BODY DOTC
AS
PROCEDURE NEW_EMP (V_EMPNO IN NUMBER , V_ENAME IN VARCHAR2 , V_JOB IN VARCHAR2 , V_SAL IN NUMBER ,
V_DEPTNO IN NUMBER)
IS
BEGIN
INSERT INTO EMP (EMPNO,ENAME,JOB,SAL,DEPTNO) VALUES(V_EMPNO,V_ENAME,V_JOB,V_SAL,V_DEPTNO);
END NEW_EMP;
PROCEDURE EMP_RESIG (V_EMPNO IN NUMBER)
IS
BEGIN
DELETE FROM EMP WHERE EMPNO=V_EMPNO;
END EMP_RESIG;
END DOTC;
/
In Package Body we have manipulated
the procedures which we declared in Package Specification. So
what we defined in Package specification we can do manipulation in Package Body
which benefits programmer to hide the transaction information from the user.
Output of Above package is as below :
Writing one Another Simple Package by
using one simple procedure and function.
Package Specification:
create or replace package DOTC is
procedure insert_record (p_ID IN
TEST.ID%TYPE,
P_NAME IN
TEST.NAME%TYPE,
P_AMOUNT IN
TEST.AMOUNT%TYPE,
P_ADDRESS IN TEST.ADDRESS%TYPE);
function get_record (p_id IN NUMBER)
return
TEST%ROWTYPE;
end DOTC;
/
Package Body:
create or replace package BODY DOTC is
procedure insert_record (p_ID IN
TEST.ID%TYPE,
P_NAME IN
TEST.NAME%TYPE,
P_AMOUNT IN
TEST.AMOUNT%TYPE,
P_ADDRESS IN
TEST.ADDRESS%TYPE ) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO TEMP
VALUES(P_ID,P_NAME,P_AMOUNT,P_ADDRESS);
COMMIT;
END INSERT_RECORD;
FUNCTION GET_RECORD (P_ID IN NUMBER )
RETURN TEST%ROWTYPE
IS
V_TEST TEST%ROWTYPE;
BEGIN
SELECT * INTO V_TEST FROM TEST WHERE
ID=P_ID;
RETURN V_TEST;
END GET_RECORD;
BEGIN
DBMS_OUTPUT.PUT_LINE('CONTROL IS GIVEN
TO PACKAGE SPECIFICATION');
END DOTC;
/
Output of above Package is as below:
Advantage of using Packages in PL/SQL.
Why we use Package as we can do by
using Stored Procedures and functions .
So let’s see the Advantage of package and why Package should use in
PL/SQL and it’s benefits:
Information Hiding
With packages, you can specify which
types, items, and subprograms are public (visible and accessible) or private
(hidden and inaccessible). So we create
Package body to hide the information.
Modularity
Packages let you encapsulate logically
related types, items, and subprograms in a named PL/SQL module. Each package is
easy to understand, and the interfaces between packages are simple, clear, and
well defined.
Easier Application Design
When designing an application, all you
need initially is the interface information in the package specification. You
can code and compile a specification without its body. Then, stored subprograms
that reference the package can be compiled as well. You need not define the
package body fully until you are ready to complete the application.
Added Functionality
Packages can be used to add
functionality of the programs.
Better Performance
When you call a packaged subprogram
for the first time, the whole package is loaded into memory. Later calls to
related subprograms in the package require no disk I/O.
Important : We can use procedures,
functions etc. in run time by specifying packagename.proc_name etc. . Now we are able to understand about the
Package and its Advantages in PL/SQL program. One thing needs to very clear
that Packages Specification can exist without Package Body whereas Package Body
can’t exist without Package Specification .
Please read this blog very carefully
and also take reference for stored procedure , functions , cursor etc from my
previous blog and try to make some different package in which you can use
multiple procedures , functions etc. and do the manipulation in Package body and
see the benefits of your Program.
Thanks and Please let me know if you
have any doubts on Packages.
Comments
Post a Comment