Is there a way to restart a cursor? Oracle

Posted by Solid1Snake1 on Stack Overflow See other posts from Stack Overflow or by Solid1Snake1
Published on 2012-09-27T21:27:21Z Indexed on 2012/09/27 21:37 UTC
Read the original article Hit count: 139

Filed under:
|

I am trying to do something such as:

for(int i = 0; i<10; i++)
{
    for(int j = 0; j<10; j++)
    {
        Blah;
    }
}

//As you can see each time that there is a different i, j starts at 0 again.

Using cursors in Oracle. But if I'm correct, after I fetch all rows from a cursor, it will not restart. Is there a way to do this?

Here is my sql:

CREATE OR REPLACE PROCEDURE SSACHDEV.SyncTeleappWithClientinfo
as
teleCase NUMBER;

CURSOR TeleAppCursor
is
    Select 
        distinct(casenbr)
    from TeleApp;


CURSOR ClientInfoCursor
is
    Select casenbr 
    from clientinfo 
    where trim(cashwithappyn) is null;

BEGIN

    open TeleAppCursor;
    open ClientInfoCursor;

    LOOP
        fetch TeleAppCursor into teleCase;
        EXIT when TeleAppCursor%NOTFOUND;

        LOOP
            fetch ClientInfoCursor into clientCase;
            EXIT when ClientInfoCursor%NOTFOUND;
                if clientCase = teleCase then

                    update ClientInfo 
                    set cashwithappyn = (select cashwithappyn from teleapp where casenbr = clientCase) 
                    where casenbr = clientCase;

                    break;
                end if;
        END LOOP;
    END LOOP;

END;

I did check online and was unable to find anything on this.

© Stack Overflow or respective owner

Related posts about sql

Related posts about Oracle