Oracle编写接收逗号分隔ID字符串的z_names表删除存储过程报错问题

问题描述 #

我正在编写一个存储过程，通过输入的逗号分隔ID字符串来删除z_names表中的对应记录（使用特殊字符逗号,作为分隔符）。
示例输入：'1,4'
z_names表结构如下：

运行该存储过程后，ID为1和4的记录（jim和alex）需要被删除。
原错误代码如下：

create or replace procedure prc_z_names_del (par_string in varchar2)
is
  V_del_query  varchar2(200);
begin
  V_del_query := 'delete from '  || z_names|| 'WHERE ID = ' ||
    select to_number(trim(regexp_substr(par_string, '[^,]+', 1, level)))
    from dual
    connect by level <= regexp_count(par_string, ',') + 1||';';
  EXECUTE IMMEDIATE V_del_query;
end;
/

错误原因 #

• 固定表名z_names不需要动态拼接，且拼接时未加引号会被识别为未定义变量
• 动态SQL拼接时缺少空格，会导致语法错误，比如拼接后会出现无效字符串z_namesWHERE
• 直接将返回多行结果的SELECT子句拼接到字符串中，不符合PL/SQL语法规则
• 匹配多个ID值需要用IN关键字，不能用等号=

正确实现 #

推荐写法（无动态SQL，更安全） #

不需要使用动态SQL即可实现需求，无SQL注入风险：

create or replace procedure prc_z_names_del (par_string in varchar2)
is
begin
  delete from z_names
  where id in (
    select to_number(trim(regexp_substr(par_string, '[^,]+', 1, level)))
    from dual
    connect by level <= regexp_count(par_string, ',') + 1
  );
  -- 可根据实际事务需求决定是否添加commit
  commit;
end;
/

动态SQL写法（适用表名动态的场景） #

如果确实需要使用动态SQL，建议使用绑定变量避免注入风险：

create or replace procedure prc_z_names_del (par_string in varchar2)
is
  V_del_query  varchar2(500);
begin
  V_del_query := 'delete from z_names where id in (
    select to_number(trim(regexp_substr(:1, ''[^,]+'', 1, level)))
    from dual
    connect by level <= regexp_count(:1, '','') + 1
  )';
  EXECUTE IMMEDIATE V_del_query using par_string, par_string;
  commit;
end;
/

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