How do you declare and use PL/SQL collections? Give an example.

In PL/SQL (Procedural Language/Structured Query Language), collections provide a powerful way to store and manipulate multiple values within a single variable. PL/SQL offers several types of collections, including nested tables, associative arrays (also known as index-by tables), and varrays (variable-size arrays). Here is an in-depth explanation of how to declare and use PL/SQL collections, along with an example:

1. Declaring PL/SQL Collections:

* To declare a PL/SQL collection, you need to specify the collection type and optionally its size or limit.
* Collection types can be declared at the schema level, package level, or within a PL/SQL block.
* The syntax for declaring PL/SQL collections is as follows:
```
sql`DECLARE
collection_name collection_type;`
```
* Here, `collection_name` is the name of the collection variable, and `collection_type` represents the specific collection type being declared.
2. Using PL/SQL Collections:

* Once a collection is declared, you can use various built-in methods and operations to manipulate and access the collection's elements.
* The specific operations and methods available depend on the type of collection being used.a. Nested Tables:

* A nested table is an unordered collection of elements, similar to a dynamic array.
* To use a nested table, you can declare it using the `TABLE OF` syntax, specifying the element type.
* Elements in a nested table can be accessed using indexes or iterated over using loops.
* Here's an example of declaring and using a nested table collection:
```
sql`DECLARE
TYPE employee_list IS TABLE OF employees%ROWTYPE;
emp_collection employee_list := employee_list(); -- Initializing an empty nested table

BEGIN
-- Adding elements to the nested table
emp_collection.extend; -- Adding a new empty element at the end
emp_collection(1).employee_name := 'John Doe';
emp_collection(1).salary := 5000;

-- Accessing elements of the nested table
DBMS_OUTPUT.PUT_LINE('First employee: ' || emp_collection(1).employee_name);

-- Iterating over the nested table
FOR i IN 1..emp_collection.count LOOP
DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_collection(i).employee_name);
END LOOP;
END;
/`
```
b. Associative Arrays (Index-By Tables):

* An associative array, also known as an index-by table, is a collection that uses a key-value pair structure, similar to a hash table or a dictionary.
* Associative arrays do not have predefined bounds and can be sparse, meaning they can have gaps between indexes.
* To use an associative array, you declare it using the `TYPE` keyword and specify the key and element types.
* Here's an example of declaring and using an associative array collection:
```
sql`DECLARE
TYPE emp_salaries IS TABLE OF NUMBER INDEX BY VARCHAR2(100);
emp_collection emp_salaries;

BEGIN
-- Adding elements to the associative array
emp_collection('John Doe') := 5000;
emp_collection('Jane Smith') := 6000;

-- Accessing elements of the associative array
DBMS_OUTPUT.PUT_LINE('John Doe Salary: ' || emp_collection('John Doe'));

-- Iterating over the associative array
FOR employee IN emp_collection.FIRST..emp_collection.LAST LOOP
DBMS_OUTPUT.PUT_LINE('Employee: ' || employee || ', Salary: ' || emp_collection(employee));
END LOOP;
END;
/`
```