PL/SQL Function to Return Table Type

As i described in my previous post PL/SQL Procedure to return Table Type. In this post i m goin to show the same thing but with database Function to return multiple rows.

--Create Database Type as Object

create or replace type TypeForFunction
as
object
(EmpCd number,
EmpName Varchar2(60),
DepartCode number,
Salary number );

--Create table of the Type

Create or replace type TableForFunction as table of TypeForFunction; 

--Create Database Function

Create or replace
FUNCTION MyFunction
(deptCode number) RETURN TableForFunction
is 
OutTableParam TableForFunction;
begin
select cast(
Multiset(select empno, ename, deptno, sal
from scott.emp
where deptno = deptcode)
as TableForFunction)
into outtableparam
from dual;

return OutTableParam;
end;

--To return values from this function

SQL> select * from table(MyFunction(10));

Output would be like



EMPCD                 EMPNAME          DEPARTCODE      SALARY                
---------------------- ------------------------ ---------------------- ----------------------
7782                   CLARK                       10                     2450                  
7839                   KING                          10                     5000                  
7934                   MILLER                     10                     1300