如何在PL/SQL过程中执行"DELETE FROM ... WHERER .... IN VARIABLE"

Question

我想在程序结束之前执行删除指令。首先，我在光标中获取一些数据。在这个 CURSOR 的循环中，对于我的 CURSOR 的每条记录，我将 id 值连接到名为"final_list"的变量中。在循环结束时，我想像这样执行 DELETE 指令：从my_field final_list my_table中删除。但不起作用。

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;

删除指令不起作用，但没有错误消息。当我在经典SQL表中执行值为"final_list"变量的相同DELETE指令时，它就是工作。

有人有想法吗？

Answer 1

是的：最好的方法不是遍历游标，运行一个选择语句，然后运行一个删除语句;相反，您可以在单个 delete 语句中完成所有操作，例如：

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

(注意未经测试。

然后，您可以将其放入一个过程中：

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

这样做将是

• 更快
• 更容易理解，
• 更易于调试(您可以在过程之外自行运行 DELETE 语句，只需很少或无需对其进行任何更改(
• 将来更易于维护。