На рис 20 6 дана версия той же хранимой процедуры на диалекте Oracle PL/SQL И здесь есть несколько отличий от диалектов Transact-SQL и Infornix: ш специальный раздел declare является частью блока begin. . .end и содержит

объявления локальных переменных этого блока; ш инструкция select into имеет ту же форму, что и в хранимых процедурах

Informix, и применяется для занесения значения, возвращаемого запросом, в

локальную переменную; ш синтаксис операторов присваивания взят из языка Pascal (:=). Эта пара символов

используется в Oracle PL/SQL вместо инструкции let

/* Процедура для проверки общей стоимости заказов клиента */ create procedure chk tot(c num in integer) as declare

/* Объявляем две локальные переменные */ tot ord number(16, 2); msg text varcliar (30) ,

begin

/* Вычисляем общую стоимость заказов клиента */ select sum(amount) into tot ord from orders where cust = c nura;

/* В зависимости от величины суммы заносим в переменную соответствующее сообщение */

if tot ord < 30000.00

msg text := большой объем заказов

else

msg text := малый объем заказов /* Выполняем дальнейшую обработку сообщения */

end;

.%20.6. Использование локалмых пещиенных в Oracle PL/S(3L-S\

Локальные переменные в хранимых процедурах могут использоваться в качестве источника данных в выражениях SQL везде, где допускаются константы. При вычислении такого выражения имя переменной заменяется ее текущим значением. Кроме того, в инструкциях SQL локальные переменные можно использовать для хранения значений, полученных в результате вычисления выражения или выполнения запроса. И то и друюе вы видели в примерах программ, представленных на рис. 20.4-20 6

Блоки инструкций

Практически во всех хранимых процедурах, кроме самых простых, возникает необходимость объединения некоторой последовательности инструкций в группу, интерпретируемую как одна инструкция Например, если в вашей процедуре используется конструкция if. . . then. . . else, то, скорее всего, возможность группировки инструкций вам понадобится, поскольку большинство диалектов SPL требует, чтобы каждая ветвь условной конструкции состояла только из одной инструкции.

в Transact-SQL структура блока инструкций очень проста:

/* Блок инструкций Transact-SQL */ begin

/* Здесь должна располагаться последовательность инструкций SQL */

Единственной задачей ключевых слов begin . . .end является ограничение блока инструкций; они не влияют на область видимости локальных переменных или других объектов базы данных. Процедуры, циклы и другие конструкции Transact-SQL оперируют единственной инструкцией, и поэтому в них очень часто используются блоки, объединяющие несколько инструкций в одну.

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

/* Блок инструкций Informix SPL */

/* Объявления локальных переменных блока */

define . . .

/* Определение обработчика исключений */ on exception . . .

/* Последовательность инструкций SQL */ begin . . .

Раздел объявления переменных не обязателен. Вы видели его пример в процедуре на рис. 20.5. Необязателен и раздел обработки исключений (о нем рассказывается далее в этой главе) У ключевых слов begin. . . end та же роль, что и в Transact-SQL: они объединяют последовательность инструкций SQL в единый блок. Между ними не обязательно должно быть несколько инструкций - может быть и одна инструкция, если, к примеру, вы хотите объявить переменные или назначить специальную обработку исключений только для нее.

В Informix не требуется столь часто использовать блоки инструкций, как в Transact-SQL И для циклов, и для условных конструкций предусмотрены маркеры заверщения (if. . .end if, while. . .end while, for. . .end for), благодаря чему в них можно включать фуппы инсфукций (завершая каждую из них точкой с запятой). Поэтому явное определение блока инструкций только ради их фуппировки требуется редко

В Oracle PL/SQL возможности создания блоков инструкций те же, что и в Informix. Допускается выделять фуппу инсфукций и определять для нее локальные переменные вместе с обработчиком исключений:

Блок инструкций Oracle PL/SQL */ I* Объявления локальных переменных блока */ declare . . .

Последовательность инструкций SQL */

begin . . .

/* Определение обработчика исключений */ exception . . .

end;

Все три раздела блока не обязательны. Часто встречаются простые блоки begin. .. end или блоки declare. .. begin. .. end, состоящие ИЗ объявлений Переменных и набора инструкций. Как и в Informix, в Oracle предусмотрены специальные маркеры конца цикла и условных конструкций, поэтому в таких конструкциях явное определение блоков инструкций с помошью ключевых слов begin.. .end не требуется.

Возврат значения из процедуры

в дополнение к хранимым процедурам многие диалекты SPL поддерживают хранимые функции. От процедур последние отличаются тем, что возвращают значения, тогда как процедуры этого не делают. Допустим, к примеру, что вы определили хранимую процедуру, которая получает идентификатор клиента и вычисляет общую стоимость его заказов Если определить эту процедуру как функцию, полученная стоимость может быть возвращена в вызывающую программу.

На рис. 20.7 приведен пример такой функции для Oracle PL/SQL. Обратите внимание на предложение returns в ее объявлении, которое сообщает СУБД тип данных возвращаемого значения. В большинстве СУБД хранимую функцию можно вызвать в интерактивном режиме, и возвращенное ею значение будет выведено на экран. Если же вызвать функцию из хранимой процедуры, то возвращенное ею значение можно использовать в дальнейших вычислениях или сохранить в переменной.

/* Возвращает общую стоимость заказов клиента */ create function get tot ords (c num m integer) returns number(16, 2)

/* Объявляем локальную переменную для хранения итога */ declare tot ord number(16, 2);

begin

/* Простой запрос, возвращающий

единственную запись с итоговой суммой */ select sum(amount) into tot otd from orders where cust = c num;

/* Возвращаем полученную сумму

в качестве значения функции */ return tot ord;

end;

Рис 20.7. Хранимая функция в Oracle PL/SQL

Многие диалекты SPL допускают использование хранимых функций в выражениях. В частности, это верно для диалекта Oracle PL/SQL, поэтому функцию, приведенную на рис. 20.7, можно, например, включить в условие отбора:

SELECT COMPANY, NAME

FROM CUSTOMERS, SALESREPS