We have already seen in the previous tutorial how to create Cursor based Record Type Variable based on Simple Cursor which returns a single row of data. Now the question arises here is that can we use the same single record datatype variable with the cursor which returns multiple rows of data? To know the answer, read along & learn how to handle multiple values returned by a cursor using single cursor based record datatype variable.

As we are dealing with Cursor-Based records thus a little bit knowledge of cursor is required. For the sake of simplicity and to make this concept easy to understand I will use a Simple Explicit-Cursor for the demonstration.

Step 1: Declare a Simple Explicit Cursor

A Cursor-based record datatype requires an already created cursor. This cursor will become an underlying base for our record type variable. All the fields of the record type variable which is created using this cursor will have the same name and datatype as that of the columns used in the SELECT-List of the cursor.

SET SERVEROUTPUT ON;
DECLARE
 CURSOR cur_RebellionRider IS
 SELECT first_name, salary FROM employees 
 WHERE employee_id > 200;

Unlike the cursor from the previous tutorial which is returning a single row of data, this cursor will return multiple rows. All these rows will consist of the first name and salary of all the employees with employee id greater than 200.

Step2: Declare the Cursor Based Record Datatype Variable

As we have created the cursor, now we are all set to declare our record variable using this cursor.

var_emp    cur_RebellionRider%ROWTYPE;

Var_emp is the record type variable and as it is based on the cursor cur_RebellionRider thus we can proudly call it a Cursor-Based record type variable. Now we have to see whether this single record variable is capable of holding all the data returned by the underlying cursor cur_RebellionRider.

Step 3: Initialize the Cursor-Record Variable

As we discussed in the PL/SQL tutorial 34 that initialization of a record variable is the process of assigning some values to it. In case of Cursors, FETCH-INTO statement does this work. But we have to make sure that we have followed the Cursor Life Cycle properly.

If you don’t know what is this Cursor Life Cycle and the steps involved in creating a Cursor then checkout this blog on “Introduction to Database Cursor”.

BEGIN
   OPEN cur_RebellionRider; 
  LOOP 
    FETCH cur_RebellionRider INTO var_emp;  
    EXIT WHEN cur_RebellionRider%NOTFOUND; 
    DBMS_OUTPUT.PUT_LINE (var_emp.first_name||' '||var_emp.salary );
  END LOOP;--Simple Loop End
  CLOSE cur_RebellionRider;
END;

The above execution section of PL/SQL block which we are creating here has been explained line-by-line in the video tutorial on my YouTube channel.  Do check that out.

So that is it. That’s all we have to. Now let’s combine all these chunks of code which we saw in different steps above into a single anonymous PL/SQL block.

Cursor-Based Record Datatype Variable in Oracle Database

SET SERVEROUTPUT ON;
DECLARE
  CURSOR cur_RebellionRider IS
  SELECT first_name, salary FROM employees
  WHERE employee_id > 200; 
  var_emp   cur_RebellionRider%ROWTYPE;
BEGIN
  OPEN cur_RebellionRider;
  LOOP
    FETCH cur_RebellionRider INTO var_emp;
    EXIT WHEN cur_RebellionRider%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE (var_emp.first_name||' '||var_emp.salary);
  END LOOP;
  CLOSE cur_RebellionRider;
END;

When you compile and run the above code you will get all the data which FETCH-INTO statement fetched from the cursor cur_RebellionRider and stored into the Cursor-based Record variable var_emp. This implies that we can indeed handle multiple rows of data using a single Cursor Based Record.

In my PL/SQL video tutorial I asked that can we simplify this code or is there any other way of doing the same task. The answer is yes, there are multiple ways of achieving the same result and one of them is by using “Cursor For-Loop”. This is a special kind of loop which declares the record variable as well as Opens, Fetches and Closes the underlying cursor implicitly in the background for you. You can read more Cursor For-Loop here.

 

Reference Book for SQL Expert 1z0-047 Affiliate link
OCA Oracle Database SQL Certified Expert Exam Guide (Exam 1Z0-047)

Here is the code done using Cursor For-Loop which is equivalent to the above code. As you can see it is much less complex with few Line-of-Codes (LOCs).

SET SERVEROUTPUT ON;
BEGIN
  FOR var_emp IN (SELECT first_name, salary FROM employees
 WHERE employee_id >200)
  LOOP
    DBMS_OUTPUT.PUT_LINE(var_emp.first_name||' '||var_emp.salary);
  END LOOP;
END;

Both the codes will return the same result as both are doing the same task.

Hope you enjoyed this detailed PL/SQL tutorial. Please do make sure to share this on your social and tag me as I am giving away RebellionRider’s merchandise every month to a randomly selected user. Also follow me on my Social Media [Twitter/Facebook/Instagram] for regular updates.

Thanks & Have a great day!

RELATED ARTICLESMORE FROM AUTHOR