PL/SQL存储过程未触发'table exists'异常处理逻辑问题排查

我写了一个创建备份表的存储过程，现在遇到的问题是：当备份表已存在时，table exists异常处理块里的IF语句没法正常触发。预期逻辑是：如果备份表已存在，且USER.RECORD表中对应的DO_NOT_DELETE字段值为'NO'，就删除原备份表并重新创建；如果是'YES'就回滚操作。

原存储过程代码：

CREATE OR REPLACE PROCEDURE USER.CREATE_BACKUP_TABLE (t_owner varchar2, t_name varchar2, t_retention number, t_can_delete_tag varchar2) as
/*
*******************************************************************************************************
Object Name:  CREATE_BACKUP_TABLE
*/-- Private variables
icnt         number;
sqlcmd       varchar2(1000);
backup_table varchar2(1000);
l_affx       varchar2(30) := 'BKP';
l_date       varchar2(30) := to_char(sysdate,'yyyy_mm_dd');
table_exists exception;
table_does_not_exist exception;
pragma exception_init(table_exists, -00955);
pragma exception_init(table_does_not_exist, -00942);
l_delete USER.RECORD.DO_NOT_DELETE%type;
--
--
begin
for tn in (
select table_name, owner
  from all_tables
where table_name = t_name
   and owner = t_owner
)
LOOP
    BEGIN
    backup_table := tn.owner||'.'||tn.table_name||'_'||l_date||'_'||l_affx;
    sqlcmd := 'create table '||backup_table||' AS SELECT * FROM '||tn.owner||'.'||tn.table_name;
    dbms_output.put_line(sqlcmd);
    execute immediate sqlcmd;
    --
    exception
    when table_exists then
       sqlcmd := 'select do_not_delete into :l_delete from user.record where backup_table = '''||backup_table||'''';
       execute immediate sqlcmd;
       if l_delete = 'NO' then
       sqlcmd := 'drop table '||backup_table||' cascade constraints';
       dbms_output.put_line(sqlcmd);
       execute immediate sqlcmd;
       sqlcmd := 'create table '||backup_table||' AS SELECT * FROM '||tn.owner||'.'||tn.table_name;
       execute immediate sqlcmd;
              else if l_delete = 'YES' then
              dbms_output.put_line('DML Action: Rollback');
              Rollback;
              end if;
                  end if;
                       when others then
                       dbms_output.put_line('ERROR: Preceeding statement failed with '||substr(sqlerrm(sqlcode),1,120));
                       dbms_output.put_line('DML Action: Rollback');
                       Rollback;
end;
   END LOOP;
end;
/

问题根源及修复方案 #

1. 动态SQL绑定变量错误 #

查询USER.RECORD的动态SQL存在两处问题：一是用字符串拼接传递参数，容易引发SQL注入且可能导致参数解析错误；二是绑定变量的into位置错误，Oracle中execute immediate接收查询结果的into要放在语句之后。

修复后代码：

sqlcmd := 'select do_not_delete from user.record where backup_table = :p_backup_table';
execute immediate sqlcmd into l_delete using backup_table;

2. 异常处理块语法错误 #

原代码中when others分支的缩进错误，导致它被嵌套在table_exists分支的IF逻辑里，语法不合法，异常无法被正确捕获。同时else if应改为elsif，避免不必要的嵌套逻辑。

3. 未处理无数据的异常 #

如果USER.RECORD中没有对应备份表的记录，会抛出no_data_found异常，原代码中这个异常会被when others捕获，但无法明确告知错误原因，需要单独处理该场景。

完整修复后的存储过程 #

CREATE OR REPLACE PROCEDURE USER.CREATE_BACKUP_TABLE (t_owner varchar2, t_name varchar2, t_retention number, t_can_delete_tag varchar2) as
/*
*******************************************************************************************************
Object Name:  CREATE_BACKUP_TABLE
*/
-- Private variables
icnt         number;
sqlcmd       varchar2(1000);
backup_table varchar2(1000);
l_affx       varchar2(30) := 'BKP';
l_date       varchar2(30) := to_char(sysdate,'yyyy_mm_dd');
table_exists exception;
table_does_not_exist exception;
pragma exception_init(table_exists, -00955);
pragma exception_init(table_does_not_exist, -00942);
l_delete USER.RECORD.DO_NOT_DELETE%type;

begin
    for tn in (
        select table_name, owner
        from all_tables
        where table_name = t_name
          and owner = t_owner
    ) loop
        begin
            backup_table := tn.owner||'.'||tn.table_name||'_'||l_date||'_'||l_affx;
            sqlcmd := 'create table '||backup_table||' AS SELECT * FROM '||tn.owner||'.'||tn.table_name;
            dbms_output.put_line(sqlcmd);
            execute immediate sqlcmd;

        exception
            when table_exists then
                begin
                    sqlcmd := 'select do_not_delete from user.record where backup_table = :p_backup_table';
                    execute immediate sqlcmd into l_delete using backup_table;

                    if l_delete = 'NO' then
                        sqlcmd := 'drop table '||backup_table||' cascade constraints';
                        dbms_output.put_line(sqlcmd);
                        execute immediate sqlcmd;
                        -- 重新创建备份表
                        sqlcmd := 'create table '||backup_table||' AS SELECT * FROM '||tn.owner||'.'||tn.table_name;
                        execute immediate sqlcmd;
                    elsif l_delete = 'YES' then
                        dbms_output.put_line('DML Action: Rollback');
                        rollback;
                    else
                        dbms_output.put_line('ERROR: DO_NOT_DELETE字段值无效，仅支持YES/NO');
                        rollback;
                    end if;
                exception
                    when no_data_found then
                        dbms_output.put_line('ERROR: USER.RECORD中未找到备份表'||backup_table||'的配置');
                        rollback;
                end;
            when others then
                dbms_output.put_line('ERROR: 执行失败，错误信息：'||substr(sqlerrm(sqlcode),1,120));
                dbms_output.put_line('DML Action: Rollback');
                rollback;
        end;
    end loop;
end;
/

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