PL/SQL存储过程中IN变量执行DELETE语句失效问题求助

核心原因 #

你遇到的问题本质是PL/SQL中字符串变量无法直接作为IN子句的多值列表。

你把final_list拼接成了类似(123,456,789)的字符串，但在执行DELETE FROM contrh_client_log WHERE contrh_client_log_id IN final_list时，PL/SQL会把整个final_list字符串当作单个值去匹配contrh_client_log_id字段。比如它实际执行的逻辑是找contrh_client_log_id = '(123,456,789)'的记录，而不是找contrh_client_log_id等于123、456或789的记录——这自然找不到任何匹配，所以没有报错也没有删除操作。

而你在常规SQL窗口直接用这个字符串的值时，是把它作为SQL语法的一部分写进去的，相当于直接执行DELETE ... IN (123,456,789)，这时候数据库会把括号里的内容解析成多个独立的数值，所以能正常生效。

解决方案 #

这里推荐三种可行的修改方式，按优先级排序：

1. 使用PL/SQL集合（推荐，安全高效） #

定义一个存储数值的集合类型，把需要删除的ID存入集合，再通过TABLE()函数将集合转换为可查询的数据集，配合IN子句使用：

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;
    -- 定义存储ID的集合类型
    TYPE id_list_type IS TABLE OF NUMBER;
    final_ids id_list_type := id_list_type();
BEGIN
    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;
        -- 将ID添加到集合中
        final_ids.EXTEND;
        final_ids(final_ids.COUNT) := id_log;
    END LOOP;
    CLOSE clients;

    -- 利用集合执行删除
    DELETE FROM contrh_client_log 
    WHERE contrh_client_log_id IN (SELECT column_value FROM TABLE(final_ids));
    
    COMMIT;
END TEST_PURGE;

2. 循环内直接删除（适合数据量小的场景） #

如果需要删除的数据量不大，可以跳过拼接步骤，在游标循环中直接执行删除操作，逻辑更简单：

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;
BEGIN
    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;
        -- 直接删除当前ID对应的记录
        DELETE FROM contrh_client_log WHERE contrh_client_log_id = id_log;
    END LOOP;
    CLOSE clients;
    
    COMMIT;
END TEST_PURGE;

3. 动态SQL（适合必须用字符串拼接的场景） #

如果一定要用字符串拼接的方式，需要用EXECUTE IMMEDIATE执行动态构建的SQL语句，这样数据库会把拼接后的字符串解析为合法的SQL语法：

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);
    delete_sql VARCHAR2(200);
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;

    -- 处理空列表的情况，避免生成无效SQL
    IF final_list IS NOT NULL AND LENGTH(final_list) > 0 THEN
        final_list := SUBSTR(final_list, 0, LENGTH(final_list) - 1);
        -- 构建动态SQL语句
        delete_sql := 'DELETE FROM contrh_client_log WHERE contrh_client_log_id IN (' || final_list || ')';
        -- 执行动态SQL
        EXECUTE IMMEDIATE delete_sql;
        COMMIT;
    END IF;
END TEST_PURGE;

注意：动态SQL存在SQL注入风险，如果id_log是用户输入的内容，一定要谨慎使用，优先选择集合方式。

内容的提问来源于stack exchange，提问作者LilFlowtante