How to execute "DELETE FROM ... WHERER .... IN VARIABLE" in PL/SQL procedure

Question

I want to execute DELETE instruction before the end of my procedure. First I get some data in my CURSOR. On the loop of this CURSOR, for each record of my CURSOR I concatenate id values in variable named "final_list". At the end of the loop I would like to execute DELETE instruction like this : DELETE FROM my_table where my_field IN final_list. But not working.

create or replace PROCEDURE TEST_PURGE is
CURSOR clients IS SELECT DISTINCT client_id
                  FROM client
                  WHERE client_description LIKE 'Test%';
client clients%ROWTYPE;
id_log client.client_id%type;
final_list VARCHAR(100);

BEGIN
  final_list:='(';
  OPEN clients;
  LOOP
    FETCH clients INTO client;
    EXIT WHEN clients%notfound;   
    SELECT log_id INTO id_log
    FROM (SELECT log_id
          FROM log
          WHERE log_client_id = client.client_id
          AND client_description LIKE 'Test%'
          ORDER BY log_date DESC)
    WHERE ROWNUM < 2; 

    final_list:=concat(final_list, id_log || ',');
  END LOOP;
  CLOSE clients;
  final_list:=SUBSTR(final_list, 0, LENGTH(final_list) - 1);
  final_list:=concat(final_list, ')');
  DBMS_OUTPUT.PUT_LINE('Id list: ' || final_list);
  DELETE FROM contrh_client_log WHERE contrh_client_log_id IN final_list;
  COMMIT; 
END TEST_PURGE;

DELETE instruction not working but no error message. When I execute the same DELETE instruction in classic SQL sheet with value of "final_list" variable, it's work.

Does anyone have an idea ?

Boneist · Accepted Answer · 2019-08-30 13:59:08Z

Yes: the best way of doing this is not by looping through a cursor, running a select statement and then a delete statement; instead, you can do it all in a single delete statement, such as:

DELETE FROM contrh_client_log
WHERE contrh_client_log_id IN (SELECT log_id
                               FROM   (SELECT l.log_client_id,
                                              l.log_id,
                                              row_number() OVER (PARTITION BY l.log_client_id ORDER BY log_date DESC) rn
                                       FROM   LOG l
                                              INNER JOIN client c ON l.log_client_id = c.client_id
                                       WHERE  l.client_description LIKE 'Test%'
                                       AND    c.client_description LIKE 'Test%')
                               WHERE   rn = 1);

(N.B. untested.)

You can then put this in a procedure:

CREATE OR REPLACE PROCEDURE test_purge AS
BEGIN
  DELETE FROM contrh_client_log
  WHERE  contrh_client_log_id IN (SELECT log_id
                                  FROM   (SELECT l.log_client_id,
                                                 l.log_id,
                                                 row_number() over(PARTITION BY l.log_client_id ORDER BY log_date DESC) rn
                                          FROM   log l
                                          INNER  JOIN client c
                                          ON     l.log_client_id = c.client_id
                                          WHERE  l.client_description LIKE 'Test%'
                                          AND    c.client_description LIKE 'Test%')
                                  WHERE  rn = 1);

  COMMIT;
END;
/

Doing it like this will be

faster,
easier to understand,
easier to debug (you can just run the delete statement on its own outside of the procedure with little or no changes to it)
easier to maintain in the future.

In general, when you're working with databases, you need to think set-based, not procedural, especially for things like this. It's akin to "which is faster: emptying a bath by using a spoon or a bucket? Answer: neither; it's faster to pull the plug!". The less work you do, the better (so long as it achieves your aims, of course!)

Collectives™ on Stack Overflow

How to execute "DELETE FROM ... WHERER .... IN VARIABLE" in PL/SQL procedure

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related