fetch

PL/SQL tutorial 67: PL/SQL Ref Cursors In Oracle Database by Manish Sharma



Sharing buttons:

What’s up internet?

I am Manish from RebellionRider.com and I am back once again with another PL/SQL Tutorial.

In today’s tutorial we will learn what PL/SQL Ref Cursors are in Oracle Database.

A little bit knowledge of database cursors will be required for the better understanding

of this tutorial.

Fortunately we already have a series covering the concepts of Cursors.

You can find that the link, as always, is in the description.

I suggest you to watch those tutorials.

So without further wasting your time let’s start this PL/SQL tutorial and learn what

are Ref Cursors in Oracle Database.

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.

Unlike static cursors which we have already discussed on this channel, a single cursor

variable can be associated with multiple SELECT statements in a single PL/SQL block.

On the other hand a static cursor can only access single SELECT statement at a time.

Cursor Variables and Ref Cursors are two inter-dependent topics so do not get confused with them.

Just take them as, in Oracle Database cursor variables exist in the form of Ref Cursors.

Let’s go ahead and see how many types of Ref Cursors we have in Oracle Database.

Types of Ref Cursors: There are two types of Ref Cursors in Oracle

Database.

These are: 1.

Strong Ref Cursors and 2.

Weak Ref Cursors.

Strong Ref Cursors Any ref cursor which has fixed return type

is called Strong Ref Cursor.

Let’s see the syntax of strong Ref Cursor and here it is…

DECLARE TYPE ref_cursor_name IS REF CURSOR RETURN

(return type);

The return type of a cursor must always be of record type only.

It can either be a record structure of a table or a user defined record structure.

You can refer PL/SQL tutorials 40, 41 and 42 to learn more about record datatype in

Oracle Database.

Weak Ref Cursors 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.

Such cursors are most frequently used ref cursors as they are open to all SELECT statements.

Whereas strong ref cursors can only be used with those SELECT statements that return the

result whose datatype matches with the one that you have fixed during the strong cursor’s

declaration.

Let’s take a look at the syntax of weak ref cursors in Oracle Database.

DECLARE TYPE ref_cursor_name IS REF CURSOR;

Except from the missing return clause the syntax is pretty much similar to the strong

ref cursors.

Apart from strong and weak Ref Cursors, Oracle Database also provides us with one more type

of special Cursor variable.

Before talking about that I just want to take a quick second to ask you to give this video

a thumbs up and if you are new here than do make sure to subscribe.

You can also help others in learning as well as help us and our channel in growing by sharing

this video on your social media.

Thanks, now let’s come back to our topic.

So we are talking about that special cursor variable provided by Oracle.

It is known by the name of Sys_RefCursor.

Sys_Refcursor 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.

We will see what that means when we will do a separate video on sys ref cursor.

Till then stay tuned and do subscribe because in upcoming videos we will do the examples

of strong and weak ref cursors as well as sys ref cursors.

Also do make sure to Like and share this video.

This a quick introduction to PL/SQL ref cursors / cursor variable in Oracle Database.

Thanks for watching.

This is Manish from RebellionRider.com