RECORD vs COLLECTION in PLSQL

In PL/SQL (Oracle's Procedural Language for SQL), "record" and "collection" have specific meanings related to how data is structured and managed within the PL/SQL program:

1. Record:

A RECORD is a composite data type in PL/SQL that can hold a single row of data with multiple fields.

• Each field can have a different data type.
• It is similar to a row in a table where each column corresponds to a field in the record.
• Example: You can define a RECORD to store details of a single employee with fields like emp_id, emp_name, and emp_salary.

Example in PL/SQL:

DECLARE
    TYPE emp_record IS RECORD (
        emp_id NUMBER,
        emp_name VARCHAR2(50),
        emp_salary NUMBER
    );
    employee emp_record;
BEGIN
    employee.emp_id := 101;
    employee.emp_name := 'John Doe';
    employee.emp_salary := 5000;
END;

2. Collection:

A COLLECTION is a data structure that can hold multiple values of the same data type or composite types. In PL/SQL, there are three types of collections: INDEX BY tables (associative arrays), VARRAY (variable-size arrays), and NESTED TABLE.

• Unlike a RECORD, a collection can store multiple rows (or multiple records). It allows you to handle bulk data efficiently.
• Collections are useful for working with sets of data in loops or for batch processing.

Example of a Nested Table in PL/SQL:

DECLARE
    TYPE emp_table IS TABLE OF VARCHAR2(50);
    employees emp_table;
BEGIN
    employees := emp_table('John Doe', 'Jane Smith', 'Michael Brown');
    FOR i IN 1..employees.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(employees(i));
    END LOOP;
END;

Summary:

A RECORD is used to group related fields into a single unit, representing a row of data.

A COLLECTION is used to store multiple elements (rows, values, or records), allowing batch operations on sets of data.