WHERE CUST REP = EMPL NUM

AND GET TOT ORDS (CDST NUM) > 10000.00

Когда СУБД вычисляет условие отбора для каждой строки в таблице результатов запроса, она использует идентификатор клиента из этой строки в качестве аргумента функции get tot ords и проверяет, не превысило ли значение, возвращаемое этой функцией, $10000. Этот же запрос можно построить иначе, включив в предложение 1ГЯ0М еще и таблицу orders и сгруплировав результат по клиентам и служащим Многие СУБД выполняют запросы с группировкой более эффективно, чем тот запрос, который приведен выше, так как последний требует обработки таблицы заказов для каждого клиента.

На рис. 20.8 представлена версия той же хранимой функции для Informix. Как видите, она мало чем отличается от версии для Oracle.

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

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

begin

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

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

Возвращаем полученную сумму б качестве значения функции */ return tot ord; end function;

Рис. 20.8. Хранимая функция в Informix SPL

Диалект Transact-SQL не поддерживает хранимые функции, подобные приведенным на рис. 20.7 и 20 8. Хранимые процедуры Transact-SQL могут возвращать код завершения, для чего в них используется инструкция return. Однако возвращаемое значение может быть только целым. Ноль указьгеает на успешное выполнение процедуры, а отрицательные значения определяют различные виды ошибок. Все системные хранимые процедуры Sybase Adaptive Server и Microsoft SQL Server следуют этому соглашению. Возвращенное хранимой процедурой значение можно сохранить в локальной переменой, используя следующую форму инструкции execute:

declare sts val mt

execute sts val = add cust XYZ Corporation, 2317, 30000.00,

50000.00, 103, Chicago

Возврат значений через параметры

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

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

На рис. 20.9 приведена хранимая процедура PL/SQL, которая получает идентификатор клиента и возвращает его имя, имя закрепленного за ним служащего ц название города, в котором расположен офис этого служащего. У процедуры четыре параметра. Первый, cnum, - входной, в нем процедуре передается идентификатор клиента. Остальные три - выходные, они используются для передачи запрощенных данных вызывающей процедуре В этом простом примере инструкция select into помещает извлеченные из таблиц данные прямо в выходные параметры хранимой процедуры. В более сложных процедурах выходные значения могут формироваться в локальных переменных и затем помещаться в выходные параметры с помощью операторов присваивания PL/SQL.

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

create procedure get cust info (c num m integer,

c name out varchar (20), r name out varchar (15), с offc out varchar (15))

begin

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

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

end;

Рис. 20.9. Хранимая пррцвтра*с выходными параквтрсШ в Orade pi/SQL

Чтобы процедура могла вызвать другую процедуру с выходными параметрами, она должна предоставить для них буферы , куда будут помещены возвращаемые значения, - ими могут быть локальные переменные или собственные выходные параметры вьвывающей процедуры. Вот фрагмент процедуры, написанной на Oracle PL/SQL, которая вызывает процедуру get cust info, приведенную на рис. 20.9.

/* Получаем информацию о клиенте 2111 */ declare the name varchar(201,

the rep varchar(15),

the city varchar(15); execute get cust info(2111, the name, the rep, the city);

Конечно, маловероятно, что эта процедура будет вызываться с непосредственно заданным идентификатором клиента, но синтаксически такой вызов вполне допустим. Для трех остальных параметров в вызове процедуры указаны переменные, в которые должны быть помещены возвращаемые значения. А вот как нельзя вызывать процедуру get cust info-

/* Получаем информацию о клиенте 2111 */

execute get cust info(2111, XY2 Со , the rep, the city);

Второй параметр в этом вызове является выходным, и в нем нельзя задавать литеральные значения

В дополнение к входным и выходным параметрам Oracle поддерживает парамет-рь1, которые одновременно являются и входными, и выходными (inout) Они передаются хранимой процедуре по тем же правилам, что и обычные выходные параметры, и, кроме того, переданные в них значения используются вызываемой процедурой как входные данные

На рис 20.10 приведена версия процедуры get CUST info, написанная на Transact-SQL. В заголовке этой процедуры выходные параметры объявлены несколько иначе, чем в примере для Oracle, и инструкция select также имеет несколько иную форму. Во всем остальном структура процедуры такая же, как и в предьщущем случае.

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

и название города, в котором расположен офис служащего */ create proc get cust infо

@с num integer,

@o name varchar (20) output,

@r naine varchar (15) output,

@c offc varchar (15) output

begin

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

запись с интересующей нас информацией */ select !3c name = company, @r name = name, @c offc = city from customers, salesreps, offices where cust num = (?c num and empl num = cust rep and office = rep office

Рис. 20 10. Хранимая процедура с выхщцнщш параметрами в Transact-SQL

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

/* Получаем информацию о клиенте 2111 */ declare the name varchar(20) declare the rep varchar(15) declare the city varchar(15) exec get cust info @c num = 2111,

@c name = the name output,

@r name = the rep output,

(3c offc = the city output

Ha рис. 20.11 представлена версия той же самой хранимой процедуры в Informix. В этой СУБД выбран другой способ возврата нескольких значений - не через выходные параметры, а через несколько выходных значений функции. Таким образом, в Informix процедура get CUST info превращается в функцию. Возвращаемые ею значения определены в предложении returning в заголовке функции и передаются назад с помощью инструкции return.