PL SQL Records Tutorial With Examples

Learn about different types of PL SQL Records and Record Types with programming examples:

In this article, we will continue with PL/SQL series. In the PL SQL Cursor And Strings tutorial, we learned about Implicit and Explicit Cursors, cursor for loop, String functions, etc.

Here, we will explore PL/SQL records and record types that PL/SQL has. We will see what are table-based, user-defined, and cursor-based records.

Also, we will discuss some basic computations done on them.

Let’s begin with the discussion!!

What You Will Learn:

PL SQL Records
Record Type As Database Object
Conclusion
- Recommended Reading

PL SQL Records

A record is a collection of connected data having varied names and data types. These data are stored in fields. A logical relation among these data exists, however they belong to different data types.

A record is used to group and represent data. %ROWTYPE attribute is used to declare a record that points a row in the database. The datatypes of the fields of a record cannot be specified or declared by us. But the RECORD data type allows us to declare user-defined records.

Suppose, we want to monitor the progress of students in a school by keeping track of the attributes like name, age, marks, and code. Thus STUDENT becomes a logical entity and the record having a field for each of the attributes helps to represent information in an organized way.

PL/SQL has three types of records. These are listed below:

Table based records
User-defined records
Cursor based records

Table Based Records

Table based records can be created with the help of the %ROWTYPE attribute.

Let us consider a table which is named STUDENT.

SELECT * FROM STUDENT

We have created the STUDENT table with the help of the SQL statement given below:

CREATE TABLE STUDENT (
   CODE INT NOT NULL,
   NAME VARCHAR (15) NOT NULL,
   AGE INT NOT NULL,
   MARKS INT NOT NULL, 
   PRIMARY KEY (CODE)
);

Insert the values to this table with SQL statements given below:

INSERT INTO STUDENT VALUES (1, 'ZIO', 14, 95);
INSERT INTO STUDENT VALUES (7, 'MARK', 15, 94);
INSERT INTO STUDENT VALUES (3, 'ROBERT', 14, 100);

Code Implementation of Table-based record, based on the above table:

DECLARE 
    student_r student%rowtype; 
BEGIN 
   SELECT * into student_r 
FROM student 
   WHERE name = 'ZIO';  
   dbms_output.put_line ('Student code: ' || student_r.code); 
   dbms_output.put_line ('Student marks: ' || student_r.marks); 
   dbms_output.put_line ('Student age: ' || student_r.age); 
END; 
/

The output of the above code:

Cursor Based Records

Cursor based records can be created with the help of the %ROWTYPE attribute.

Let us again consider a table which is named STUDENT.

Code Implementation of Cursor based record, based on the Student table:

DECLARE 
   CURSOR student_c is 
      SELECT code, name, age  
      FROM student; 
   student_r student_c%rowtype; 
BEGIN 
   OPEN student_c; 
   LOOP 
      FETCH student_c into student_r; 
      EXIT WHEN student_c%notfound; 
      DBMS_OUTPUT.put_line (student_r.code || ' ' || student_r.name); 
   END LOOP; 
END; 
/

The output of the above code:

User-Defined Records

PL/SQL allows us to create user-defined records that enable us to have different record data structures. Each record is composed of various fields.

Defining and Declaring Records

In order to create a record, we should first define a RECORD type in the declarative section of the PL/SQL block of code or within a package or inside a subprogram. Then we can do the declaration of that record type.

Syntax:

