%NOTFOUND cursor variable

[ali0482]

In what order should a open/fetch/loop set of commands in a PL/SQL block be implemented if you use the %NOTFOUND cursor variable in the exit when statement? Why?

 

[cjard]

Order is sensibly either:
OPEN LOOP FETCH EXITWHEN%NOTFOUND ENDLOOP
OPEN FETCH LOOPWHEN%FOUND FETCH ENDLOOP


Why? Because if you swap it around the loop won't run or won't fetch! The fetch has to be inside a loop in order that fetching occurs repeatedly, if you've started the loop already you must choose to exit if there is nothing to do. If youre only starting the loop when there is something to do, you must fetch first so there might be soemthing to do

There's a caveat to where you put the EXIT WHEN:

If youre going to EXIT WHEN cur%NOTFOUND during a bulk collect, you must do so after your loop body statements because fetching the last rows of a result set will cause the cursor to become NOTFOUND = true

e.g.
OPEN cur;
LOOP FETCH cur BULK COLLECT INTO buf LIMIT 100
EXIT WHEN cur%NOTFOUND
..blah
END LOOP;

If you have 110 results, you FETCH 100, NOTFOUND is false and you don't exit, process your rows, FETCH another 100 rows, only 10 fill into the buffer, and the cursor reaches the end, NOTFOUND is true and your last 10 rows don't get processed

For normal fetch you probably want the fetch at the start, if your loop body refers to the current row, i.e. you want to exit when there are no more rows, without trying to sue a nonexistant row

OPEN T1Cursor;
LOOP FETCH T1Cursor INTO a, b;
  EXIT WHEN T1Cursor%NOTFOUND;
  DELETE FROM T1 WHERE CURRENT OF T1Cursor;
END LOOP;

 

[cjard]

ps this forum is for questions about visual studio like "How do i add a toolbar"
Post oracle questinos in the oracle forum, thanks