help on cursor/if statement code
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: help on cursor/if statement code

  1. #1
    Join Date
    Apr 2002
    Posts
    29

    help on cursor/if statement code

    can someone tell me why...... after going thru the loop and if statement..and meeting the correct condition (lets say.. it found C).... instead of hitting the "exit when cursor not found", it goes up and starts the loop again... and then it raises one of the exceptions and quits. any ideas? must be my syntax

    ------------------------------------------------------------------
    OPEN Cursor;

    LOOP

    Fetch x into v_xxx;

    IF A then raise e_no_ids;

    elsif B then raise e_ids;

    elsif C then raise e_no_ids;

    elsif D then update XXX;

    elsif E then update YYY;

    else Null;

    END IF;

    EXIT WHEN cursor%NOTFOUND;
    END LOOP;


    CLOSE cursor;

    Commit;

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    Well, for clarity, it would help to use a bit more actual code. When you say "open cursor," and then do the "fetch x into v-xxx," in real life, are you coding "open x" so the fetch statement is in the correct syntax (fetch x the cursor name into v_xxx the record name)?

  3. #3
    Join Date
    Apr 2002
    Posts
    29
    there isn't much more i could add... but here it is.. hope this helps..

    -----------------------------------------------------------------

    CURSOR C_statements is
    select name, amt from accounts;

    BEGIN

    OPEN Cursor C_statements;

    LOOP

    Fetch C_statements into v_name, v_amt;

    IF A then raise e_no_ids;

    elsif B then raise e_ids;

    elsif C then raise e_no_ids;

    elsif D then update accounts_old;

    elsif E then update accounts_new;

    else Null;

    END IF;

    EXIT WHEN C_statements%NOTFOUND;
    END LOOP;


    CLOSE C_statements;

    Commit;

  4. #4
    Join Date
    May 2002
    Posts
    2,645

    Re: help on cursor/if statement code

    Originally posted by kyleknicks
    can someone tell me why...... after going thru the loop and if statement..and meeting the correct condition (lets say.. it found C).... instead of hitting the "exit when cursor not found", it goes up and starts the loop again... and then it raises one of the exceptions and quits. any ideas? must be my syntax
    First, if C evaluates to be true (IF C then raise...), you are done with the loop. You can go thru the loop as little as one time. Raising an exception is a way to exit a loop. So your "let's say it found C" means you would not hit the %NOTFOUND check.

    If the process started the loop again, you must have hit the ELSE statement and did nothing (null) except cycle thru the loop. There are two ways to exit the loop in your case: an IF-THEN evaluates to true (so you raise an exception), or your cursor is either NOTFOUND because there are no records, or you have processed the last record.

Posting Permissions

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


Click Here to Expand Forum to Full Width