Джеймс Коупман
( James Koopmann)

Работа с VARRAY в Oracle

 

Источник: Журнал DATABASE JOURNAL,
http://www.databasejournal.com/features/oracle/article.php/3322591,
http://www.databasejournal.com/features/oracle/article.php/3322821

Первая и вторая из трех статей серии о создании VARRAY и работе с ними в таблицах Oracle.

(Статья I)

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

  1. При использовании повторяющихся полей (объектов) в таблице, не надо создавать новую таблицу со своей собственной структурой, индексами и т.п.
  2. Нет необходимости создавать отношения между таблицами для создания набора согласующихся полей, т.к. данные уже находятся в одной таблице.
  3. Абстрактный тип данных (объект) может использоваться различными таблицами или объектными таблицами, что принуждает разработчиков придерживаться стандарта для столбцов абстрактного объекта.

Абстрактный тип данных

Если коротко, то абстрактный тип данных - это объектный тип, группирующий поля в единое целое. В нашем примере создается объект, спроектированный для описания заправок бензином в автопарковом хозяйстве. Запись содержит количество галлонов перекаченного бензина, дату записи и название автозаправки. Конечно, можно добавить и другие поля, но я надеюсь, что простейшего случая будет достаточно. Позднее данный объект будет использоваться при определении связи записей с данными о транспортных средствах. Здесь следует заметить, что данный объект может быть использован и другой таблицей. Например, наш парк машин также, как правило, заправляется на нашей же автозаправке. Т.е. мы можем использовать этот объект и добавить его в таблицу STATION_FILL_SCHEDULE, чтобы различать количество галлонов, заправленных в бензобаки, и количество бензина на заправке. И это хорошо, так как мы повторно используем тип и обобщаем определение полей. Для того, чтобы создать новый тип, следует выполнить следующее DDL-выражение: 

CREATE TYPE GAS_LOG_TY AS OBJECT (
 GALLONS NUMBER,
 FILLUP_DATE DATE,
 GAS_STATION VARCHAR2(255));

Массив

Объекты на основе типа GAS_LOG_TY могут создаваться непосредственно, а могут быть добавлены в таблицу. Мы хотим фиксировать последние 100 заправок автомобилей на нашей станции. Это реализуется путем создания массива, который будет содержать 100 объектов, соответствующих фактам заправок. Вот DDL для создания массива:

CREATE TYPE GAS_LOG_VA AS VARRAY(100) OF GAS_LOG_TY;

Таблица

Теперь у нас есть журнал расхода в виде массива, и осталось выполнить только DDL. Мы ведем учет по ID транспортного средства. Отметим, что столбец GAS_LOG описывается типом, который мы только что описали - GAS_LOG_VA. 

CREATE TABLE GAS_LOG
 (VIN NUMBER NOT NULL,
 GAS_LOG GAS_LOG_VA);

Описание структуры

Те, кто знаком с командой DESCRIBE в SQL*Plus, может получить полное описание созданной таблицы, не затратив много усилий. Следуйте последовательности, изображенной на Листинге 1.

  1. Сначала описывается таблица GAS_LOG, в которой имеется поле типа GAS_LOG_VA
  2. Затем можно получить описание типа GAS_LOG_VA, чтобы увидеть, что он действительно может содержать VARRAY-массив из 100 объектов GAS_LOG_TY.
  3. Описание объекта GAS_LOG_TY не предоставляет дополнительной информации в данном примере, но зато показывает полное описание всех объектов.

Листинг 1. Описание абстрактных объектов 

SQL> DESCRIBE gas_log
 Name                     Null?                 Type
 ----------------------------------------- -------- ------------------
 VIN                NOT NULL                   NUMBER
 GAS_LOG GAS_LOG_VA
 
SQL> DESCRIBE GAS_LOG_VA
 GAS_LOG_VA VARRAY(100) OF GAS_LOG_TY
 Name Null? Type
 ----------------------------------------- -------- ------------------
 GALLONS NUMBER
 FILLUP_DATE DATE
 GAS_STATION VARCHAR2(255)
 
SQL> DESCRIBE GAS_LOG_TY
 Name Null? Type
 ----------------------------------------- -------- ------------------
 GALLONS NUMBER
 FILLUP_DATE DATE
 GAS_STATION VARCHAR2(255)

Как удалить структуру

Если необходимо удалить созданную структуру данных, удаление следует начинать в порядке, обратном созданию. Если попытаться удалить сначала описание типа, то появится сообщение об ошибке ORA-02303, которое говорит о том, что нельзя удалить или изменить тип, если он используется в таблицах. Правильный порядок удаления такой: 

