As we learnt in the introduction to Ref Cursor that there are two types of PL/SQL Ref Cursors in Oracle Database.
- Strong Ref Cursors and
- Weak Ref Cursors
We will explore all the concepts of both these types of Ref Cursors in this series. With this blog we will start with the first one that is the Strong Ref Cursor.
What is PL/SQL Strong Ref Cursor in Oracle Database?
A ref cursor which has fixed return type is called a Strong Ref Cursor in Oracle Database. Because of fixed return type, 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 ref cursor’s declaration.
Can we use Strong Ref Cursor with any SELECT statement in Oracle Database?
No, we cannot use Strong Ref Cursors with any SELECT statement. This is because of the fixed ‘Return Type’.
A strong ref cursor can only be used with those SELECT statements that return the result whose datatype matches with the ‘Return Clause’ of the cursor.
Can we use any PL/SQL datatypes for declaring our Strong Ref Cursor?
No, we cannot. The return type of a strong ref cursor must always be a Record Datatype. It can either be a Table Based Record datatype or a User Defined Record Datatype.
Example of Strong Ref Cursor in Oracle PL/SQL
Let’s write an example. In this example we will create a Strong Ref Cursor based on Table Based Record Datatype.
SET SERVEROUTPUT ON DECLARE /*Create Ref Pointer Type*/ TYPE my_RefCur IS REF CURSOR RETURN employees%ROWTYPE; /*Create Cursor Variable*/ cur_var my_RefCur; rec_var employees%ROWTYPE; BEGIN OPEN cur_var FOR SELECT * FROM employees WHERE employee_id = 100; FETCH cur_var INTO rec_var; CLOSE cur_var; DBMS_OUTPUT.PUT_LINE ('Employee '||rec_var.first_name||' has salary '||rec_var.salary||'.'); END; /
Creation of a Ref Cursor is a two-step process.
Create a ref pointer type.
First we need to create a ref pointer type. Using the TYPE statement we create a Ref Cursor Type pointer as we did in the above code. In this statement you first write the keyword TYPE followed by the name of your ref cursor. After that you have to write a reserved phrase IS REF CURSOR which will tell the compiler that we are creating a type which is REF CURSOR followed by that you have to specify the RETURN clause.
TYPE my_RefCur IS REF CURSOR RETURN employees%ROWTYPE;
Create a Cursor Variable
In the second step we create a cursor variable. In order to create a cursor variable you have to first write the name of your variable followed by the name of your Ref Cursor. This variable will then be used to refer to the Ref Cursor over which it is created.
Apart from the Cursor Variable in the above code we also have an extra variable which is again of table based record datatype and designed using ‘Employees’ table. This variable will hold the data fetched from the cursor.
In the execution section of our code we have four executable statements. Let me explain these to you.
- OPEN FOR statement
The first statement is the OPEN FOR statement. It associates the SELECT statement with Cursor’s variable and opens the cursor for the statement. This statement also looks for all the resources required for processing the SELECT statement.
- FETCH statement
As we discussed in PL/SQL tutorial 26. The process of retrieving data from the cursor is called fetching. In this statement we are fetching the data from the Ref Cursor into the record variable ‘Rec_Var’.
- Close Statement
Once we are done with our cursor then it is advisable to close it so that our Oracle Engine / Server can relinquish all the resources associated with it. That’s exactly we have done in the third statement. In this statement using CLOSE keyword followed by the cursor variable we closed our ref cursor.
- Output statement
Fourth statement is a DBMS_OUTPUT statement using which we will display back the first name and the salary of the employee with employee id 100.
You can watch PL/SQL tutorial 33 in order to learn the workings of a table based record variable.
In the above code we created a PL/SQL Strong Ref Cursor with the name ‘my_RefCur’ which will return a result table based record datatype. This table based record datatype is supported on the Employees table of HR schema. Thus before executing this program we need to make sure that we are connected to the HR schema of our database.
That is the tutorial on how to create PL/SQL strong Ref Cursor using table based record datatype in Oracle Database. Hope you learnt something new. Please do make sure to share this blog on your social media with your friends.
Thanks for visiting. Stay tuned as in the next tutorial we will learn how to create strong ref cursor with user defined record variable.
Have a great day!