Oracle Tips : How to use a Cursor variable

 A cursor variable is a pointer (declared as type ref cursor) to an actual cursor. Code which is written to manipulate a cursor variable can be reused for successive assignments to different actual cursors.

 

 

--Example for using a cursor variable

DECLARE

 my_cursor        SYS_REFCURSOR;  --Declaring a cursor variable

 v_choice           NUMBER(1);

 v_dept_row dept%ROWTYPE;

 v_emp_row  emp%ROWTYPE;

BEGIN

 

 v_choice := &choice;   --Getting option

 

 --Opening cursor for different conditions using only one cursor variable in PL/SQL Body

 IF v_choice =1 THEN

    OPEN my_cursor           

            FOR select * from dept where deptno=10;

            FETCH my_cursor INTO v_dept_row;

            DBMS_OUTPUT.PUT_LINE(v_dept_row.dname);

 ELSIF v_choice =2 THEN

    OPEN my_cursor

            FOR select * from emp where ename='KING';

            FETCH my_cursor INTO v_emp_row;

            DBMS_OUTPUT.PUT_LINE(v_emp_row.ename);

 ELSE

    OPEN my_cursor

            FOR select * from emp where empno=721;

            FETCH my_cursor INTO v_emp_row;

            DBMS_OUTPUT.PUT_LINE(v_emp_row.ename);

 END IF;

 CLOSE my_cursor;

 

END;

 

--How to declare a cursor inside a cursor

DECLARE

 emp_cur           SYS_REFCURSOR;

 v_deptno          dept.deptno%type;

 v_dname          dept.dname%type;

 

 CURSOR cur_dept IS

 SELECT deptno,dname,

 CURSOR(select empno,ename from emp where deptno=dept.deptno)

 FROM dept where deptno=30;

 

 v_empno          emp.empno%type;

 v_ename          emp.ename%type;

 

BEGIN

  OPEN cur_dept;

  FETCH cur_dept INTO v_deptno,v_dname,emp_cur;

  DBMS_OUTPUT.PUT_LINE(v_dname);

  DBMS_OUTPUT.PUT_LINE('---------------------------');

  LOOP

    FETCH emp_cur INTO v_empno,v_ename;

            EXIT WHEN emp_cuR%NOTFOUND;

            DBMS_OUTPUT.PUT_LINE(v_ename);

  END LOOP;

  CLOSE emp_cur;

  CLOSE cur_dept;

END;