SQL 引发异常时在 Oracle 中继续插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1065829/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Continuing Inserts in Oracle when exception is raised
提问by Sathyajith Bhat
I'm working on migration of data from a legacy system into our new app(running on Oracle Database, 10gR2). As part of the migration, I'm working on a script which inserts the data into tables that are used by the app.
我正在将数据从旧系统迁移到我们的新应用程序（在 Oracle 数据库 10gR2 上运行）。作为迁移的一部分，我正在编写一个脚本，该脚本将数据插入到应用程序使用的表中。
The number of rows of data that are imported runs into thousands, and the source data is not clean (unexpected nulls in NOT NULL columns, etc). So while inserting data through the scripts, whenever such an exception occurs, the script ends abruptly, and the whole transaction is rolled back.
导入的数据行数达到数千，源数据不干净（NOT NULL 列中出现意外的空值等）。所以在通过脚本插入数据的时候，每当发生这样的异常时，脚本会突然结束，整个事务被回滚。
Is there a way, by which I can continue inserts of data for which the rows are clean?
Using NVL()or COALESCE()is not an option, as I'd like to log the rows causing the errors so that the data can be corrected for the next pass.
有没有办法，我可以继续插入行干净的数据？使用NVL()orCOALESCE()不是一个选项，因为我想记录导致错误的行，以便可以在下一次通过时更正数据。
EDIT: My current procedure has an exception handler, I am logging the first row which causes the error. Would it be possible for inserts to continue without termination, because right now on the first handled exception, the procedure terminates execution.
编辑：我当前的过程有一个异常处理程序，我正在记录导致错误的第一行。插入是否可以继续而不终止，因为现在在第一个处理的异常上，过程终止执行。
回答by Gary Myers
If the data volumes were higher, row-by-row processing in PL/SQL would probably be too slow. In those circumstances, you can use DML error logging, described here
如果数据量更大，PL/SQL 中的逐行处理可能会太慢。在这些情况下，您可以使用 DML 错误日志记录，描述为here
CREATE TABLE raises (emp_id NUMBER, sal NUMBER
CONSTRAINT check_sal CHECK(sal > 8000));
EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('raises', 'errlog');
INSERT INTO raises
SELECT employee_id, salary*1.1 FROM employees
WHERE commission_pct > .2
LOG ERRORS INTO errlog ('my_bad') REJECT LIMIT 10;
SELECT ORA_ERR_MESG$, ORA_ERR_TAG$, emp_id, sal FROM errlog;
ORA_ERR_MESG$ ORA_ERR_TAG$ EMP_ID SAL
--------------------------- -------------------- ------ -------
ORA-02290: check constraint my_bad 161 7700
(HR.SYS_C004266) violated
回答by Shea
Using PLSQL you can perform each insert in its own transaction (COMMIT after each) and log or ignore errors with an exception handler that keeps going.
使用 PLSQL，您可以在其自己的事务中执行每个插入（在每个之后提交），并使用持续运行的异常处理程序记录或忽略错误。
回答by diederikh
Try this:
尝试这个：
for r_row in c_legacy_data loop
begin
insert into some_table(a, b, c, ...)
values (r_row.a, r_row.b, r_row.c, ...);
exception
when others then
null; /* or some extra logging */
end;
end loop;
回答by Sai Ganesh
DECLARE
cursor;
BEGIN
loop for each row in cursor
BEGIN -- subBlock begins
SAVEPOINT startTransaction; -- mark a savepoint
-- do whatever you have do here
COMMIT;
EXCEPTION
ROLLBACK TO startTransaction; -- undo changes
END; -- subBlock ends
end loop;
END;
回答by Mark Roddy
If you use sqlldr you can specify to continue loading data, and all the 'bad' data will be skipped and logged in a separate file.
如果您使用 sqlldr，您可以指定继续加载数据，所有“坏”数据将被跳过并记录在单独的文件中。
