22/03/2013

Oracle update table while looping on it

You may find yourself needing to update a table while looping on it:

DECLARE
newValue someType;
BEGIN
FOR myCur IN (SELECT key FROM table)LOOP
    newValue:=myFunction(key);
    UPDATE table
    SET column=newValue
    WHERE key=myCur.key;
    COMMIT;
END LOOP;
END;
/

This however is not possible in Oracle, instead you should fetch the table keys into an array before and then cycle it:

DECLARE
newValue someType;
TYPE vArrayType IS VARRAY([size]) OF someType; --use VARRAY if you already know the size!
myArray vArrayType;
BEGIN
--fill the array

SELECT key
BULK COLLECT INTO myArray
FROM table;

--loop on it
FOR i IN myArray.FIRST..myArray.LAST LOOP

    newValue:=myFunction(myArray(i));
    UPDATE table
    SET column=newValue
    WHERE key=myArray(i);
    COMMIT;
END LOOP;
END;
/

No comments:

Post a Comment

With great power comes great responsibility.

Da grandi poteri derivano grandi responsabilità.