DROP TABLE GAS_LOG;
DROP TYPE GAS_LOG_VA;
DROP TYPE GAS_LOG_TY;

Вставка данных

Вставка элемента в VARRAY-массив

Чтобы вставить данные в таблицу GAS_LOG и заполнить массив данными, надо создать все необходимые объекты типа GAS_LOG_TY и заполнить ими поле GAS_LOG. Следующие два примера вставляют по одной строке в таблицу и один набор значений в поле GAS_LOG. Отметим, что ID транспортных средств разные и у нас в таблице получилось две различные строки. 

SQL> insert into gas_log values (101010101010101,gas_log_va(gas_log_ty(32,sysdate-1,'Shell')));
1 row created.
 
SQL> insert into gas_log values (222222222222222,gas_log_va(gas_log_ty(27,sysdate-1,'Texaco')));
1 row created.

Вставка нескольких элементов в VARRAY-массив

Мы создали единичный набор данных для VARRAY, теперь можно выполнить более сложную вставку с большим количеством элементов. Рассмотрим SQL-выражение, добавляющее строку в таблицу GAS_LOG. Строка содержит шесть различных записей о заправках, которые заполняют VARRAY-массив. Заметьте, что для добавления значения в VARRAY-объект GAS_LOG, надо будет полностью повторить SQL-выражение, чтобы добавить к нему седьмую запись. 

SQL> insert into gas_log values (321321321321321,gas_log_va(
gas_log_ty(45,sysdate-10,'Diamond Shamrock'),
gas_log_ty(31,sysdate-9,'Shell'),
gas_log_ty(32,sysdate-8,'Shell'),
gas_log_ty(33,sysdate-7,'Texaco'),
gas_log_ty(34,sysdate-6,'Texaco'),
gas_log_ty(35,sysdate-5,'Diamond Shamrock')));
 
1 row created.

Выборка данных

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

SQL> col gas_log for a50
SQL> select * from gas_log;
 
 VIN GAS_LOG(GALLONS, FILLUP_DATE, GAS_STATION)
