Oracle 19c中使用SET SQLFORMAT INSERT时能否为每条INSERT加错误处理块？

针对你需要为每条INSERT添加独立错误处理以避免唯一约束冲突的需求，提供以下几种可行思路：

方案1：直接生成带PL/SQL错误处理的插入语句 #

放弃SET SQLFORMAT INSERT;，手动构造包含错误捕获的PL/SQL块，让每条插入独立处理ORA-00001（唯一约束冲突）错误。示例代码如下：

set feedback off;
set termout off;
set verify off;
spool c:\temp\targetscript.sql;

-- 处理MyTable1，需根据实际列名调整拼接逻辑
SELECT 'BEGIN INSERT INTO MyTable1 (col1, col2, col3) VALUES (' 
       || '''' || col1 || '''' || ', ' 
       || col2 || ', ' 
       || '''' || col3 || '''' 
       || '); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN NULL; END;' || ';'
FROM MyTable1 where MyTable1_type= '123';

-- 处理MyTable2，同理替换表名和列名
SELECT 'BEGIN INSERT INTO MyTable2 (colA, colB) VALUES (' 
       || '''' || colA || '''' || ', ' 
       || '''' || colB || '''' 
       || '); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN NULL; END;' || ';'
FROM MyTable2 where MyTable2_type= '123';

-- 其他表按相同逻辑扩展

spool off;
set feedback on;
set termout on;
set verify on;

注意：字符串类型需加单引号转义，数字类型直接拼接即可；日期类型要使用TO_CHAR(col_date, '''YYYY-MM-DD HH24:MI:SS''')这类格式处理。

方案2：用MERGE语句替代INSERT（推荐） #

MERGE可实现「不存在则插入，存在则跳过/更新」的逻辑，无需额外错误处理，性能更优，适合跨环境数据同步场景。示例：

set feedback off;
set termout off;
set verify off;
spool c:\temp\targetscript.sql;

-- MyTable1的MERGE语句，需替换为实际唯一约束列
SELECT 'MERGE INTO MyTable1 t USING (SELECT ' 
       || '''' || col1 || '''' || ' col1, ' 
       || col2 || ' col2, ' 
       || '''' || col3 || '''' || ' col3' 
       || ' FROM DUAL) s ON (t.col1 = s.col1)'
       || ' WHEN NOT MATCHED THEN INSERT (col1, col2, col3) VALUES (s.col1, s.col2, s.col3);'
FROM MyTable1 where MyTable1_type= '123';

-- 其他表同理构造MERGE语句

spool off;
set feedback on;
set termout on;
set verify on;

这种方式直接通过匹配唯一约束列判断是否插入，逻辑清晰，也避免了PL/SQL块的额外开销。

方案3：生成普通INSERT后批量添加错误处理块 #

如果坚持使用SET SQLFORMAT INSERT;生成基础脚本，可通过文本处理工具（如Python、PowerShell）批量修改，将每条INSERT包裹到PL/SQL块中。例如：

原生成语句：

INSERT INTO MyTable1 (COL1, COL2, COL3) VALUES ('VAL1', 123, 'VAL3');

替换后：

BEGIN
  INSERT INTO MyTable1 (COL1, COL2, COL3) VALUES ('VAL1', 123, 'VAL3');
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN NULL;
END;
/

编写简单脚本读取targetscript.sql，逐行识别INSERT语句并添加前后的PL/SQL块内容即可。

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