How to declare a cursor inside another cursor

user_5908200 0
Viewed 314

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;
plsql oracle sql
2 Answers

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_73672930

You need to DECLARE variables:

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

However, in this case you can DECLARE the cursor with a variable as per @ConnorMcDonald's answer (which also needs a DECLARE keyword).

user_15092640
Related