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
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>>