Introduction to PL/SQL Ref Cursors in Oracle Database
When it comes to performance, Ref Cursors have always proven themselves superior to static cursors. This is because of the ability of a single ref cursor to get associated with multiple SELECT statements in a single PL/SQL block. This then reduces the need for writing separate explicit cursors which in turn makes them a highly efficient, flexible and robust feature of PL/SQL language. So let’s explore Ref Cursor in PL/SQL and learn more about it.
What is PL/SQL Ref Cursor?
Ref Cursor is an acronym of Reference to a Cursor
. It is a PL/SQL datatype
using which you can declare a special type of variable called Cursor Variable
Are Cursor Variable and Ref Cursor connected to each other?
That is a very good question. I saw in many books that both these names are used interchangeably. Well, both of them are two separate yet inter-dependent topics.
Cursor variable is like a pointer which refers to different context area in SGA whereas Ref Cursor is a datatype which holds a cursor value.
Then, how are Cursor Variable and Ref Cursors inter-dependent to each other?
As mentioned above, Cursor Variables are declared with the help of Ref Cursors. We can say that in Oracle Database cursor variables exist in the form of Ref Cursors.
Definition of Cursor Variable
Using all the above information we can define cursor variable as:
A variable of Ref Cursor Type is called Cursor Variable.
What is the syntax for Declaring Ref Cursor?
Here is the generalized syntax for declaring Ref cursors in Oracle Database:
TYPE [cursor_variable_name] IS REF CURSOR [RETURN (return_type)];
Syntax can be modified and used depending on what type of Ref Cursors you want to use in your application. With that said let’s see the types of Ref Cursors available in Oracle Database.
How many types of ref cursors do we have?
There are two types of Ref Cursors in PL/SQL. These are:
- Strong Ref Cursor and
- Weak Ref Cursor
What is Strong Ref Cursor in Oracle Database?
Any Ref Cursor which has fixed return type is called a Strong Ref Cursor.
Such ref cursors can only be used with those SELECT statements
that return the result whose datatype matches with the one that we have fixed during the strong cursor’s declaration.
Syntax of Strong Ref Cursors in PL/SQL
Here is the strong ref cursor’s syntax.
TYPE cursor_variable_name IS REF CURSOR
RETURN (return type);
Return clause plays a very vital role in declaring a Ref Cursor. It restricts its scope and makes your Ref Cursor limited to only those SELECT statements that return the result whose datatype matches with the one you specified in the RETURN clause while declaring it.
Also the return type of a Ref Cursor must always be of Record Datatype. It can either be record structure of a table or user defined record structure.
What is Weak Ref Cursor in Oracle Database?
In contrast to strong ref cursors, weak ref cursors are those which do not have any return type. In simple words, those ref cursors which do not have fixed return type are called weak ref cursors.
Since weak Ref Cursors do not have any fixed return type thus they are open to all SELECT statements and this makes them one of the most used Ref Cursors in PL/SQL.
Syntax of Weak Ref Cursors in PL/SQL
TYPE ref_cursor_name IS REF CURSOR;
Except from the missing return clause the syntax is pretty much similar to the strong ref cursors.
A Canadian study suggests that humans are biologically wired to be lazy. Also, what is the point of having a computer with all these processing powers when we have to do all the hard work of declaring a Ref pointer type and then creating a variable.
Well, my dear friends if you are one of those who want to refrain from doing all the hard work of creating a Cursor Variable then Oracle PL/SQL has an option for you. This option will serve as an implicitly created cursor variable to you. Let’s see what it is.
Sys Ref cursor is an Oracle built in cursor variable. It declares a weak ref cursor and that too without declaring the ref pointer type. It is mostly used as a generic cursor which can be passed as an argument to a stored sub program.
That’s it for this tutorial on the introduction to PL/SQL Ref Cursors in Oracle Database. Stay tuned as in the upcoming tutorials we will do some practical demonstration of the Ref Cursor in Oracle PL/SQL.
You can also refer to the video tutorial on the same topic on my YouTube channel for more details. Thanks for stopping by. Have a great day!
Need your help in deciding!
If you have noticed from the last couple of blogs we have changed our format from traditional paragraph writing to Question-Answer based articles. This new format is more focused on covering topic-based questions which could help you with your Certification exam as well as your Interview. Please let us know how do you feel about these newly styled blogs? Should we produce more such blogs in the Question-Answer format or switch back to the old style? You can Tweet any one of these options:
- Old paragraphs style blogs are good please bring those back: Tweet This or
- New style is more focused and detailed. I love it: Tweet This
You can also send us your opinions and suggestions on our Facebook Page
SQL Script and Presentation used
You can DOWNLOAD SQL script and presentation used in the Video and in this article.
NO SQL Script used in Video and in this article
How do you like this blog? Is there anything you want us to improve? Tell us, what you feel on our Facebook page
and on our Twitter