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;