Packages:
======
A package is a combination of procedure and function.A package is a schema object that groups logically related PL/SQL types, items, and subprograms.Package also contains two parts.
- Package specification
- Package body
NOTE :1) Package specification and package body both start with a create statement and ends with end statement.
2) Package specification name and package body name should be unique.
3) Package body does not existed without package specification
Syntax :
=======
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,
programs,
declarations of cursors, procedures, and functions,
headers of procedures and functions]
END [package_name];
NOTE : IN the above syntax [] represents optional
{} represents compulsory
=======
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,
programs,
declarations of cursors, procedures, and functions,
headers of procedures and functions]
END [package_name];
NOTE : IN the above syntax [] represents optional
{} represents compulsory
we consider the following two tables i.e emp, dept to write SQL queries in the coming PL/SQL programs.
Example-1 :write a package to display the employee details on employee number passed as in parameter and find out his annual salary.
SQL>Create or replace package techsnibpack1
Is
Procedure p1
(P_empno in emp.empno%type,
P_ename out emp.ename%type,
P_sal out emp.sal%type,
P_deptno out emp.deptno%type);
Function myfun
(f_empno in emp.empno%type)
Return number;
End;
/
Package Created
SQL>Create or replace package body techsnibpack1
Is
Procedure p1
(p_empno in emp.empno%type,
P_ename out emp.ename%type,
P_sal out emp.sal%type,
P_deptno out emp.deptno%type)
Is
Begin
Select ename,sal,deptno into p_ename,p_sal,p_deptno from emp where where empno=p_empno;
End p1;
Function myfun
(f_empno in emp.empno%type)
Return number
Is
c number;
Begin
Select sal*12 into c from emp where empno=p_empno;
Return c;
End myfun;
End;
/
Package body created
Execution Process :
============
SQL> var a varchar2(15);
SQL> var b number
SQL> var c number
SQL> var d number
SQL> exec techsnibpack1.p1(7939,a,b,c);
PL/SQL Successfully completed
SQL> print a
A
---------------------------------------------
SMITH
SQL> print b
B
---------------------------------------------
800
SQL> print c
C
---------------------------------------------
20
SQL> exec :d:=techsnibpack1.myfun(7369);
PL/SQL procedure successfully completed
SQL> print d
D
---------------------------------------------
9600
Is
Procedure p1
(P_empno in emp.empno%type,
P_ename out emp.ename%type,
P_sal out emp.sal%type,
P_deptno out emp.deptno%type);
Function myfun
(f_empno in emp.empno%type)
Return number;
End;
/
Package Created
SQL>Create or replace package body techsnibpack1
Is
Procedure p1
(p_empno in emp.empno%type,
P_ename out emp.ename%type,
P_sal out emp.sal%type,
P_deptno out emp.deptno%type)
Is
Begin
Select ename,sal,deptno into p_ename,p_sal,p_deptno from emp where where empno=p_empno;
End p1;
Function myfun
(f_empno in emp.empno%type)
Return number
Is
c number;
Begin
Select sal*12 into c from emp where empno=p_empno;
Return c;
End myfun;
End;
/
Package body created
Execution Process :
============
SQL> var a varchar2(15);
SQL> var b number
SQL> var c number
SQL> var d number
SQL> exec techsnibpack1.p1(7939,a,b,c);
PL/SQL Successfully completed
SQL> print a
A
---------------------------------------------
SMITH
SQL> print b
B
---------------------------------------------
800
SQL> print c
C
---------------------------------------------
20
SQL> exec :d:=techsnibpack1.myfun(7369);
PL/SQL procedure successfully completed
SQL> print d
D
---------------------------------------------
9600
Example-2 :write a package to display the employee details who are working in sales department and count the number of employees working in that.
SQL>Create or replace package techsnibpack2
Is
Procedure p1;
Function myfun
return number;
End;
/
Package Created
SQL>Create or replace package body techsnibpack2
Is
Procedure p1
Is
a emp%rowtype;
cursor c is select * from emp
where deptno=(select deptno from dept where dname=’SALES’);
Begin
open c;
Loop
Fetch c into a;
if c%found then
dbms_output.put_line(a.empno);
dbms_output.put_line(a.ename);
dbms_output.put_line(a.sal);
dbms_output.put_line(a.hiredate);
else
exit;
end if;
End loop;
close c;
End p1;
Function myfun
return number
Is
c number;
Begin
select count(*) into c from emp where deptno=(select deptno from dept where dname=’SALES’);
Return c;
End myfun;
End;
/
Package body created
Is
Procedure p1;
Function myfun
return number;
End;
/
Package Created
SQL>Create or replace package body techsnibpack2
Is
Procedure p1
Is
a emp%rowtype;
cursor c is select * from emp
where deptno=(select deptno from dept where dname=’SALES’);
Begin
open c;
Loop
Fetch c into a;
if c%found then
dbms_output.put_line(a.empno);
dbms_output.put_line(a.ename);
dbms_output.put_line(a.sal);
dbms_output.put_line(a.hiredate);
else
exit;
end if;
End loop;
close c;
End p1;
Function myfun
return number
Is
c number;
Begin
select count(*) into c from emp where deptno=(select deptno from dept where dname=’SALES’);
Return c;
End myfun;
End;
/
Package body created
Example-3 :write a package to display the employee details whose salary is greater than any employee salary working under 20th department and find out their sum of the salaries.
SQL>Create or replace package techsnibpack3
Is
Procedure p1;
Function myfun
Return number;
End;
/
Package Created
SQL>Create or replace package body techsnibpack3
Is
Procedure p1
Is
a emp%rowtype;
Cursor c is select * from emp where sal>any(select sal from emp where deptno=20);
Begin
Open c;
Loop
Fetch c into a;
if c%found then
dbms_output.put_line(a.empno);
dbms_output.put_line(a.ename);
dbms_output.put_line(a.sal);
dbms_output.put_line(a.hiredate);
else
Exit;
End if;
End loop;
Close c;
End p1;
Function myfun
Return number
Is
c number;
Begin
Select sum(sal) into c from emp where sal>any(select sal from emp where deptno=20);
Return c;
End myfun;
End;
/
Package body created
Is
Procedure p1;
Function myfun
Return number;
End;
/
Package Created
SQL>Create or replace package body techsnibpack3
Is
Procedure p1
Is
a emp%rowtype;
Cursor c is select * from emp where sal>any(select sal from emp where deptno=20);
Begin
Open c;
Loop
Fetch c into a;
if c%found then
dbms_output.put_line(a.empno);
dbms_output.put_line(a.ename);
dbms_output.put_line(a.sal);
dbms_output.put_line(a.hiredate);
else
Exit;
End if;
End loop;
Close c;
End p1;
Function myfun
Return number
Is
c number;
Begin
Select sum(sal) into c from emp where sal>any(select sal from emp where deptno=20);
Return c;
End myfun;
End;
/
Package body created
Example-4 :write a package to find out the maximum salary employee details working in DALLAS and CHICAGO and find out his total salary.
SQL>Create or replace package techsnibpack4
Is
Procedure p1;
Function myfun
Return number;
End;
/
Package Created
SQL>Create or replace package body techsnibpack4
Is
Procedure p1
Is
a emp%rowtype;
Begin
Select emp.* into a from emp
where sal in(select max(sal) from emp
where deptno in(select deptno from dept where loc in(‘DALLAS’,’CHICAGO’);
dbms_output.put_line(a.ename);
dbms_output.put_line(a.sal);
dbms_output.put_line(a.hiredate);
End p1;
Function myfun Return number
Is
c number;
Begin
Select sal*12 into c from emp
where sal in(select max(sal) from emp
where deptno in(select deptno from dept where loc in(‘DALLAS’,’CHICAGO’);
Return c;
End myfun;
End;
/
Package body created
Is
Procedure p1;
Function myfun
Return number;
End;
/
Package Created
SQL>Create or replace package body techsnibpack4
Is
Procedure p1
Is
a emp%rowtype;
Begin
Select emp.* into a from emp
where sal in(select max(sal) from emp
where deptno in(select deptno from dept where loc in(‘DALLAS’,’CHICAGO’);
dbms_output.put_line(a.ename);
dbms_output.put_line(a.sal);
dbms_output.put_line(a.hiredate);
End p1;
Function myfun Return number
Is
c number;
Begin
Select sal*12 into c from emp
where sal in(select max(sal) from emp
where deptno in(select deptno from dept where loc in(‘DALLAS’,’CHICAGO’);
Return c;
End myfun;
End;
/
Package body created
--> Syntax to see the list of all sub programs
SQL> select * from user_source;
--> Syntax to see the source code of a particular program
SQL> select text from user_source where name=’<sub program name>’;
--> Syntax to drop the package
SQL> drop package <package_name>;
![]() | ![]() | ![]() |
No comments:
Post a Comment