Oracle Record Types

RECORDS

Records are composed of multiple elements and are a composite data structure that can hold the data items of various kinds. It seems like a row that exists in a table in the database, but a record's elements cannot stand on its own. It consists of multiple fields, similar to a row of a database table.

Here is the syntax of a composite datatype:

Syntax:

Syntax.png

Oracle supports three kinds of records:

Record types.png

Note: To create a table-based or cursor_based record use the %ROWTYPE attribute.

Syntax: <record_name> <table_name or cursor_name>%ROWTYPE.

TABLE BASED RECORDS

As the name implies, table-based records are records with a structure drawn from the structure of tables, in other words whose set of columns are derived from a list of the columns of a table. The name of the fields in the records must be similar to the name of the column in the table.

To understand the example clearly, we are assuming a table known as EMPLOYEES.

Example:

Employee table.png
 

DECLARE

   Employee_Info Employees%rowtype;

BEGIN

   SELECT * into Employee_Info

   FROM Employees

   WHERE ID = 1004;

 

   dbms_output.put_line('Emp id: ' || Employee_Info.id);

   dbms_output.put_line('Employee Name: ' || Employee_Info.name);

   dbms_output.put_line('Employee Address: ' || Employee_Info.Designation);

   dbms_output.put_line('Employee Salary: ' || Employee_Info.Salary);

   dbms_output.put_line('Employee City: ' || Employee_Info.City);

END;

Result:
Emp_id : 1004
Employee Name : SHREYA
Employee Designation : Senior Developer
Employee Salary : 50k
Employee_City : KANPUR

CURSOR BASED RECORDS

Records with a derived from the SELECT list of a cursor is known as Cursor Based Records. They have a structure that matches the elements of a predefined cursor.

Example: Assuming the preceding Employee table and Employee_cur as a cursor:
 

DECLARE

   CURSOR Employee_cur is

      SELECT id, name, Designation

      FROM Employees;

   Employee_Info Employee_cur%rowtype;

BEGIN

   OPEN Employee_cur;

   LOOP

      FETCH Employee_cur into Employee_Info;

      EXIT WHEN Employee_cur%NOTFOUND;

      DBMS_OUTPUT.put_line (Employee_Info.id || ' ' || Employee_Info.name || ' ' ||    

      Employee_Info.Designation);

   END LOOP;

END;


Result

Result.png


PROGRAMMER DEFINED / USER DEFINED RECORDS

User / Programmer defined records provided by Oracle allows us to define the record structures of various types that consist of various 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;

Example
 
Suppose we want to keep the records of our customers, and for that we must have some attributes of the customers like: their ID, Name, Contact Number, and Country and so on.

To access the records we need to define them first, so let us define our records.

Defining Records
 

DECLARE

TYPE Customers IS RECORD

(

Cust_ID  Number,

Cust_Name  varchar(50),

Contact_Num NUMBER),

Country varchar(50)

);

Customer1 Customers;

Customer2 Customers;

Customer3 Customers;

Customer4 Customers;


Now, access these records using the (.) operator.

Accessing Records
 

DECLARE

TYPE Customers IS RECORD

      (

        Cust_id Number

        Cust_Name varchar(20),

        Contact_Num Number,

        Country varchar(50),

      );

   Customer1 Customers;

   Customer2 Customers;

   Customer3 Customers;

  Customer4 Customers;

BEGIN

// specify or Assign the records to the Customers//

   -- Customer 1 specification

   Customer1.Cust_id  := #00211;

   Customer1.Cust_Name := 'Richard';

   Customer1.Contact_Num := 8943264000;

   Customer1.Country := 'USA';

 

   -- Customer 2 specification

   Customer2.Cust_id := #00311;

   Customer2.Cust_Name := 'Charls';

   Customer2.Contact_Num := 8745353655;

   Customer2.Country := 'Australia';


   -- Customer 3 specification

   Customer3.Cust_id := #00411;

   Customer3.Cust_Name := 'Jenifer';

   Customer3.Contact_Num := 5367345644;

   Customer3.Country := 'Europe';


   -- Customer 4 specification

   Customer4.Cust_id := #00311;

   Customer4.Cust_Name := 'George';

   Customer4.Contact_Num := 7777564211;

   Customer4.Country := 'England';


// Print the records of all the 4 customers//


   -- Print Customer 1 record

   dbms_output.put_line('Customer 1 Cust_id : '|| Customer1.Cust_id);

   dbms_output.put_line('Customer 1 Cust_Name : '|| Customer1.Cust_Name);

   dbms_output.put_line('Customer 1 Contact_Num : '|| Customer1.Contact_Num);

   dbms_output.put_line('Customer 1 Country : ' || Customer1.Country);

 

   -- Print Customer 2 record

   dbms_output.put_line('Customer 2 Cust_id : '|| Customer2.Cust_id);

   dbms_output.put_line('Customer 2 Cust_Name : '|| Customer2.Cust_Name);

   dbms_output.put_line('Customer 2 Contact_Num : '|| Customer2.Contact_Num);

   dbms_output.put_line('Customer 2 Country : '|| Customer2.Country);


   -- Print Customer 3 record

   dbms_output.put_line('Customer 3 Cust_id : '|| Customer3.Cust_id);

   dbms_output.put_line('Customer 3 Cust_Name : '|| Customer3.Cust_Name);

   dbms_output.put_line('Customer 3 Contact_Num : '|| Customer3.Contact_Num);

   dbms_output.put_line('Customer 3 Country : '|| Customer3.Country);


   -- Print Customer 4 record

   dbms_output.put_line('Customer 4 Cust_id : '|| Customer4.Cust_id);

   dbms_output.put_line('Customer 4 Cust_Name : '|| Customer4.Cust_Name);

   dbms_output.put_line('Customer 4 Contact_Num : '|| Customer4.Contact_Num);

   dbms_output.put_line('Customer 4 Country : '|| Customer4.Country);


END;

Result

customer 1 Cust_id : #00211
customer 1 Cust_Name : Richard
customer 1 Contact_Num : 8943264000
customer 1 Country : USA
customer 2 Cust_id : #00311
customer 2 Cust_Name : Charls
customer 2 Contact_Num : 8745353655
customer 2 Country : Australia

customer 3 Cust_id : #00311
customer 3 Cust_Name : Jenifer
customer 3 Contact_Num :
5367345644

customer 3 Country : Europe
customer 4 Cust_id : #00511
customer 4 Cust_Name : George
customer 4 Contact_Num : 7777564211
customer 4 Country : England