Updated April 5, 2023
Introduction to PL/SQL Record
Pl/SQL record help you to manage and use the data required in the PL/ SQL programs effectively and efficiently. The Pl/ SQL record is made up of a number of fields that can have different data types and data structures. In fact, the record itself is a composite data structure made up of multiple fields. Each and every field in the record has its own value. In this article, we will learn the usages of the record in PL/ SQL various types of the records and their use in the PL/ SQL programs with the help of some examples.
Syntax or composition of PL/SQL Record:
Generally, In PL/ SQL program the use of field level operations is performed which involves managing, manipulating, and accessing each and every individual field separately. But many of the times there is a need where we have to access the whole record instead of a single field. In that case, the field-level operations become very time-consuming and difficult to specify. The use of PL/ SQL records helps to make the record-level operations in the program easy as each and every wholesome record is picked and manipulated. Consider the below example where we have a customer record consisting of his name, mobile number, email address, and residential address.
As shown above, the record can contain any number of and any type of field values in it. This makes the program simpler by working on record-level data accessing.
Types of PL/SQL Record
There are three categories in which we can segregate the records in Pl/ SQL. The types of Pl/ SQL records are as listed below.
- Table-based records
- Cursor Based records
- Program-Defined records.
We will discuss one by each type of record along with its example. The sample table we will use for our demonstration is customers details whose contains can be seen by firing the following query –
SELECT * FROM [customers_details]Whose output is –
Example #1 – Table-based Record
We can define any of the types of records in Pl/ SQL in the declaration section of our PL/ SQL program by specifying the package in a block of declaration. For declaring the table-based record in Pl/ SQL we make the use of an attribute of %ROWTYPE along with the name of the table. Each of the individual fields in the table-based record corresponds to the column of the table. The following syntax will help you to understand how we can declare a table-based record –
DECLARE name of record name of table%ROWTYPE
Consider an example where we have to declare a record with the name cust_record for the customer_details table that exists in our PL/ SQL database. This can be done by using the following statement –
DECLARE
cust_record customers%rowtype;
BEGIN
SELECT * into cust_record
FROM customer_details
WHERE customer_id = 105;
dbms_output.put_line('Customer ID: ' || cust_record.customer_id);
dbms_output.put_line('Customer First Name: ' || cust_record.f_name);
dbms_output.put_line('Customer Email Id: ' || cust_record.email_id);
dbms_output.put_line('Customer Bill Amount: ' || cust_record.bill_amount);
END;The output of the execution of the above statement is as shown below –
Example #2 – Cursor Based Record
The cursor select statement in PL/ SQL helps in preparing a cursor-based record where each and every individual field, column, or alias pointed by a cursor select statement are considered for creating a cursor-based record.
In order to declare a cursor-based record, we will have to make the use of an explicit cursor in our program with the %ROWTYPE attribute whose syntax is as shown below –
DECLARE name of record name of cursor%ROWTYPE;Let us consider one example demonstrating the declaration of cursor-based record. Consider that we will make the use of the same customer_details table to retrieve its table data records by using cursors shown in the below program –
DECLARE
CURSOR cust_record is
SELECT customer_id, f_name, mobile_number
FROM customers_details;
customer_record cust_record%rowtype;
BEGIN
OPEN cust_record;
LOOP
FETCH cust_record into customer_record;
EXIT WHEN cust_record%notfound;
DBMS_OUTPUT.put_line(customer_record.customer_id || ' ' || customer_record.f_name);
END LOOP;
END;In the above example, the cust_record and customer_record have the same structure of customer_details table record. We have declared the customer_record which is a cursor-based record by using a cursor variable cust_record.
The cust_record is an explicit cursor that retrieves the data from the customer details table retrieving the name, mobile number, email address, and residential address of the customer in the record.
The output of the execution of the above statement is as shown below –
Example #3 – Program-Defined Record
Cursor-based records and table-based records work only when they are declared on the existing database structures such as tables and columns of the database. When we want to declare a record that is not based on the existing database structure, we will have to make use of Program-Defined records.
The steps for declaring the program-based records are as prescribed below –
The structure that we want according to our requirements should be used while declaring a record type.
We will then need to declare a record which will be based upon the record type that we have declared previously.
The syntax of declaring program-based record type is as shown below –
TYPE record type IS RECORD (
name of field1 data type1 [[NOT NULL] := | DEFAULT value to be assigned by default],
name of field2 data type2 [[NOT NULL] := | DEFAULT value to be assigned by default],
...
);The syntax of declaring the program-based record on the predefined record type is as shown below –
Name of record type;
Let us consider the same example of customer details for demonstrating the program-based record in Pl/ SQL with the help of the below example –
DECLARE
type customers is record
(f_name varchar(50),
l_name varchar(50),
mobile_number varchar(100),
customer_id number);
customer1 customers;
customer2 customers;
BEGIN
-- Customer 1 specification
customer1.f_name := 'Ranbir';
customer1.l_name := 'Kapoor';
customer1.mobile_number := '9897945565';
customer1.customer_id := 111;
-- Customer 2 specification
customer2.f_name := 'Hrithik';
customer2.l_name := 'Roshan';
customer2.mobile_number := '7887455862';
customer2.customer_id := 110;
-- Display Customer 1 record
dbms_output.put_line('Customer 1 f_name : '|| customer1.f_name);
dbms_output.put_line('Customer 1 l_name : '|| customer1.l_name);
dbms_output.put_line('Customer 1 mobile_number : '|| customer1.mobile_number);
dbms_output.put_line('Customer 1 customer_id : ' || customer1.customer_id);
-- Display Customer 2 record
dbms_output.put_line('Customer 2 f_name : '|| customer2.f_name);
dbms_output.put_line('Customer 2 l_name : '|| customer2.l_name);
dbms_output.put_line('Customer 2 mobile_number : '|| customer2.mobile_number);
dbms_output.put_line('Customer 2 customer_id : '|| customer2.customer_id);
END;
/The output of the execution of the above statement is as shown below –
Conclusion
We can make the use of record level accessing in PL/ SQL programs which makes the manipulation easier. There are three types of records that can be used in programs of PL/ SQL which are table-based, cursor-based, and Program-Defined records respectively. It is important to declare a record before you use it further in the program.
Recommended Articles
We hope that this EDUCBA information on “PL/SQL Record” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
