Collection and Record | Context of Oracle SQL

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.

---

2. 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.