【PL/SQL】游标提取循环

注意exit when mycur%notfound; 放置的位置不同，输出结果也不同。
第一个例子中循环在 it is over 提示后，又重复输出了  owner is  SYS,vid is 3；这是因为当提取到游标结果集的最后一行之后，不再提取其他的行。所以变量 vid ，vowner 不会被改变，当再次输出这些变量的语句时，就会重复输出了
declare
  vid t.object_id%type;
  vowner t.owner%type;
  vnum integer;
  cursor  mycur is select object_id ,owner from t where rownum <7;
 begin
  -- select object_id ,owner into vid,vowner from t where rownum =2;
  open mycur;
   loop
      fetch mycur into vid,vowner;
      if vnum = 6 then
         dbms_output.put_line('it is over');
         vnum :=vnum+1;
         end if;
         vnum :=mycur%rowcount;
      dbms_output.put_line(vnum||' owner is  '||vowner||','||'vid is '||vid);
   if mycur%notfound then
     dbms_output.put_line('there is no data finded!');
   end if;
    exit when mycur%notfound;  
   end loop;
 close mycur;
 end;
 
 输出为：
owner is  SYS,vid is 1
owner is  SYS,vid is 46
owner is  SYS,vid is 28
owner is  SYS,vid is 15
owner is  SYS,vid is 29
owner is  SYS,vid is 3
it is over
owner is  SYS,vid is 3
there is no data finded!
======================================================================
此例子与上一例子的区别是 exit when 语句的位置。放在了fetch 之后。
结果集被取出完毕之后，便退出loop。下面的语句不再执行。
declare
  vid t.object_id%type;
  vowner t.owner%type;
  vnum integer;
  cursor  mycur is select object_id ,owner from t where rownum <7;
 begin
  -- select object_id ,owner into vid,vowner from t where rownum =2;
  open mycur;
   loop
      fetch mycur into vid,vowner;  
      exit when mycur%notfound; 
      if vnum = 6 then
         dbms_output.put_line('it is over');
         vnum :=vnum+1;
         end if;
         vnum :=mycur%rowcount;
      dbms_output.put_line(vnum||'owner is  '||vowner||','||'vid is '||vid);
   if mycur%notfound then
     dbms_output.put_line('there is no data finded!');
   end if;
   end loop;
 close mycur;
 end;
 输出为：
1 owner is  SYS,vid is 1
2 owner is  SYS,vid is 46
3 owner is  SYS,vid is 28
4 owner is  SYS,vid is 15
5 owner is  SYS,vid is 29
6 owner is  SYS,vid is 3