To the point guide to PL/SQL

What are records? 

Records are a type of datatypes is defined as a placeholder to store data in fields where field in the record has its own datatype and name.

Records are composite datatypes which is a combination of different datatypes such as varchar, char etc. 

PL/SQL Record Declaring:

To declare a record there are three ways:

• Table-based record
• Cursor-based record
• Programmer-defined records.

Table-based Record Declaring:

User need to use a table name with %ROWTYPE attribute to declare a table based record. The field of the record has the same name and data type as the table column.

Example:

DECLARE
   student_rec  student%rowtype;
BEGIN
   SELECT * into student_rec
   FROM stud_details
   WHERE id = 2;

   dbms_output.put_line('Student ID: ' || student_rec.Stud_id);
   dbms_output.put_line('Student Name: ' || student_rec.sname);
   dbms_output.put_line('Student Subject: ' || student_rec.subject);
   END;
/

Result:

Student ID: 2
Student Name: Suman
Student Subject: History
PL/SQL procedure successfully operation.

Cursor-based record:

To define a cursor for record based on a cursor, to declare the cursor variable user use %ROWTYPE with it.

Example

DECLARE
   CURSOR student_cur is
      SELECT Stud_id, sname, subject 
      FROM stud_details;
   student_rec student_cur%rowtype;
BEGIN
   OPEN student_cur;
 LOOP
   FETCH student_cur into student_rec;
   EXIT WHEN student_cur%notfound;
   DBMS_OUTPUT.put_line(student_rec.sname || '  ' || student_rec.marks);
 END LOOP;
END;
/

Result:

David  90
Suman  89
Rathan  91
PL/SQL procedure successfully completed.

User-Defined Records

A user-defined record type provided by PL/SQL which allows defining different record structures. Each record consists of different fields.

• Stud_id
• Name
• Stream
• Address

Defining a Record:

Syntax:

TYPE
type_name IS RECORD
  ( field_name1  datatype1  [NOT NULL]  [:= DEFAULT EXPRESSION],
      ...
   field_nameN  datatypeN  [NOT NULL]  [:= DEFAULT EXPRESSION);
record-name  type_name; 

Fields:

By using dot (.) operator user can access any field of a record.

Example:

DECLARE
   type info is record
      (Stud_id number,
       Name varchar(30),
       Stream varchar(30),
        Country varchar(50)
       );
   student1 info;
  BEGIN
   -- student 1 Details
   student1.Stud_id  := 1;
   student1.Name := 'Roopa'; 
   student1.Stream := 'Computer Science';
   student1.Country := 'India';
 
    -- Print student 1 record
   dbms_output.put_line('Student 1 id : '|| student1.Stud_id);
   dbms_output.put_line('Student 1 Name : '|| student1.Name);
   dbms_output.put_line('Student 1 Stream : '|| student1.Stream);
   dbms_output.put_line('Student 1 Country : ' || student1.Country);
END;
/

Result:

Student 1 id : 1
Student 1 Name : Roopa
Student 1 Stream : Computer Science
Student 1 Country : India
PL/SQL procedure successfully completed. 

Records as Subprogram Parameters:

The same way we pass a variable, user can pass a record as a subprogram parameter.

Example:

DECLARE
   type info is record
      (Stud_id number,
       Name varchar(30),
       Stream varchar(30),
        Country varchar(50)
       );

   student1 info;

PROCEDURE printdetails (student  info) IS
BEGIN
   dbms_output.put_line('Student id : '|| student.Stud_id);
   dbms_output.put_line('Student Name : '|| student.Name);
   dbms_output.put_line('Student Stream : '|| student.Stream);
   dbms_output.put_line('Student Country : ' || student.Country);
END;

BEGIN
   -- student 1 Details
   student1.Stud_id  := 1;
   student1.Name := 'Roopa'; 
   student1.Stream := 'Computer Science';
   student1.Country := 'India';

-- student 2 Details
   student1.Stud_id  := 2;
   student1.Name := 'Anupama'; 
   student1.Stream := 'Information Science';
   student1.Country := 'Australia';

 
   --To print student info using procedure
   printdetails(student1);
END;
/ 

Result:

Student  id : 1
Student  Name : Roopa
Student  Stream : Computer Science
Student  Country : India
Student  id : 2
Student  Name : Anupama
Student  Stream : Information Science
Student  Country : Australia

PL/SQL procedure successfully completed.