PL/SQL Associative Array

In PL/SQL, an Associative Array (also known as an index-by table) is a collection of key-value pairs where each key is unique, allowing for efficient retrieval of the associated value. Associative arrays are particularly useful when we need to store data in a non-contiguous manner, enabling us to index elements with non-integer or sparse keys.

PL/SQL Associative Array is especially useful for managing sparse datasets or non-sequential data. In this article, we will explain how to create, use, and manipulate associative arrays in PL/SQL, with clear examples to demonstrate their flexibility.

PL/SQL Associative Array

An associative array can be indexed by an integer or a string. It allows for fast lookups and dynamic data manipulation without defining a fixed size. Unlike VARRAYs and nested tables, associative arrays do not require consecutive memory allocation. They are highly efficient for performing lookups or managing non-contiguous data without needing to define a fixed array size.

Syntax:

TYPE array_name IS TABLE OF element_type INDEX BY index_type;

key terms

• array_name: The name of the associative array.
  
• element_type: The data type of the elements stored in the array.
  
• index_type: The data type of the index (commonly PLS_INTEGER or VARCHAR2).

Example 1: Associative Array with Integer Index

In this example, we will demonstrate how to use an associative array with an integer index, where employee IDs are used as unique keys. The array stores employee salaries, allowing efficient retrieval of salary data using the employee ID. This approach is ideal for managing data where the index is numeric and non-contiguous.

Query:

DECLARE
  -- Declare an associative array type
  TYPE employee_salary IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  -- Declare a variable of this type
  emp_salaries employee_salary;
BEGIN
  -- Insert data into the associative array
  emp_salaries(101) := 50000;
  emp_salaries(102) := 60000;
  emp_salaries(103) := 55000;

  -- Access and display the data
  DBMS_OUTPUT.PUT_LINE('Salary of employee 101: ' || emp_salaries(101));
  DBMS_OUTPUT.PUT_LINE('Salary of employee 102: ' || emp_salaries(102));
  DBMS_OUTPUT.PUT_LINE('Salary of employee 103: ' || emp_salaries(103));
END;

Output

Explanation:

• The associative array employee_salary is declared with a key type of PLS_INTEGER and stores NUMBER values representing employee salaries.
  
• Values are inserted into the array using the employee ID as the index.
  
• The DBMS_OUTPUT.PUT_LINE function displays the salaries for each respective employee.

Example 2: Associative Array with String Index

This example illustrates how to use an associative array with a string index, where product names serve as the keys. The array stores product prices, making it easy to retrieve the price of each product using its name as the index. Associative arrays indexed by strings are useful for mapping descriptive labels to data values.

Query:

DECLARE
  -- Declare an associative array type
  TYPE product_price IS TABLE OF NUMBER INDEX BY VARCHAR2(50);
  -- Declare a variable of this type
  product_prices product_price;
BEGIN
  -- Insert data into the associative array
  product_prices('Laptop') := 1200;
  product_prices('Smartphone') := 800;
  product_prices('Tablet') := 400;

  -- Access and display the data
  DBMS_OUTPUT.PUT_LINE('Price of Laptop: ' || product_prices('Laptop'));
  DBMS_OUTPUT.PUT_LINE('Price of Smartphone: ' || product_prices('Smartphone'));
  DBMS_OUTPUT.PUT_LINE('Price of Tablet: ' || product_prices('Tablet'));
END;

Output

Explanation:

• The associative array product_price is declared with a key type of VARCHAR2(50) and stores NUMBER values representing product prices.
  
• Products and their prices are inserted into the array using the product name as the index.
  
• The DBMS_OUTPUT.PUT_LINE function displays the prices for each respective product.

Benefits of Associative Arrays in PL/SQL

1. Dynamic Sizing: Associative arrays grow dynamically without needing predefined size constraints.
   
2. Efficient Lookups: Fast retrieval based on unique keys (integers or strings) ensures quick access to data.
   
3. Sparse Data Handling: Ideal for handling non-contiguous data where only specific indices contain values.
   
4. Flexible Indexing: Can be indexed using either integers or strings, making them versatile for different use cases.

Limitations of Associative Arrays

• Memory Restrictions: Associative arrays reside in PL/SQL memory, meaning they are not stored in the database permanently.
  
• No Multi-dimensional Arrays: Associative arrays cannot directly support multi-dimensional indexing.
  
• Not Persistent: Data stored in associative arrays is lost after the PL/SQL block execution ends.

Conclusion

Associative arrays in PL/SQL provide a powerful and flexible way to manage collections of data without the constraints of fixed indexing or contiguous storage. They are particularly useful for where dynamic data retrieval is required, allowing for efficient data manipulation and access. PL/SQL Associative Array can optimize code's efficiency when dealing with dynamic datasets.