Advertisement Space

Working with complex data structures in PL/SQL

PL/SQL offers three types of collections (associative arrays, nested tables, VARRAYs) and records. Mastering these is essential for writing efficient PL/SQL, especially when combined with BULK COLLECT.

Recipe #1 - Associative arrays (INDEX BY tables)

Associative arrays are the most flexible collection type. They can be indexed by integers or strings and don't need to be initialized:

DECLARE
  TYPE t_salary_map IS TABLE OF NUMBER INDEX BY VARCHAR2(100);
  l_salaries t_salary_map;
BEGIN
  l_salaries('SMITH') := 800;
  l_salaries('JONES') := 2975;
  l_salaries('KING')  := 5000;

  -- Check if a key exists
  IF l_salaries.EXISTS('JONES') THEN
    DBMS_OUTPUT.put_line('Jones earns: ' || l_salaries('JONES'));
  END IF;

  -- Iterate over all entries
  DECLARE
    l_key VARCHAR2(100) := l_salaries.FIRST;
  BEGIN
    WHILE l_key IS NOT NULL LOOP
      DBMS_OUTPUT.put_line(l_key || ': ' || l_salaries(l_key));
      l_key := l_salaries.NEXT(l_key);
    END LOOP;
  END;
END;

Recipe #2 - PL/SQL records

Records group related fields together, similar to a struct in other languages. The easiest way is to anchor a record to a table row:

DECLARE
  l_emp emp%ROWTYPE;
BEGIN
  SELECT * INTO l_emp FROM emp WHERE empno = 7839;

  DBMS_OUTPUT.put_line(l_emp.ename || ' works in dept '
    || l_emp.deptno || ' and earns ' || l_emp.sal);
END;

You can also define custom record types:

DECLARE
  TYPE t_employee IS RECORD (
    name    VARCHAR2(100),
    dept    VARCHAR2(50),
    salary  NUMBER,
    is_mgr  BOOLEAN
  );
  l_emp t_employee;
BEGIN
  l_emp.name   := 'John';
  l_emp.dept   := 'Engineering';
  l_emp.salary := 75000;
  l_emp.is_mgr := FALSE;

  DBMS_OUTPUT.put_line(l_emp.name || ' — ' || l_emp.dept);
END;

Recipe #3 - Nested tables

Nested tables are like dynamic arrays that can be used in SQL. Unlike associative arrays, they can be stored in database columns:

DECLARE
  TYPE t_name_list IS TABLE OF VARCHAR2(100);
  l_names t_name_list := t_name_list();
BEGIN
  -- Extend and populate
  l_names.EXTEND(3);
  l_names(1) := 'Alice';
  l_names(2) := 'Bob';
  l_names(3) := 'Charlie';

  -- Loop through
  FOR i IN 1..l_names.COUNT LOOP
    DBMS_OUTPUT.put_line(i || ': ' || l_names(i));
  END LOOP;

  -- Delete an element (creates a sparse collection)
  l_names.DELETE(2);

  DBMS_OUTPUT.put_line('Count after delete: ' || l_names.COUNT);
END;

Useful collection methods

All collections support these methods: