declare  

cursor c1 is select level m, level*10 n from dual connect by level < 3;

n  number;

m number;

begin 

-- Open explicit  cursor fetch record one by one exit after exhausting close cursor

open c1;  

 loop 

   fetch c1 into m,n;

   dbms_output.put_line('m'||m||  '  n'||n);

   exit when c1%notfound;

end loop;

close c1;

  /* No need to explicitly open the cursor  fetch will not work as c2 is rowtype ,so here is an implicit            copy from  cursor initial record pointer to c2 ,,Cursor will be closed automatically once iteration            exhausts all the records , cursor record values can be used via c2 record attributes . */

 for c2 in c1 loop 

 m:= c2.m;  n:= c2.n;

   dbms_output.put_line('for m'||m||  '  n'||n);

end loop;


end;

Comments

Post a Comment

Popular posts from this blog

 declare   n  number; begin     select 1 into n from dual; if sql%found then -- if rows returned dbms_output.put_line('implicit1'||sql%rowcount);  end if; if sql%notfound then --if rows not returned dbms_output.put_line('implicit2'||sql%rowcount);  else  dbms_output.put_line('dump sql%notfound ' );  end if; if sql%isopen then --if cursor is in open state dbms_output.put_line('implicit3'||sql%rowcount);  else  dbms_output.put_line('dump sql%isopen' );  end if; end;
Read more
 declare  temp SYS_REFCURSOR; n  number; begin   -- open sys_refcursor for select statement open  temp for select level from dual connect by level < 4 ; loop -- cursor is in open state , cursor attributes are applicable   fetch temp  into n; -- fetch current record from temp assign to n      dbms_output.put_line('n'||n);    exit when temp%notfound;-- loop through the curesor until all the dataset exhausts end loop;   if  temp%found then      dbms_output.put_line('tempfound'||temp%rowcount);   end if;      if  temp%notfound then    dbms_output.put_line('tempnotfound'||temp%rowcount);   end if;     if  temp%isopen then    dbms_output.put_line('tempisopen'||temp%rowcount);   end if;      close temp;    end;
Read more
 declare   n  number; begin  -- Implicit cursor is opened , counter i copied the datatype as %rowtype fetch can't be used. for i in  (select level from dual connect by level < 4 ) loop  n :=i.level;   --fetch i into n;-- ORA-06550: line 10, column 9: PLS-00456: item 'I' is not a cursor     dbms_output.put_line('n'||n);    end loop; end;
Read more