【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