PACKAGE WITH 3 PROCEDURE AND 6 CURSOR

 PACKAGE WITH 3 PROCEDURE AND 6 CURSOR

-------PACKAGE SPECIFICATION------

create or replace package  dk_p is
procedure dk_proc1;
procedure dk_proc2;
procedure dk_proc3;
end dk_p;

--------PACKAGE BOBY-----
create or replace package body dk_p is
--------procedure1-------------
procedure dk_proc1 is
cursor dk_tech is
select count(gender) from technic  where gender='m';
t1 number;
cursor dk_tech1 is
select count(gender) from technic where gender='f';
t2 number;
begin
open dk_tech;
fetch dk_tech into t1;
dbms_output.put_line('count of male in technical is:'||t1);
close dk_tech;

begin
open dk_tech1;
fetch dk_tech1 into t2;
dbms_output.put_line('count of female in technical is:'||t2);
close dk_tech1;
end;
end;
--------procedure2-------------
procedure dk_proc2 is
cursor k_f is
select count(gender) from func  where gender='m';
fu1 number;
cursor k_f1 is
select count(gender) from func where gender='f';
fu2 number;

begin
open k_f;
fetch k_f into fu1;
dbms_output.put_line('count of male in functional is:'||fu1);
close k_f;

begin
open k_f1;
fetch k_f1 into fu2;
dbms_output.put_line('count of female in functional is:'||fu2);
close k_f1;
end;end;
--------procedure3-------------
procedure dk_proc3 is

cursor dk_dba is
select count(gender) from dba  where gender='m';
db1 number;

cursor dk_dba1 is
select count(gender) from dba where gender='f';
db2 number;

begin
open dk_dba;
fetch dk_dba into db1;
dbms_output.put_line('count of male in dba is:'||db1);
close dk_dba;

begin
open dk_dba1;
fetch dk_dba1 into db2;
dbms_output.put_line('count of female in dba is:'||db2);
close dk_dba1;
end;
end;
end dk_p;

----------EXECUTE  PACKAGE----------
set serveroutput on
begin
dk_p.dk_proc1;
dk_p.dk_proc2;
dk_p.dk_proc3;
end;

------------OUTPUT--------------
count of male in technical is:4
count of female in technical is:2
count of male in functional is:4
count of female in functional is:2
count of male in dba is:5
count of female in dba is:2
PL/SQL procedure successfully completed.

HOW TO CREATE AND EXECUTE PACKAGE(PROCEDURE,FUNCTION):

HOW TO CREATE AND EXECUTE PACKAGE(PROCEDURE,FUNCTION):

select * from tb1;

create or replace package packs
is
procedure ab(x in number,y in number,z out number);
function bb(eid tb1.id%type)
return varchar;
end packs;
create or replace package body packs
is
procedure ab(x in number,y in number,z out number)
is
begin
z:=x+y;
dbms_output.put_line(z);
end;
function bb(eid tb1.id%type)
return varchar
is
ename tb1.name%type;
begin
select name into ename from tb1 where id=eid;
return (ename);
end;
end packs;

declare
x  number;
y  number;
z  number;
begin
x:=10;
y:=20;
packs.ab(x,y,z);
end;
declare
retname varchar2(20);
eid number;
begin
eid:=1217;
retname:=techebs.packs.bb(eid);
dbms_output.put_line(retname);
end;


select techebs.packs.bb(1217) from dual;

declare
z1 number;
begin
techebs.packs.ab(60,50,z1);
end;

PL/SQL PACKAGE

PL/SQL PACKAGE

DEFINITION

A package is a schema object that groups logically related PL/SQL types, items, and subprograms. Packages usually have two parts, a specification and a body, although sometimes the body is unnecessary. 

EXAMPLE

select * from tb1;

create or replace package packs
is
procedure ab(x in number,y in number,z out number);

function bb(eid tb1.id%type)
return varchar;
end packs;

create or replace package body packs
is
procedure ab(x in number,y in number,z out number)
is
begin
z:=x+y;
dbms_output.put_line(z);
end;
function bb(eid tb1.id%type)
return varchar
is
ename tb1.name%type;
begin
select name into ename from tb1 where id=eid;
return (ename);
end;
end packs;


declare
x  number;
y  number;
z  number;
begin
x:=10;
y:=20;
packs.ab(x,y,z);
end;

declare
retname varchar2(20);
eid number;
begin
eid:=1217;
retname:=techebs.packs.bb(eid)

;
dbms_output.put_line(retname);
end;


select techebs.packs.bb(1217) from dual;

declare
z1 number;
begin
techebs.packs.ab(60,50,z1);
end;