PL/SQL - Records

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 pbrary. 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_pne( Customer ID:   || customer_rec.id); 
   dbms_output.put_pne( Customer Name:   || customer_rec.name); 
   dbms_output.put_pne( Customer Address:   || customer_rec.address); 
   dbms_output.put_pne( 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_pne(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 Chaitap 
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 pbrary. 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 Ap  ;  
   book1.subject :=  C Programming Tutorial ; 
   book1.book_id := 6495407;  
   -- Book 2 specification 
   book2.title :=  Telecom Bilpng ; 
   book2.author :=  Zara Ap ; 
   book2.subject :=  Telecom Bilpng Tutorial ; 
   book2.book_id := 6495700;  
  
  -- Print book 1 record 
   dbms_output.put_pne( Book 1 title :  || book1.title); 
   dbms_output.put_pne( Book 1 author :  || book1.author); 
   dbms_output.put_pne( Book 1 subject :  || book1.subject); 
   dbms_output.put_pne( Book 1 book_id :   || book1.book_id); 
   
   -- Print book 2 record 
   dbms_output.put_pne( Book 2 title :  || book2.title); 
   dbms_output.put_pne( Book 2 author :  || book2.author); 
   dbms_output.put_pne( Book 2 subject :  || book2.subject); 
   dbms_output.put_pne( 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 Ap 
Book 1 subject : C Programming Tutorial 
Book 1 book_id : 6495407 
Book 2 title : Telecom Bilpng 
Book 2 author : Zara Ap 
Book 2 subject : Telecom Bilpng 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_pne ( Book  title :    || book.title); 
   dbms_output.put_pne( Book  author :   || book.author); 
   dbms_output.put_pne(  Book  subject :   || book.subject); 
   dbms_output.put_pne(  Book book_id :   || book.book_id); 
END; 
   
BEGIN 
   -- Book 1 specification 
   book1.title  :=  C Programming ; 
   book1.author :=  Nuha Ap  ;  
   book1.subject :=  C Programming Tutorial ; 
   book1.book_id := 6495407;
   
   -- Book 2 specification 
   book2.title :=  Telecom Bilpng ; 
   book2.author :=  Zara Ap ; 
   book2.subject :=  Telecom Bilpng 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 Ap 
Book subject : C Programming Tutorial 
Book  book_id : 6495407 
Book title : Telecom Bilpng 
Book author : Zara Ap 
Book subject : Telecom Bilpng Tutorial 
Book book_id : 6495700  

PL/SQL procedure successfully completed.