Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

how to skip a row in a loop in pl/sql? 2

Status
Not open for further replies.

huchen

Programmer
Jan 24, 2006
68
US
Hello,
I want to skip some rows in a for loop, such as:

for i in (select col_1, col2 from tab_a)
loop
if i.col_1 ='A' then
continue;
end if;
end loop;

I want to use "continue" to skip a row.
I used "continue" and "next", and none of them works.
What is the key word for "continue"?

Thanks
 
Huchen,

From a logic perspective, there are multiple methods to achieve your objectives:

Method 1 -- "GOTO":
Code:
begin
    for r in (select rownum rn, last_name from s_emp) loop
        if mod(r.rn,2)=0 then
           goto yada;
        end if;
        dbms_output.put_line(r.rn||': '||r.last_name);
    <<yada>>
        null;
    end loop;
end;

1: Velasquez
3: Nagayama
5: Ropeburn
7: Menchu

Method 2 -- "IF...THEN...ELSE":
Code:
begin
    for r in (select rownum rn, last_name from s_emp) loop
        if mod(r.rn,2)=0 then
           null;
        else
           dbms_output.put_line(r.rn||': '||r.last_name);
        end if;
    end loop;
end;
/

1: Velasquez
3: Nagayama
5: Ropeburn
7: Menchu

Method 3 -- -- Streamlined "IF...THEN...ELSE" (My preferences):
Code:
begin
    for r in (select rownum rn, last_name from s_emp) loop
        if mod(r.rn,2)<>0 then
           dbms_output.put_line(r.rn||': '||r.last_name);
        end if;
    end loop;
end;
/

1: Velasquez
3: Nagayama
5: Ropeburn
7: Menchu

Method 4 -- Relocate work to a procedure:
Code:
declare
    procedure work_to_do (row number, txt varchar2) is
        begin
           dbms_output.put_line(row||': '||txt);
        end;
begin
    for r in (select rownum rn, last_name from s_emp) loop
        if mod(r.rn,2)<>0 then
           work_to_do(r.rn,r.last_name);
        end if;
    end loop;
end;
/

1: Velasquez
3: Nagayama
5: Ropeburn
7: Menchu
Let us know if any of these meet with your approval.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]
 
Thank you very much, this is exactly what I want. I thought loop always goes with continue and exit.
Thanks a million
 
Being of the lazy persuasion, I'd prefer to exclude rows I don't want to process from my cursor:
Code:
for i in (select col_1, col2 from tab_a WHERE col_1 != 'A')
loop
  if i.col_1 ='A' then
    continue;
  end if;
end loop;
 
And yes, Huchen, I do prefer Carp's method (of filtering in the CURSOR definition) versus filtering in the code via an "IF" statement, for example. The following shows how much tighter my earlier code can be using Carp's method:
Code:
begin
    for r in (select rn, last_name
                from (select rownum rn, last_name from s_emp)
               where mod(rn,2)<>0) loop
        dbms_output.put_line(r.rn||': '||r.last_name);
    end loop;
end;
/

1: Velasquez
3: Nagayama
5: Ropeburn
7: Menchu
But to clarify (for the "Recreational Readers" that may see this thread later):

1) The WHERE clause in Carp's code, above, causes the "IF" statement to become extraneous, and
2) "continue" is not a valid key word in PL/SQL...it was Carp's method of indicating <more work happens here>, right Dave?

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]
 
Actually I did use Carp's method before I got response from you. I was curious on how to use "continue" in pl/sql. After I read Oracle doc and your response, I now know that there is no 'continue' in pl/sql loop.
Thank you very much.
 
No, Dave - as previously mentioned, I am of the lazy persuasion. It was an artifact left over from a sloppy cut/paste operation. Thank you for clarifying that for our viewing audience!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top