PL SQL Tutorial

PL SQL Packages

PL SQL Packages are grouping of Variables , Stored procedures and Functions together or Package is a schema object, which lets you to group related data structures, sub-routines into a single unit. Packages has 2 parts

  • Package Specification
  • Package Body

Package Specification has declarations. Package Body has actual definitions of stored procedures and functions.

Package Data Variables( cursors, collection objects) declared outside of the functions or procedures,in package specification or package body is called as Package Data. These are also called as a Global Variables, Scope of these Variables are within session

Package Intialization:

Package specification and body example

Package Specification

create or replace package  Pkg_Temp 

function toCelcius ( f IN number ) return number;
function toFareinheat (C IN number) return number;
end Pkg_Temp;

Package Body
create or replace package BODY Pkg_Temp 

function toCelcius ( f IN number ) return number
c number;

c:= (((f-32)/9)*5);
return c;
end toCelcius;

function toFareinheat (C IN number) return number

F number;
 F:= (((C/5)*9)+32);
 return F;
end toFareinheat;

end Pkg_Temp;
Calling Package Functions
select PKG_TEMP.toFareinheat(45),PKG_TEMP.toCelcius(109) from dual;