Skip to content
Advertisement

How to declare a cursor inside another cursor

How declare another cursor inside cursor c_employees?

Note: cursor c_employees return employees_id and this id pass to other cursor in the where clause.

For example

cursor c_employees
        is
            Select employees_id from employees;
begin
    for e in c_employees loop
        begin
           --How here define other cursor from cursor_employees I get employees_id and put as parameter.

           cursor c_leaves is Select hours from my_table where employees_id = e.employees_id;
           for j in c_leaves loop
                begin
                   Insert into table2(......
                end;
           end loop;

Advertisement

Answer

Cursors can take parameters hence

cursor c_employees    is
            Select employees_id from employees;
cursor c_leaves(e_id int)
  is Select hours from my_table where employess_id = e_id;
            
begin
    for e in c_employees loop
        begin
           for j in c_leaves(e.employees_id) loop
                begin
                   Insert into table2(......
                end;
           end loop;

But generally, when you start seeing cursors with cursors etc etc…its also time to look at whether the queries can be replaced with a JOIN. For example, it might be the case that the code above could just be

insert into table2
select ..
from employees e, my_table m
where e.employess_id = m.employess_id;
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement