oracle while 中的 PL/SQL 游标

PL/SQL cursor in the while

提问by Junchen Liu

This is what I am trying to achieve:

这就是我想要实现的目标：

1. define a cursor

2. which return me list of rows

3. use the rows to do some modify tables

4. repeat 2 and 3 until cursor returns no result.

1. 定义游标

2. 返回我的行列表

3. 使用行做一些修改表

4. 重复 2 和 3，直到光标没有返回结果。

this is what I have done so far

这是我到目前为止所做的

DECLARE
  CURSOR c1 IS
    [My SQL HERE];

BEGIN

   FOR r1 in c1 LOOP
   [modify tables]

END; 

question is where do I put the while? I would do it before the for loop. but whats the syntax in pl/sql to describe c1 has result?

问题是我把时间放在哪里？我会在 for 循环之前做。但是 pl/sql 中描述 c1 的语法有结果吗？

Note after c1 fullly finish I need to rerun the cursor code. Make another iteration . Because the result will be different. That's why I needed the while

注意在 c1 完全完成后，我需要重新运行游标代码。进行另一次迭代。因为结果会不一样。这就是为什么我需要一段时间

回答by Junchen Liu

The answer is combine 'while' with 'for '. Obviously only if you want to run the cursor multiple times, because you are update/alter something inside the cursor, and the condition changed, thus you need to re-run the cursor again.

答案是结合“while”和“for”。显然只有当您想多次运行游标时，因为您正在更新/更改游标内的某些内容，并且条件发生了变化，因此您需要再次重新运行游标。

here is the basic structure:

这是基本结构：

 DECLARE
  CURSOR c1 IS
    [My SQL HERE];

BEGIN

   WHILE CONDITION LOOP

   FOR r1 in c1 LOOP
   [modify tables]
   END LOOP; -- for loop

   [Check The Condition]

   END LOOP; -- while loop

 END; 

e.g

例如

  DECLARE

  counts NUMBER := -1;
  CURSOR c1 IS
    [Statement Here];

BEGIN

   WHILE count != 0 LOOP

   FOR r1 in c1 LOOP
   [modify tables]
   END LOOP; -- for loop

   SELECT count(*) ...... INTO counts

   END LOOP; -- while loop

END; 

回答by Alexander Tokarev

First of all I would recommend to think about conventional update without cursors because the best part of them is slower. I also don't understand what for you would like to replace FOR by WHILE. They work nearly similar. At any case the best source of advice is the documentation from Oracle http://docs.oracle.com/cd/B14117_01/appdev.101/b10807/13_elems020.htm

首先，我建议考虑没有游标的常规更新，因为它们中最好的部分速度较慢。我也不明白你想用 WHILE 替换 FOR。它们的工作方式几乎相似。无论如何，最好的建议来源是 Oracle http://docs.oracle.com/cd/B14117_01/appdev.101/b10807/13_elems020.htm的文档

回答by Alex Poole

You don't need a separate WHILE. The FOR ... LOOPwill iterate over the rows returned by the cursor:

您不需要单独的WHILE. 该FOR ... LOOP会遍历光标返回的行：

FOR r1 IN c1 LOOP
    -- r1 is the current row from the cursor
END LOOP;

There are two ways to handle a cursor, so perhaps that's caused confusion; the alternativeis something like:

有两种处理游标的方法，所以这可能会引起混淆；另一种方法是：

OPEN c1;
LOOP
    FETCH c1 INTO r1; -- where r1 is declared
    EXIT WHEN c1%NOTFOUND;
    -- do something with r1
END LOOP;
CLOSE c1;

Personally I usually find the FOR ... LOOPsyntax simpler.

我个人通常觉得FOR ... LOOP语法更简单。