----------------- --------------------------------------------------
 101010101010101 GAS_LOG_VA(GAS_LOG_TY(32, '19-FEB-04', 'Shell'))
 222222222222222 GAS_LOG_VA(GAS_LOG_TY(27, '19-FEB-04', 'Texaco'))
 321321321321321 GAS_LOG_VA(GAS_LOG_TY(45, '10-FEB-04', 'Diamond Sh
 amrock'), GAS_LOG_TY(31, '11-FEB-04', 'Shell'), GA
 S_LOG_TY(32, '12-FEB-04', 'Shell'), GAS_LOG_TY(33,
 '13-FEB-04', 'Texaco'), GAS_LOG_TY(34, '14-FEB-04
 ', 'Texaco'), GAS_LOG_TY(35, '15-FEB-04', 'Diamond
 Shamrock'))

После более тщательного форматирования и использования функции TABLE для столбца GAS_LOG, результат становится более привычным. Обратите внимание, что отобрано восемь строк, несмотря на то, что фактически выбирались только три строки из таблицы GAS_LOG. 

SQL> col vin for 9999999999999999
SQL> col gas_station for a40
SQL> set linesize 132
SQL> select a.vin,var.* from gas_log a, table(gas_log) var;
 
 VIN GALLONS FILLUP_DA GAS_STATION
----------------- ---------- --------- ----------------------------
 101010101010101 32 19-FEB-04 Shell
 222222222222222 27 19-FEB-04 Texaco
 321321321321321 45 10-FEB-04 Diamond Shamrock
 321321321321321 31 11-FEB-04 Shell
 321321321321321 32 12-FEB-04 Shell
 321321321321321 33 13-FEB-04 Texaco
 321321321321321 34 14-FEB-04 Texaco
 321321321321321 35 15-FEB-04 Diamond Shamrock
 
8 rows selected.

Следующее SQL-выражение приведено, чтобы еще раз отобразить данные, которые выбираются при использовании функции TABLE, и показать, как ссылаться на поля в VARRAY-массиве GAS_LOG. 

SQL> select a.vin,var.gallons,var.fillup_date,var.gas_station
 2 from gas_log a, table(gas_log) var;
 
 VIN GALLONS FILLUP_DA GAS_STATION
----------------- ---------- --------- -------------------
 101010101010101 32 19-FEB-04 Shell
 222222222222222 27 19-FEB-04 Texaco
 321321321321321 45 10-FEB-04 Diamond Shamrock
 321321321321321 31 11-FEB-04 Shell
 321321321321321 32 12-FEB-04 Shell
 321321321321321 33 13-FEB-04 Texaco
 321321321321321 34 14-FEB-04 Texaco
 321321321321321 35 15-FEB-04 Diamond Shamrock
 
8 rows selected.

Хотя не все будут согласны с тем, что создание и использование VARRAY-массивов в таблицах - это лучшее, что можно сделать, я считаю, что в некоторых случаях они архитектурно весьма полезны. В следующих двух частях данной серии, я надеюсь, что смогу их переубедить, и мы рассмотрим, как использовать массивы и какую выгоду это этого можно получить. Тем временем создайте объекты и поупражняйтесь с ними, чтобы немного привыкнуть. В следующий раз создадим еще несколько объектов и код, которые улучшат жизнь благодаря их использованию.

(Статья II)

Данная статья – вторая в серии из трех частей, в которой завершится процесс создания VARRAY-массивов и будет показано, как скрыть детали и сложность структуры от конечных пользователей.

В части 1 этой серии, мы узнали, как создавать абстрактные типы данных и определять массив в виде поля таблицы. Механизм вставки и извлечения данных из этой структуры не слишком сложен, но требует несколько больше усилий и, без сомнения, выглядит несколько запутанным для человека, работающего со структурами обычных таблиц Oracle и обычным DML. В этой статье рассмотрим, как уйти от сложностей в использовании VARRAY, чтобы разработчики или конечные пользователи могли взаимодействовать с этими структурами путем обычных табличных операций insert и select.

Вставка данных

Вставка одного элемента в массив VARRAY

Как было показано в первой части серии, чтобы вставить данные в таблицу GAS_LOG и заполнить массив данными, надо иметь объектный тип, соответствующий факту заправки (GAS_LOG_TY), необходимый при заполнении столбца GAS_LOG. Вот пример вставки в массив VARRAY из первой части. Если вернуться назад, можно увидеть, что несколько значений добавить в VARRAY-массив гораздо сложнее. 

SQL> insert into gas_log values (101010101010101,gas_log_va(gas_log_ty(32,sysdate-1,'Shell')));
1 row created.

Упрощаем вставку

Поскольку при вставке нельзя ссылаться на отдельный элемент VARRAY, а только на массив целиком, то добавление одного значения становится несколько более трудным. Чтобы добавить значение в массив, требуется одновременно вставлять все значения, которые там есть, вместе с новым. Затем, чтобы разработчикам было проще, создадим процедуру, обеспечивающую манипуляции с массивом, например, для вставки нового значения. Рисунок 1 содержит необходимый код.

Рис 1. Динамический метод для вставки в VARRAY 

CREATE OR REPLACE PROCEDURE gas_log_insert
( in_vin IN NUMBER,
 in_gallons IN NUMBER,
 in_fillup_date IN DATE,
 in_gas_station IN VARCHAR2) AS
pr_gas_log_va gas_log_va := gas_log_va();
BEGIN
EXECUTE IMMEDIATE
'SELECT gas_log FROM gas_log WHERE vin = :1 FOR UPDATE OF gas_log'
 INTO pr_gas_log_va USING in_vin;
pr_gas_log_va.EXTEND;
pr_gas_log_va(pr_gas_log_va.LAST) := gas_log_ty(in_gallons,in_fillup_date,in_gas_station);
EXECUTE IMMEDIATE
'UPDATE gas_log SET gas_log = :1 WHERE vin = :2'
 USING pr_gas_log_va, in_vin;
EXCEPTION
 WHEN NO_DATA_FOUND THEN
 EXECUTE IMMEDIATE
 'INSERT INTO gas_log
 VALUES (:1,gas_log_va(gas_log_ty(:2,:3,:4)))'
 USING in_vin,in_gallons,in_fillup_date,in_gas_station;
END gas_log_insert;
/

Теперь, если потребуется вставить запись в VARRAY-массив, потребуется выполнить только следующее простое выражение. Это стандартный способ выполнения процедур в Oracle и большинство программистов используют его. В то же время, это не просто INSERT. В конце статьи будут выполняться и простые INSERT-ы, знакомые всем. Однако, данная процедура – весьма важный шаг к простому механизму и, конечно же, может использоваться разработчиками.

Вставка с использованием процедуры gas_log_insert

SQL> exec gas_log_insert(101010101010101,22,sysdate,'Sinclare');

Упрощаем выборку данных

В первой части можно было заметить сложность выборки данных из VARRAY-массива. Если выполнять select непосредственно из таблицы, то информация отображается в плохо читаемом виде. Используя в SELECT-е функцию TABLE, данные извлекаются в более удобном для чтения виде, но тогда требуется умение использовать функции в обычных SQL-выражениях, что может вызывать проблемы у начинающих разработчиков. На самом деле хотелось бы выполнять известное всем выражение SELECT. Для этого необходимо использовать табличную функцию, обеспечивающую представление данных, извлекаемых обычным SELECT-ом.

Еще один абстрактный объект

Создадим собственный объектный тип GAS_LOG2_TY, затем – таблицу объектов типа GAS_LOG2_TY, назовем ее GAS_LOG_TBL_TY. Таблица GAS_LOG_TBL_TY будет использоваться в простом запросе из табличной функции. 

CREATE TYPE GAS_LOG2_TY AS OBJECT
 (VIN NUMBER,
 GALLONS NUMBER,
 FILLUP_DATE DATE,
 GAS_STATION VARCHAR2(255));
/
CREATE TYPE
 GAS_LOG_TBL_TY AS TABLE OF GAS_LOG2_TY;
/

Функция

На рисунке 2 приведено несколько PL/SQL-выражений. Они не представляют особого практического интереса, просто обратите внимание, что SQL-выражение, которое использовалось в части 1, теперь находится внутри функции.

Рис 2. Функция для выборки данных 

CREATE OR REPLACE FUNCTION GAS_LOG_FN
 RETURN GAS_LOG_TBL_TY PIPELINED IS
 PRAGMA AUTONOMOUS_TRANSACTION;

TYPE ref0 IS REF CURSOR;

cur0 ref0;

out_rec gas_log2_ty

 := gas_log2_ty(NULL,NULL,NULL,NULL);

BEGIN

OPEN cur0 FOR

'select a.vin,var.gallons,var.fillup_date,var.gas_station '||

' from gas_log a, table(gas_log) var ';

LOOP

 FETCH cur0 INTO out_rec.vin, out_rec.gallons, out_rec.fillup_date, out_rec.gas_station;

 EXIT WHEN cur0%NOTFOUND;

 PIPE ROW(out_rec);

END LOOP;

CLOSE cur0;

RETURN;

END GAS_LOG_FN;

/

Для упрощения SQL, использующего select, необходимо построить представление на основе это функции. Рис. 3 содержит код для создания представления, заметьте – функция TABLE вызывает функцию GAS_LOG_FN.

Рис 3. Представление для табличной функции 

CREATE OR REPLACE VIEW GAS_LOG_VW AS
 SELECT a.vin, a.gallons, a.fillup_date, a.gas_station
 FROM TABLE(GAS_LOG_FN) a
/

Теперь мы просто можем выполнить select из данного представления. 

SQL> select * from gas_log_vw where vin = 101010101010101;
 
 VIN GALLONS FILLUP_DA GAS_STATION
----------------- ---------- --------- ------------
 101010101010101 32 19-FEB-04 Shell
 101010101010101 22 20-FEB-04 Sinclare
 101010101010101 55 20-FEB-04 Texaco

Обещанный упрощенный INSERT

Теперь можно выполнить простой SQL-оператор INSERT. Надо обратить внимание на то, что табличная функция не может использоваться в DML-операциях, таких как INSERT, UPDATE или DELETE. Чтобы обойти это, создадим триггер INSTEAD OF INSERT на только что созданное представление GAS_LOG_VW. Рис. 4 содержит DDL для триггера.

Рис 4. INSTEAD OF TRIGGER для вставки данных 

CREATE OR REPLACE TRIGGER GAS_LOG_TRIGGER
INSTEAD OF INSERT ON GAS_LOG_VW
FOR EACH ROW
BEGIN
gas_log_insert(:new.vin,:new.gallons,:new.fillup_date,:new.gas_station);
END;
/

Вот теперь для добавления значений в VARRAY-массив можно выполнить обычный INSERT. 

SQL> INSERT INTO gas_log_vw
 VALUES (101010101010101,55,sysdate,'Texaco');

Итак, в данной статье приведены инструменты и продемонстрированы методы для упрощения работы с VARRAY-массивами в таблицах. Теперь можно выполнять все типы DML-операций с массивами, как с обычными таблицами. Счастье на лицах разработчиков и конечных пользователей, безусловно, перевесит те несколько строк кода, которые только что были созданы. Помните, что ваша задача - использовать преимущества нововведений Oracle, и в то же время они должны быть достаточно просты, чтобы все могли ими воспользоваться или, как нашем случае, даже не догадываться об их существовании. В следующий раз мы рассмотрим вопросы производительности при использовании VARRAY. Оставайтесь с нами.