PL/SQL Record

A PL/SQL Record is a group of related data items stored in individual fields, each with its own attribute name and data type. To access any field of a record, we use the dot (.) operator.

PL/SQL can handle the following type of records:

• Table Based Record.
• Cursor Based Record.
• User Define Records.

Table Based Record:

%ROWTYPE attributes enables a programmer to create table based and cursor based records.

We are using the EMP table to show the concept of Table Based Record.

DECLARE 
  emp_rec emp%rowtype; 
BEGIN 
   SELECT * into emp_rec 
   FROM emp
   WHERE empno=7839 ;  
   dbms_output.put_line('Employee ID: ' || emp_rec.empno); 
   dbms_output.put_line('Employee Name: ' || emp_rec.ename); 
   dbms_output.put_line('Employee Department: ' || emp_rec.deptno); 
   dbms_output.put_line('Employee Salary: ' || emp_rec.sal); 
END;  

Cursor Based Record:

For Cursor Based Record also we are using  %ROWTYPE. To show the concept of Cursor Based Record we are using MEP table.

DECLARE 
   CURSOR emp_cur is 
      SELECT EMPNO, ENAME, SAL  
      FROM emp; 
   emp_rec emp_cur%rowtype; 
BEGIN 
   OPEN emp_cur; 
   LOOP 
      FETCH emp_cur into emp_rec; 
      EXIT WHEN emp_cur%notfound; 
      DBMS_OUTPUT.put_line(emp_rec.empno || ' ' || emp_rec.ename); 
   END LOOP; 
END;

User Define Record:

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 employee details. 

You might want to track the following attributes about each employee−

• Employee Name
• Employee ID
• Employee SAL
• Employee Department Number

Now we are creating the above record

DECLARE
TYPE employee_details_typ IS RECORD
(emp_name varchar2(50),
 emp_id   number,
emp_sal   number,
emp_dent_no number
);

/*Now we are declaring record-name  type_name;*/

EMP_record1 employee_details_typ ;
EMP_record2 employee_details_typ ;
.....

Example:

DECLARE 
  TYPE employee_details_typ IS RECORD
(emp_name varchar2(50),
 emp_id   number,
emp_sal   number,
emp_dent_no number
);
   emp_datils1 employee_details_typ;
   emp_datils2 employee_details_typ;
BEGIN 
   -- employee1 specification 
   emp_datils1.emp_name  := 'Rabindra'; 
   emp_datils1.emp_id := 2315;  
   emp_datils1.emp_sal := 50000; 
   emp_datils1.emp_dent_no := 10;  
   -- employee1  specification 
   emp_datils2.emp_name  := 'Samir'; 
   emp_datils2.emp_id := 2316;  
   emp_datils2.emp_sal := 50000; 
   emp_datils2.emp_dent_no := 20;   
  

END;