help on cursor/if statement code

[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

------------------------------------------------------------------
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;

[stecal]

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)?

[kyleknicks]

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;

[stecal]

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.