Delete from a table inside a cursor loop
435085May 8 2009 — edited May 8 2009Hi,
I have a problem related to using a delete from a table inside a cursor. Here is the case.
The cursor I am using is based on a join condition from two tables.
First table has the fields - item, store, type
The second table has item, store, price.
The first table can have 2 rows for the same item, store but with a different type( M or N). The second can have only one row for the item/store.
The cursor is like this
select a.type, b.*
from table a, table b
where a.item=b.item
and a.store = b.store
order by a.type = in this case 2 rows results,
Now the rest of code looks like :
open cursor;
fetch cursor into l_row;
loop
if cursor%not found then
exit
end if;
<conditions> if type ='M' then
delete from table b;
else
<operations>
fetch cursor into l_row;
end loop;
When I delete the row in table b, I don't need to treat the other row with type N. But the cursor still does the last fecth with type N, thought no record in the table.
I don't want to put a commit after delete, as this will affect my entire program. If anyone can help me with an idea of how to do the delete and stop reading the cursor after that ?
Thx.