PLSQL Package

Package

Package :-A package is a group of PL/SQL types, objects, stored procedures and functions. 

Package specification :-The specification part of a package declares the public types, variables, constants, and subprograms that are visible outside the immediate scope of the package. 

Package body:-The body of a package defines the objects declared in the specification, as well as private objects that are not visible to applications outside the package.

Example:-

create or replace package my_api
as
       function emp_sal(eno number) return number;
       function emp_name(eno number) return varchar2;
       procedure emp_info(eno number,name out varchar2);
       procedure sal_raise (emp_id number, sal_incr number);
end;
/

create or replace package body my_api
as
       function emp_sal(eno number) return number
       is
           vsal number;
       begin
          select sal into vsal from emp where empno=eno;
          return vsal;
       end emp_sal;
       function emp_name(eno number) return varchar2
       is
            vname varchar2(20);
       begin
            select ename into vname from emp where empno=eno;
       return vname;
       end emp_name;
       procedure emp_info(eno number,name out varchar2)
       is
       begin
       select ename into name from emp where empno=eno;
       end;
       procedure sal_raise (emp_id number, sal_incr number)
       is
       begin
          update emp set sal=sal + sal_incr where empno=emp_id;
          if sql%notfound then
          raise_application_error(-20011, 'Invalid Employee Number:' || TO_CHAR(emp_id));
         end if;
      end sal_raise;
end my_api;
/