SQL 如何从游标中获取、删除、提交
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5757268/
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
how to fetch, delete, commit from cursor
提问by Omnipresent
I am trying to delete a lot of rows from a table. I want to try the approach of putting rows I want to delete into a cursor and then keep doing fetch, delete, commit on each row of the cursor until it is empty.
我正在尝试从表中删除很多行。我想尝试将要删除的行放入游标的方法，然后继续对游标的每一行进行提取、删除、提交，直到它为空。
In the below code we are fetchingrows and putting them in a TYPE.
在下面的代码中，我们是fetching行并将它们放在一个TYPE.
How can I modify the below code to remove the TYPE from the picture and just simply do fetch,delete,commiton the cursor itself.
如何修改下面的代码以从图片中删除 TYPE 并且只需简单地fetch,delete,commit在光标本身上进行操作。
OPEN bulk_delete_dup;
LOOP
FETCH bulk_delete_dup BULK COLLECT INTO arr_inc_del LIMIT c_rows;
FORALL i IN arr_inc_del.FIRST .. arr_inc_del.LAST
DELETE FROM UIV_RESPONSE_INCOME
WHERE ROWID = arr_inc_del(i);
COMMIT;
arr_inc_del.DELETE;
EXIT WHEN bulk_delete_dup%NOTFOUND;
END LOOP;
arr_inc_del.DELETE;
CLOSE bulk_delete_dup;
回答by Justin Cave
Why do you want to commit in batches? That is only going to slow down your processing. Unless there are other sessions that are trying to modify the rows you are trying to delete, which seems problematic for other reasons, the most efficient approach would be simply to delete the data with a single DELETE, i.e.
为什么要批量提交？那只会减慢您的处理速度。除非有其他会话正在尝试修改您尝试删除的行（由于其他原因这似乎有问题），否则最有效的方法是简单地使用单个 DELETE 删除数据，即
DELETE FROM uiv_response_income uri
WHERE EXISTS(
SELECT 1
FROM (<<bulk_delete_dup query>>) bdd
WHERE bdd.rowid = uri.rowid
)
Of course, there may well be a more optimal way of writing this depending on how the query behind your cursor is designed.
当然，根据游标背后的查询的设计方式，很可能有更优的编写方式。
If you really want to eliminate the BULK COLLECT (which will slow the process down substantially), you could use the WHERE CURRENT OF syntax to do the DELETE
如果你真的想消除 BULK COLLECT（这会大大减慢进程的速度），你可以使用 WHERE CURRENT OF 语法来执行 DELETE
SQL> create table foo
2 as
3 select level col1
4 from dual
5 connect by level < 10000;
Table created.
SQL> ed
Wrote file afiedt.buf
1 declare
2 cursor c1 is select * from foo for update;
3 l_rowtype c1%rowtype;
4 begin
5 open c1;
6 loop
7 fetch c1 into l_rowtype;
8 exit when c1%notfound;
9 delete from foo where current of c1;
10 end loop;
11* end;
SQL> /
PL/SQL procedure successfully completed.
Be aware, however, that since you have to lock the row (with the FOR UPDATE clause), you cannot put a commit in the loop. Doing a commit would release the locks you had requested with the FOR UPDATE and you'll get an ORA-01002: fetch out of sequence error
但是请注意，由于您必须锁定该行（使用 FOR UPDATE 子句），您不能将提交放入循环中。执行提交将释放您使用 FOR UPDATE 请求的锁，并且您将收到 ORA-01002: fetch out of sequence 错误
SQL> ed
Wrote file afiedt.buf
1 declare
2 cursor c1 is select * from foo for update;
3 l_rowtype c1%rowtype;
4 begin
5 open c1;
6 loop
7 fetch c1 into l_rowtype;
8 exit when c1%notfound;
9 delete from foo where current of c1;
10 commit;
11 end loop;
12* end;
SQL> /
declare
*
ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 7
You may not get a runtime error if you remove the locking and avoid the WHERE CURRENT OF syntax, deleting the data based on the value(s) you fetched from the cursor. However, this is still doing a fetch across commit which is a poor practice and radically increases the odds that you will, at least intermittently, get an ORA-01555: snapshot too old error. It will also be painfully slow compared to the single SQL statement or the BULK COLLECT option.
如果删除锁定并避免使用 WHERE CURRENT OF 语法，根据从游标中获取的值删除数据，则可能不会出现运行时错误。但是，这仍然是跨提交进行提取，这是一种糟糕的做法，并且从根本上增加了您至少间歇性地获得 ORA-01555: snapshot too old 错误的几率。与单个 SQL 语句或 BULK COLLECT 选项相比，它也会非常缓慢。
SQL> ed
Wrote file afiedt.buf
1 declare
2 cursor c1 is select * from foo;
3 l_rowtype c1%rowtype;
4 begin
5 open c1;
6 loop
7 fetch c1 into l_rowtype;
8 exit when c1%notfound;
9 delete from foo where col1 = l_rowtype.col1;
10 commit;
11 end loop;
12* end;
SQL> /
PL/SQL procedure successfully completed.
Of course, you also have to ensure that your process is restartable in case you process some subset of rows and have some unknown number of interim commits before the process dies. If the DELETEis sufficient to cause the row to no longer be returned from your cursor, your process is probably already restartable. But in general, that's a concern if you try to break a single operation into multiple transactions.
当然，您还必须确保您的进程可以重新启动，以防您处理某些行子集并且在进程终止之前有一些未知数量的临时提交。如果DELETE足以导致不再从游标返回该行，则您的进程可能已经可以重新启动。但一般来说，如果您尝试将单个操作分解为多个事务，则这是一个问题。
回答by tbone
A few things. It seems from your company's "no transaction over 8 second" rule (8 seconds, you in Texas?), you have a production db instance that traditionally supported apps doing OLTP stuff (insert 1 row, update 2 rows, etc), and has now also become the batch processing db (remove 50% of the rows and replace with 1mm new rows).
一些东西。从贵公司的“超过 8 秒无交易”规则（8 秒，您在德克萨斯州？）看来，您有一个生产数据库实例，该实例传统上支持执行 OLTP 内容的应用程序（插入 1 行、更新 2 行等），并且具有现在也成为批处理数据库（删除 50% 的行并替换为 1mm 的新行）。
Batch processing should be separated from OLTP instance. In a batch ("data factory") instance, I wouldn't try deleting in this case, I'd probably do a CTAS, drop old table, rename new table, rebuild indexes/stats, recompile invalid objs approach.
批处理应与 OLTP 实例分开。在批处理（“数据工厂”）实例中，在这种情况下我不会尝试删除，我可能会执行 CTAS、删除旧表、重命名新表、重建索引/统计信息、重新编译无效的 objs 方法。
Assuming you are stuck doing batch processing in your "8 second" instance, you'll probably find your company will ask for more and more of this in the future, so ask the DBAs for as much rollback as you can get, and hope you don't get a snapshot too old by fetching across commits (cursor select driving the deletes, commit every 1000 rows or so, delete using rowid).
假设你在你的“8 秒”实例中被困在做批处理，你可能会发现你的公司将来会要求越来越多的这样的东西，所以请 DBA 尽可能多地回滚，并希望你不要通过跨提交获取快照（光标选择驱动删除，每 1000 行左右提交一次，使用 rowid 删除）。
If DBAs cant help, you may be able to first create a temp table containing the rowids that you wish to delete, and then loop through the temp table to delete from main table (avoid fetching across commits), but your company will probably have some rule against this as well as this is another (basic) batch technique.
如果 DBA 无法提供帮助，您可以先创建一个包含要删除的 rowid 的临时表，然后遍历临时表以从主表中删除（避免跨提交获取），但您的公司可能会有一些对此的规则以及这是另一种（基本）批处理技术。
Something like:
就像是：
declare
-- assuming index on someCol
cursor sel_cur is
select rowid as row_id
from someTable
where someCol = 'BLAH';
v_ctr pls_integer := 0;
begin
for rec in sel_cur
loop
v_ctr := v_ctr + 1;
-- watch out for snapshot too old...
delete from someTable
where rowid = rec.row_id;
if (mod(v_ctr, 1000) = 0) then
commit;
end if;
end loop;
commit;
end;
