SQL Oracle | How to DELETE from a table that changes its size

Question

I have a table call TRANSACTIONS. I'm trying to delete not all the transactions from the table. So it could be like this:

DELETE FROM TRANSACTIONS tr WHERE tr.Id != "123456"

But the problem is that I don't know the transactions that are not going to be delete. So sometimes my query could be like this

DELETE FROM TRANSACTIONS tr WHERE tr.Id != "123456" AND tr.Id != "23566"

or this

DELETE FROM TRANSACTIONS tr WHERE tr.Id != "123456" AND tr.Id != "23566" AND tr.Id != "222222"

And I want to put it into a STORE PROCEDURE. How can I find a way to make this procedure?

Answer 1

One option is to create additional table which will contain ID values you want to keep:

create table keep_id (id number);

Query which deletes all rows but IDs stored above would then be

delete from transactions a
where not exists (select null
                  from keep_id b
                  where b.id = a.id
                 );

If you want to create a procedure out of it, no problem - just enclose it into

create or replace procedure p_del_transaction as
begin
  delete from transaction ...
end;
/

Answer 2

Use a collection:

CREATE TYPE string_list AS TABLE OF VARCHAR2(20);

Then you can use:

CREATE PROCEDURE delete_transactions(
  i_except IN string_list
)
IS
BEGIN
  DELETE FROM transactions
  WHERE id NOT MEMBER OF i_except;
END;
/

and call it using:

BEGIN
  delete_transactions(string_list('123456', '23566', '222222'));
END;
/

db<>fiddle here