Home » SQL & PL/SQL » SQL & PL/SQL » Skipping Records in a cursor loop (Oracle 9.2.0.4)
Skipping Records in a cursor loop [message #417953] Wed, 12 August 2009 04:56 Go to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member
Hi all,

I am following some documentation that deals with looping through records in a cursor.

I want to skip to the next record if a value in a record meets a particular criteria, without continuing the logic.

I cannot find anything about this and how to do it.

Could somebody suggest how?

Thanks in advance,
Matt
Re: Skipping Records in a cursor loop [message #417955 is a reply to message #417953] Wed, 12 August 2009 05:02 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
What is your code?
I think there is clause called "IF", which can be used for comparisons (meets a particular criteria).
You can use that in your loop.

By
Vamsi
Re: Skipping Records in a cursor loop [message #418030 is a reply to message #417955] Wed, 12 August 2009 10:49 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member
Hi,

my code (well, the cursor part of it) is:

open c_distMatrix;
loop
fetch c_distMatrix into r_dist;
exit when c_distMatrix%NOTFOUND;
--compare each field and update the counter
v_matchCount:= 0;

if r_wip.transtype = r_dist.transtype then 
v_matchCount:= v_matchcount+1;
elseif r_wip.transtype <> r_dist.transtype then

--I want to skip to the next record at this stage

end if;

if r_wip.clientclass = r_dist.client_class then
v_matchCount:= v_matchcount+1;
end if;
end loop;
close c_distMatrix;


is there some kind of 'NEXT' statement or similar?
does that make sense?
Re: Skipping Records in a cursor loop [message #418035 is a reply to message #417953] Wed, 12 August 2009 11:25 Go to previous messageGo to next message
cookiemonster
Messages: 13960
Registered: September 2008
Location: Rainy Manchester
Senior Member
No.

Just rearrange your IF statement so that nothing else is done in the loop if that condition is true.

Alternatively rewrite your cursor so it checks that condition in the where clause so that it never selects the records your not interested in in the first place.

The second solution is generally better if you can manage it.

While you're at it I strongly recommend using a FOR LOOP for this.
Re: Skipping Records in a cursor loop [message #418036 is a reply to message #418030] Wed, 12 August 2009 11:37 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
I support Cookiemonster's comment.

To jump to the next iteration of a loop there is a "continue" statement which can be used. http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/controlstructures.htm#CJAJAAFG. But I would really not recommend it for something this simple. Use if or conditions in the query..

But why do you set your v_matchcount to 0 for each iteration in the loop? You would always get a value between 0 and 2 after an iteration (and thus also after the end of the loop), and you're not using the value within the loop.
Re: Skipping Records in a cursor loop [message #418039 is a reply to message #417953] Wed, 12 August 2009 11:51 Go to previous messageGo to next message
cookiemonster
Messages: 13960
Registered: September 2008
Location: Rainy Manchester
Senior Member
Scarily I didn't even realise there was a continue statement, but then I'd never use it anyway.

I like my code to along the lines of:
FOR rec in (<SELECT STATEMENT>) LOOP

  IF <condition> THEN

    <do stuff>

  END IF;

END LOOP;


If I can merge the IF statement into the SELECT even better - less rows processed, runs faster.

And if I can do everything in a single SQL statement that's best of all.
Re: Skipping Records in a cursor loop [message #418059 is a reply to message #418039] Wed, 12 August 2009 15:06 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
Yes, I was saying the same.
By the way it seems, continue is newly added in 11g.
OP is on 9.2.0.4

By
Vamsi
Re: Skipping Records in a cursor loop [message #418082 is a reply to message #418059] Wed, 12 August 2009 21:45 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
If you really want to raise some hairs on this board try this alternative:

...
   for r1 in (your select statement here) loop
      ...
      if (your criteria is met) then goto theend; end if;
      ...
      ...
   <<theend>> null;
   end loop;
...
end;



some people just can't stand a goto.

Good luck, Kevin
Re: Skipping Records in a cursor loop [message #418111 is a reply to message #418082] Thu, 13 August 2009 01:05 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Kevin Meade wrote on Thu, 13 August 2009 04:45
some people just can't stand a goto.

Let me put that into perspective:
People that come to these boards to ask more-or-less basic questions should NOT use goto, nor be advised to do so.
Most of the time (I still have to see the first exception), gotos are the result of either lazyness or inability to comprehend (and therefore rewrite/restructure) a piece of code.
I never ever had to use a goto in all the zillion lines of code produced so far.
So yeah, I will say it: "Goto is evil."
Previous Topic: group by and order by
Next Topic: Explain plan differents for same query, resulting in longer query execution time
Goto Forum:
  


Current Time: Tue Dec 03 06:13:19 CST 2024