Recursive function in oracle?

Question

I don't know exactly how should i implement this in oracle, it just seemed like recursion to me. I have this table

 |     EMPLOYEES   |
 -------------------
 |   employee_id   |
 -------------------
 |    manager_id   |
 ___________________

And i want to get the number of all direct and indirect subordinates of a specific employee (given his id as a parameter). To be more specific, every employee (except 1) has a manager, but, x may be the manager of employee b, and employee b could be the manager of employee c, so c is also a subordinate of x. Until now all i could come up with is a list of employee_id and bulk collect first "wave" of subordinates

CREATE OR REPLACE TYPE subordinates AS VARRAY(10) OF NUMBER(4);
/
DECLARE list subordinates;


CREATE OR REPLACE FUNCTION f_sub(v_id employees.employee_id%type) RETURN NUMBER IS 
       
        e_count number;
        
       
                            
BEGIN
    SELECT employee_id BULK  COLLECT INTO list
    FROM EMPLOYEES
    WHERE manager_id = v_id;
    return list.count;

end;

Is there a reason you want to use a function? Oracle has hierarchical queries and recursive subquery factoring you could use. — Alex Poole
– Alex Poole, Commented Dec 22, 2021 at 13:53
I just had a test today and this was in the 'easy' category and couldn't do it, i'm just curious what it would look like as a function — Dragos Polifronie
– Dragos Polifronie, Commented Dec 22, 2021 at 14:02
Exactly the same example from the oracle docs is here. You need a connect by — astentx
– astentx, Commented Dec 22, 2021 at 14:10

EJ Egyed · Accepted Answer · 2021-12-22 14:10:54Z

2

You can use a query like this one to get the count of all employees that directly or indirectly report to a manager:

    SELECT COUNT (*) - 1     AS number_of_subordinates
      FROM EMPLOYEES
CONNECT BY PRIOR employee_id = manager_id
START WITH employee_id = v_id;

The query needs to subtract 1 from the COUNT(*) because the query will return the employee_id that you are passing as one of the rows of the query.

This query will return...

-1 if the v_id (employee_id) doesn't exists
0 if the v_id exists, but there are no employees that report to that employee
1+ the number of employees that report to that employee

answered Dec 22, 2021 at 14:10

EJ Egyed

6,1341 gold badge11 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alex Poole · Accepted Answer · 2021-12-22 14:55:06Z

You should really use hierarchical queries or recursive subquery factoring, but...

i'm just curious what it would look like as a function

so here are two ways to do it with a recursive function. The first uses a query to get the count of direct subordinates for a specified ID, and makes a recursive call to the function for each of those subordinate IDs, summing the result:

create or replace function count_subordinates (v_id employees.employee_id%type)
return number
is
  l_count number;
begin
  select count(*) + coalesce(sum(count_subordinates(employee_id)), 0)
  into l_count
  from employees
  where manager_id = v_id;

  return l_count;
end;
/

The second uses a cursor loop to get all the subordinate IDs, and increments a counter by one each time around the loop, and makes the recursive call directly from PL/SQL (rather than from within an SQL query as the first one does):

create or replace function count_subordinates (v_id employees.employee_id%type)
return number
is
  l_count number := 0;
begin
  for e in (
      select employee_id
      from employees
      where manager_id = v_id
  )
  loop
    l_count := l_count + 1;
    l_count := l_count + count_subordinates(e.employee_id);
  end loop;

  return l_count;
end;
/

db<>fiddle with some basic sample data, showing the result from calling the function with various starting IDs; I've included EJ Egyed's hierarchical version to show they get the same result (except for a non-existent starting ID, which returns zero in mine - you can change that if you need to, of course.)

But unless the test you took specifically asked for a recursive function, I imagine they would have expected a hierarchical query approach. Or possibly recursive subquery factoring, which you could do with:

with rcte (employee_id) as (
  select employee_id
  from employees
  where manager_id = 1 -- starting ID
  union all
  select e.employee_id
  from rcte r
  join employees e on e.manager_id = r.employee_id
)
select count(employee_id)
from rcte;

db<>fiddle with that approach added at the end.

Collectives™ on Stack Overflow

Recursive function in oracle?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related