Results 1 to 3 of 3

Thread: loop in cursors

  1. #1
    Join Date
    May 2003
    Posts
    47

    Question Unanswered: loop in cursors

    hi,
    how can i forcefully move to the next record..

    example...
    fetch ....
    loop

    if condition satisfies
    move to next record
    end if

    stmt 1;
    stmt 2;

    end loop

  2. #2
    Join Date
    Sep 2004
    Posts
    16

    Use collections

    Hi,
    You could use collections to do this.
    Basically, you collect all records that you need into a collection table. There are built in functions that can be applied to the collections - like 'next' in your case, which would return the index of the next record.

    The following is an example, which would check if the salary is greater than 1000. If it is, then it would simply go to the next record, and not process the current one.


    declare
    type saltype is table of number;--collection type
    mytable saltype; --collection variable of the type
    i binary_integer; -- index for looping
    begin
    select sal bulk collect into mytable from emp where sal is not null;
    --bulk collect works faster

    i := mytable.first; --start from the first record of the collection

    while i is not null
    loop
    if mytable(i) > 1000 then
    i := mytable.next(i); -- skip and move to next record
    else
    --do your stmt1, stmt2 processing here
    i := mytable.next(i); --advance to the next rec
    end if;

    end loop;
    end;

    Hope this helps !

    -Vinita
    Last edited by vinitasinha; 11-03-04 at 07:26.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You can do it like this:
    Code:
    for rec in (select * from emp)
    loop
      if condition_satisfied then
        goto DONE;
      end if;
      -- Process this record
      stmt1;
      stmt2;
      <<DONE>>
      null;
    end loop;
    Or if you don't like GOTO (and who does, really ):
    Code:
    for rec in (select * from emp)
    loop
      if NOT condition_satisfied then
        -- Process this record
        stmt1;
        stmt2;
      end if;
    end loop;
    ... which is actually neater in this case. However, the GOTO may be preferable if there are several such tests in the loop:
    Code:
    for rec in (select * from emp)
    loop
      if condition1_satisfied then
        goto DONE;
      end if;
      -- Process this record
      stmt1;
      stmt2;
      if condition2_satisfied then
        goto DONE;
      end if;
      -- Process this record a bit more
      stmt3;
      stmt4;
      if condition3_satisfied then
        goto DONE;
      end if;
      -- Process this record a bit more
      stmt5;
      stmt6;
      <<DONE>>
      null;
    end loop;
    versus:
    Code:
    for rec in (select * from emp)
    loop
      if NOT condition1_satisfied then
        -- Process this record
        stmt1;
        stmt2;
        if NOT condition2_satisfied then
          -- Process this record a bit more
          stmt3;
          stmt4;
          if NOT condition3_satisfied then
            -- Process this record a bit more
            stmt5;
            stmt6;
          end if;
        end if;
      end if;
    end loop;
    Actually, I still prefer the nested IF approach in this example! But imagine if there were 12 such tests instead of 3...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •