oracle 使用“更新”批量收集

bulk collect using "for update"

提问by Grant

I run into an interesting and unexpected issue when processing records in Oracle (11g) using BULK COLLECT.

在使用 BULK COLLECT 在 Oracle (11g) 中处理记录时，我遇到了一个有趣且意外的问题。

The following code was running great, processing through all million plus records with out an issue:

以下代码运行良好，处理所有数百万条记录没有问题：

-- Define cursor
cursor My_Data_Cur Is
Select col1
      ,col2
from My_Table_1;
…

-- Open the cursor
open My_Data_Cur;

-- Loop through all the records in the cursor
loop

  -- Read the first group of records
  fetch My_Data_Cur
  bulk collect into My_Data_Rec
  limit 100;

  -- Exit when there are no more records to process
  Exit when My_Data_Rec.count = 0;

  -- Loop through the records in the group
  for idx in 1 .. My_Data_Rec.count
  loop
    … do work here to populate a records to be inserted into My_Table_2 …
  end loop;

  -- Insert the records into the second table
  forall idx in 1 .. My_Data_Rec.count
  insert into My_Table_2…;

  -- Delete the records just processed from the source table
  forall idx in 1 .. My_Data_Rec.count
  delete from My_Table_1 …;

  commit;
end loop;

Since at the end of processing each group of 100 records (limit 100) we are deleting the records just read and processed, I though it would be a good idea to add the “for update” syntax to the cursor definition so that another process couldn't update any of the records between the time the data was read and the time the record is deleted.

由于在处理每组 100 条记录（限制为 100 条）时，我们将删除刚刚读取和处理的记录，我认为将“for update”语法添加到游标定义中是个好主意，以便另一个进程可以' 在读取数据和删除记录之间不更新任何记录。

So, the only thing in the code I changed was…

所以，我改变的代码中唯一的一件事是……

cursor My_Data_Cur
is
  select col1
        ,col2
from My_Table_1
for update;

When I ran the PL/SQL package after this change, the job only processes 100 records and then terminates. I confirmed this change was causing the issue by removing the “for update” from the cursor and once again the package processed all of the records from the source table.

当我在此更改后运行 PL/SQL 包时，该作业仅处理 100 条记录，然后终止。我通过从游标中删除“for update”并再次处理源表中的所有记录来确认此更改导致了问题。

Any ideas why adding the “for update” clause would cause this change in behavior? Any suggestions on how to get around this issue? I'm going to try starting an exclusive transaction on the table at the beginning of the process, but this isn't an idea solution because I really don't want to lock the entire table which processing the data.

为什么添加“for update”子句会导致这种行为改变？有关如何解决此问题的任何建议？我将尝试在进程开始时在表上启动一个独占事务，但这不是一个想法解决方案，因为我真的不想锁定处理数据的整个表。

Thanks in advance for your help,

在此先感谢您的帮助，

Grant

授予

采纳答案by Justin Cave

The problem is that you're trying to do a fetch across a commit.

问题是您正在尝试跨提交进行提取。

When you open My_Data_Curwith the for updateclause, Oracle has to lock every row in the My_Data_1table before it can return any rows. When you commit, Oracle has to release all those locks (the locks Oracle creates do not span transactions). Since the cursor no longer has the locks that you requested, Oracle has to close the cursor since it can no longer satisfy the for updateclause. The second fetch, therefore, must return 0 rows.

当您My_Data_Cur使用该for update子句打开时，Oracle 必须先锁定My_Data_1表中的每一行，然后才能返回任何行。当您commit，Oracle 必须释放所有这些锁（Oracle 创建的锁不跨越事务）。由于游标不再具有您请求的锁，Oracle 必须关闭游标，因为它不再满足该for update子句。因此，第二次提取必须返回 0 行。

The most logical approach would almost always be to remove the commitand do the entire thing in a single transaction. If you really, really, really need separate transactions, you would need to open and close the cursor for every iteration of the loop. Most likely, you'd want to do something to restrict the cursor to only return 100 rows every time it is opened (i.e. a rownum <= 100clause) so that you wouldn't incur the expense of visiting every row to place the lock and then every row other than the 100 that you processed and deleted to release the lock every time through the loop.

最合乎逻辑的方法几乎总是commit在单个事务中删除并完成整个事情。如果您真的，真的，真的需要单独的事务，则需要为循环的每次迭代打开和关闭游标。最有可能的是，您想要做一些事情来限制游标在每次打开时只返回 100 行（即rownum <= 100子句），这样您就不会产生访问每一行以放置锁然后每一行的费用除了您每次通过循环处理和删除以释放锁定的 100 之外。

回答by Maheswaran Ravisankar

Adding to Justin's Explantion.

添加到贾斯汀的解释。

You should have seen the below error message.Not sure, if your Exceptionhandler suppressed this.

您应该已经看到以下错误消息。不确定，您的Exception处理程序是否禁止了此消息。

And the message itself explains a Lot!

消息本身解释了很多！

For this kind of Updates, it is better to create a shadow copy of the main table, and let the public synonym point to it. While some batch id, creates a private synonym to our main table and perform the batch operations, to keep it simpler for maintenance.

对于这种更新，最好创建一个主表的影子副本，并让公共同义词指向它。虽然一些批处理 id，为我们的主表创建一个私有同义词并执行批处理操作，以使其更易于维护。

Error report -
ORA-01002: fetch out of sequence
ORA-06512: at line 7
01002. 00000 -  "fetch out of sequence"
*Cause:    This error means that a fetch has been attempted from a cursor
           which is no longer valid.  Note that a PL/SQL cursor loop
           implicitly does fetches, and thus may also cause this error.
           There are a number of possible causes for this error, including:
           1) Fetching from a cursor after the last row has been retrieved
           and the ORA-1403 error returned.
           2) If the cursor has been opened with the FOR UPDATE clause,
           fetching after a COMMIT has been issued will return the error.
           3) Rebinding any placeholders in the SQL statement, then issuing
           a fetch before reexecuting the statement.
*Action:   1) Do not issue a fetch statement after the last row has been
           retrieved - there are no more rows to fetch.
           2) Do not issue a COMMIT inside a fetch loop for a cursor
           that has been opened FOR UPDATE.
           3) Reexecute the statement after rebinding, then attempt to
           fetch again.

Also, you can change you Logic by Using rowid

此外，您可以通过使用更改逻辑 rowid

An Example for Docs:

文档示例：

DECLARE
-- if "FOR UPDATE OF salary" is included on following line, an error is raised
   CURSOR c1 IS SELECT e.*,rowid FROM employees e;
   emp_rec  employees%ROWTYPE;
BEGIN
   OPEN c1;
   LOOP
     FETCH c1 INTO emp_rec; -- FETCH fails on the second iteration with FOR UPDATE
     EXIT WHEN c1%NOTFOUND;
     IF emp_rec.employee_id = 105 THEN
       UPDATE employees SET salary = salary * 1.05 WHERE rowid = emp_rec.rowid;
         -- this mimics WHERE CURRENT OF c1
     END IF;
     COMMIT;  -- releases locks
   END LOOP;
END;
/

You have to fetch a record row by row!! update it using the ROWID AND COMMIT immediately . And then proceed to the next row!

您必须逐行获取记录！立即使用 ROWID 和 COMMIT 更新它。然后继续下一行！

But by this, you have to give up the Bulk Bindingoption.

但这样一来，你就不得不放弃这个Bulk Binding选择了。