”;
In this chapter, 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 cursorbased 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.
”;