CODEWITHSUNDEEP
sqloracle-database
Solid1Snake1
Solid1Snake1

Reputation: 272

Is there a way to restart a cursor? Oracle

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.

Upvotes: 1

Views: 9292

Answers (2)

kurosch
kurosch

Reputation: 2312

You don't need the second cursor at all, just use the set operations in Oracle to update the appropriate records without manually searching for them yourself:

DECLARE
    v_teleCase      TeleApp.teleCase%TYPE;
    v_cashwithappyn TeleApp.cashwithappyn%TYPE

    CURSOR TeleAppCursor
    is
        Select 
            distinct casenbr, cashwithappyn
        from TeleApp;

BEGIN

    open TeleAppCursor;

    LOOP
        fetch TeleAppCursor into v_teleCase, v_cashwithappyn;
        EXIT when TeleAppCursor%NOTFOUND;

        UPDATE ClientInfo
        SET cashwithappyn = v_cashwithappyn
        WHERE casenbr = v_teleCase
        AND trim(cashwithappyn) is null;

    END LOOP;

END;

It's also a good idea to not have variables with the same name as columns.

Upvotes: 0

Conrad Frix
Conrad Frix

Reputation: 52645

Instead of restarting the Cursor you could use a table variable to store the results of sql statement and then loop over the table an arbitrary number of times.

Here's an example using the SQL Fiddle Sample data. 

DECLARE 
    CURSOR c1 IS 
      SELECT id, 
             TYPE, 
             details 
      FROM   supportcontacts; 

    TYPE contactrec 
      IS TABLE OF c1%ROWTYPE INDEX BY BINARY_INTEGER; 

    acontact    c1%ROWTYPE; 
    contactlist CONTACTREC; 
    counter     INTEGER; 
BEGIN 
    counter := 0; 

    OPEN c1; 

    LOOP 
        FETCH c1 INTO acontact; 

        IF c1%FOUND THEN 
          counter := counter + 1; 
        END IF; 

        Contactlist(counter) := acontact; 

        IF c1%NOTFOUND THEN 
          EXIT; 
        END IF; 
    END LOOP; 

    CLOSE c1; 



    FOR i IN 1..5 LOOP 
        FOR j IN 1..counter LOOP 
            dbms_output.Put_line(Contactlist(j).type  || ' ' || Contactlist(j).details); 
        END LOOP; 
    END LOOP; 
END; 

/

which outputs 

Email [email protected]
Twitter @sqlfiddle
Email [email protected]
Twitter @sqlfiddle
Email [email protected]
Twitter @sqlfiddle
Email [email protected]
Twitter @sqlfiddle
Email [email protected]
Twitter @sqlfiddle

Here's the SQL Fiddle but I can't figure out how to see the output from dbms_output

Upvotes: 4

Related Questions