So now that we are done with database cursors, it is time to take a look at something which is long due. Also since I have already promised to do this tutorial while doing PL/SQL tutorial 30 on Cursor For Loop. So here we are, this tutorial will be an introduction to Records in Oracle Database.

This PL/SQL tutorial is created through the perspective of Oracle Database certification and Job Interview. Thus in this tutorial you will find the answers of some most commonly asked questions in an easy language. So let’s start this tutorial with the most obvious question –

What is a Record in Oracle Database?

Records are composite data structures made up of different components called fields. These fields can have different data types. This means that you can store data of different data types in a single record variable. Similar to the way we define columns in a row of a table.

Definition
A record is a group of related data items stored in fields, each with its own name and datatype. ~Oracle Docs.

Types of Record datatype in Oracle database

In Oracle PL/SQL we have three types of Record datatype.

1. Table Based Record
2. Cursor Based Record, and
3. User Defined Record.

How to declare a Record Datatype in Oracle Database.  (%ROWTYPE attribute.)

Similar to %TYPE which is for declaring an anchored datatype variable, Oracle PL/SQL provides us an attribute % ROWTYPE for declaring a variable with record datatype.

Syntax of Record Datatype

Table Based Record

Variable_ name   table_name%ROWTYPE;

Variable Name: A user defined name given to the variable. You have to specify the name of the variable which could be anything. But you have to follow the oracle guidelines for variable name.

Table Name: Table name will be the name of the table over which the record datatype is created. This table will serve as the base table for the record. Also the Oracle Server will then create corresponding fields in the record that will have the same name as that of a column in the table.

%ROWTYPE: Attribute which indicates the compiler that the variable which is declared is of Record Type.

Cursor based Record

Variable_ name   cursor_name%ROWTYPE;

The syntax of cursor based record is pretty similar to the one which we just saw. You just have to specify the name of the cursor in place of table name, rest of the things remain same.

How to access data stored is Record Type Variable.

Whenever you create a record based on a table, oracle server will create fields corresponding to each column of the table and all those fields have the same name as a column in the table. That makes referencing or accessing data from the record much easier. Here is how you access data from record variable.

Record_variable_name.column_name_of_the_table; 

Using dot (.) notation you can access the data stored into the field of the record.

Hope you enjoyed Reading. Do make sure to read the next blog where we discussed the example of Record datatype which will help you in better understanding the concepts of record in Oracle Database.

Do make sure to share this blog. Sharing could help you win the RebellionRider merchandise. Don’t forget to tag me. Thank for reading. Have a great day!