469,890 Members | 1,421 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,890 developers. It's quick & easy.

PL/SQL How to user "continue" in a FOR LOOP

Hi,
I am trying to skip a record if some conditions match in a for loop, what is the exact keyword to use that in PL/SQL.

e.g,
Expand|Select|Wrap|Line Numbers
  1.  
  2. DECLARE
  3. ..
  4. i NUMBER;
  5. BEGIN
  6.  
  7. FOR i <10
  8. LOOP
  9.  
  10.     IF i = 5
  11. -- I want to skip the update statement if i is equal to 5 in this example
  12.         "Continue"
  13.     END IF
  14.  
  15. UPDATE ABC ....;
  16.  
  17. END LOOP;
  18.  
Sep 25 '06 #1
5 52700
debasisdas
8,127 Expert 4TB
With the continue statement you can skip to the next iteration in a loop.

try to use the following sample code

Expand|Select|Wrap|Line Numbers
  1. begin  
  2. for i in 1..3  loop    
  3. dbms_output.put_line(’i=’||to_char(i));    
  4. if ( i = 2 )  then       
  5. continue;     
  6. end if;    
  7. dbms_output.put_line(’ Only if i is not equal to 2 ′);  
  8. end loop;
  9. end;
  10.  
Note: this is a feature of Oracle 11g . So try in that version only.
Dec 24 '07 #2
debasisdas
8,127 Expert 4TB
You can also use the continue when statement to determine when to go to the next iteration in the loop.
Expand|Select|Wrap|Line Numbers
  1. begin  
  2. for i in 1..3  loop    
  3. dbms_output.put_line(’i=’||to_char(i));    
  4. continue when ( i = 2 );    
  5. dbms_output.put_line(’Only if i is not equal to 2′);  
  6. end loop;
  7. end;
Dec 24 '07 #3
amitpatel66
2,367 Expert 2GB
Hi,
I am trying to skip a record if some conditions match in a for loop, what is the exact keyword to use that in PL/SQL.

e.g,

DECLARE
..
i NUMBER;
BEGIN

FOR i <10
LOOP

IF i = 5
-- I want to skip the update statement if i is equal to 5 in this example
"Continue"
END IF

UPDATE ABC ....;

END LOOP;
In case if you want to try out in lower versions of oracle, then try this:

Expand|Select|Wrap|Line Numbers
  1. declare
  2. begin
  3. for i in 1..10 loop
  4. declare
  5. my_execp EXCEPTION;
  6. BEGIN
  7. IF(I = 5) THEN
  8. RAISE my_exscep;
  9. ELSE
  10. UPDATE ABC....;
  11. END IF;
  12. EXCEPTION
  13. WHEN my_excep THEN
  14. dbms_output.put_line('dont update now!!');
  15. END;
  16. END LOOP;
  17. END;
  18.  
I have not tested this code. I hope this should help you out to achive your requirement!!
Dec 24 '07 #4
Hi,
I am trying to skip a record if some conditions match in a for loop, what is the exact keyword to use that in PL/SQL.

e.g,
Expand|Select|Wrap|Line Numbers
  1.  
  2. DECLARE
  3. ..
  4. i NUMBER;
  5. BEGIN
  6.  
  7. END LOOP;
  8.  
Have not used Continue, but the following works

Expand|Select|Wrap|Line Numbers
  1.  
  2. DECLARE
  3. ..
  4. i NUMBER;
  5. BEGIN
  6.  
  7. FOR i <10
  8. LOOP
  9.     IF i = 5
  10.      UPDATE ABC ....;
  11.     END IF
  12. END LOOP;
  13.  
Jan 12 '08 #5
Dave44
153 100+
Have not used Continue, but the following works

Expand|Select|Wrap|Line Numbers
  1.  
  2. DECLARE
  3. ..
  4. i NUMBER;
  5. BEGIN
  6.  
  7. FOR i <10
  8. LOOP
  9.     IF i = 5
  10.      UPDATE ABC ....;
  11.     END IF
  12. END LOOP;
  13.  
yep i agree with you completely, keep it simple. except in this case i think he said no update if its 5 so we'd need IF i <> 5 then
Jan 12 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

By using this site, you agree to our Privacy Policy and Terms of Use.