In this blog, we will discuss Records in PL/SQL. A record is a data structure that can hold data items of different kinds. Records consist of different fields, similar to a row of a database table.
For example, you want to keep track of your books in a library. You might want to track the following attributes about each book, such as Title, Author, Subject, Book ID. A record containing a field for each of these items allows treating a BOOK as a logical unit and allows you to organize and represent its information in a better way.
PL/SQL can handle the following types of records −
• Table-based
• Cursor-based records
• User-defined records

Table-Based Records

The %ROWTYPE attribute enables a programmer to create table-based and cursor-based records.
The following example illustrates the concept of table-based records. We will be using the CUSTOMERS table we had created and used in the previous chapters −

DECLARE 
customer_rec customers%rowtype; 
BEGIN 
SELECT * into customer_rec 
FROM customers 
WHERE id = 5; 
dbms_output.put_line('Customer ID: ' || customer_rec.id); 
dbms_output.put_line('Customer Name: ' || customer_rec.name); 
dbms_output.put_line('Customer Address: ' || customer_rec.address); 
dbms_output.put_line('Customer Salary: ' || customer_rec.salary); 
END; 
/
When the above code is executed at the SQL prompt, it produces the following result −
Customer ID: 5 
Customer Name: Hardik 
Customer Address: Bhopal 
Customer Salary: 9000

PL/SQL procedure successfully completed.

Cursor-Based Records

The following example illustrates the concept of cursor-based records. We will be using the CUSTOMERS table we had created and used in the previous chapters −

DECLARE 
CURSOR customer_cur is 
SELECT id, name, address 
FROM customers; 
customer_rec customer_cur%rowtype; 
BEGIN 
OPEN customer_cur; 
LOOP 
FETCH customer_cur into customer_rec; 
EXIT WHEN customer_cur%notfound; 
DBMS_OUTPUT.put_line(customer_rec.id || ' ' || customer_rec.name); 
END LOOP; 
END; 
/

When the above code is executed at the SQL prompt, it produces the following result −
1 Ramesh
2 Khilan
3 kaushik
4 Chaitali
5 Hardik
6 Komal

PL/SQL procedure successfully completed.

User-Defined Records

PL/SQL provides a user-defined record type that allows you to define the different record structures. These records consist of different fields. Suppose you want to keep track of your books in a library. You might want to track the following attributes about each book −
• Title
• Author
• Subject
• Book ID
Defining a Record
The record type is defined as −