TYPE
type_n IS RECORD
 ( field_name1 datatype1 [NOT NULL] [:= DEFAULT EXPRESSION],
 field_name2 datatype2 [NOT NULL] [:= DEFAULT EXPRESSION],
 ...
record-name type_n;

Here,

type_n is the type specifier that is used to declare variables.
datatype1 is the PL/SQL data type except for REF CURSOR.
EXPRESSION gives the value of a similar data type as datatype1.

Like Varray and nested table types, record types are not allowed to be created or held in a database. We can use %TYPE and %ROWTYPE to mention the field types.

DECLARE 
TYPE employee IS RECORD 
(name  varchar (15), 
   designation  varchar (15),  
   emp_code   number); 
BEGIN
   <<Block of executable code>>
END;

Once we have done the definition of record type, we can declare a record of that type as well.

DECLARE 
TYPE employee IS RECORD 
(name  varchar (15), 
   designation  varchar (15),  
   emp_code   number); 
--  record declaration
employee1 employee;
employee2 employee;

BEGIN
   <<Block of executable code>>
END;

Here the employee1 and employee2 are identifiers, representing complete records.

Initializing Records

We can initialize a record with some values as shown in the code example below:

DECLARE
   TYPE marks IS RECORD (
      code SMALLINT := 0,
      score SMALLINT := 0);
BEGIN
  <<Block of executable code>>
END;

We can set a NOT NULL constraint on the fields of a record which ensures null value is not assigned to a specific field. It is mandatory to initialize the fields having NOT NULL constraints.

DECLARE 
TYPE employee IS RECORD 
(name  varchar(15), 
   designation  varchar(15), 
   age integer(2)  NOT NULL := 35,
   emp_code   number); 
--  record declaration
employee1 employee
employee2 employee
BEGIN
   <<Block of executable code>>
END;

Accessing Fields

We need to use the dot (.) Operator to access fields of a record. The fields in a record are used with the name.

Syntax:

record_name.field_name

If we want to access the field age in the record employee, it should be:

employee.age

Code Implementation for accessing fields in a record:

DECLARE 
TYPE employee IS RECORD 
(name  varchar(15), 
   designation  varchar(15),  
   code   number); 
--  record declaration

emp1 employee;
emp2 employee;
 
BEGIN 
   -- Employee 1 details 
   emp1.name  := 'Lio'; 
   emp1.designation  := 'Intern'; 
   emp1.code  := 02; 

   -- Employee 2 details 
   emp2.name  := 'Dan'; 
   emp2.designation  := 'Analyst'; 
   emp2.code  := 04;  
  
  -- Print emp 1 details in console
   dbms_output.put_line ('Employee 1 name : '|| emp1.name); 
   dbms_output.put_line('Employee 1 designation : '|| emp1.designation); 
   dbms_output.put_line ('Employee 1 code : '|| emp1.code); 
-- Print emp 2 details in console
   dbms_output.put_line ('Employee 2 name : '|| emp2.name); 
   dbms_output.put_line('Employee 2 designation : '|| emp2.designation); 
   dbms_output.put_line ('Employee 2 code : '|| emp2.code); 
END; 
/

The output of the above code:

Record Passed As Subprogram Parameter

We can have a record passed as a parameter to a subprogram. This is similar to passing a variable. We have to use the dot (.) operator to access fields in the record.

Code Implementation for passing records as a subprogram parameter:

DECLARE 
TYPE employee IS RECORD 
(name  varchar (15), 
   designation  varchar (15),  
   code   number); 
--  record declaration

emp1 employee;
emp2 employee;
  
PROCEDURE employee_details (e employee) IS 
BEGIN 
   dbms_output.put_line ('Employee name : '|| e.name); 
   dbms_output.put_line ('Employee designation : '|| e.designation); 
   dbms_output.put_line ('Employee code : '|| e.code); 
END; 
   
BEGIN 
     -- Employee 1 details 
   emp1.name  := 'Lio'; 
   emp1.designation  := 'Intern'; 
   emp1.code  := 02; 

   -- Employee 2 details 
   emp2.name  := 'Dan'; 
   emp2.designation  := 'Analyst'; 
   emp2.code  := 04;   
   
   -- Use procedure for employee details 
  employee_details (emp1); 
  employee_details (emp2); 
END; 
/

The output of the above code:

Record Type As Database Object

We can create a Record type as a database object. We need to have a record type with the columns of the database table.

Code Implementation for record type as a database object:

CREATE TYPE SCHOOL IS OBJECT
(
SCHOOL_NO NUMBER,
SCHOOL_NAME VARCHAR (15)
);		
/

The output of the above code:

Insert And Update Statements In Records

Let us consider a table which is named PLAYERS.

SELECT * FROM PLAYERS

We have created the Players table with the help of the SQL statement given below:

CREATE TABLE PLAYERS (
   PLAYER_ID INT NOT NULL,
   NAME VARCHAR (15) NOT NULL,
   AGE INT NOT NULL,
   COUNTRY VARCHAR (15) NOT NULL,       
   PRIMARY KEY (PLAYER_ID)
);

Then inserted values to this table with SQL statements given below:

INSERT INTO PLAYERS VALUES (1, 'WILLS', 24, 'USA');
INSERT INTO PLAYERS VALUES (8, 'HENRY', 34, 'FRANCE');
INSERT INTO PLAYERS VALUES (5, 'TIM', 22, 'ITALY');

Now we can insert another row with the help of %ROWTYPE. Thus there is no need to separately mention each of the fields.

Code Implementation to insert data to Players table with %ROWTYPE:

DECLARE
  i_player players%ROWTYPE;
BEGIN
  -- setting values for a player
  i_player.player_id  := 9;
  i_player.name := 'SMITH';
  i_player.age  := 30;
  i_player.country  := 'GERMANY';
  -- inserting a new record in table
  INSERT INTO players VALUES i_player;
END;

The output of the above code:

Now let us see the changes reflected to the table Players.

SELECT * FROM PLAYERS

We can update a particular row from a %ROWTYPE record with the help of keywords SET ROW.

Code Implementation to update a row from a %ROWTYPE record.

DECLARE
  i_player players%ROWTYPE;
BEGIN
-- fetch the information with id 8 
  SELECT * INTO i_player 
  FROM players 
  WHERE player_id = 8;
 
-- modify the player age
 i_player.age  := 26;
  -- update the player
  UPDATE players
  SET ROW = i_player
  WHERE player_id =  i_player.player_id ;
END;

The output of the above code:

Now, let us see the changes reflected to the table Players after updating a row.

SELECT * FROM PLAYERS

Please note that the AGE of the player with ID 8 has been updated to 26. Previously it was 34.

Nested Records

We can have a record that contains a field that resides in a different record. This is known as nesting a record. It is an efficient way to structure our code and achieve encapsulation.

Code Implementation with the nested record:

DECLARE	
  TYPE first_last_name IS RECORD (
    first_name VARCHAR2 (15),
    last_name VARCHAR2 (15)
  );
              TYPE name IS RECORD (
              city VARCHAR2(10),
              f_name first_last_name,
              l_name first_last_name
              );
  full_name name;
BEGIN
  full_name.city := 'Michigan';
  dbms_output.put_line ('City name: ' || full_name.city);
  -- assigning full name and print in console
  full_name.f_name.first_name := 'SILVER';
  dbms_output.put_line ('First name: ' || full_name.f_name.first_name);
  full_name.l_name.last_name := 'BEACH';
  dbms_output.put_line ('Last name: ' || full_name.l_name.last_name);
END;

The output of the above code:

In the above implementation, there is a record type called first_last_name. Then in the name record type, there are two fields’ f_name and l_name which are dependent on the first_last_name record type.

Frequently Asked Questions

Q #1) What is Record in PL/SQL?

Answer: A record in PL/SQL is declared with the help of the %ROWTYPE attribute. It is a group of fields having a name.

Q #2) What is the use of record type in PL/SQL?

Answer: A record type is a data type that enables us to create a new data type having a column structure. After the creation of the record type, it is stored in the database as a new data type and shall be used later to declare variables in a program.

Q #3) Why do we use Rowtype in PL/SQL?

Answer: Rowtype is an attribute that provides a record type. It is used to represent a row in the database. The variables declared with %ROWTYPE are like those declared with the name of the data type.

Q #4) What is type and Rowtype in PL/SQL?

Answer: The type is the data type of the variable or database column to a particular variable. The %Rowtype gives the record type that represents a full row of table/column selected by a cursor/view.

Q #5) What is a record data type?

Answer: The record data type is basically a data type that represents values and variables. A record is considered as a data structure.

Q #6) What is the difference between Rowtype and type record?

Answer: The type record is used to design and build our customized data type with a particular number of values to store. The %Rowtype is used when we are required to work with an entire record.

Conclusion

In this tutorial, we have discussed in detail some basic concepts of PL SQL Records that are essential to developing knowledge on it. We have covered the following topics listed below:

Records
Types of Records
Operations on Records

<<PREV Tutorial | NEXT Tutorial>>

PL SQL Records

Table Based Records

Cursor Based Records

User-Defined Records

Record Type As Database Object

Insert And Update Statements In Records

Nested Records

Frequently Asked Questions

Conclusion

Recommended Reading