FOR LOOP to go from top to bottom of the table in ORACLE??? 1

[babe1898]

Hi all,

The following is my situation: I have Table called Table_Name which contains 100 entries(rows). What I would like to do is count the number of rows of my table which can be done with the function COUNT. Then, use the result in my FOR loop. Now, I'd like to know if it is possible loop through the table from top to bottom. For instance, in the first iteration, the loop points to the first row of the table and extract the needed information. Then, second iteration points to the second row of the table and extract the needed information, and so on until the last row of the table. I could do this if and only if the Primary Keys of my table are in sequence. However, in my case, the Primary keys are not in sequence. It could be 23, 24, 35, 43...

So, I would like to know if it is possible to loop through the table in this way...Basically, like a pointer that remembers where it was..Thank you in advance for your help..

I'm using ORACLE 10g.

My PL/SQL
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DECLARE

numrecords NUMBER(38); --Number of jobs


BEGIN

--Count the number of records to be used in a for loop
SELECT COUNT(*) INTO numrecords FROM TABLE_NAME;


FOR loop_counter IN 0..numrecords LOOP

//QUERIES HERE such as SELECT...


END LOOP;

END;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[lewisp]

Use a cursor loop.

DECLARE
  CUSROR cur1 IS
    SELECT blah
    FROM   waffle
    ORDER BY primary_key;
BEGIN
  FOR loop1 IN cur1
  LOOP
    do_something;
  END LOOP;
END;

 

[babe1898]

hi lewisp,

Thank you so much for this idea...I'll try it and let you know . Thank again.

Babe1898