After table based record variable, the next tutorial in Record datatype variables series is Cursor Based Record thus here we will learn what Cursor Based Record Datatype Variables are and how to create them in Oracle Database.

What are Cursor Based Records in Oracle Database?

Cursor based records are those variables whose structure is derived from the SELECT list of an already created cursor. As we know that record datatypes are composite data structures made up of multiple fields and in the case of Cursor based record these fields are initialized by fetching data from the SELECT-LIST of the cursor that was used to create this variable.

How To Create Cursor Based Record In Oracle Database?

The creation of cursor based record variable involves:

  • Declaration of Cursor based Record.
  • Initialization of Cursor Based Record and
  • Accessing the data stored into the cursor based record variable.

Let’s discuss each of these above steps one by one.

Declare Cursor Based Record In Oracle Database

As it is obvious that in order to declare a Cursor Based Record we need to have a Cursor over which we can design our Record variable. For the demonstration let’s have the same cursor which we have discussed in PL/SQL tutorial 27 on “How to Create Simple Explicit Cursor.”

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

Once you have created your cursor then you are all set to declare your Cursor based Record variable.

var_emp  cur_RebellionRider%ROWTYPE;

Declaration of Cursor Based Record is pretty much similar to the declaration of table based record. The declaration starts with the name of the record variable, which is completely user defined followed by the name of an already created cursor. And at the end “%ROWTYPE” attribute which indicates that we are declaring a Record Datatype variable to the compiler.

For more detail on how to declare a cursor based record in Oracle Database please watch my Video Tutorial on the same topic.

Initialize Cursor Based Record Datatype

Once you have successfully declared your record datatype variable next you have to initialize it and by that I mean putting some value into the record variable. Though the initializing process of cursor based record is very simple and pretty much similar to the one we have seen in the previous tutorial on “Table Based Record” but unlike table records here we are initializing a record variable which is based on a cursor thus we have to make sure that we should follow the cursor cycle properly.

BEGIN
  OPEN cur_RebellionRider;
  FETCH cur_RebellionRider INTO var_emp;

Suggested Reading: Database Cursors

Every cursor needs to be opened before being used. Thus here we first opened the cursor and then using FETCH-INTO statement we fetched the data into the record variable “var_emp”.

Access data from the cursor based record

After Declaration and Initialization of cursor based record the thing which we have to learn is How to access data from the cursor based record.

DBMS_OUTPUT.PUT_LINE (var_emp.first_name);
DBMS_OUTPUT.PUT_LINE (var_emp.salary);

You can see in the above two statements that once again we used the Dot (.) notation for fetching the data from the record variable. Though these two statements look very similar to one which we have seen in table based record variable tutorial, but they are not. In case of table based record we have to write the name of the column of the table but in case of cursor based record we can only write the name of those columns of the table which we specified in the SELECT-List of the cursor.

Once you have declared, initialize and access the data then make sure to close the cursor.

Now let’s compile all the above line of codes (LOCs) together and make them into a single PL/SQL block.

SET SERVEROUTPUT ON;
DECLARE
  CURSOR cur_RebellionRider
  IS 
  SELECT first_name, salary FROM employees 
  WHERE employee_id = 100;
  --Cursor Based Record Variable Declare
  var_emp cur_RebellionRider%ROWTYPE;
BEGIN
  OPEN cur_RebellionRider;
  FETCH cur_RebellionRider INTO var_emp;
  DBMS_OUTPUT.PUT_LINE (var_emp.first_name);
  DBMS_OUTPUT.PUT_LINE (var_emp.salary);
  CLOSE cur_RebellionRider;
END;

So that is how we create a cursor based record. Hope you enjoyed reading. Do make sure to share this blog on your social media with your friends. Also since I am giving away RebellionRider’s merchandise to randomly selected winners so make sure to tag me.

Let’s wind up this tutorial on Cursor Based Record with a simple question.

Can we declare a Cursor Based Record Datatype variable before the cursor in PL/SQL block?
Hint: Modify the above code and put the Cursor Variable’s declaration before the cursor declaration and check the result.

If you know the answer, then send it to me on my Facebook and Twitter and don’t forget to follow me there for regular updates. Thanks for reading & have a great day.

RELATED ARTICLESMORE FROM AUTHOR