Package

Oracle PL/SQL Package Example

 

Oracle Package Example

create or replace package XXC05_PACK
is
procedure XXC05_PROCD1(id number);
end XXC05_PACK;

create or replace package body XXC05_PACK
is
procedure XXC05_PROCD1(id number)
is
l_name varchar2(100);
begin
select last_name into l_name
from employees
where employee_id=id;
dbms_output.put_line(‘EMP ID’||chr(9)||id);
end;
end XXC05_PACK;

declare
begin
XXC05_PACK.XXC05_PROCD1(100);
end;

———————————————————————————

create or replace package XXC05_global_varr
is
id number:=100;
name varchar2(200):=’SAJAL’;
end XXC05_global_varr;

execute dbms_output.put_line(‘EMP ID’||chr(9)||XXC05_global_varr.id);

execute dbms_output.put_line(‘EMP NAME’||chr(9)||XXC05_global_varr.name);

————————————————————————————

create or replace package XXC05_PAC1
is
procedure XXC05_PRO1(id number);
procedure XXC05_PRO1(name varchar2);
procedure XXC05_PRO1(id number,name varchar2);
end;

create or replace package body XXC05_PAC1
is
procedure XXC05_PRO1(id number)
is
emp_id number;
begin
select employee_id into emp_id
from employees
where employee_id = id;
dbms_output.put_line(‘EMPLOYEE_ID’||chr(9)||emp_id);
end;
procedure XXC05_PRO1(name varchar2)
is
emp_id number;
begin
select employee_id into emp_id
from employees
where last_name = name;
dbms_output.put_line(‘EMPLOYEE_ID’||chr(9)||emp_id);
end;
procedure XXC05_PRO1(id number,name varchar2)
is
emp_id number;
begin
select employee_id into emp_id
from employees
where employee_id = id
and last_name = name;
dbms_output.put_line(‘EMPLOYEE_ID’||chr(9)||emp_id);
end;
end XXC05_PAC1;

————————————————————————————————-

create or replace package XXC05_PAC1
is
procedure XXC05_PRO1(id number);
procedure XXC05_PRO2(name varchar2);
end;

create or replace package body XXC05_PAC1
is
procedure XXC05_PRO1(id number)
is
emp_id number;
l_name varchar2(100);
begin
select employee_id,last_name into emp_id,l_name
from employees
where employee_id = id;
dbms_output.put_line(‘EMPLOYEE_ID’||chr(9)||emp_id);
dbms_output.put_line(‘LAST NAME’||chr(9)||l_name);
XXC05_PRO2(l_name);
end;
procedure XXC05_PRO2(name varchar2)
is
emp_id number;
begin
select employee_id into emp_id
from employees
where last_name = name;
dbms_output.put_line(‘EMPLOYEE_ID’||chr(9)||emp_id);
end;
end XXC05_PAC1;

——————————————————————————-

create or replace package XXC05_PACKA1
is
cursor c1 is
select employee_id,last_name
from employees;
procedure XXC05_id;
procedure XXC05_name;
end XXC05_PACKA1;

create or replace package body XXC05_PACKA1
is
procedure XXC05_id
is
id number;
name varchar2(200);
begin
open c1;
fetch c1 into id,name;
for i in 1..10
loop
dbms_output.put_line(‘EMPLPOYEE_ID’||chr(9)||id);
dbms_output.put_line(‘EMPLPOYEE_name’||chr(9)||name);
end loop;
end XXC05_id;
procedure XXC05_name
is
id number;
name varchar2(200);
begin
open c1;
fetch c1 into id,name;
for i in 1..10
loop
dbms_output.put_line(‘ID’||chr(9)||id);
dbms_output.put_line(‘NAME’||chr(9)||name);
end loop;
end XXC05_name;
end XXC05_PACKA1;

drop package package_name;

ADVANTAGES: –
1. EASY TO MAINTENANCE
2. COLLECTION OF SUBPROGRAMS
3. OVERLOADING
4. BETTER FERFORMANCE
5. EASIER APPLICATION DESIGN