#shorts
--drop type emp_nt;
--drop type emp_ot;
--drop function fn_get_empInfo;
--Step 1
--Record type object
create or replace type emp_ot as object (
empno number(4)
, ename varchar2(10)
, job varchar2(9)
, mgr number(4)
, hiredate date
, sal number(7,2)
, comm number(7,2)
, deptno number(2)
);
--Step 2
--Table type object
create or replace type emp_nt is table of emp_ot;
--Step 3
--Create a table function to display the dept specific employee results...
create or replace function fn_get_empInfo
(p_deptno in dept.deptno%type default null)
return emp_nt
as
l_emp_rc emp_nt := emp_nt();
begin
for rec in (select * from emp where deptno = p_deptno or p_deptno is null)
loop
l_emp_rc.extend;
l_emp_rc(l_emp_rc.count) := emp_ot(rec.empno, rec.ename, rec.job, rec.mgr, rec.hiredate, rec.sal, rec.comm, rec.deptno);
end loop;
return l_emp_rc;
end fn_get_empInfo;
--Final step to call function and check the results...
--specific department...
select * from table(fn_get_empInfo(10));
--all departments...
select * from table(fn_get_empInfo());