Ref Cursor

Ref Cursor:

A REF CURSOR is basically a data type. A variable created based on such a data type is generally called a cursor variable. A cursor variable can be associated with different queries at run-time. The primary advantage of using cursor variables is their capability to pass result sets between sub programs (like stored procedures, functions, packages etc.).

Ref Cursor is of two types:

1. Strong ref cursor: This has a return type defined.

2. Weak ref cursor: This doesn’t have a return type.

Simple Example of Ref Cursor:

Example 1:

DECLARE

TYPE R_CURSOR IS REF CURSOR;

C_EMP R_CURSOR;

EN EMP.ENAME%TYPE;

BEGIN

OPEN C_EMP FOR SELECT ENAME FROM EMP;

LOOP

FETCH C_EMP INTO EN;

EXIT WHEN C_EMP%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(EN);

END LOOP;

CLOSE C_EMP;

END;

Example 2:

DECLARE

TYPE EMP_TAB IS REF CURSOR;

EMP_REC EMP_TAB;

EMP_SRC EMP%ROWTYPE;

DEPTID NUMBER(2):=10;

SQL_STMT Varchar2(200);

BEGIN

SQL_STMT:='SELECT * FROM EMP WHERE DEPTNO=:D';

OPEN EMP_REC FOR SQL_STMT USING DEPTID;

LOOP

FETCH EMP_REC INTO EMP_SRC;

EXIT WHEN EMP_REC%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(EMP_SRC.ENAME);

END LOOP;

CLOSE EMP_REC;

END;

EXAMPLE 3:

DECLARE

TYPE R_CURSOR IS REF CURSOR;

C_EMP R_CURSOR;

TYPE REC_EMP IS RECORD

(

NAME VARCHAR2(20),

SAL NUMBER(6)

);

ER REC_EMP;

BEGIN

OPEN C_EMP FOR SELECT ENAME,SAL FROM EMP;

LOOP

FETCH C_EMP INTO ER;

EXIT WHEN C_EMP%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(ER.NAME || ' - ' || ER.SAL);

END LOOP;

CLOSE C_EMP;

END;