Let's focus on PL/SQL collections and relevant terminologies associated with collections in PL/SQL.
In Oracle PL/SQL, collections are complex data types that allow you to handle multiple values in a single variable. Collections can be used to store, process, and manage data in a variety of ways, making them essential in PL/SQL programming. Here are the main concepts, terminologies, and types of collections in PL/SQL.
PL/SQL Collections
PL/SQL collections are similar to arrays in other programming languages but offer more flexibility. There are three main types of collections in Oracle PL/SQL:
Associative Arrays (Index-By Tables)
Nested Tables
Varrays (Variable-Size Arrays)
- Associative Arrays (Index-By Tables)
Definition:
An associative array is a collection of key-value pairs where the key is an index (either integer or string), and the value is the data being stored. It is also called an index-by table.
Terminology:
Index: The key used to uniquely identify elements in the associative array. The index can be of type INTEGER or VARCHAR.
Element: The value stored in the collection. Each element is associated with an index.
Usage:
Typically used for fast lookups where the keys are non-contiguous (i.e., you can have gaps between indices).
Example:
DECLARE
TYPE assoc_array IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
my_array assoc_array;
BEGIN
my_array(1) := 'First';
my_array(2) := 'Second';
DBMS_OUTPUT.PUT_LINE(my_array(1)); -- Output: First
END;
- Nested Tables
Definition:
A nested table is an ordered collection of elements, where each element can be a scalar value, an object, or another collection. It is similar to an array in other programming languages, but it can grow dynamically and is stored in memory.
Terminology:
Element: The data stored in the nested table, which can be any data type.
Collection Type: The datatype definition for the nested table. It’s defined with the TABLE OF keyword.
Usage:
Nested tables are often used to store sets of data and are useful when you need dynamic resizing.
Example:
DECLARE
TYPE nested_table_type IS TABLE OF VARCHAR2(100);
my_nested_table nested_table_type := nested_table_type('Apple', 'Banana', 'Cherry');
BEGIN
DBMS_OUTPUT.PUT_LINE(my_nested_table(1)); -- Output: Apple
END;
- Varrays (Variable-Size Arrays)
Definition:
A varray (short for variable-size array) is an ordered collection of elements that has a predefined maximum size. Varrays are typically used when you know the size of the collection in advance, and they provide better memory efficiency for smaller, fixed-size collections.
Terminology:
Maximum Size: The maximum number of elements that can be stored in a varray.
Element: The data stored in the varray, which can be of any datatype.
Usage:
Varrays are most useful when the number of elements is small and fixed. They can be stored in database tables as well.
Example:
DECLARE
TYPE varray_type IS VARRAY(5) OF VARCHAR2(100);
my_varray varray_type := varray_type('One', 'Two', 'Three');
BEGIN
DBMS_OUTPUT.PUT_LINE(my_varray(1)); -- Output: One
END;
Common PL/SQL Collection Terminologies
Below are some important terminologies and concepts that are associated with collections in PL/SQL.
- Collection Types
Declared Types: In PL/SQL, collections are often declared as custom types using the TYPE keyword. This allows you to define collections of specific data types (e.g., TABLE OF, VARRAY).
- Bulk Operations:
PL/SQL collections are commonly used with bulk operations like BULK COLLECT and FORALL, which allow for efficient processing of multiple rows of data in a single operation.
BULK COLLECT: A PL/SQL feature that allows for fetching multiple rows from a SQL query into a collection in one go, significantly improving performance.
FORALL: A PL/SQL feature that allows for efficient processing of DML (Data Manipulation Language) operations on a collection, reducing context switching between PL/SQL and SQL engines.
- Initializing Collections
Initialization: Collections can be initialized either explicitly or implicitly.
Explicit Initialization: When you declare and initialize a collection in the same statement.
Implicit Initialization: When you declare a collection without initializing it, and then assign values to it later in the code.
- Collection Methods:
PL/SQL collections provide built-in methods that allow you to manipulate collections effectively. Some of the key methods include:
COUNT: Returns the number of elements in a collection.
EXTEND: Adds one or more elements to a collection.
TRIM: Removes one or more elements from the end of a collection.
DELETE: Deletes elements from a collection.
- Null Elements:
A collection can have NULL elements, which are placeholders for data that might not be available. Handling NULL values appropriately is essential for ensuring correct collection operations.
- Nested Collection Types:
You can create complex collection structures by using collections within collections. For example, a nested table can contain varrays, and an associative array can contain nested tables.
- Scope of Collections:
Local Collections: Defined within a PL/SQL block or procedure, their scope is limited to the block.
Global Collections: Defined at the package level, making them accessible to all procedures/functions in the package.
PL/SQL Collection Example: Bulk Collect with Nested Table
DECLARE
TYPE nested_table_type IS TABLE OF VARCHAR2(100);
TYPE assoc_array IS TABLE OF nested_table_type INDEX BY PLS_INTEGER;
my_array assoc_array;
my_nested_table nested_table_type;
BEGIN
-- Assign values to the nested table
my_nested_table := nested_table_type('Apple', 'Banana');
-- Assign the nested table to the associative array
my_array(1) := my_nested_table;
DBMS_OUTPUT.PUT_LINE(my_array(1)(1)); -- Output: Apple
END;
Summary of PL/SQL Collections Terminologies
Conclusion
PL/SQL collections provide an efficient way to handle and manipulate multiple values in a single variable. By understanding the different types of collections (Associative Arrays, Nested Tables, and Varrays) and their associated terminologies, you can leverage them effectively for a variety of use cases, including bulk data operations, dynamic collection handling, and more complex data processing scenarios in PL/SQL.