Ref Cursor

What is REF-Cursor with example

Ref Cursor:-

Ref Cursor is a dynamic cursor in Oracle. We can change cursor definition as run time but  it define and declare once.

Two type of Ref Cursor

Week Ref Cursor:- Week ref cursor never return a value.

Strong Ref Cursor:- Strong ref cursor must return a value.

Example of week    ref cursor
Example 1-

DECLARE
TYPE XXC_REF_CUR IS REF CURSOR;
XXC_REF_CUR_C1 XXC_REF_CUR;
EMP_ID EMPLOYEES.EMPLOYEE_ID%TYPE;
L_NAME EMPLOYEES.LAST_NAME%TYPE;
BEGIN
OPEN XXC_REF_CUR_C1 FOR
SELECT EMPLOYEE_ID,LAST_NAME
FROM EMPLOYEES;
LOOP
FETCH XXC_REF_CUR_C1 INTO EMP_ID,L_NAME;
EXIT WHEN XXC_REF_CUR_C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘EMPLOYEE_ID IS  ‘||EMP_ID||’ and ‘||’LAST_NAME IS  ‘||L_NAME);
END LOOP;
CLOSE XXC_REF_CUR_C1;
OPEN XXC_REF_CUR_C1 FOR
SELECT DEPARTMENT_ID,DEPARTMENT_NAME
FROM DEPARTMENTS;
LOOP
FETCH XXC_REF_CUR_C1 INTO DEPT_ID,DEPT_NAME;
EXIT WHEN XXC_REF_CUR_C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘DEPARTMENT ID IS  ‘||DEPT_ID||’ and ‘||’DEPARTMENT NAME IS  ‘||DEPT_NAME);
END LOOP;
CLOSE XXC_REF_CUR_C1;
END;

Example 2-

DECLARE
TYPE XXC_REF_CUR IS REF CURSOR;
XXC_REF_CUR_C1 XXC_REF_CUR;
EMP_REC EMPLOYEES%ROWTYPE;
DEPT_REC DEPARTMENTS%ROWTYPE;
BEGIN
OPEN XXC_REF_CUR_C1 FOR
SELECT *
FROM EMPLOYEES;
LOOP
FETCH XXC_REF_CUR_C1 INTO EMP_REC;
EXIT WHEN XXC_REF_CUR_C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘EMPLOYEE_ID IS  ‘||EMP_REC.EMPLOYEE_ID||’ and ‘||’LAST_NAME IS  ‘||EMP_REC.LAST_NAME);
END LOOP;
CLOSE XXC_REF_CUR_C1;
OPEN XXC_REF_CUR_C1 FOR
SELECT *
FROM DEPARTMENTS;
LOOP
FETCH XXC_REF_CUR_C1 INTO DEPT_REC;
EXIT WHEN XXC_REF_CUR_C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘DEPARTMENT ID IS  ‘||DEPT_REC.DEPARTMENT_ID||’ and ‘||’DEPARTMENT NAME IS  ‘||DEPT_REC.DEPARTMENT_NAME);
END LOOP;
CLOSE XXC_REF_CUR_C1;
END;

Example of Strong ref cursor

DECLARE
TYPE XXC_EMP_REF_CUR IS REF CURSOR RETURN EMPLOYEES%ROWTYPE;
XXC_EMP_REF_CUR_C1 XXC_EMP_REF_CUR;
TYPE XXC_DEPT_REF_CUR IS REF CURSOR RETURN DEPARTMENTS%ROWTYPE;
XXC_DEPT_REF_CUR_C1 XXC_DEPT_REF_CUR;
EMP_REC EMPLOYEES%ROWTYPE;
DEPT_REC DEPARTMENTS%ROWTYPE;
BEGIN
OPEN XXC_EMP_REF_CUR_C1 FOR
SELECT *
FROM EMPLOYEES;
LOOP
FETCH XXC_EMP_REF_CUR_C1 INTO EMP_REC;
EXIT WHEN XXC_EMP_REF_CUR_C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘EMPLOYEE_ID IS  ‘||EMP_REC.EMPLOYEE_ID||’ and ‘||’LAST_NAME IS  ‘||EMP_REC.LAST_NAME);
END LOOP;
CLOSE XXC_EMP_REF_CUR_C1;
OPEN XXC_DEPT_REF_CUR_C1 FOR
SELECT *
FROM DEPARTMENTS;
LOOP
FETCH XXC_DEPT_REF_CUR_C1 INTO DEPT_REC;
EXIT WHEN XXC_DEPT_REF_CUR_C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘DEPARTMENT ID IS  ‘||DEPT_REC.DEPARTMENT_ID||’ and ‘||’DEPARTMENT NAME IS  ‘||DEPT_REC.DEPARTMENT_NAME);
END LOOP;
CLOSE XXC_DEPT_REF_CUR_C1;
END;

SYS Ref Cursor:-

DECLARE
XXC_EMP_REF_CUR SYS_REFCURSOR;
EMP_REC EMPLOYEES%ROWTYPE;
BEGIN
OPEN XXC_EMP_REF_CUR FOR SELECT * FROM EMPLOYEES;
LOOP
FETCH XXC_EMP_REF_CUR INTO EMP_REC;
EXIT WHEN XXC_EMP_REF_CUR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(EMP_REC.EMPLOYEE_ID||CHR(9)||EMP_REC.LAST_NAME||CHR(9)||EMP_REC.HIRE_DATE);
END LOOP;
END;