Records in PL/SQL.

For more info visit

A record, as the name signifies, is a collection of more than one fields in a variable. For those of you who are familiar with language "C", you can compare a record to a structure in "C". The fields of a record can be of sql  or pl/sql type.

There are two ways in which a record can be declared.

1) Using Anchor Declarations(Table Based or Cursor Based Records).

ex:   employee_details employee%ROWTYPE;

Here employee is a table . Suppose, employee table has structure as shown below.

 EMP_ID                                             NOT NULL                 NUMBER
 EMP_NAME                                    NOT NULL                 VARCHAR2(10)

 We can collect the values corresponding to some row of a table in the record as

SELECT * into employee_details FROM employee WHERE emp_id  = 1;

and after that, we can access or manipulate the fields in the record using ".".

employee_details.emp_name

In the same way, we can also declare a record of Cursor type

CURSOR employee_cursor is SELECT * into employee_details FROM employee WHERE emp_id  = 1;

emp_cur employee_cursor%ROWTYPE;

We can directly fetch values from cursor into our record as

OPEN employee_cursor;

FETCH employee_cursor INTO emp_cur;

2) Programmer Defined Records:

In a programmer defined records, we can decide the field of our records. It doesn't have to be dependent on any table or cursor for its structure. These type of records are declared as

TYPE emp_rec IS RECORD

( name employee.emp_name%TYPE,

age NUMBER(3),

salary NUMBER(10) := 0

);

employee_record emp_rec;

In this type of record, we are free to define our own fields with different datatypes. We can always specify [NOT NULL]:=[DEFAULT] with the fields of a record. These types of records are useful when we want to collect data from different tables or when our record has nothing to do with tables or cursors.

We can manipulate the fields of these type of records in a number of ways, for example

employee_record.name := 'John';

select name into employee_record.name from employee where emp_id = 1;

 Note:

1) Two records cannot be compared as rec1=rec2, even if they are of same record type. Comparision of two records can only be done on field basis.

2) Values of a record can be assigned to other record if they are of same record type.

rec1 := rec2;

3) Records can be passed as parameters or can be returned from a function.

4) IS NULL cannot be used to check if all the fields of a record are NULL.

5) NULL can be assigned to a record as

rec1 := NULL;

6) We can insert the whole record into a table(without using individual fields), only if the record is declared using anchored declaration.

emp_rec employee%ROWTYPE;

insert into employee values emp_rec;