Oracle 游标两次通过最后一项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/896319/
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
Oracle cursor running through the last item twice
提问by Margaret
I have a a cursor loop that's building a string by concatenating the contents of a table together, using code along these lines:
我有一个游标循环，它通过将表的内容连接在一起来构建一个字符串，使用代码如下：
OPEN cur_t;
LOOP
FETCH cur_t INTO v_texttoadd;
v_string := v_string || v_texttoadd;
EXIT WHEN cur_t%notfound;
END LOOP;
The problem is, of course, that the last item gets added twice because the system runs through it once more before realising that there's nothing more to find.
当然，问题是最后一项被添加了两次，因为系统在意识到没有更多东西可以找到之前再次运行它。
I tried playing around with something like
我试着玩弄类似的东西
OPEN cur_t;
WHILE cur_t%found;
LOOP
FETCH cur_t INTO v_texttoadd;
v_string := v_string || v_texttoadd;
END LOOP;
But that didn't seem to return anything at all.
但这似乎根本没有任何回报。
What kind of syntax should I be using so that each row only appears in the resulting string once?
我应该使用什么样的语法，以便每一行只在结果字符串中出现一次？
回答by Petros
You can try this:
你可以试试这个：
OPEN cur_t;
LOOP
FETCH cur_t INTO v_texttoadd;
EXIT WHEN cur_t%notfound;
v_string := v_string || v_texttoadd;
END LOOP;
This works because %notfound is set when FETCH is executed and there aren't any more rows to fetch. In your example you checked %notfound after the concatenation and as a result, you had the duplicate in the end.
这是有效的，因为在执行 FETCH 时设置了 %notfound，并且没有更多的行要提取。在您的示例中，您在连接后检查了 %notfound，结果，您最终得到了重复项。
回答by Rob van Wijk
Right answers have already been given, but just elaborating a bit.
已经给出了正确的答案，但只是阐述了一点。
Simulating your current situation:
模拟您当前的情况：
SQL> declare
2 cursor cur_t
3 is
4 select ename
5 from emp
6 where deptno = 10
7 ;
8 v_texttoadd emp.ename%type;
9 v_string varchar2(100);
10 begin
11 open cur_t;
12 loop
13 fetch cur_t into v_texttoadd;
14 v_string := v_string || v_texttoadd;
15 exit when cur_t%notfound;
16 end loop
17 ;
18 dbms_output.put_line(v_string);
19 end;
20 /
CLARKKINGMILLERMILLER
PL/SQL-procedure is geslaagd.
Here MILLER is printed twice. By just switching the EXIT statement and the v_string assignment, you get the desired result:
这里 MILLER 打印了两次。只需切换 EXIT 语句和 v_string 赋值，就可以得到想要的结果：
SQL> declare
2 cursor cur_t
3 is
4 select ename
5 from emp
6 where deptno = 10
7 ;
8 v_texttoadd emp.ename%type;
9 v_string varchar2(100);
10 begin
11 open cur_t;
12 loop
13 fetch cur_t into v_texttoadd;
14 exit when cur_t%notfound;
15 v_string := v_string || v_texttoadd;
16 end loop
17 ;
18 dbms_output.put_line(v_string);
19 end;
20 /
CLARKKINGMILLER
PL/SQL-procedure is geslaagd.
However, your PL/SQL code becomes easier when using a cursor-for-loop. You can then skip the v_texttoadd variable and the number of lines in your loop decreases:
但是，当使用游标循环时，您的 PL/SQL 代码会变得更容易。然后，您可以跳过 v_texttoadd 变量，循环中的行数会减少：
SQL> declare
2 cursor cur_t
3 is
4 select ename
5 from emp
6 where deptno = 10
7 ;
8 v_string varchar2(100);
9 begin
10 for r in cur_t
11 loop
12 v_string := v_string || r.ename;
13 end loop
14 ;
15 dbms_output.put_line(v_string);
16 end;
17 /
CLARKKINGMILLER
PL/SQL-procedure is geslaagd.
You can also use straight SQL to accomplish the job. An example with the SQL model clause, if you are on version 10g or higher:
您也可以直接使用 SQL 来完成这项工作。SQL 模型子句的示例，如果您使用的是 10g 或更高版本：
SQL> select string
2 from ( select string
3 , rn
4 from emp
5 where deptno = 10
6 model
7 dimension by (rownum rn)
8 measures (ename, cast(null as varchar2(100)) string)
9 ( string[any] order by rn desc = ename[cv()] || string[cv()+1]
10 )
11 )
12 where rn = 1
13 /
STRING
-----------------------------------------------------------------------------------
CLARKKINGMILLER
1 rij is geselecteerd.
Regards, Rob.
问候，罗伯。
回答by Erich Kitzmueller
%notfound is set when fetch fails to retrieve a new row.
当 fetch 无法检索新行时设置 %notfound。
another possible way (this one avoiding the "if"s and "exit when"s):
另一种可能的方式（这种方式避免了“if”和“exit when”）：
OPEN cur_t;
FETCH cur_t INTO v_texttoadd;
WHILE cur_t%found LOOP
v_string := v_string || v_texttoadd;
FETCH cur_t INTO v_texttoadd;
END LOOP;
回答by Margaret
Simple answer, though possibly not the best:
简单的答案，虽然可能不是最好的：
OPEN cur_t;
LOOP
FETCH cur_t INTO v_texttoadd;
IF cur_t%found THEN
v_string := v_string || v_texttoadd;
END IF;
EXIT WHEN cur_t%notfound;
END LOOP;
回答by mshthn
I have made many cursor-based solutions in Microsoft SQL, which always worked perfectly (Good old days! I want my SQL Server back so much!) however all answers to this question proved to be wrong for me, the very last row of the cursor is ALWAYS executed twice, no matter how closely I followed the method suggested by them.
我在 Microsoft SQL 中制作了许多基于游标的解决方案，它们总是完美运行（过去的好日子！我非常想要我的 SQL Server！）但是对这个问题的所有答案对我来说都是错误的，最后一行游标总是执行两次，无论我多么密切地遵循他们建议的方法。
Forget the non-while loopand forget exit, this is what you MUST do to avoid double execution (pretty much what you do in T-SQL too!).
忘记 non-whileloop并忘记exit，这是您必须做的才能避免双重执行（几乎就是您在 T-SQL 中所做的！）。
cursor c_mm is select a, b, c, d from mytable;
begin
open c_mm;
fetch c_mm into a, b, c, d;
while (not c_mm%notfound) loop
-- do what you have to do here
fetch c_mm into a, b, c, d;
end loop;
close c_mm;
end;
Which I really don't understand is why all Oracle knowledge base articles and forum posts (and stackoverflow) promotes this exit-in-a-loop solution which is obviously wrong!
我真的不明白为什么所有 Oracle 知识库文章和论坛帖子（和 stackoverflow）都提倡这种退出循环解决方案，这显然是错误的！
