PL/SQL Records

A PL/SQL record is a composite data structure that is a group of related data stored in fields. Each field in the PL/SQL record has its own name and data type.

A record is a group of related data items stored in fields, each with its own name and datatype. Records are composed of a group of fields, similar to the columns in a row. The %ROWTYPE attribute lets you declare a PL/SQL record that represents a row in a database table, without listing all the columns.

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.

Syntax :

DECLARE
 table_based_record table_name%ROWTYPE;

Example :

DECLARE
 r_emp employees%ROWTYPE;
 n_emp_id employees.employee_id%TYPE := 200;
BEGIN
 SELECT *
 INTO r_emp
 FROM employees
 WHERE employee_id = n_emp_id;
 dbms_output.put_line('ID: ' || r_emp.id); 
 dbms_output.put_line('Name: ' || r_emp.name); 
 dbms_output.put_line('Salary: ' || r_emp.salary);
END;
/

Cursor-Based Records

The following example illustrates the concept of 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

PL/SQL provides a user-defined record type that allows you to define the different record structures. These records consist of different fields.

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;

Example :

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.

Example :

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

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.

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

Post your Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s