/* Проверяем, не следует ли завершить цикл раньше ♦/ exit when (test value = special item); end loop;

Однако чаще условие окончания цикла встраивается прямо в маркер цикла. Тело цикла выполняется раз за разом до тех пор, пока это условие истинно. Предположим, например, что вы хотите уменьшать плановые объемы продаж для каждого офиса до тех пор, пока их сумма по всей компании не станет меньшей $2400000. План каждого офиса должен быть уменьшен на сумму, кратную $10000. Вот (не особенно эффективный) цикл из хранимой процедуры, написанной на Transact-SQL, выполняющий эту задачу:

/* Уменьшаем плановые объемы продаж до тех пор,

пока их сумма не станет меньше $2400000 */ while (select sum(target) from offices) < 2400000.00 begin

update offices

set target = target - 10000.00

Блок begin. . . end в этом цикле не обязателен, поскольку его тело и так состоит из единственной инструкции, но большинство циклов while в Transact-SQL его содержат.

А вот версия того же цикла для Oracle PL/SQL:

/* Уменьшаем плановые объемы продаж до тех пор,

пока их сумма не станет меньше $2400000 */ select sum(target) into total tgt from offices; while {total tgt < 2400000.00) loop

update offices

set target = target - 10000.00;

select sum(target) into total tgt from offices; end loop;

Как видите, вложенная в цикл while инструкция select диалекта Transact-SQL заменена инструкцией select into, в которой результат запроса присваивается локальной переменной. При каждом проходе цикла таблица offices обновляется и общая сумма вычисляется снова.

Ниже показана третья версия этого же цикла - для СУБД Informix:

/* Уменьшаем плановые объемы продаж до тех пор,

пока их сумма не станет меньше $2400000 */ Select sum(target) into total tgt from offices; while (total tgt < 2400000.00)

update offices

set target = target - 10000.00;

select sum(target) into total tgt from offices; end while;

В различных диалектах SPL используются и другие варианты создания циклов, Но их возможности и синтаксис аналогичны описанным в этих трех примерах.

Другие управляющие конструкции

Некоторые диалекты SPL включают дополнительные управляющие конструкции Например, в Informix инструкция exit прерывает нормальное выполнение цикла и передает управление инструкции, следующей непосредственно за циклом. Инструкция continue также прерывает нормальное выполнение цикла, но вызывает переход к следующей итерации цикла. У обеих инструкций имеется по три формы - для каждого из типов циклов, которые они могут прерывать:

exit for;

continue for;

exit while;

continue while;

exit foreach;

continue foreach;

В Transact-SQL единственная инструкция break заменяет все три варианта инструкции exit; инструкция continue в этом диалекте тоже только одна. В Oracle инструкция exit выполняет ту же функцию, что и в Informix, а инструкция continue отсутствует

Еще один способ изменения хода выполнения хранимых процедур - это переход по метке, выполняемый инструкцией goto. В больщинстве диалектов метка представляет собой идентификатор, за которым следует двоеточие Как правило, выход по метке за пределы цикла не допускается, как не допускается и переход внутрь цикяа или условной конструкции. Следует помнить, что, как и в классических языках программирования, использование инструкции сото не поощряется, поскольку она затрудняет понимание и отладку программ.

Циклическая обработка наборов записей

Одной из самых распространенных ситуаций, в которых требуется циклическое выполнение определенных действий, является построчная обработка набора записей, возвращенного некоторым запросом. Во всех основных диалектах SPL для этого предусмотрены специальные конструкции. Концептуально они подобны встраиваемым в клиентские приложения инструкциям declare cursor, open, fetch и close

встроенного SQL или соответствующим вызовам API-функций Однако результат запроса в данном случае направляется не приложению, а хранимой процедуре, которая выполняется самой СУБД. Соответственно, результирующие данные оказываются не в переменных клиентского приложения, а в локальных переменных хранимой процедуры.

В качестве иллюстрации предположим, что нам нужно заполнить две таблицы данными из таблицы orders. В первую из них, bigorders, войдут имена клиентов и стоимости заказов на сумму более $10000, а во вторую, smallorders, войдут имена служащих и стоимости заказов на сумму менее $1000. Конечно, лучшим и наиболее эффективным решением этой задачи было бы выполнение двух отдельных инструкций insert с подчиненными запросами на выборку, но мы поступим иначе:

1) выполним запрос на выборку стоимости заказа, имени клиента и имени служащего для каждого заказа;

2) извлечем стоимость заказа из очередной строки результирующего набора записей и проверим, попадает ли она в диапазон, соответствующий таблице bigorders или smallorders;

3) если обнаружено попадание в диапазон, вставим строку в соответствующую таблицу;

4) повторим действия, указанные в пунктах 2 и 3, для всех строк набора записей;

5) сохраним изменения в базе данных.

На рис. 20.13 приведена хранимая процедура Oracle, выполняющая эти действия. Набор записей, определяющий запрос, задан в разделе объявлений процедуры; мы назвали его o cursor. В том же разделе объявлена переменная curs row, используемая для доступа к строкам набора записей. Ее тип rowtype (т.е. запись) подобен типу данных struct языка С; он представляет собой набор отдельных значений столбцов. Включив в объявление переменной curs row имя набора записей, мы указали, что элементы структуры rowtype будут иметь те же имена и типы, что и столбцы в таблице результатов запроса.

create procedure sort orders {) declare

/* Набор для запроса */

cursor o cursor IS

select amount, company, name

from orders, customers, salesreps where cust = cust num and rep = empl num;

/* Переменная для хранения получаемых записей */ curs row o cursor%rowtype,

begin

/* Цикл обработки всех строк в таблице результатов запроса */

for curs row m o cursor

loop

/* Обработка малых заказов */ if {curs row.amount < 1000.00) then insert into smallorders

values (curs row.name, curs row.amount);

/* Обработка больших заказов */ elsif (curs row.amount > 10000.00) then insert into bigorders

values (curs row.company, curs row.amount);

end if; end loop;

commit; end;

Рис 20.13. Цикл (тя) обработки бора записей --pracle PL/SQL

Созданный нами запрос обрабатывается в специальном цикле for. СУБД выполняет этот запрос (по сути, она выполняет эквивалент инструкции open встроенного SQL) до того, как перейдет в тело цикла Затем в цикле СУБД извлекает следующую запись из таблицы результатов запроса и помещает значения столбцов в переменную cuRs Row, откуда хранимая процедура может их извлекать. Далее выполняются инструкции в теле цикла. Когда все строки будут обработаны, набор записей автоматически закроется и работа хранимой процедуры продолжится с инструкции, следующей за циклом for.