Associative Arrays Oracle PL/SQL — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

В этом учебном материале вы узнаете, как использовать Associative Arrays (ассоциативный массив) в Oracle PL/SQL с синтаксисом и примерами.

Описание

В Oracle PL/SQL Associative Arrays, также известные как индексные таблицы, в которых для значений индекса используя произвольные числа и строки. Associative Arrays - это набор пар ключ-значение, где каждый ключ уникален и используется для нахождения соответствующего значения в массиве.

Синтаксис

Синтаксис для определения, а затем объявление переменной типа Associative Arrays в Oracle PL/SQL.

TYPE type_assoc_arr IS TABLE OF element_type [NOT NULL]
INDEX BY [PLS_INTEGER | BINARY_INTEGER | VARCHAR2(size_limit)];
INDEX BY key_type;
var_type type_assoc_arr;

Параметры или аргументы

type_assoc_arr – имя типа Associative Arrays
element_type - любой тип данных PL/SQL, за исключением REF CURSOR
key_type тип индекса, может быть числовым: PLS_INTEGER или BINARY_INTEGER, это может быть также VARCHAR2 или один из его подтипов VARCHAR, STRING или LONG.
var_type - имя переменной типа Associative Arrays

Примечание

Типы RAW, LONG RAW, ROWID, CHAR и CHARACTER не разрешены в качестве ключей для Associative Arrays.
Associative Arrays могут хранить данные с использованием значения первичного ключа в качестве индекса, где значения ключа не являются последовательными.
Когда вы ссылаетесь на элемент Associative Arrays, который использует ключ на основе VARCHAR2, вы можете использовать другие типы, такие как DATE или TIMESTAMP, если они могут быть преобразованы в VARCHAR2 с помощью функции TO_CHAR.
Не используйте TO_CHAR (SYSDATE) в качестве ключа.

Примеры

Рассмотрим некоторые примеры чтобы понять как работать с ассоциативным массивом в Oracle PL/SQL.

Associative Arrays, индексированный VARCHAR2

DECLARE
  -- Ассоциативный массив, индексированный строкой:
  
  TYPE population IS TABLE OF NUMBER  -- Тип ассоциативного массива
    INDEX BY VARCHAR2(64);            --  индексированный по строке
  
  city_population  population;        -- переменная ассоциативного массива
  i  VARCHAR2(64);                    -- Скалярная переменная
  
BEGIN
  -- Добавление элементов (пары ключ-значение) в ассоциативный массив: 
  city_population('Деревня')   := 2000;
  city_population('Райцентр')  := 750000;
  city_population('Мегаполис') := 1000000;
 
-- Изменим значение, связанное с ключом «Деревня»: 
  city_population('Деревня')  := 2001;
 
  -- Печатать ассоциативный массив:
 
  i := city_population.FIRST;  -- Получим первый элемент массива
 
  WHILE i IS NOT NULL LOOP
    DBMS_Output.PUT_LINE
      ('Население ' || i || ' равно ' || city_population(i)||' жителей');
    i := city_population.NEXT(i);  -- Получим следующий элемент массива
  END LOOP;
END;

В результате получим:
Население Деревня равно 2001 жителей
Население Мегаполис равно 1000000 жителей
Население Райцентр равно 750000 жителей

DECLARE

-- Ассоциативный массив, индексированный строкой:

TYPE population IS TABLE OF NUMBER -- Тип ассоциативного массива

INDEX BY VARCHAR2(64); -- индексированный по строке

city_population population; -- переменная ассоциативного массива

i VARCHAR2(64); -- Скалярная переменная

BEGIN

-- Добавление элементов (пары ключ-значение) в ассоциативный массив:

city_population('Деревня') := 2000;

city_population('Райцентр') := 750000;

city_population('Мегаполис') := 1000000;

-- Изменим значение, связанное с ключом «Деревня»:

city_population('Деревня') := 2001;

-- Печатать ассоциативный массив:

i := city_population.FIRST; -- Получим первый элемент массива

WHILE i IS NOT NULL LOOP

DBMS_Output.PUT_LINE

('Население ' || i || ' равно ' || city_population(i)||' жителей');

i := city_population.NEXT(i); -- Получим следующий элемент массива

END LOOP;

END;

В результате получим:

Население Деревня равно 2001 жителей

Население Мегаполис равно 1000000 жителей

Население Райцентр равно 750000 жителей

В этом примере мы определили тип ассоциативного массива population, индексированного строкой, объявили переменную этого типа city_population, заполнили переменную тремя элементами, изменили значение одного элемента и напечатали значения (в порядке сортировки, а не в порядке создания). (FIRST и NEXT - методы коллекций).

Associative Arrays, индексированный целым числом

В этом примере определяется тип ассоциативного массива, индексированный PLS_INTEGER, и функция возвращает ассоциативный массив.

