Call Oracle from ADO.NET: C#, OCI: Create the Package Header and Body : Listing 5

Create the package body and header for the CRUD operations associated with the business entity Employee in order to manage its persistent state.

create or replace package human_resources
as
   type t_cursor is ref cursor;
   procedure select_employee(cur_employee out t_cursor);
   procedure insert_employee(p_empno number, p_ename 
      varchar2, p_job varchar2, p_mgr number, p_hiredate date, 
      p_sal number, p_comm number, p_deptno number);
   procedure update_employee(p_empno number, p_ename 
      varchar2, p_job varchar2, p_mgr number, p_hiredate date, 
      p_sal number, p_comm number, p_deptno number);
   procedure delete_employee(p_empno number);
end human_resources;

create or replace package body human_resources
as
   procedure select_employee(cur_employee out t_cursor)
   is
   begin
      open cur_employee for select empno, ename, job, mgr, 
         hiredate, sal, comm, deptno from emp; 
   end select_employee;
   procedure insert_employee(p_empno number, p_ename 
      varchar2, p_job varchar2, p_mgr number, p_hiredate date, 
      p_sal number, p_comm number, p_deptno number)
   is
   begin
      insert into emp(empno, ename, job, mgr, hiredate, sal, 
         comm, deptno)
      values(p_empno, p_ename, p_job, p_mgr, p_hiredate, 
         p_sal, p_comm, p_deptno);
   end insert_employee;
   procedure update_employee(p_empno number, p_ename 
      varchar2, p_job varchar2, p_mgr number, p_hiredate date, 
      p_sal number, p_comm number, p_deptno number)
   is
   begin
      update emp
      set ename=p_ename, job=p_job, mgr=p_mgr, 
         hiredate=p_hiredate, sal=p_sal, comm=p_comm, 
         deptno=p_deptno
      where empno=p_empno;
   end update_employee;
   procedure delete_employee(p_empno number)
   is
   begin
      delete from emp
      where empno=p_empno;
   end delete_employee;
end human_resources;
comments powered by Disqus
Most   Popular
Upcoming Events

.NET Insight

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.