RECORDS in PL/SQL

Introduction

Record is a data-structure which can hold differnt type of datatypes. Record can be imagined as a database table with multiple columns

We have the following types of record in PL/SQL
1) Cursor Based Records
2) Table Based Records
3) User Defined Records

Example of Cursor Based Record
%ROWTYPE is used for Cursor and Table Based Records


DECLARE
   CURSOR cur_employee is
      SELECT name, eno, dept
      FROM employees
   employee_rec cur_employee%rowtype;
BEGIN
   OPEN customer_cur;
   LOOP
      FETCH cur_employee into employee_rec
      EXIT WHEN cur_employee %notfound;
      DBMS_OUTPUT.put_line(employee_rec.name || ' ' || employee_rec.dept);
   END LOOP;
END;
/



Example of Table Based Record
%ROWTYPE is used for Cursor and Table Based Records


DECLARE
   employee_rec employees%rowtype;
BEGIN
   SELECT * into employee_rec
   FROM employees
   WHERE eno = 293215;

   dbms_output.put_line('Employee Name : ' || employee_rec.name);
   dbms_output.put_line('Employee Number : ' || employee_rec.eno);
   dbms_output.put_line('Department : ' || employee_rec.dept );
END;
/


Example of User Defined Record


DECLARE
   type employee is record
      (name varchar(100),
       eno varchar(10),
       dept varchar(100)
    );
   employee_rec employee;

BEGIN
   -- Employee assignment
      employee_rec.name := 'John';
      employee_rec.eno  := '21232';   
      employee_rec.dept    := 'Sales';

   -- Print Employee record
   dbms_output.put_line('Employee Name : '|| employee_rec.name );
   dbms_output.put_line('Employee Number : '|| employee_rec.eno );
   dbms_output.put_line('Employee Department : '|| employee_rec.dept );

END;
/