I would like to create a procedure like this: After a lot of inserts and updates driven by a cursor loop all rows in a target table have to be deleted, which are not referenced in this cursor.
Is it possible to use the result set of my cursor in the delete statement? When I try something like shown below I get "ORA-00942: Table or View does not exist".
How can I avoid to write the same complex sql statement a second time?
create procedure update_list
as
cursor c1 is
select a, b, c ...
-- here follows a complex query with several unions
;
begin
for c1_rec in c1 loop
-- here I do a lot of inserts in updates in a table t
end loop;
open c1;
delete from table t
where (x,y,z) not in
(select a, b, c from c1);
close c1;
end;