在 oracle 中提交批量更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42697818/
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
Bulk update with commit in oracle
提问by Thej
I am performing bulk update operation for a record of 1 million records. I need to COMMIT in between every 5000 records how can I perform?
我正在为 100 万条记录执行批量更新操作。我需要在每 5000 条记录之间进行 COMMIT 我该如何执行？
update tab1 t1
set (col1,col2,col3,col4)=
(select col1,col2,col3,col4 from tab_m where row_id= t1.row_id);
回答by XING
If you are looking for a solution in PLSQLyou can do it by using BULK INSERT/UPDATEas below:
如果您正在寻找解决方案，PLSQL您可以使用BULK INSERT/UPDATE以下方法：
DECLARE
c_limit PLS_INTEGER := 100;
CURSOR employees_cur
IS
SELECT employee_id
FROM employees
WHERE department_id = department_id_in;
TYPE employee_ids_t IS TABLE OF employees.employee_id%TYPE;
l_employee_ids employee_ids_t;
BEGIN
OPEN employees_cur;
LOOP
FETCH employees_cur
BULK COLLECT INTO l_employee_ids
LIMIT c_limit; -- This will make sure that every iteration has 100 records selected
EXIT WHEN l_employee_ids.COUNT = 0;
FORALL indx IN 1 .. l_employee_ids.COUNT SAVE EXCEPTIONS
UPDATE employees emp -- Updating 100 records at 1 go.
SET emp.salary =
emp.salary + emp.salary * increase_pct_in
WHERE emp.employee_id = l_employee_ids(indx);
commit;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -24381
THEN
FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
LOOP
-- Caputring errors occured during update
DBMS_OUTPUT.put_line (
SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX
|| ‘: ‘
|| SQL%BULK_EXCEPTIONS (indx).ERROR_CODE);
--<You can inset the error records to a table here>
END LOOP;
ELSE
RAISE;
END IF;
END;
回答by Avrajit Roy
Per th question, if you only want to continue updating even if record fails with error logging then i think you should go with the DML error logging clause of Oracle. Hope this helps.
每个问题，如果您只想继续更新，即使记录失败并记录错误，那么我认为您应该使用 Oracle 的 DML 错误记录条款。希望这可以帮助。
BEGIN
DBMS_ERRLOG.CREATE_ERROR_LOG('TAB1');
UPDATE tab1 t1
SET
(
COL1,
COL2,
COL3,
COL4
)
=
(SELECT COL1,COL2,COL3,COL4 FROM TAB_M WHERE ROW_ID= T1.ROW_ID
) LOG ERRORS REJECT LIMITED UNLIMITED;
END;
