PLSQL

PL/SQL Records

PL/SQL Records

Records are the data structures, which can hold data items, data items of different kinds. These consists of different fields, similar to a row of database details. 

They are three different kinds of records: -

  1.  Table-based

The %ROWTYPE attribute enables the programmer for creating table-based and cursorbased records.

Example: 

DECLARE 

   teacher_rec teachers%rowtype; 

BEGIN 

   SELECT * into teacher_rec 

   FROM teachers 

   WHERE id = 2;  

   dbms_output.put_line('teachers ID: ' || teacherer_rec.id); 

   dbms_output.put_line('teachers Name: ' || teacher_rec.name); 

   dbms_output.put_line('teachers Address: ' || teacher_rec.address); 

   dbms_output.put_line('teachers Salary: ' || teacher_rec.salary); 
END; 
/
Output:
teacher ID: 2
teacher Name: sai 
teacher Address: Hyderabad 
teacher Salary: 2500
PL/SQL procedure successfully completed.
Cursor-based records
Example:
DECLARE 
   CURSOR teacher_cur is 
      SELECT id, name, address  
      FROM teachers; 
   teacher_rec teacher_cur%rowtype; 
BEGIN 
   OPEN teacher_cur; 
   LOOP 
      FETCH teacher_cur into teacher_rec; 
      EXIT WHEN teacher_cur%notfound; 
      DBMS_OUTPUT.put_line(teacher_rec.id || ' ' || teacher_rec.name); 
   END LOOP; 
END; 
/
Output:
1 ranjitha 
2 sai 
3 shakthi 
4 vignesh 
5 gayathri  
PL/SQL procedure successfully completed.
User-defined records
We have user-defined records in PL/SQL that allows us for defining the record structures. And these records, consists of different fields.
Syntax:
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;