Стивен Ферстайн, член Oracle Ace Лучшие приемы применения PL/SQL в Oracle Database 11g и многоуровневые коллекции, индексируемые по строковым значениям.
Источник: журнал Oracle Magazine, http://www.oracle.com/technology/oramag/oracle/07-nov/o67plsql.html
Стивен Ферстайн - Steven Feuerstein (steven.feuerstein@quest.com) - провозгласитель (evangelist) PL/SQL из компании Quest Software. Он опубликовал 10 книг по языкам программирования Oracle, включая "Oracle PL/SQL Programming and Oracle PL/SQL Best Practices" (O’Reilly Media). Его миссия (как Стивен Ферстайн сам говорит о себе) состоит в повышении качества и количества тестирования PL/SQL-программ.
Как-то мне пришлось разбираться с большим фрагментом макаронного кода (spaghetti code - неструктурная программа - www.multitran.ru/ - скорее всего, автор образно хочет сравнить этот код с клубком спутанных макарон - А.Бачин), доработать и, в частности, внести изменения в очень сложный для понимания цикл. Я счел, что нужно сделать "хирургическую операцию" ("surgical strike") - дописать в нужное место новую обработку, а затем с минимальными хлопотами пропустить остаток логики в теле цикла. Как это было сделать лучше всего?
Разве не мучительно вносить изменения в существующую программу, которая напичкана изощренной логикой ( "макаронный код")? И разве не страшно вносить эти изменения без тестовой программы, которую можно запустить позднее, чтобы убедиться в отсутствии непреднамеренно привнесенных багов?
Что ж, покажем то, что часто называется "делать [все равно] придется".
Когда сталкиваешься с подобной ситуацией, хочется сделать самое маленькое, как можно более изолированное изменение, чтобы таким образом минимизировать эффект воздействия.
Все это примерно так, как в ситуации со следующим текстом:
и вам надо добавить немного кода между "здесь сложная для понимания логика" и "здесь много того же самого". Если заданные условия удовлетворены, надо выполнить некий новый код, а затем пропустить остаток тела цикла и перейти к следующей итерации.
BEGIN
... много-много запутанного кода
FOR index IN 1 .. my_collection.COUNT
LOOP
... здесь сложная для понимания логика
... здесь много того же самого
END LOOP;
END;
PROCEDURE someone_elses_mess
/*
// Автор: Надолго-сбежавший консультант
// Создан: Ужасный сотрудник
*/
IS
Это можно сделать несколькими способами:
* Добавить оператор IF
* Использовать оператор GOTO
* В Oracle Database 11g воспользоваться новым оператором CONTINUE
Вот как тело цикла будет выглядеть с оператором IF:
LOOP
... здесь сложная для понимания логика
IF новое_условие
THEN
... здесь новый код
CONTINUE;
END IF;
... здесь много того же самого
END LOOP;
END;
BEGIN
... много-много запутанного кода
FOR index IN 1 .. my_collection.COUNT
LOOP
... здесь сложная для понимания логика
IF новое_условие
THEN
... здесь новый код
ELSE
... здесь много того же самого
END IF;
END LOOP;
END;
Теперь, на примере этого кода, все три метода выглядят разумно и работа получается сделанной. Однако, если вы боретесь с экстремально сложной, изощренной программой, то оператор IF становится сложным. Необходимо убедиться, что выражение ELSE поставлено в нужном месте и обрамляет корректную логику. Это означает, что надо найти оператор END LOOP для этого цикла, который будет на сотни строк ниже в программе, с множеством других END LOOP-ов посередине.
GOTO позволяет просто перейти в конец цикла, однако, сначала опять надо найти конец этого цикла и затем добавить метку и заглушку "NULL", чтобы GOTO мог хоть на что-нибудь перейти.
Но лучшее решение этой проблемы-доступное после перехода на Oracle Database 11g-просто вызвать PL/SQL-процедуру, с которой необходимо продолжить выполнение в цикле, пропустив оставшуюся часть тела этой итерации. Ясно, просто и объяснимо.
И, между прочим, как и с оператором EXIT, с CONTINUE можно использовать выражение WHEN и также указать метку END (полезно для вложенных циклов).
Вот пример CONTINUE WHEN:
Вот метод с GOTO:
BEGIN
... много-много запутанного кода
FOR index IN 1 .. my_collection.COUNT
LOOP
... здесь сложная для понимания логика
IF новое_условие
THEN
... здесь новый код
GOTO конец_цикла;
END IF;
... здесь много того же самого
<<конец_цикла>>
NULL; -- Заглушка
END LOOP;
END;
BEGIN
... много-много запутанного кода
FOR index IN 1 .. my_collection.COUNT
LOOP
... здесь сложная для понимания логика
/* Я хочу выполнить эту новую логику
и затем "пропуск". */
... здесь новый код
CONTINUE WHEN новое_условие;
... здесь много того же самого
END LOOP;
END;
И, наконец, метод с CONTINUE (новой возможностью, появившейся в Oracle Database 11g):
BEGIN
... много-много запутанного кода
FOR index IN 1 .. my_collection.COUNT
Следующий блок показывает, как пропустить не только остаток от внутреннего цикла, но также и внешний цикл с помощью метки в CONTINUE:
SQL> PACKAGE wora_manager
2 IS
3 c_folk CONSTANT CHAR (4) := ‘FOLK’;
4 c_rock CONSTANT CHAR (4) := ‘ROCK’;
5
6 PROCEDURE reset_counts;
7
8 PROCEDURE song_requested (title_in IN wora_songs.title%TYPE);
9
10 PROCEDURE song_played (title_in IN wora_songs.title%TYPE);
11
12 FUNCTION song_requested_count (title_in
13 IN wora_songs.title%TYPE)
14 RETURN PLS_INTEGER;
15
16 FUNCTION folk_requested_count
17 RETURN PLS_INTEGER;
18 END wora_manager;
BEGIN
<<outer_loop >>
FOR o_index IN 1 .. my_collection.COUNT
LOOP
<<inner_loop>>
FOR i_index
IN your_collection.FIRST ..
your_collection.LAST
LOOP
... много кода
/* Пропускаем остаток этого и внешнего цикла
если условие верно */
CONTINUE outer_loop
WHEN условие_верно;
... остальная логика внутреннего цикла
END LOOP inner_loop;
... остальная логика внешнего цикла
END LOOP outer_loop;
END;
Как контролировать песни?
Я работаю на радиостанции (назовём ее WORA- название не настоящее), и мне нужно написать программу, которая отслеживает, сколько раз запрашивалась и проигрывалась песня в заданном интервале времени, а также следит за количеством песен в каждой из двух категорий: фольклор или рок (упрощая задачу). Хотя список доступных песен хранится в таблице базы данных, такая информация в ней не хранится; она активна только в текущей сессии. Я вижу, как написать программу с помощью создания нескольких таблиц базы данных и написания кучки SQL, однако сомневаюсь, что это может оказаться легкой задачей.
Существует действительно более легкий способ: использовать многоуровневые, индексируемые по строкам (string-indexed) коллекции!
Хотя синтаксис многоуровневых, индексируемых по строкам коллекций может вначале показаться немного сложноватым, вы увидите, что как только рука наберет скорость, а вы будете внимательны в том, как определять эти структуры, они сделают вашу жизнь очень и очень лёгкой.
Итак, при создании кода, описанного в вопросе, следует начать с описания требуемой функциональности в спецификации пакета.
Листинг 1, например, это спецификация, которая, полагаю, соответствует требованиям (я думаю, она несколько упрощена, чем на настоящей радиостанции, и основана на таблице wora_songs).
Что представлено в спецификации пакета wora_manager Листинга 1:
• Строки 3 и 4: Объявляются константы, чтобы предотвратить неоднократное применение констант в коде.
• Строка 6: Программа для сброса счетчика при необходимости.
• Строки 8-10: Процедуры song_requested и song_played фиксируют каждый факт запроса или проигрывания песни (по названию).
• Строки 12-17: Позволяют извлечь количество запросов каждой песни из категории фольклорной музыки.
Да, я знаю - нужны дополнительные функции для рока и "количество проигранных песен", однако объем этой статьи ограничен, поэтому их вам надо будет добавить самим. Кроме того, я не буду включать в эту статью код, который не требуется для демонстрации использования коллекций, а именно, вставки в таблицу wora_songs, и функцию для поиска категории по названию песни. Однако вы можете посмотреть весь этот код, если скачаете файл http://otn.oracle.com/oramag/oracle/07-nov/o67plsql.zip.
Листинг 2 показывает пример использования пакета wora_manager.
Листинг 2: Использование пакета the wora_manager
SQL> BEGIN
2 wora_manager.song_requested (
3 ‘If I were a rich man’);
4
5 wora_manager.song_requested (‘Peace train’);
6
7 DBMS_OUTPUT.PUT_LINE (
8 wora_manager.song_requested_count (
9 ‘If I were a rich man’)
10 );
11 wora_manager.song_played (‘Peace train’);
12 END;
SQL> PROCEDURE song_requested (title_in IN VARCHAR2)
2 IS
3 l_category wora_songs.CATEGORY%TYPE :=
4 category_for_title (title_in);
5 BEGIN
6 g_song_data (l_category) (title_in).requested_count :=
7 g_song_data (l_category) (title_in).requested_count + 1;
8 EXCEPTION
9 WHEN NO_DATA_FOUND
10 THEN
11 g_song_data (l_category) (title_in).requested_count := 1;
12 END song_requested;
Когда спецификация пакета определена и скомпилирована, можно создавать тесты этих программ-еще до написания тела пакета. Вместо тестов перейдем к его реализации. Итак, вот данные, которые необходимо отслеживать:
• Сколько песен было запрошено/проиграно в категории
• Сколько раз была запрошена/проиграна песня
Есть много способов использования структур данных для хранения этих данных. Листинг 3 показывает метод, который применен в теле пакета wora_manager.
Листинг 3: Тело пакета wora_manager
В процедуре song_requested, входной параметр это название песни, что позволяет вызвать функцию для извлечения категории для этого названия. Эти два значения (категория и название) являются индексными значениями для коллекции коллекций g_song_data.
Как видно из строки 6 Листинга 4, для идентификации записи, которая содержит количественную информацию для песни, используется следующий синтаксис:
g_song_data (l_category) (title_in)
Другими словами, g_song_data (l_ category) указывает на элемент в коллекции коллекций для этой категории (в этой "внешней" коллекции как минимум два элемента, так как я работаю только с песнями фольклора и рока). Затем переходим к счетчикам соответствующей песни, используя ее название как индекс во "внутренней" коллекции.
Затем инкрементируется requested_count на 1. Если песня запрашивается первый раз, Oracle Database сгенерирует исключение NO_DATA_FOUND, из-за попытки обратиться к элементу по индексу, который не был определен. Поэтому это исключение необходимо обработать и установить количество равным 1.
Чтобы эти действия стали более понятными, рассмотрим анонимный блок Листинга 5.
Листинг 5: Анонимный блок
SQL> CREATE OR REPLACE PACKAGE BODY wora_manager
2 IS
3 TYPE counts_rt IS RECORD (
4 requested_count PLS_INTEGER
5 , played_count PLS_INTEGER
6 );
7
8 TYPE song_counts_tt IS TABLE OF counts_rt
9 INDEX BY wora_songs.title%TYPE;
10
11 TYPE by_category_tt IS TABLE OF song_counts_tt
12 INDEX BY wora_songs.CATEGORY%TYPE;
13
14 g_song_data by_category_tt;
Вот основные строки в теле пакета wora_manager (Листинг 3):
• Строки 3-6: объявляется тип-запись для хранения двух видов счетчиков-запрошено и проиграно.
• Строки 8 и 9: тип коллекции (PL/SQL-версия массива), в которой каждый элемент коллекции - это одна из таких записей, а индекс коллекции - это строки-заголовки песен.
• Строки 11-13: объявляется тип коллекции, в котором каждый элемент коллекции - это другая коллекция, со счетчиками песен, и индекс в коллекции - это строка-категория (фольклор или рок).
• Строка 14: переменная типа "коллекция коллекций". Эта переменная будет хранить всю информацию со счетчиками.
На Листинге 4 показана реализация процедуры song_requested.
Листинг 4: Процедура Song_requested
SQL> DECLARE
2 l_songs_in_category song_counts_tt;
3 l_song_counts counts_rt;
4 BEGIN
5 l_songs_in_category := g_song_data (‘FOLK’);
6 l_song_counts :=
7 l_songs_in_category (If I had a rocket launcher’);
8 DBMS_OUTPUT.put_line (l_song_counts.requested_count);
9 END;
Промежуточные структуры данных пропущены. Объявлена коллекция записей со счетчиками (l_songs_in_category) и запись со счетчиками (l_song_counts). На Листинге 5, строке 5, извлекаются все счетчики для фольклорных песен. На строке 6 и 7, извлекаются счетчики (запись) для одной песни. На строке 8, отображается одно из полей записи.
Я надеюсь, что если у вас нет опыта работы с коллекциями и, частности, с многоуровневыми коллекциями, эта статья может немного помочь в их использовании. Кроме того, примите во внимание, сколько всего можно сделать, имея такой небольшой код. Я просто указываю текстовое значение (название или категорию) как значение индекса, а PL/SQL автоматически сортирует всю мою информацию и очень четко отслеживает её.
Логика для извлечения количества заданной песни также очень проста, как показано на Листинге 6.
Листинг 6: функция song_requested_count
SQL> FUNCTION song_requested_count (title_in IN VARCHAR2)
2 RETURN PLS_INTEGER
3 IS
4 l_category wora_songs.CATEGORY%TYPE :=
5 category_for_title (title_in);
6 BEGIN
7 RETURN g_song_data
8 (l_category) (title_in).requested_count;
9 EXCEPTION
10 WHEN NO_DATA_FOUND
11 THEN
12 RETURN 0;
13 END song_requested_count;