/* Возвращает имя клиента, имя закрепленного за ним служащего

и название города, в котором расположен офис служащего */ create function get cust infо(c num integer)

returning varcliar (20) , varchar (15) , varchar (15)

define c name varchar(20); define r name varchar(15); define c offc varchar(15);

/* Простой запрос, возвращающий единственную запись

с интересующей нас информацией */ select company, name, city into c name, r name, c offc from customers, salesreps, offices where cust nuir! = c nura and erapl nuir! = cust rep and office = rep office;

/* Возвращаем три значения */ return c name, r name, c offc; end function.

Рис. 20.1 J. Хранимая функция в Informbc SPL, возвращающая несколько значений

Инструкция call в Informix, используемая для вызова подобных функци11, также содержит специальное предложение returning, предназначенное для приема возвращаемых значений;

/* Получаем информацию о клиенте 2111 */ define the name varchar(20) define the rep varchar(15) define the city varchar(15) call get cust info(2111)

returning the name, the rep, the city;

Как и в Transact-SQL, в Informix имеется версия инструкции call, позволяющая передавать параметры по именам:

call get cust inf о (с питп = 2111)

returning the narne, the rep, the city;

Условное выполнение

Одним из базовых элементов хранимых процедур является конструкция if. then. . .else, используемая для организации ветвлений внутри процедуры. Давайте снова вернемся к рис. 20.1 с процедурой add cust, добавляющей в базу данных информацию о новом клиенте. Предположим, что правила добавления нового клиента изменились и теперь плановый объем продаж служащего не может быть увеличен более чем на некоторую определенную сумму. Если предполагаемая сумма заказов клиента на первый год составляет не более $20000, эта сумма будет добавлена к плану служащего, но если эта сумма больше $20000, к плану будут добавлены фиксированные $20000. Модифицированная версия процедуры приведена на рис. 20.12. Конструкция if. . . then. . . else работает точно так же, как и в обычных языках программирования.

/* Процедура для добавления данных о новом клиенте */ create procedure add cust (

c name in varchar(20), /* имя клиента */

с num in integer, /* идентификатор клиента */

c7ed lim m number(16, 2), /* лимит кредита */

tgt sls m number(16, 2), /* объем продаж */

с rep m integer, /* идентификатор служащего */

coffc m varchar(15)) /* город, в котором расположен офис */

begin

/* Добавляем новую строку в таблицу CUSTOMERS */

insert into customers (cust num, company, cust rep, credit limit) values (c num, c narae, c rep, cred iim) ,-

/* Обновляем запись в таблице SALESREPS */

if tgt sls <= 20000.00

then

update salesreps

set quota = quota + tgt sls where empl num = c rep,-

else

update salesreps

set quota = quota + 20000.00 where empl num = c rep,-end if,-

/* Обновляем запись в таблице OFFICES */ update offices

set target = target + tgt sls where city = c offc,-

/* Завершаем транзакцию */ commit;

end.

Рис 20. Т2;Условные консфукнвхранАшх процедурок

Все диалекты SPL допускают создание вложенных инструкций if. В некоторых диалектах даже имеются специальные разновидности условных конструидай, позволяющие организовывать множественное ветвление. Предположим, например, что в зависимости от того, окажется ли предполагаемая общая стоимость заказов клиента меньще $20000, между $20000 и $50000 или больше $50000, процедура add cust должна выполнять три разных действия. В Oracle PL/SQL этот алгоритм будет реализован так:

/* Определяем, в какой диапазон попадает стоимость заказов */ if tgt sls < 20000.00 then

/* Обработка заказов наименьшего объема */

Isif tgt sls < 50000.00 then

/* Обработка заказов среднего объема */

else

/* Обработка заказов максимального объема */ end if;

в диалекте Informix допускаются такие же конструкции, только ключевое слове elsif меняется на elif.

Циклы

Еще одним базовым элементом хранимых процедур является конструкция для многократного выполнения группы инструкци11 - проще говоря, цикл. Циклы могут быть разными: в зависимости от используемого вами диалекта SPL могут поддерживаться циклы for со счетчиком итераций (в которых значение целочисленной переменной уменьшается или утвеличивается при каждом проходе цикла, пока не достигнет заданного предела) ши циклы vjhile, в которых условие продолжения цикла вычисляется в начале или конце группы составляющих его инструкций.

Предположим, что мы хотим циклически выполнять некоторую группу инструкций, изменяя значение переменной-счетчика с именем ГТЕМ ыим в пределах от 1 до 10. Вот как это делается в Oracle PL/SQL:

/* Обрабатываем каждьт из 10 элементов */ for item num m 1..10 loop

/* Обрабатываем текущий элемент */

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

Последовательность инструкций, составляющих тело цикла, должна выполниться 10 раз, и при каждом проходе цикла значение переменной item num увеличивается на 1. Инструкция Ехгт обеспечивает возможность выхода из цикла раньше, причем это можно сделать как по заданному условию, так и безо всякого условия.

А вот тот же пример, написанный на диалекте Informix (обратите внимание на синтаксические отличия и дополнительные возможности этого цикла):

/* Обрабатываем каждый из 10 элементов */ for item num = 1 to 10 step 1

/* Обрабатываем текущий элемент */

/* Проверяем, не следует ли завершить цикл раньше */ if (itein num = special atem) then exit for; end for;

Второй распространенной формой цикла является выполнение последовательности инструкций до тех пор, пока остается или пока не станет истинным заданное условие. Ниже дан пример такого цикла в Oracle PL/SQL. Чтобы этот цикл когда-нибудь остановился, внутри его тела должна осуществляться проверка условия окончания цикла (в данном случае проверяется равенство двух переменных), и если это условие истинно, должна вьшолняться команда выхода из цикла:

/* Циклически обрабатываем некоторые данные */

loop

/* Очередной шаг обработки */