Ref Cursors

Ref cursors are cursor variables.

Strongly typed example:

DECLARE
    TYPE rc_dept IS REF CURSOR RETURN departments%ROWTYPE;  -- strongly typed
    rc_dept_cur rc_dept;
    l_dept_rowtype departments%ROWTYPE;
    l_id departments.dept_id%TYPE := 1;
    l_dept_id departments.dept_id%TYPE;
    l_dept_name departments.dept_name%TYPE;
BEGIN
    OPEN rc_dept_cur FOR
        SELECT * FROM departments
            WHERE dept_id = l_id;
    l_id := 2;
    LOOP
        FETCH rc_dept_cur INTO l_dept_rowtype;
        EXIT WHEN rc_dept_cur%NOTFOUND;
        dbms_output.PUT_LINE(l_dept_rowtype.dept_id);
    END LOOP;
    OPEN rc_dept_cur FOR  -- reuse ref cursor
        SELECT * FROM departments
            WHERE dept_name = 'Accounting';
    LOOP
        FETCH rc_dept_cur INTO l_dept_id, l_dept_name;
        EXIT WHEN rc_dept_cur%NOTFOUND;
        dbms_output.PUT_LINE(l_dept_id);
    END LOOP;
    CLOSE rc_dept_cur;
END;

User-defined type example:

DECLARE
    TYPE dept_rec IS RECORD(dept_id departments.dept_id%TYPE,
                            dept_name departments.dept_name%TYPE);
    TYPE rc_dept IS REF CURSOR RETURN dept_rec;
    rc_dept_cur rc_dept;
    l_dept_rec dept_rec;
    l_choice NUMBER := 1;
BEGIN
    IF l_choice = 1 THEN
        OPEN rc_dept_cur FOR
            SELECT * FROM departments
                WHERE dept_id = 1;
    ELSE
        OPEN rc_dept_cur FOR
            SELECT * FROM departments
                WHERE dept_name = 'Accounting';
    END IF;
    LOOP
        FETCH rc_dept_cur INTO l_dept_rec;
        EXIT WHEN rc_dept_cur%NOTFOUND;
        dbms_output.PUT_LINE(l_dept_rec.dept_id);
    END LOOP;
    CLOSE rc_dept_cur;
END;

Weakly typed example:

DECLARE
    TYPE rc_weak IS REF CURSOR;
    rc_weak_cur rc_weak;
    l_dept_rowtype departments%ROWTYPE;
    l_emp_rowtype employee%ROWTYPE;
BEGIN
    OPEN rc_weak_cur FOR
        SELECT * FROM departments
            WHERE dept_id = 1;
    LOOP
        FETCH rc_weak_cur INTO l_dept_rowtype;
        EXIT WHEN rc_weak_cur%NOTFOUND;
        dbms_output.PUT_LINE(l_dept_rowtype.dept_name);
    END LOOP;
    OPEN rc_weak_cur FOR
        SELECT * FROM employee
            WHERE emp_dept_id = 2;
    LOOP
        FETCH rc_weak_cur INTO l_emp_rowtype;
        EXIT WHEN rc_weak_cur%NOTFOUND;
        dbms_output.PUT_LINE(l_emp_rowtype.emp_id);
    END LOOP;
    CLOSE rc_weak_cur;
END;