Skipping Records in a cursor loop [message #417953] |
Wed, 12 August 2009 04:56 |
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 #418030 is a reply to message #417955] |
Wed, 12 August 2009 10:49 |
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 |
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 #418039 is a reply to message #417953] |
Wed, 12 August 2009 11:51 |
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 #418111 is a reply to message #418082] |
Thu, 13 August 2009 01:05 |
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."
|
|
|