PL/SQL by Example / Composite Data Types

Arrays

The topic of structures capable of holding multiple values is somewhat extensive due to the division of the Oracle database into an SQL part and a procedural PL/SQL extension. Also, individual database versions bring slight differences in the use of given structures. Therefore, I will limit myself to demonstrating the most commonly used types, namely the associative array (“index-by table”) declared in PL/SQL and the nested table declared at the SQL level.

Associative Array

Unfortunately, the PL/SQL extension does not contain any pre-prepared types for arrays of numbers or strings, so it is necessary to first declare the type and only then declare the actual variable (I will focus on using types declared in packages in one of the future articles).

DECLARE
  -- associative array of numbers indexed by integer
  TYPE Number_TT IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  lt_Number Number_TT;
  -- associative array of words indexed by string
  TYPE Dictionary_TT IS TABLE OF VARCHAR2(4000) INDEX BY VARCHAR2(4000);
  lt_Dictionary Dictionary_TT;
BEGIN
  lt_Number(1):=NULL;
  lt_Number(2):=200;
  dbms_output.put_line(lt_Number(2)); -- 200
  -- number of elements in the array
  dbms_output.put_line(lt_Number.COUNT); -- 2
  lt_Dictionary('prague'):='praha';
  lt_Dictionary('munich'):='mnichov';
  dbms_output.put_line(lt_Dictionary('prague')); -- praha
  -- does an element exist at the given index?
  IF (lt_Dictionary.EXISTS('vienna')) THEN
    -- does not exist, nothing will be printed
    dbms_output.put_line(lt_Dictionary('vienna'));
  END IF;
  -- deleting the array
  lt_Dictionary.DELETE;
  dbms_output.put_line(lt_Dictionary.COUNT); -- 0
END;

Just like with a scalar variable, values can also be assigned to a composite type using an SQL query. The command BULK COLLECT INTO is used for this purpose.

DECLARE
  TYPE Number_TT IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  lt_Employee_ID Number_TT;
BEGIN
  -- load all employee IDs sorted in ascending order
  SELECT employee_id
    BULK COLLECT INTO
         lt_Employee_ID
    FROM employees
    ORDER BY employee_id ASC
  ;
  -- does at least one exist?
  IF (lt_Employee_ID.COUNT>0) THEN
    dbms_output.put_line(lt_Employee_ID(1)); -- 100
  END IF;
END;

Nested Table

Another very commonly used type of array is the so-called nested table. After declaration, which is done in the SQL part, anyone with the appropriate rights can work with it. It is therefore not limited only to a specific block.

CREATE OR REPLACE TYPE Number_TT AS TABLE OF NUMBER;
/

DECLARE
  lt_Number Number_TT;
  lt_OtherNumber Number_TT;
BEGIN
  -- initialization
  lt_Number:=Number_TT();
  -- allocation of three positions
  lt_Number.EXTEND(3);
  -- population
  lt_Number(1):=100;
  lt_Number(2):=200;
  lt_Number(3):=300;
  dbms_output.put_line(lt_Number.COUNT); -- 3
  -- population during initialization
  lt_OtherNumber:=Number_TT(100, 200, 300);
  dbms_output.put_line(lt_OtherNumber.COUNT); -- 3
END;

The relatively tedious work with initialization and memory allocation, which is avoided with associative arrays, is balanced by the advantage of being able to query using the SQL language. It is sufficient to wrap the array in the keyword TABLE(), and the value is accessed via VALUE().

DECLARE
  l_Sum NUMBER;
  l_Average NUMBER;
  lt_Number Number_TT;
BEGIN
  lt_Number:=Number_TT(100, 200, 300);
  SELECT SUM(VALUE(t))
       , AVG(VALUE(t))
    INTO l_Sum
       , l_Average
    FROM TABLE(lt_Number) t -- "t" serves only as an alias
  ;
  dbms_output.put_line(l_Sum); -- 600
  dbms_output.put_line(l_Average); -- 200
END;

Record

A somewhat more advanced composite type is the record, which substitutes the classic structure known from other programming languages. It is a variable grouping several logically related elements.

DECLARE
  -- type declaration
  TYPE Employee_T IS RECORD(
      emp_id NUMBER
    , first_name VARCHAR2(20)
    , last_name VARCHAR2(20)
  );
  -- variable declaration of the given type
  l_Employee Employee_T;
BEGIN
  l_Employee.emp_id:=100;
  l_Employee.first_name:='Steven';
  l_Employee.last_name:='King';
  dbms_output.put_line(l_Employee.last_name); -- King
END;

The record data type is particularly useful for temporarily storing a record from a table. The type %ROWTYPE is used to load a single row with all columns of a table.

DECLARE
  TYPE Employee_T IS RECORD(
      emp_id employees.employee_id%TYPE
    , first_name employees.first_name%TYPE
    , last_name employees.last_name%TYPE
  );
  l_Employee Employee_T;
  l_Job jobs%ROWTYPE; -- row type
BEGIN
  -- number of columns and types must match
  SELECT employee_id
       , first_name
       , last_name
    INTO l_Employee
    FROM employees
    WHERE (employee_id=100)
  ;
  dbms_output.put_line(l_Employee.last_name); -- King
  -- all columns
  SELECT *
    INTO l_Job
    FROM jobs
    WHERE (job_id='IT_PROG')
  ;
  dbms_output.put_line(l_Job.job_title); -- Programmer
END;

And finally, two examples – an associative array of records and an associative array of table rows.

DECLARE
  TYPE Employee_T IS RECORD(
      emp_id employees.employee_id%TYPE
    , first_name employees.first_name%TYPE
    , last_name employees.last_name%TYPE
  );
  -- array of records
  TYPE Employee_TT IS TABLE OF Employee_T INDEX BY PLS_INTEGER;
  -- actual variable of type array of records
  lt_Employee Employee_TT;
BEGIN
  -- only selected columns
  SELECT employee_id
       , first_name
       , last_name
    BULK COLLECT INTO
         lt_Employee
    FROM employees
  ;
  dbms_output.put_line(lt_Employee.COUNT); -- 107
END;

DECLARE
  -- array of rows
  TYPE Job_TT IS TABLE OF jobs%ROWTYPE INDEX BY PLS_INTEGER;
  -- actual variable of type array of rows
  lt_Job Job_TT;
BEGIN
  -- all columns, all rows
  SELECT *
    BULK COLLECT INTO
         lt_Job
    FROM jobs
  ;
  dbms_output.put_line(lt_Job.COUNT); -- 19
END;