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

cred lim money(16, 2), /* лимит кредита */

tgtsls money(16, 2), /* объем продаж */

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

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

/* Добавляем новую строку в таблицу CUSTOMERS */ insert into customers (cust num, company, cust rep, crtj. ximit) values (c num, c name, c rep, cred lim);

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

set quota = quota + tgt sls where empl num = c rep;

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

set target = target + tgt sls where city = c offc;

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

Рис. 20.3. Прр1Ш(щЛЩ СШТ ла диствктв Informix

Во всех диалектах, в которых используется инструкщ, create procedure, хранимая процедура может быть удалена соответствующей Иструкцией drop Рко-cedure:

drop procedure add cust

Вызов хранимой процедуры

Хранимую процедуру можно вызывать по-разному: из гриложения с помощью соответствующей инструкции SQL, из другой хранимой проц.дуры а также в интерактивном режиме. Синтаксис вызова хранимых процедур зацисит от используемого диалекта. Вот пример вызова процедуры add CUST на диал%е PL/SQL:

EXECUTE add CUSTСXYZ Corporation, 2317, 30000.00, 50C)00, 103, Chicago)

Передаваемые процедуре параметры задаются в том поряд;, g каком они объявлены, в виде списка, заключенного в скобки. При вызове из [ругой хранимой процедуры или триггера ключевое слово execute может быть опуо, тогда вся инструкция чуть упрощается:

to CUSr(xyz Corporation, 2317, 30000.00, 50000.00, Ij Chicago)

На диалекте Transact-SQL вызов этой же процедуры ш>Щялш так:

EXECUTE add CUST XYZ Corporation, 2317, 30000.00, 50()o.oO, 103, Chicago

Скобки здесь не нужны - просто перечислите значения параметров через запя- после имени процедурьь Ключевое слово execute ыщцо сократить до ехеС. Кроме того, задавая параметры, можно явно указать щ имена, что позволяет

перечислять параметры в любом порядке. Вот альтернативный пример вызова процедура, add cust на диалекте Transact-SQL, полностью эквивалентный предыдущему:

EXEC ADD CUST @C NAME = XYZ Corporation,

@C NUM = 2317, @CRED LIM = 30000.00,

eTGT SLS = 50000.00,

@C REP = 103,

@C OFFC = Chicago

Ha диалекте Informix та же инструкция будет выглядеть так:

EXECUTE PROCEDURE ADD CUST СXYZ Corporation, 2317, 30000.00,

50000.00, 103, Chicago)

И снова параметры заданы в виде списка, разделенного запятыми и заключенного Б скобки. Эта форма инструкции может применяться в любом контексте. Например, ее можно использовать для вызова хранимой процедуры из клиентского приложения А при вызове из другой хранимой процедуры можно использовать еще и такой эквивалентный синтаксис;

CALL ADD CUST (XYZ Corporation, 2317, 30000.00, 50000.00, ЮЗ, Chicago)

Переменные хранимых процедур

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

На рис. 20.4 приведен фрагмент несложной хранимой процедуры на диалекте Transact-SQL, которая вычисляет общую стоимость заказов указанного клиента и в зависимости от того, превысит ли эта сумма $30000, сохраняет в переменной одно из двух сообщений. Обратите внимание: диалект Transact-SQL требует, чтобы все имена переменных, как и имена параметров, начинались с символа @. Инструкция declare объявляет локальные переменные процедуры. В данном случае имеются две переменные: одна типа money, а другая типа varchar.

/* Процедура для проверки общей стоимости заказов клиента */ create proc chk tot

@c num integer /* один входной параметр */

/* Объявляем две локальные переменные */ declare (?tot ord money, emsg text varchar (30)

begin

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

20.4. Использовонивошль х1мвнных в Tranft-QL

Инструкция select, написанная на диалекте Transact-SQL, выполняет еще одну функцию - присваивает значения переменным. Вот простейший пример использования этой инструкции для занесения текста сообщения в переменную @msg text.

SELECT @MSG TEXT = большой объем заказов

Занесение в переменную обшей стоимости заказов в начале процедуры - более сложный пример. Здесь инструкция select одновременно используется и для выполнения запроса, генерирующего присваиваемое переменной значение.

На рис. 20.5 представлена версия той же хранимой процедуры в СУБД Informix. Она имеет несколько отличий:

локальные переменные объявляются с помощью инструкции define;

имена переменных являются обычными идентификаторами SQL, они не начинаются со специального символа;

для занесения результата запроса в локальную переменную используется специальная инструкция select into;

чтобы просто присвоить переменной значение, можно воспользоваться инструкцией let.

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

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

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

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

If tot ord < 30000.00

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

else

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

end procedure;

°нс. 20.5. Использование локальных перв/шнных в Informix SP,L