17 Jan 2013 @ 11:26 PM 

In this post, we’ll talk about fetching collections of data into structures, using CURSORS and FETCH.

To fetch a collection of data row by row, consider the following block of code –

declare

cursor cursor1 is
select tabname, defname from REFTABLE order by tabname,refno;
tableName REFTABLE.tabname%TYPE;
expresion REFTABLE.defname%TYPE;

begin

open cursor1;
loop

fetch cursor1 into tableName,expresion;
EXIT WHEN cursor1%NOTFOUND OR cursor1%NOTFOUND IS NULL;

dbms_output.put_line(cursor1%ROWCOUNT || ‘. ‘ || tableName);
— INSERT FURTHER PROCESSING HERE

end loop;
close cursor1;

end;

In case you are dealing with huge collections and you only want to process the first 100, substitute the following line with the ‘EXIT WHEN’ in the code above –

EXIT WHEN cursor1%NOTFOUND OR cursor1%NOTFOUND IS NULL OR cursor1%ROWCOUNT>100;

CURSOR ATTRIBUTES (such as NOTFOUND) can be used to control the fetching behaviour.

The above code block is a useful way of fetching collections of data row-by-row. In the next post we’ll talk about BULK COLLECT, which fetches entire collections much faster.

Posted By: Kevin
Last Edit: 17 Jan 2013 @ 11:26 PM

EmailPermalink
Tags
Tags:
Categories: basics, Snippets, SQL


 

Responses to this post » (None)

 
Post a Comment

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

 


 Last 50 Posts
Change Theme...
  • Users » 66
  • Posts/Pages » 25
  • Comments » 4
Change Theme...
  • VoidVoid « Default
  • LifeLife
  • EarthEarth
  • WindWind
  • WaterWater
  • FireFire
  • LightLight