What Is Record Data Type in PL SQL?

Using Records

You can use records to store and manipulate related data elements. Once you have defined a record, you can declare variables of that record type and assign values to its individual fields.

In the example above, we declared an employee_rec record type with three fields: id, name, and salary. We then declared a variable emp of type employee_rec and assigned values to its fields using the dot notation.

To access the fields of a record, you use the dot notation. For example, emp.id refers to the id field of the emp record variable.

Nested Records

You can also define nested records, where a record can have another record as one of its fields. This allows you to represent complex data structures.

TYPE address_rec IS RECORD(
  street VARCHAR2(100),
  city VARCHAR2(50),
  state VARCHAR2(50)
);

TYPE employee_rec IS RECORD(
  id employees.employee_id%TYPE,
  name employees.last_name%TYPE,
  address address_rec
);

emp employee_rec; -- Declare a variable of type employee_rec

-- Assigning values to nested fields
emp.address.street := '123 Main St';
emp.city := 'New York';
emp.state := 'NY';
BEGIN
  -- Accessing nested fields
  dbms_output.name);
  
  dbms_output.put_line('Address: ' || emp.street ||
                      ', ' || emp.city ||
                      ', ' || emp.state);
END;

TYPE employee_table_type IS TABLE OF employee_rec INDEX BY PLS_INTEGER;
  
employees employee_table_type;
  
BEGIN
  -- Adding records to collection
  employees(1).id := 1001;
  employees(1).name := 'Smith';
  employees(1).salary := 5000;
  
  employees(2).id := 1002;
  employees(2).name := 'Johnson';
  employees(2).salary := 6000;
  
  -- Accessing records in collection
  FOR i IN employees.FIRST..employees.LAST LOOP
    dbms_output.put_line('Employee ID: ' || employees(i).id);
    dbms_output.put_line('Employee Name: ' || employees(i).name);
    dbms_output.put_line('Employee Salary: ' || employees(i).salary);
    dbms_output.put_line('');
  END LOOP;
END;