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;

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

The above is the detailed content of RECORD vs COLLECTION in PLSQL. For more information, please follow other related articles on the PHP Chinese website!