Oracle execute immediate多语句统一异常捕获写法是否正确

你写的两种实现方式都存在逻辑错误，达不到“仅跳过已存在对象、其余语句正常执行”的预期。 #

PL/SQL的异常处理机制是：当BEGIN块内某条语句抛出可被捕获的异常时，会立刻终止当前块内后续所有语句的执行，直接跳转到当前块对应的EXCEPTION段执行处理逻辑，处理完就退出当前块。
你现在把所有DDL语句放在同一个BEGIN块里，共用一个异常处理段，会出现以下问题：

• 对于新增字段的代码：只要任意一条ALTER TABLE语句触发“列已存在”异常，后面所有待执行的加字段语句会被直接跳过，完全不会执行。比如第一条加字段就报已存在，剩下5张表的加字段操作根本不会跑。
• 对于建索引的代码：逻辑问题和加字段完全一致，只要任意一条建索引语句触发已存在异常，后续所有建索引语句全被跳过。
• 对于你补充的改列+建表代码：问题更明显，如果第一条MODIFY语句触发“列已经允许为空”的异常，后面的建备份表语句会直接被跳过，完全不会执行。

你绑定的异常错误码本身是正确的：

• ORA-01430：要新增的列已在表中存在
• ORA-00955：待创建的对象（索引、表等）名称已被现有对象占用
• ORA-01408：相同列组合的索引已存在
• ORA-01442：要修改的列属性已经和目标属性一致
  但当前写法如果后续随意添加全量异常捕获，会导致非预期错误（如表不存在、权限不足、语法错误）被吞掉，大幅提升排查难度。

给每一条DDL语句单独套一层独立的BEGIN-EXCEPTION-END子块，每条语句的异常只在自己的子块内处理，不会触发外层块中断，也就不会影响其他语句的执行。

新增字段逻辑修正示例 #

----------------------------------
-- Add columns --
----------------------------------
DECLARE
   col_allready_exists EXCEPTION;
   PRAGMA EXCEPTION_INIT(col_allready_exists, -01430);
BEGIN
   -- 每条语句单独套子块
   BEGIN
      EXECUTE IMMEDIATE 'ALTER TABLE XtkEnumValue ADD iOrder NUMBER(20) DEFAULT 0';
   EXCEPTION
      WHEN col_allready_exists THEN
         dbms_output.put_line('XtkEnumValue.iOrder already exists, skip.');
   END;

   BEGIN
      EXECUTE IMMEDIATE 'ALTER TABLE XtkReport ADD iDisabled NUMBER(3) DEFAULT 0';
   EXCEPTION
      WHEN col_allready_exists THEN
         dbms_output.put_line('XtkReport.iDisabled already exists, skip.');
   END;

   BEGIN
      EXECUTE IMMEDIATE 'ALTER TABLE XtkWorkflow ADD iDisabled NUMBER(3) DEFAULT 0';
   EXCEPTION
      WHEN col_allready_exists THEN
         dbms_output.put_line('XtkWorkflow.iDisabled already exists, skip.');
   END;

   BEGIN
      EXECUTE IMMEDIATE 'ALTER TABLE XtkRights ADD tsLastModified TIMESTAMP(6) WITH TIME ZONE';
   EXCEPTION
      WHEN col_allready_exists THEN
         dbms_output.put_line('XtkRights.tsLastModified already exists, skip.');
   END;

   BEGIN
      EXECUTE IMMEDIATE 'ALTER TABLE NmsSeedMember ADD tsLastModified TIMESTAMP(6) WITH TIME ZONE';
   EXCEPTION
      WHEN col_allready_exists THEN
         dbms_output.put_line('NmsSeedMember.tsLastModified already exists, skip.');
   END;

   BEGIN
      EXECUTE IMMEDIATE 'ALTER TABLE NmsTrackingUrl ADD tsLastModified TIMESTAMP(6) WITH TIME ZONE';
   EXCEPTION
      WHEN col_allready_exists THEN
         dbms_output.put_line('NmsTrackingUrl.tsLastModified already exists, skip.');
   END;
END;
/

建索引逻辑修正示例 #

----------------------------------
-- Create indexes --
----------------------------------
DECLARE
    already_exists EXCEPTION;
    columns_indexed EXCEPTION;
    PRAGMA EXCEPTION_INIT ( already_exists, -955 );
    PRAGMA EXCEPTION_INIT (columns_indexed, -1408);
BEGIN
    -- 每条建索引语句单独套子块
    BEGIN
        EXECUTE IMMEDIATE 'CREATE INDEX XTKRIGHTS_TSLASTMODIFIED_IDX ON XTKRIGHTS(tsLastModified)';
    EXCEPTION
        WHEN already_exists OR columns_indexed THEN
            dbms_output.put_line('XTKRIGHTS_TSLASTMODIFIED_IDX already exists, skip.');
    END;

    BEGIN
        EXECUTE IMMEDIATE 'CREATE INDEX ER_TSLASTMODIFIED_IDX_CC057ED6 ON NmsSeedMember(tsLastModified)';
    EXCEPTION
        WHEN already_exists OR columns_indexed THEN
            dbms_output.put_line('ER_TSLASTMODIFIED_IDX_CC057ED6 already exists, skip.');
    END;

    BEGIN
        EXECUTE IMMEDIATE 'CREATE INDEX RL_TSLASTMODIFIED_IDX_E5F04BF5 ON NmsTrackingUrl(tsLastModified)';
    EXCEPTION
        WHEN already_exists OR columns_indexed THEN
            dbms_output.put_line('RL_TSLASTMODIFIED_IDX_E5F04BF5 already exists, skip.');
    END;
END;
/

补充场景（改列属性+建备份表）修正示例 #

DECLARE
   allready_null EXCEPTION;
   object_allready_exists EXCEPTION;
   PRAGMA EXCEPTION_INIT(allready_null, -01442);     
   PRAGMA EXCEPTION_INIT(object_allready_exists, -955);        
BEGIN
   BEGIN
      EXECUTE IMMEDIATE 'ALTER TABLE NMSACTIVECONTACT MODIFY (ISOURCEID NULL)';
   EXCEPTION
      WHEN allready_null THEN
         dbms_output.put_line('NMSACTIVECONTACT.ISOURCEID is already nullable, skip.');
   END;

   BEGIN
      EXECUTE IMMEDIATE 'CREATE TABLE NMSACTIVECONTACT_CPY as SELECT * FROM NMSACTIVECONTACT where 1=0';
   EXCEPTION
      WHEN object_allready_exists THEN
         dbms_output.put_line('NMSACTIVECONTACT_CPY already exists, skip.');
   END;
END;
/

• 不要为了省事在异常块里加WHEN OTHERS THEN NULL这类全量异常捕获逻辑，否则遇到表不存在、权限不足、SQL语法错误这类非预期问题时，异常会被直接吞掉，不会报错中断，你会误以为所有语句执行成功，实际漏执行了大量操作，后续排查非常困难。只捕获你明确需要跳过的特定异常即可，其余异常正常抛出，方便定位问题。
• 执行脚本前最好先在测试环境验证一遍，确认逻辑符合预期再跑生产。

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