PL/SQL Collections 🎯

Welcome to our deep dive into PL/SQL Collections! In this lesson, we'll explore different types of collections and learn how to use them effectively. Let's get started!

What are PL/SQL Collections? 📝

Collections in PL/SQL are data structures that hold multiple values. They are an essential part of PL/SQL programming as they help manage and manipulate large amounts of data efficiently.

Types of PL/SQL Collections 📝

PL/SQL provides several types of collections, each with its unique characteristics and use cases. Let's take a closer look at the four main types:

Arrays
Associative Arrays
Records
NLSType (PL/SQL's version of a List)

Arrays 📝

Arrays are ordered collections of elements of the same data type. They are zero-based indexed, meaning the first element's index is 0.

Creating an Array 💡

plsql

TYPE my_array_type IS ARRAY(1 to 5) OF NUMBER;
my_array my_array_type;

Accessing and Modifying Array Elements 💡

plsql

my_array(1) := 1;
my_array(2) := 2;
...
my_array(5) := 5;

print_array(my_array);

In the above example, we've defined an array called my_array of type my_array_type and length 5. We then initialize the array with numbers and print it using a function print_array (not defined here).

Associative Arrays 📝

Associative arrays, also known as hash tables, are collections with named indices instead of positional ones. They are useful when you need to look up values based on a key.

Creating an Associative Array 💡

plsql

TYPE my_assoc_type IS TABLE OF VARCHAR2 INDEX BY VARCHAR2;
my_assoc my_assoc_type;

Accessing and Modifying Associative Array Elements 💡

plsql

my_assoc('key1') := 'value1';
my_assoc('key2') := 'value2';
...

print_assoc(my_assoc);

In this example, we've defined an associative array called my_assoc of type my_assoc_type and used keys to store and retrieve values.

Records 📝

Records are user-defined collections of attributes of different data types. They are useful when you want to group related data together.

Creating a Record 💡

plsql

CREATE OR REPLACE TYPE my_record_type AS OBJECT (
  name VARCHAR2(50),
  age NUMBER
);

my_record my_record_type;

Accessing and Modifying Record Attributes 💡

plsql

my_record.name := 'John Doe';
my_record.age := 30;

print_record(my_record);

In this example, we've defined a record called my_record of type my_record_type and set its attributes using dot notation.

NLSType 📝

NLSType, or nested tables, are collections of other collections. They are useful when you need to store complex data structures.

Creating an NLSType 💡

plsql

CREATE OR REPLACE TYPE my_nlst_type AS TABLE OF my_array_type;
my_nlst my_nlst_type;

Accessing and Modifying NLSType Elements 💡

plsql

DECLARE
  nested_array my_array_type;
BEGIN
  nested_array(1) := 1;
  nested_array(2) := 2;
  ...

  my_nlst.EXTEND;
  my_nlst(my_nlst.COUNT) := nested_array;
END;

In this example, we've defined an NLSType called my_nlst of type my_nlst_type and extended it with an array called nested_array.

Practical Examples 💡

In the following sections, we'll explore practical examples using the collections we've learned.