Collection and Record | Context of Oracle SQL

In Oracle PL/SQL, a record and a collection are distinct types of data structures, and one is not a type of the other. However, they can be used together in a way that helps manage complex data.

Here’s a clear breakdown of the relationship between records and collections:

1. Record

A record is a composite data type that groups related fields of potentially different data types together. Each field within a record can have its own data type.

Records are used when you want to represent a row of data where different fields (attributes) have different data types.

A record is not a collection, but it can be stored within a collection.

Example of a Record:

DECLARE

-- Define a record to hold employee information

TYPE employee_record IS RECORD (

emp_id NUMBER,

emp_name VARCHAR2(50),

emp_salary NUMBER

);

-- Declare a record variable

emp employee_record;

BEGIN

-- Assign values to the fields of the record

emp.emp_id := 1001;

emp.emp_name := 'John Doe';

emp.emp_salary := 50000;

-- Display record values

DBMS_OUTPUT.PUT_LINE('ID: ' || emp.emp_id);

DBMS_OUTPUT.PUT_LINE('Name: ' || emp.emp_name);

DBMS_OUTPUT.PUT_LINE('Salary: ' || emp.emp_salary);

END;

Here, employee_record is a record, and it holds different data types: NUMBER for emp_id, VARCHAR2 for emp_name, and NUMBER for emp_salary.

1. Collection

A collection is a data structure that holds multiple values. A collection can store elements of the same data type (e.g., all numbers or all strings) or records of different types if needed.

The collection can be of three types in Oracle PL/SQL:

Associative Arrays (previously called PL/SQL tables)

Nested Tables

Varrays

A collection is not a record, but you can store records in a collection. Collections are useful for managing multiple records or values together.

Example of a Collection of Records:

DECLARE

-- Define a record type

TYPE employee_record IS RECORD (

emp_id NUMBER,

emp_name VARCHAR2(50),

emp_salary NUMBER

);

-- Define a collection (nested table) to store records

TYPE emp_table IS TABLE OF employee_record;

employees emp_table;  -- Declare the collection variable

BEGIN

-- Initialize the collection with records

employees := emp_table(

employee_record(1001, 'John Doe', 50000),

employee_record(1002, 'Jane Smith', 60000)

);

-- Iterate over the collection and display record details

FOR i IN 1..employees.COUNT LOOP

    DBMS_OUTPUT.PUT_LINE('ID: ' || employees(i).emp_id ||

                         ', Name: ' || employees(i).emp_name ||

                         ', Salary: ' || employees(i).emp_salary);

END LOOP;

END;

Key Points:

A Record is not a collection. It’s a composite data type used to group related fields (with potentially different data types).

A Collection is a data structure that stores multiple elements of the same data type (like arrays or lists). However, you can store records (which can have different data types inside them) in collections.

Conclusion:

Record is not a type of collection.

Collection is not a type of record.

However, a collection can hold records as elements, allowing you to manage groups of complex data in an organized manner.