RECORD vs COLLECTION in PLSQL

#webdev #javascript #beginners #fullstack

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;