Define Ref Cursor and its types with Examples

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;