Packages - Simplified Learning

Packages

Package is a collection of sub programs which contains functions and procedures.
Package contains two sections
1. Package Specification
2. Package Body
Package specification and package body name should starts with create and ends with end statement.
Here package specification name and package body name should be same.
Package contains only functions or only procedures or combinations both also.

Develop a package employee details when department number passed as IN value.

create or replace package sixpack
   is
   procedure p1
   (p_deptno in emp.deptno%type);
   function myfun
   (p_deptno in emp.deptno%type)
   return number;
end;

create or replace package body sixpack
   is 
   procedure p1
   (p_deptno in emp.deptno%type)
   is
   a emp%rowtype;
   cursor c is select * from emp where deptno=p_deptno;
begin
   open c;
   loop
      fetch c into a;
      if c% found then
         dbms_output.put_line(a.empno||' '||a.sal||' '||a.deptno);
      else
         exit;
      end if;
   end loop;
end p1;
function myfun (p_deptno in emp.deptno%type)
   return number is
   c number;
begin
   select count(*) into c from emp where deptno=p_deptno;
   return c;
end myfun;
end;

Execution Process

SQL> var a number
SQL> exec :a:=sixpack.myfun(30)
PL/SQL procedure successfully completed.
SQL> print a
   A
---------
   6

That’s it guys. This is all about PL/SQL Tutorial. Let me know your comments and suggestions about this tutorial. Thank you.

Packages