如何从游标中获取，删除，提交 [英] how to fetch, delete, commit from cursor

问题描述

我正在尝试从表中删除很多行.我想尝试将要删除的行放入游标中，然后继续对游标的每一行进行获取，删除，提交操作，直到它为空.

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.

在下面的代码中，我们是fetching行，并将它们放在TYPE中.

In the below code we are fetching rows and putting them in a TYPE.

如何修改以下代码以从图片中删除TYPE，而只需对光标本身进行fetch,delete,commit即可.

How can I modify the below code to remove the TYPE from the picture and just simply do fetch,delete,commit on the cursor itself.

    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;

推荐答案

为什么要批量提交?那只会减慢您的处理速度.除非有其他会话试图修改要删除的行，否则由于其他原因，这似乎是有问题的，最有效的方法就是简单地使用单个DELETE(即

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 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.

如果您确实要消除批量收集"(这将大大减慢该过程)，则可以使用"WHERE CURRENT OF"语法进行删除"

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

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.

但是请注意，由于必须锁定行(使用FOR UPDATE子句)，因此无法在循环中放置提交.进行提交将释放您使用FOR UPDATE请求的锁，并且您将得到ORA-01002:提取顺序错误

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

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

如果删除锁定并避免使用WHERE CURRENT OF语法，而是根据从游标中获取的值删除数据，则可能不会出现运行时错误.但是，这仍然是跨提交读取的，这是一种不好的做法，并且从根本上增加了您至少会间歇性地获得ORA-01555:快照太旧的错误的几率.与单个SQL语句或BULK COLLECT选项相比，它也将非常缓慢.

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.

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.

当然，您还必须确保您的流程可重新启动，以防您处理行的某些子集并且在流程终止之前进行一些未知的临时提交.如果DELETE足以导致不再从游标返回该行，则您的进程可能已经重新启动.但是总的来说，如果您尝试将一个操作分解为多个事务，那就很担心了.

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 DELETE is 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.

这篇关于如何从游标中获取，删除，提交的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！