?Recursive Function Returning Union of Ref Cursors 1

[Chao]

Hi,
I need to produce a report for employee list, listing the subordinates of an employee in a tree structure (the hierarchy of command). The table structure is as follow,

Employee_ID Work_For Emp_Name
1 0 Boss
2 1 Manager 1
3 2 Emp 1 of Manager 1
4 2 Emp 2 of Manager 2
...
20 1 Manager 2
21 20 Emp 1 of Manager 2
22 20 Emp 2 of Manager 2

I was hoping to be able to use a recursive function that returns a ref cursor, here is the algorithm and the question:

Function ABC(
i_emp_no in Employees.Employee_ID%Type) Return refCursor
IS
Cursor curEmp IS
SELECT Employee_ID, Work_For
FROm Employees
WHERE Work_For = i_emp_no;
v_emp_id Employees.Employee_ID%Type;
v_Work_For Employees.Work_For%Type;
v_curReturn1 Ref Cursor;
v_curReturn2 ref cursor;
BEGIN
Open curEmp;
Loop
Fetch curEmp INTO v_emp_id, v_Work_For;
Exit when curEmp%notfound;
if v_Work_For <> 0 then
v_curReturn2 := ABC(v_emp_id);
end if;
OPEN curReturn1 FOR
SELECT * FROM Employees
WHERE Employee_ID = v_emp_id;
...
end loop;
close curEmp;
Return curReturn2 Union curReturn1;
END ABC;

My question is:
I would like to combine the result from v_curReturn2 and curReturn1. Does anybody know how I can merge the above 2 result-sets?
Perhaps there is a better approach than this one. I'd appreciated it if you can poin it out to me.

 

[karluk]

You don't need a recursive function to do this. Use a hierarchical query instead. Then you can get all the subordinates of an employee with a single query. It would be something like

select * from Employees
start with Work_For = i_emp_no
connect by prior Employee_ID = Work_For;

 

[Chao]

Hi Karluk,

The data in the example contains at most 3 levels deep, but in reality, it could be deeper. The reason for me to use a recursive function so that it can be generic without concern for the depth of the traversal. For example

Employee_ID
1 BOSS
|---- 2 Works for 1
|---- 3 works for 2
|---- 31 works for 3
|---- 331 works for 31
...
|---- 41 works for 3
|---- 4 works for 2
...

Can you be more specific?

 

[karluk]

Please read the documentation on hierarchical queries. They are not limited by the depth of the tree being searched. I haven't tested the query in my previous message, but I think it's very close to what you need.