Login

Username:

Password: 

Join us Now |  Forgot Password? | Forgot UserName?

PL/SQL

Learn step by step



PL/SQL Packages

PL/SQL package is a group of related functions, procedures, types, cursors. etc. PL/SQL package is like a library once written stored in the Oracle database and can be used by many applications.

A PL/SQL package has two parts: package specification and package body.

  • A package body contains the code that implements the package specification.
  • A package specification is the public interface of your applications. The public means the stored function, procedures, types, etc., are accessible from other applications.

Advantages

  • Package are improve the performance to loading the multiple object into memory at once, therefore, subsequent calls to related program doesn't required to calling physically I/O.
  • Package are support overloading to overload functions and procedures.
  • Package are reduce the traffic because all block execute all at once.
  • You can create package to store all related functions and procedures are grouped together into single unit called packages.
  • All function and procedure within a package can share variable among them.
  • Package are reliable to granting a privileges.

Creating PL/SQL Package Specification

The package specification is required when you create a new package. The package specification lists all the objects which are publicly accessible from other applications. The package specification also provides the information that developers need to know in order to use the interface. In short, package specification is the package’s API.

If the package specification does not contains any stored functions, procedures and no private code is needed, you don’t need to have a package body. These packages may contain only type definition and variables declaration. Those variables are known as package data. The scope of package data is global to applications. It is recommended that you should hide as much as package data as possible and use get and set functions to read and write that data. By doing this, you can prevent your package data changed unintentionally.

It is important to note that you must compile the package specification before package body.

Here is the syntax of creating PL/SQL package specification:
CREATE [OR REPLACE] PACKAGE package_name
[ AUTHID { CURRENT_USER | DEFINER } ]
{ IS | AS }
[definitions of public TYPES
,declarations of public variables, types, and objects
,declarations of exceptions
,pragmas
,declarations of cursors, procedures, and functions
,headers of procedures and functions]
END [package_name];

The CREATE PACKAGE statement is used to define a new package specification. If you want to rebuild existing package you can use REPLACE keyword instead of CREATE keyword. In the package specification, you can define new types, declare global variables, types, objects, exceptions, cursors, procedures and functions.

Below is an example of creating a new package specification called personnel. The personnel package contains two functions: get_fullname() and get_salary() based on employee’s ID. CREATE OR REPLACE PACKAGE personnel AS -- get employee's fullname FUNCTION get_fullname(n_emp_id NUMBER) RETURN VARCHAR2; -- get employee's salary FUNCTION get_salary(n_emp_id NUMBER) RETURN NUMBER; END personnel;

CREATE OR REPLACE PACKAGE personnel AS
-- get employee's fullname
FUNCTION get_fullname(n_emp_id NUMBER)
RETURN VARCHAR2;
-- get employee's salary
FUNCTION get_salary(n_emp_id NUMBER)
RETURN NUMBER;
END personnel;
Creating PL/SQL Package Body

PL/SQL package body contains all the code that implements stored functions, procedures and cursors listed in the package specification.

The following illustrates the syntax of creating package body:
CREATE [OR REPLACE] PACKAGE BODY package_name
{ IS | AS }

[definitions of private TYPEs
,declarations of private variables, types, and objects
,full definitions of cursors
,full definitions of procedures and functions]
[BEGIN
sequence_of_statements

[EXCEPTION
exception_handlers ] ]

END [package_name];

The syntax is similar to the package specification except the keyword BODY and the implemented code of package specification.

The package body can contain an optional package initialization section that appears at the end of the package body. The package initialization sections starts with the BEGIN keyword and end with the EXCEPTION section or END keyword of the package. The package initialization section is executed when the application references to the package element at the first time.

The following illustrates package body of the personnel package:

/*
Package personnel body
*/
CREATE OR REPLACE PACKAGE BODY personnel AS
-- get employee's fullname
FUNCTION get_fullname(n_emp_id NUMBER) RETURN VARCHAR2 IS
v_fullname VARCHAR2(46);
BEGIN
SELECT first_name || ',' || last_name
INTO v_fullname
FROM employees
WHERE employee_id = n_emp_id;

RETURN v_fullname;

EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
WHEN TOO_MANY_ROWS THEN
RETURN NULL;
END; -- end get_fullname

-- get salary
FUNCTION get_salary(n_emp_id NUMBER) RETURN NUMBER IS
n_salary NUMBER(8,2);
BEGIN
SELECT salary
INTO n_salary
FROM employees
WHERE employee_id = n_emp_id;

RETURN n_salary;

EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
WHEN TOO_MANY_ROWS THEN
RETURN NULL;
END;
END personnel;
Example

PL/SQL Package example step by step explain to you, you are create your own package using this reference example. We have emp1 table having employee information,

Emp_NO Emp_NAME Emp_DEPT Emp_SALARY
1 Jitendra Developer 30k
2 Pankaj Programmer 30k
3 Ramesh Designer 25k
4 Ankita HR 50k
Package Specification Code

Create Package specification code for defining procedure, function IN or OUT parameter and execute package specification program.

CREATE or REPLACE PACKAGE pkg1
IS | AS
PROCEDURE pro1
(no in number, name out varchar2);
FUNCTION fun1
(no in number)
RETURN varchar2;
END;
Package Body Code

Create Package body code for implementing procedure or function that are defined package specification. Once you implement execute this program.

CREATE or REPLACE PACKAGE BODY pkg1
IS
PROCEDURE pro1(no in number,info our varchar2)
IS
BEGIN
SELECT * INTO temp FROM emp1 WHERE eno = no;
END;

FUNCTION fun1(no in number) return varchar2
IS
name varchar2(20);
BEGIN
SELECT ename INTO name FROM emp1 WHERE eno = no;
RETURN name;
END;
END;
Pl/SQL Program calling Package

Now we have a one package pkg1, to call package defined function, procedures also pass the parameter and get the return result.

/* pkg_prg.sql */ DECLARE
no number := &no;
name varchar2(20);
BEGIN
pkg1.pro1(no,info);
dbms_output.put_line('Procedure Result');
dbms_output.put_line(info.eno||' '||
info.ename||' '||
info.edept||' '||
info.esalary||' '||);
dbms_output.put_line('Function Result');
name := pkg1.fun1(no);
dbms_output.put_line(name);
END;
PL/SQL Program Result

Now execute the above created pkg_prg.sql program to asking which user information you want to get, you put user id and give information.

SQL>@pkg_prg
no number &n=2
Procedure Result
2 marks jems Program Developer 38K
Function Result
marks jems

PL/SQL procedure successfully completed.
Package Alter

You can update package code you just recompile the package body,

Alter Syntax
ALTER PACKAGE package_name COMPILE BODY;

Recompile the already created/executed package code,

Alter Code
SQL>ALTER PACKAGE pkg1 COMPILE BODY;

Package body Altered.
PL/SQL Package Drop

You can drop package using package DROP statement,

Drop Syntax
DROP PACKAGE package_name;

Drop the pkg1 program that was we created,

Drop Code
SQL>DROP PACKAGE pkg1;

Package dropped.


Related Videos