plsql - pass curosr variable to subquery in cirsor iterating loop -

1 purpose:

i try 2 person each department highest salary. 

2 how try achieve it:

declare   type empl_table table of employees.employee_id%type index binary_integer;   empl empl_table;   cursor departmennts_id      select department_id departments;  begin    depart_row in departmennts_id   loop   select employee_id bulk collect empl          (       select employee_id       employees       department_id= depart_row.department_id       order salary desc     ) rownum<3; end loop; end; 

3 problem:

where department_id= depart_row.department_id 

when change depart_row.department_id fixed id number(ex. 80) query works. if use depart_row.department_id empl.count 0.

where making mistake?

for each iteration of outer cursor you're putting rows empl_table. each time code loops department_id , re-executes inner select replaces contents of collection. thus, if last department seen outer cursor happens have no employees associated it, end empty collection.

your best bet eliminate separate cursor on departments , use single cursor want, in:

select *   (select department_id,                salary,                row_number() on                  (partition department_id                   order salary desc) emp_rank           employees           order department_id, emp_rank)   emp_rank < 3 

sqlfiddle here.

share , enjoy.