One of the advantage of REF CURSOR is, you can use defined CURSOR type variable to open a new cursor after closing the previous. So by saving multiple CURSOR definations in DECLARATION. Using REF CURSOR you can also define multiple cursor variables, if you need to open multiple cursors at a time
F.ex:
Code:
DECLARE
TYPE cCurType is REF CURSOR;
cMyFirstCursor cCurType;
cMySecondCursor cCurType;
BEGIN
OPEN cMyFirstCursor FOR
SELECT id, name
FROM table_x;
LOOP
-- do processing
END LOOP;
CLOSE cMyFirstCursor;
OPEN cMyFirstCursor FOR
SELECT id, name
FROM table_y;
LOOP
-- do processing
OPEN cMySecondCursor FOR
SELECT id, name
FROM table_y1;
LOOP
-- do processing for second cursor
END LOOP;
CLOSE cMySecondCursor;
END LOOP;
CLOSE cMyFirstCursor;
OPEN cMyFirstCursor FOR
SELECT id, name
FROM table_z;
LOOP
-- do processing
END LOOP;
CLOSE cMyFirstCursor;
END;
Where as one of the normal CURSOR declaration would be
Code:
DECLARE
CURSOR c1 IS
SELECT id, name
FROM table_x;
CURSOR c2 IS
SELECT id, name
FROM table_y;
CURSOR c3 IS
SELECT id, name
FROM table_z;
BEGIN
OPEN c1;
LOOP
-- do processing
END LOOP;
CLOSE c1;
OPEN c2;
LOOP
-- do processing
END LOOP;
CLOSE c2;
OPEN c3;
LOOP
-- do processing
END LOOP;
CLOSE c3;
END;
HTH
Sameer