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
x
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;