How to write a database package in Oracle PL/SQL.

Packages in Oracle PL/SQL is all in a box. It consists of everything from procedures, functions, global variables, cursors, exceptions every thing that you can do in a PL/SQL block. So here I am just trying to write a small PL/SQL package.

When starting writing package we should just keep in mind one thing that packages consist of a declaration (specification) section and a definition (body) section. You cannot compile a body of package without creating a specification. So here I am going to write a package spec and a package body.

I will be using 1 table employee_master and columns used are emp_name, date_of_birth and emp_id as search key.

--Package specification

create or replace package emp_pkg is
   type tbl_emp is table of employee_master%rowtype;
   function getEmpName(empid number) return varchar;
   function getEmpDOB(empid number) return Date;
   procedure displayEmpName;
end emp_pkg;

--Package Body

create or replace package body emp_pkg is
 function getEmpName(empid number) return varchar is
   empName employee_master.emp_name%type;
 begin
      select emp_name into empName from employee_master
       where emp_id = empid;
       return empName;
       exception
       when no_data_found then
       return null;
 end getEmpName;

 function getEmpDOB(empid number) return Date is
   empDOB employee_master.date_of_birth%type;
 begin
      select date_of_birth into empDOB from employee_master
       where emp_id = empid;
       return empDOB;
       exception
       when no_data_found then
       return null;
 end getEmpDOB;

 procedure displayEmpName is
   cursor rec is select * from employee_master where emp_grade='S';
   v_tbl_emp tbl_emp;
 begin
      open rec;
      loop
          fetch rec bulk collect into v_tbl_emp;
          for i in 1..v_tbl_emp.count loop
              dbms_output.put_line(v_tbl_emp(i).emp_name||','||v_tbl_emp(i).date_of_birth);
          end loop;
      end loop;
 end displayEmpName;
end emp_pkg;

You can also watch the video on plsql packages in Youtube : https://www.youtube.com/watch?v=tHi0Xifws_4
Also can subscribe to my channel : https://www.youtube.com/subhro190776