TYPE 
type_name IS RECORD 
( field_name1 datatype1 [NOT NULL] [:= DEFAULT EXPRESSION], 
field_name2 datatype2 [NOT NULL] [:= DEFAULT EXPRESSION], 
... 
field_nameN datatypeN [NOT NULL] [:= DEFAULT EXPRESSION); 
record-name type_name;
The Book record is declared in the following way −
DECLARE 
TYPE books IS RECORD 
(title varchar(50), 
author varchar(50), 
subject varchar(100), 
book_id number); 
book1 books; 
book2 books;

Accessing Fields
To access any field of a record, we use the dot (.) operator. The member access operator is coded as a period between the record variable name and the field that we wish to access. Following is an example to explain the usage of record −

DECLARE 
type books is record 
(title varchar(50), 
author varchar(50), 
subject varchar(100), 
book_id number); 
book1 books; 
book2 books; 
BEGIN 
-- Book 1 specification 
book1.title := 'C Programming'; 
book1.author := 'Nuha Ali '; 
book1.subject := 'C Programming Tutorial'; 
book1.book_id := 6495407; 
-- Book 2 specification 
book2.title := 'Telecom Billing'; 
book2.author := 'Zara Ali'; 
book2.subject := 'Telecom Billing Tutorial'; 
book2.book_id := 6495700; 

-- Print book 1 record 
dbms_output.put_line('Book 1 title : '|| book1.title); 
dbms_output.put_line('Book 1 author : '|| book1.author); 
dbms_output.put_line('Book 1 subject : '|| book1.subject); 
dbms_output.put_line('Book 1 book_id : ' || book1.book_id); 

-- Print book 2 record 
dbms_output.put_line('Book 2 title : '|| book2.title); 
dbms_output.put_line('Book 2 author : '|| book2.author); 
dbms_output.put_line('Book 2 subject : '|| book2.subject); 
dbms_output.put_line('Book 2 book_id : '|| book2.book_id); 
END; 
/
When the above code is executed at the SQL prompt, it produces the following result −
Book 1 title : C Programming 
Book 1 author : Nuha Ali 
Book 1 subject : C Programming Tutorial 
Book 1 book_id : 6495407 
Book 2 title : Telecom Billing 
Book 2 author : Zara Ali 
Book 2 subject : Telecom Billing Tutorial 
Book 2 book_id : 6495700

PL/SQL procedure successfully completed.
Records as Subprogram Parameters
You can pass a record as a subprogram parameter just as you pass any other variable. You can also access the record fields in the same way as you accessed in the above example −

DECLARE 
type books is record 
(title varchar(50), 
author varchar(50), 
subject varchar(100), 
book_id number); 
book1 books; 
book2 books; 
PROCEDURE printbook (book books) IS 
BEGIN 
dbms_output.put_line ('Book title : ' || book.title); 
dbms_output.put_line('Book author : ' || book.author); 
dbms_output.put_line( 'Book subject : ' || book.subject); 
dbms_output.put_line( 'Book book_id : ' || book.book_id); 
END; 

BEGIN 
-- Book 1 specification 
book1.title := 'C Programming'; 
book1.author := 'Nuha Ali '; 
book1.subject := 'C Programming Tutorial'; 
book1.book_id := 6495407;

-- Book 2 specification 
book2.title := 'Telecom Billing'; 
book2.author := 'Zara Ali'; 
book2.subject := 'Telecom Billing Tutorial'; 
book2.book_id := 6495700; 

-- Use procedure to print book info 
printbook(book1); 
printbook(book2); 
END; 
/ 
When the above code is executed at the SQL prompt, it produces the following result −
Book title : C Programming 
Book author : Nuha Ali 
Book subject : C Programming Tutorial 
Book book_id : 6495407 
Book title : Telecom Billing 
Book author : Zara Ali 
Book subject : Telecom Billing Tutorial 
Book book_id : 6495700

PL/SQL procedure successfully completed.

So, this brings us to the end of blog. This Tecklearn ‘Handling Records in PL-SQL’ blog helps you with commonly asked questions if you are looking out for a job in Oracle Pl-SQL. If you wish to learn Oracle PL-SQL and build a career in Database domain, then check out our interactive, Oracle PL-SQL Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

Oracle PL SQL Training

Oracle PL-SQL Training

About the Course

Oracle PL/SQL online training course provides you the complete skills needed to create, implement and manage robust database applications using the Oracle Database tools. Our expert instructors will help you to master PL SQL advanced features, from performance to maintainability to the application code architecture. Our best online classes will help you to gain a precise knowledge of PL SQL language, architecture, interactions with the SQL engine, data types, and much more. The entire training is in line with the Oracle PL/SQL certification.

Why Should you take Oracle PL-SQL Training?

• The Average salary of a Senior Oracle PL-SQL Developer is $131,878 per annum – ZipRecuiter.com
• PL-SQL has a market share of 23% globally.
• IBM, TCS, Tech Mahindra, Oracle, Wipro & other MNCs worldwide use Pl-SQL for their database deployments.

What you will Learn in this Course?

Introduction to Oracle SQL
• Database Models
• RDBMS
• Components of SQL
• DataTypes
• DDL-Create, Alter, Rename, Drop, Truncate
Manipulating Data using SQL
• Constraints –Unique, Not Null, Primary Key, Check Constraint, Foreign Key
• DML Commands-Insert, Update, Delete
• Order by Clause
• Group Functions
• SET Operators- Union All, Union, Intersect, Minus
• TCL Commands-Commit, RollBack, Savepoint
Oracle Views and Synonyms
• Types of Views
• Synonyms
• Types of Synonyms
• Indexes
• Types of Indexes
Using Subqueries to Solve Queries
• Subqueries
• Co-Related Subquery
OLAP Functions
• OLAP Features
• Roll Up
• Model Clause
• Dimension Modelling
Conditional Statement
• Block
• Variable Attributes
• Nested Blocks
• Conditional Control Statements
• Iterative Controls (Loop)
Cursor Management
• Types of Cursor
• Writing Explicit cursors
• Explicit cursor functions
• Advance Explicit cursor
• Cursor with parameters
Exception Handling
• Handling Exception
• Handling Exception with PL/SQL Predefined Exceptions,
• User Defined Exceptions
• Non-Predefined Error
• Function for trapping Exception
• Trapping user-defined Exception
Subprogram, Procedure and passing parameters and Advance Package Concepts and functions
• Important Features of Sub-Programs
• Procedure
• Functions
Trigger Management
• Introduction to Triggers
• Types of Triggers
• Compound Triggers
Oracle Job Scheduling
Large Object Functions
• Large object functions – BFILENAME, EMPTY_BLOB, EMPTY_CLOB etc
Important Features of Oracle
Advance level- Scripting

386