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