DECLARE
  TYPE sum_multiples IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
  n  PLS_INTEGER := 5;   -- number of multiples to sum for display
  sn PLS_INTEGER := 10;  -- number of multiples to sum
  m  PLS_INTEGER := 3;   -- multiple

  FUNCTION get_sum_multiples (
    multiple IN PLS_INTEGER,
    num      IN PLS_INTEGER
  ) RETURN sum_multiples
  IS
    s sum_multiples;
  BEGIN
    FOR i IN 1..num LOOP
      s(i) := multiple * ((i * (i + 1)) / 2);  -- sum of multiples
    END LOOP;
    RETURN s;
  END get_sum_multiples;

BEGIN
  DBMS_OUTPUT.PUT_LINE (
    'Sum of the first ' || TO_CHAR(n) || ' multiples of ' ||
    TO_CHAR(m) || ' is ' || TO_CHAR(get_sum_multiples (m, sn)(n))
  );
END;

В результате получим сумму первых пяти чисел кратных 3:
Sum of the first 5 multiples of 3 is 45

DECLARE

TYPE sum_multiples IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;

n PLS_INTEGER := 5; -- number of multiples to sum for display

sn PLS_INTEGER := 10; -- number of multiples to sum

m PLS_INTEGER := 3; -- multiple

FUNCTION get_sum_multiples (

multiple IN PLS_INTEGER,

num IN PLS_INTEGER

) RETURN sum_multiples

s sum_multiples;

BEGIN

FOR i IN 1..num LOOP

s(i) := multiple * ((i * (i + 1)) / 2); -- sum of multiples

END LOOP;

RETURN s;

END get_sum_multiples;

BEGIN

DBMS_OUTPUT.PUT_LINE (

'Sum of the first ' || TO_CHAR(n) || ' multiples of ' ||

TO_CHAR(m) || ' is ' || TO_CHAR(get_sum_multiples (m, sn)(n))

);

END;

В результате получим сумму первых пяти чисел кратных 3:

Sum of the first 5 multiples of 3 is 45

Associative Arrays подходит для:

Относительно небольшая таблица поиска, которая может быть построена в памяти каждый раз, когда вы вызываете подпрограмму или инициализируете пакет, который ее объявляет.
Передача коллекций на сервер базы данных и обратно.

Заметка:

Вы не можете объявить тип Associative Arrays на уровне схемы. Поэтому, чтобы передать переменную Associative Arrays в качестве параметра в отдельную сохраненную подпрограмму, вы должны объявить тип этой переменной в спецификации пакета. Это делает тип доступным для вызываемой подпрограммы (которая объявляет формальный параметр этого типа) и вызывающей подпрограммы или анонимного блока (который объявляет и передает переменную этого типа)
Например.

Передача ассоциативного массива в автономную подпрограмму

--создаем тип ассоциативного массива aa_type в пакете aa_pkg
CREATE OR REPLACE PACKAGE aa_pkg IS
  TYPE aa_type IS TABLE OF INTEGER INDEX BY VARCHAR2(15);
END;
--создаем процедуру с параметром aa_pkg.aa_type
CREATE OR REPLACE PROCEDURE print_aa (
  aa aa_pkg.aa_type
) IS
  i  VARCHAR2(15);
BEGIN
  i := aa.FIRST;
 
  WHILE i IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE (aa(i) || '  ' || i);
    i := aa.NEXT(i);
  END LOOP;
END;
--выполняем
DECLARE
  aa_var  aa_pkg.aa_type;
BEGIN
  aa_var('zero') := 0;
  aa_var('one') := 1;
  aa_var('two') := 2;
  print_aa(aa_var);
END;
Результат:
1  one
2  two
0  zero

--создаем тип ассоциативного массива aa_type в пакете aa_pkg

CREATE OR REPLACE PACKAGE aa_pkg IS

TYPE aa_type IS TABLE OF INTEGER INDEX BY VARCHAR2(15);

END;

--создаем процедуру с параметром aa_pkg.aa_type

CREATE OR REPLACE PROCEDURE print_aa (

aa aa_pkg.aa_type

) IS

i VARCHAR2(15);

BEGIN

i := aa.FIRST;

WHILE i IS NOT NULL LOOP

DBMS_OUTPUT.PUT_LINE (aa(i) || ' ' || i);

i := aa.NEXT(i);

END LOOP;

END;

--выполняем

DECLARE

aa_var aa_pkg.aa_type;

BEGIN

aa_var('zero') := 0;

aa_var('one') := 1;

aa_var('two') := 2;

print_aa(aa_var);

END;

Результат:

1 one

2 two

0 zero

Итог

Самый эффективный способ передачи коллекций на сервер базы данных и обратно - использовать Associative Arrays с оператором FORALL или BULK COLLECT.
Associative Arrays предназначен для временного хранения данных. Чтобы сделать Associative Arrays постоянным для жизни сеанса базы данных, объявите его в спецификации пакета и заполните его в теле пакета.