oralce循环问题，求助

counter1219 发表于 2013-06-27 16:15

declare
cursor c1 is select view_name from all_views where rownum <=10 order by view_name;
vname varchar2(40);
begin
open c1;
fetch c1 into vname;
while c1%FOUND LOOP
DBMS_OUTPUT.put_LINE(vname);
END LOOP;
CLOSE c1;
END;

为什么查询出来的结果，进入死循环了呢，结果如下：

JAVASNM
JAVASNM
JAVASNM
JAVASNM
JAVASNM
JAVASNM
JAVASNM
.............

查询出来的结果应该是下面的结果的，
SQL> select view_name from all_views where rownum <=10 order by view_name;

VIEW_NAME
------------------------------
JAVASNM
KU$_10_1_AUDIT_VIEW
KU$_10_1_COMMENT_VIEW
KU$_10_1_DBLINK_VIEW
KU$_10_1_FHTABLE_VIEW
KU$_10_1_HISTGRM_MAX_VIEW
KU$_10_1_HISTGRM_MIN_VIEW
KU$_10_1_HTABLE_VIEW
KU$_10_1_IND_STATS_VIEW
KU$_10_1_IOTABLE_VIEW

10 rows selected

SQL>

asdf2110 发表于 2013-06-28 15:09

循环内要不断 fetch 才行

回复 1# counter1219

counter1219 发表于 2013-06-28 19:56

麻烦解释下需要怎么修改，不是先获取（fetch）再循环的么？回复 2# asdf2110

asdf2110 发表于 2013-07-02 09:04

fetch 每次只能fetch一条啊
如果想获取第二、第三，。。。就要不断的调用 fetch

回复 3# counter1219

counter1219 发表于 2013-07-04 17:23

create or replace procedure qqpro
as
cursor c1 is select id from qq where rownum <=4 order by id;
vname varchar2(40);
begin
open c1;
LOOP
exit when c1%NOTFOUND;
fetch c1 into vname;
DBMS_OUTPUT.put_LINE(vname);
END LOOP;
CLOSE c1;
END;

SQL> exec qqpro;

PL/SQL procedure successfully completed

SQL> set serveroutput on;
SQL> exec qqpro;

1
2
3
4
4 -------- 为什么最后一行会打印两次？PL/SQL procedure successfully completed

SQL>select id from qq where rownum <=4 order by id;

ID
-----
1
2
3
4

SQL>

counter1219 发表于 2013-07-04 17:41

create or replace procedure qqpro
as
cursor c1 is select id from qq where rownum <=4 order by id;
vname varchar2(40);
begin
open c1;
LOOP
fetch c1 into vname;
exit when c1%NOTFOUND;DBMS_OUTPUT.put_LINE(vname);对换位置后，执行结果是正确的了，这个是什么原因？
END LOOP;
CLOSE c1;
END;

doni 发表于 2013-07-16 11:20

循环逻辑问题，先做逻辑判断，还是先输出，当然结果就不一样了

页: [1]

Chinaunix's Archiver

oralce循环问题，求助