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;