Recursive function in oracle?-CodePudding

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;

CodePudding user response：

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

CodePudding user response：

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.