PL/SQL - Records

A PL/SQL record is a data structure that can hold data items of different kinds.

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.

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

Cursor-Based Records

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

User-Defined Records

Suppose if you want to keep track of your books in a college library. The following parameters will help you.

• Title
• Author
• Subject
• Book ID

Defining a Record

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;

Accessing Fields

DECLARE
   type t_books is record
      (v_title varchar(50),
       v_author varchar(50),
       v_subject varchar(100),
       v_book_id number);
   book1 t_books;
   book2 t_books;
BEGIN
   -- Book 1 specification
   book1.title  := 'C++ Programming';
   book1.author := 'Yaswanth '; 
   book1.subject := 'C++ Programming Tutorial';
   book1.book_id := 6493407;

   -- Book 2 specification
   book2.title := 'Telecommunications';
   book2.author := 'BSNL';
   book2.subject := 'Telecommunications Tutorial';
   book2.book_id := 6496700;

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

Records as Subprogram Parameters

DECLARE
   type t_books is record
      (v_title  varchar(50),
      v_author  varchar(50),
      v_subject varchar(100),
      v_book_id   number);
   book1 books;
   book2 books;

PROCEDURE printbook (book t_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 := 'Yaswanth '; 
   book1.subject := 'C++ Programming Tutorial';
   book1.book_id := 6496407;

   -- Book 2 specification
   book2.title := 'Telecommunications';
   book2.author := 'Zara Ali';
   book2.subject := 'Telecommunications Tutorial';
   book2.book_id := 6496700;

   -- Use procedure to print book info
   printbook(book1);
   printbook(book2);
END;
/

Complete Tutorial