REF CURSOR

REF CURSOR

The REF CURSOR is a data type in the Oracle. REF CURSOR also referred as Cursor Variables.Cursor variables are like pointers to result sets. Cursor can be attached to only one query while REF CURSOR can be used to associate multiple queries.

 

Example :-

 

Declare

TYPE empcurtyp IS REF CURSOR RETURN emp%ROWTYPE;

refcur1  empcurtyp;

Begin

Open refcur1 for select * from emp;

Open refcur1 for select * from dept;

End;

 

REF CURSOR can be categorized into three

  1. Strong Ref Cursor

Ref Cursors which has a return type is classified as Strong Ref Cursor.

 

Example :-

Declare

TYPE empcurtyp IS REF CURSOR RETURN emp%ROWTYPE;

…..

End;

 

  1. Weak Ref Cursor

Ref Cursors which has no return type is classified as Weak Ref Cursor.

 

Example :-

 

Declare

TYPE empcurtyp IS REF CURSOR;

…..

End;

 

  1. System Ref Cursor

This is a system defined Ref Cursor. This also considered weak. System Ref Cursor need not declare explicitly.

Declare

empcurtyp SYS_REFCURSOR;

…..

End;

 

Advantages

  1. Ref Cursor it self is a data type and easy to declare
  2. More flexible because it is not tied to a specific query
  3. Easily pass as arguments from subroutine to subroutine.
  4. Very handy in transferring data between multi-language application (ex:- Java and Oracle, Dot.net and Oracle, Oracle Forms and Oracle). Since it is a pointer to the result set any client and server program can use the pointer to access the data.
  5. Cursor variables are bind variables

 

Dis-advantages

  1. Ref Cursors are not efficient as StatiC Cursor.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s