| moving to next record in loop [message #33815] |
Wed, 03 November 2004 13:26  |
L Boren
Messages: 16
Registered: July 2004
|
Junior Member |
|
|
I've been searching online and I am trying to find the correct syntax for breaking out of a loop used with a cursor and fetching the next record. I have a very long stored procedure in which I pull the records in a table into a cursor and loop through them performing multiple processing steps. At some points in the loop, the record cannot be processed, at which time I need to record an error and fetch the next record....without running any of the code that follows. Can anyone advise on the syntax for doing that? I know I can use exit to end the loop, but how to I force the procedure to fetch the next record in the cursor in the middle of the loop? Here is my basic syntax (using an Oracle 9i db):
CURSOR c_Temp IS
SELECT column1, column2, column3
from table1
BEGIN
OPEN c_Temp;
LOOP
--OPEN c_AssetTemp;
/*Step 1: Retrieve each row into PL/SQL variables*/
FETCH c_Temp
INTO variable1,
variable2,
variable3
EXIT WHEN c_Temp%NOTFOUND;
begin
...<multiple if/else/end if blocks such as below>
if <condition> then
<goto next record in cursor>
elsif <condition> then
<perform calculations & insert some records in other tables>
elsif <condition> then
<perform calculations & insert some records in other tables>
else
<goto next record in cursor>
end if;
end;
END LOOP;
CLOSE c_Temp;
|
|
|
|
| Re: moving to next record in loop [message #33816 is a reply to message #33815] |
Wed, 03 November 2004 13:41  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
would a simple structure like this work for you? An implicit cursor is easier to code too...
for i in (select col1, col2 from tab) loop
begin
if i.col1 > 10 then
...
else
...
end if;
-- catch exceptions on current record
exception
when others then
log error...
end;
end loop; |
|
|
|
| Re: moving to next record in loop [message #33844 is a reply to message #33816] |
Thu, 04 November 2004 11:41 |
L Boren
Messages: 16
Registered: July 2004
|
Junior Member |
|
|
|
That structure would work I suppose, but I don't see where it is breaking our of the current record and going to the next record in the loop. My current procedure is quite large (1600 lines) and the structure works, so I don't want to redo it...I just need to know how to, at certain points in the loop, control the flow so that it goes to the next record in the loop.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
