Массив переменной длины (VARRAY)

Подобно двум другим типам коллекций, массивы переменной длины являются одномерными коллекциями, состоящими из однородных элементов.

Особенности:

• Размер всегда ограничен и массив не может быть разреженным.
• Максимальное количество элементов указывается при определении типа.
• Могут использоваться как в PL\SQL так и в таблицах.
• Порядок элементов при сохранение и извлечении, в отличии от вложенных таблиц, сохраняется.
• Индекс – положительное число от 1 до 2,147,483,647
• Нельзя удалить произвольный элемент массива

Использование массива переменной длины в SQL

Создаем тип:

CREATE TYPE XX_TYPE_ORDERS IS VARRAY(5) OF VARCHAR2(100);

Создаем таблицу:

CREATE TABLE XX_ORDERS (ORDER_ID NUMBER,ORDER_NAME XX_TYPE_ORDERS);

Вставляем строки в таблицу:

INSERT INTO XX_ORDERS VALUES (1, XX_TYPE_ORDERS('Order 1','Order 2','Order 3'));
INSERT INTO XX_ORDERS VALUES (2, XX_TYPE_ORDERS('Order 21','Order 22','Order 23'));

Просматриваем данные в таблице:

SQL> select * from XX_ORDERS;
 
  ORDER_ID ORDER_NAME
---------- ----------
         1 XX_TYPE_ORDERS('Order 1','Order 2','Order 3')
         2 XX_TYPE_ORDERS('Order 21','Order 22','Order 23')

Обновляем массив данных:

SQL> UPDATE XX_ORDERS
  2  SET ORDER_NAME = XX_TYPE_ORDERS('ORDER 100','ORDER 200','ORDER 300')
  3  WHERE ORDER_ID=2
  4  /
 
1 row updated
 
SQL> select * from XX_ORDERS where order_id = 2;
 
  ORDER_ID ORDER_NAME
---------- ----------
         2 XX_TYPE_ORDERS('Order 100','Order 200','Order 300')

Использование массива переменной длины в PL\SQL

SQL> set serveroutput on size unlimited;
SQL> DECLARE
  2   TYPE  XX_VAR_TYPE IS VARRAY(100)  OF  NUMBER  ;
  3   l_array  XX_VAR_TYPE :=  XX_VAR_TYPE(100,200,300,400,500);
  4  BEGIN
  5   l_array.TRIM(3);
  6   FOR I IN 1..l_array.COUNT
  7   LOOP
  8   DBMS_OUTPUT.PUT_LINE(l_array(I));
  9   END LOOP;
 10  END;
 11  /
 
100
200
 
PL/SQL procedure successfully completed

Пример с использованием методов

DECLARE
  TYPE emplist IS VARRAY(2) OF NUMBER;
  v_employees emplist := emplist();
BEGIN
 
  dbms_output.put_line('COUNT='||v_employees.COUNT);
  dbms_output.put_line('FIRST='||v_employees.first);
  dbms_output.put_line('LAST='||v_employees.last);
 
  dbms_output.put_line(chr(10)||'Add 1');
  v_employees.EXTEND;
  v_employees(1) := 30;
  dbms_output.put_line('COUNT='||v_employees.COUNT);
  dbms_output.put_line('FIRST='||v_employees.first);
  dbms_output.put_line('LAST='||v_employees.last);
 
  dbms_output.put_line(chr(10)||'Add 2');
  v_employees.EXTEND;
  v_employees(2) := 40;
  dbms_output.put_line('COUNT='||v_employees.COUNT);
  dbms_output.put_line('FIRST='||v_employees.first);
  dbms_output.put_line('LAST='||v_employees.last);
 
  dbms_output.put_line(chr(10)||'Add 3');
  v_employees.EXTEND;
  v_employees(3) := 50;
  dbms_output.put_line('COUNT='||v_employees.COUNT);
  dbms_output.put_line('FIRST='||v_employees.first);
  dbms_output.put_line('LAST='||v_employees.last); 
 
EXCEPTION
 when others then
  dbms_output.put_line(SQLERRM);
END;
 
COUNT=0
FIRST=
LAST=
 
Add 1
COUNT=1
FIRST=1
LAST=1
 
Add 2
COUNT=2
FIRST=1
LAST=2
 
Add 3
ORA-06532: Subscript outside of limit

Источник.