Records and Collections in Oracle PL/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.
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;
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.
FAQs
Q: What is the difference between a record and a collection in Oracle PL/SQL?
A: A record is a composite data type that groups related fields of potentially different data types together, while a collection is a data structure that holds multiple values of the same or different data types.
Q: Can a record be stored in a collection?
A: Yes, a record can be stored in a collection, allowing you to manage groups of complex data in an organized manner.
Q: What are the different types of collections in Oracle PL/SQL?
A: The collection can be of three types in Oracle PL/SQL: Associative Arrays, Nested Tables, and Varrays.
