#shorts
--clear record/table type and function
drop type emp_nt;
drop type emp_ot;
drop function fn_get_empInfo_pt;
--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_pt
(p_deptno in dept.deptno%type default null)
return emp_nt pipelined
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
pipe row (emp_ot(rec.empno, rec.ename, rec.job, rec.mgr, rec.hiredate, rec.sal, rec.comm, rec.deptno));
end loop;
return;
end fn_get_empInfo_pt;
--Final step to call function and check the results...
--specific department -- employees...
select * from table(fn_get_empInfo_pt(10));
--all deparment -- employees
select * from table(fn_get_empInfo_pt());
