REF Cursor | Oracle

Using REF CURSORs (or cursor variable)is one of the most powerful, flexible, and scalable ways to return query results.

Its a datatype whose value is the memory address of a SQL query result. So, basically its a pointer to a result-set. It allows a user to pass the reference of this resultset among all the programs that need access to it.

Like a normal cursor, its data is accessible in the forward only direction. And also a REF CURSOR is read-only and its data cannot be updated.

TYPES
- Strong REF cursor
- Weak REF cursor

While declaring, if the return type of ref cursor is defined, it becomes a strong cursor.

TYPE emp_curs IS REF CURSOR
RETURNING emp%ROWTYPE;

And if the returning block, returns a ref cursor of a different type, then the one defined, it will raise a "ROWTYPE_MISMATCH" exception.

Diff between a cursor and REF cursor
At the basic level, they are both same. But a normal cursor is quite static in its definition, and its query cannot be changed. Whereas a REF cursor can be opened and used as per the requirement.

And a REF cursor can be passed across program blocks, but a normal cursor can't.

----------

A REF cursor is a variable , so it can be passed as parameter across program blocks.

TYPE emp_curs IS REF CURSOR
RETURNING emp%ROWTYPE;

se emp_curs;

Here the se is a variable of ref cursor type and it can be used for emp table records.


oracle-plsql-ref-cursors