database - 事务处理后脚本不起作用

Question

在自动生成的脚本中，我有数千条如下所示的插入语句...

INSERT INTO HR_EMP_VISA_INFO (V_PERSON_ID, V_VISA_STATUS, V_DEPENDANT, V_COUNTRY_OF_ISSUE, V_VISA_TYPE, V_ISSUE_DATE, V_EXPIRY_DATE, V_I94_EXPIRY_DATE, V_I94_EXTENDED, V_I94_EXTENDED_DATE, V_PLACE_OF_ISSUE, V_ENTRY_TYPE, V_DEPENDANT_NAME, V_RELATIONSHIP, V_APPROXIMATE_VISA_COST, V_ACTUAL_VISA_COST, V_CURRENCY, V_VISA_UTILIZED, V_REMARKS, V_VISA_NUMBER) 
VALUES (4190.0, 'Active', 'N', 'USA', 'H1B', '2013/06/25 00:00:00', '2015/09/30 00:00:00', NULL, NULL, NULL, 'Sydney', 'Multiple', NULL, NULL, '25700.00', NULL, 'LKR', 'N', NULL, 'V1000001');

如果我把 begin .... commit;结尾;这需要工作，即没有任何内容插入到表中。

例如:

begin
// all insert statements
//
commit;
end;

如何在上面的脚本中包含事务处理？我主要关心的是在上述任何插入语句失败时回滚事务，并且尽可能减少整个脚本所花费的时间。现在需要 10 多分钟才能完成。

Answer 1

要处理回滚并使事务原子化，您几乎已经完成了，请看下面。为了加快插入速度，您可以插入“APPEND_VALUES”提示，但正如文档所说:

In direct-path INSERT, data is appended to the end of the table, rather than using existing space currently allocated to the table. As a result, direct-path INSERT can be considerably faster than conventional INSERT.

片段:

set serveroutput on;

begin

// all insert statements with APPEND_VALUES hint
//eg.
INSERT /*+ APPEND_VALUES */ INTO HR_EMP_VISA_INFO (V_PERSON_ID, V_VISA_STATUS, V_DEPENDANT, V_COUNTRY_OF_ISSUE, V_VISA_TYPE, V_ISSUE_DATE, V_EXPIRY_DATE, V_I94_EXPIRY_DATE, V_I94_EXTENDED, V_I94_EXTENDED_DATE, V_PLACE_OF_ISSUE, V_ENTRY_TYPE, V_DEPENDANT_NAME, V_RELATIONSHIP, V_APPROXIMATE_VISA_COST, V_ACTUAL_VISA_COST, V_CURRENCY, V_VISA_UTILIZED, V_REMARKS, V_VISA_NUMBER) 
VALUES (4190.0, 'Active', 'N', 'USA', 'H1B', '2013/06/25 00:00:00', '2015/09/30 00:00:00', NULL, NULL, NULL, 'Sydney', 'Multiple', NULL, NULL, '25700.00', NULL, 'LKR', 'N', NULL, 'V1000001');
//
commit;
exception when others then
rollback;
dbms_output.put_line('Insert failed due to: '|| SQLERRM);
end;