ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【PL/SQL】游标提取循环

【PL/SQL】游标提取循环

原创 Linux操作系统 作者:杨奇龙 时间:2011-02-25 13:17:39 0 删除 编辑
注意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

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22664653/viewspace-687930/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
MySQL DBA NoSQL DEVOPS

注册时间:2009-10-07

  • 博文量
    1026
  • 访问量
    7702078