PL/SQL - Records

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

1.Table-based
2.Cursor-based records
3.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; 
/

Output:
Customer ID: 5
Customer Name: Hardik
Customer Address: Bhopal
Customer Salary: 9000

Simple Calculations Using Record

declare 
c_rec calc%rowtype;
res1 int:=0;
c_sn int:=1;
begin
select * into c_rec from calc where sn=c_sn;

case 
	when c_sn=1 then 
		res1:=c_rec.fno+c_rec.sno;
		dbms_output.put_line(res1);
		update calc set res=res1 where sn=c_sn;

	when c_sn=2 then 
		res1:=c_rec.fno-c_rec.sno;
		dbms_output.put_line(res1);
		update calc set res=res1 where sn=c_sn;

	when c_sn=3 then 
		res1:=c_rec.fno*c_rec.sno;
		dbms_output.put_line(res1);
		update calc set res=res1 where sn=c_sn;

	when c_sn=4 then 
		res1:=c_rec.fno/c_rec.sno;
		dbms_output.put_line(res1);
		update calc set res=res1 where sn=c_sn;
	else
	dbms_output.put_line('invalid');
end case;
	
end;
/

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 custom	er_rec; 
      EXIT WHEN customer_cur%notfound; 
      DBMS_OUTPUT.put_line(customer_rec.id || ' ' || customer_rec.name); 
   END LOOP; 
END; 
/

Output:
1 Ramesh
2 Khilan
3 kaushik
4 Chaitali
5 Hardik
6 Komal

Salary Update Using Record

Salary <2000 add 100
Salary <5000 add 300
Salary <19000 add 500

DECLARE 
   
   
   CURSOR c_customers is 
      SELECT * FROM customers; 

   c_rec c_customers%rowtype;	
BEGIN 
   OPEN c_customers; 
   LOOP 
   FETCH c_customers into c_rec; 
      EXIT WHEN c_customers%notfound; 
      if(c_rec.salary<2000) then
	update customers set salary=c_rec.salary+100 where id=c_rec.id;
      
       elsif (c_rec.salary<=5000) then
        update customers set salary=c_rec.salary+300 where id=c_rec.id;
       elsif (c_rec.salary<=19000) then
         update customers set salary=c_rec.salary+500 where id=c_rec.id;
	end if;
   END LOOP; 
   CLOSE c_customers; 
END; 
/

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

Output:
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

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

Output:
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