Welcome

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:

SERIALLY_REUSABLE

    This PRAGMA, Tells the PL/SQL Runtime engine, that package data shuould not be persist between references. This is used to reduce per-user memory requirements when the package data is required only for duration of the call not for the session.

Package specification and body example

Package Specification

create or replace package  Pkg_Temp 
as

function toCelsius ( f IN number ) return number;
function toFahrenheit (C IN number) return number;
end Pkg_Temp;

Package Body

create or replace package BODY Pkg_Temp 
as

function toCelsius ( f IN number ) return number
is
c number;
begin

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

function toFahrenheit (C IN number) return number
is

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

end Pkg_Temp;

Calling Package Functions

SQL>select PKG_TEMP.toFahrenheit(45),PKG_TEMP.toCelsius(109) from dual;
PKG_TEMP.TOFahrenheit(45) PKG_TEMP.TOCelsius(109)
------------------------- -----------------------
		      113	       42.7777778