Циклы. Цикл WHILE или FOR либо друшл подобная структура позволяет многократно вьшолнять последовательность инструкций SQL до тех пор, пока не выполнится заданное условие окончания цикла. В некоторые реализации язьпса SQL включены специальные циклы для прохода по всем строкам в таблице результатов запроса.

Блоки инструкций. Последовательность инструкций SQL может быть сгруппирована в единый блок и использована в других управляющих конструкциях как одна инструкция.

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

Именованные процедуры. Последовательность инструкций SQL можно объединить в группу, дать ей имя и назначить формальные входные и выходные параметры, так что получится обычная подпрограмма или функция, какие используются в традиционных языках программирования. Созданную таким образом процедуру можно вызывать по имени, передавая ей нужные значения в качестве входных параметров. Если она является функцией, возвращающей значение, то его можно использовать в выражениях.

Набор элементов, реализующих все эти возможности, составляет язык хранимых процедур (SPL - Stored Procedure Language). Впервые механизм хранимых процедур был предложен компанией Sybase в ее популярном продукте Sybase SQL Server. С тех пор их поддержка была встроена во многие СУБД. Одни компании смоделировали в своих продуктах конструкции языков С или Pascal, тогда как другие предпочли сохранить исходный стиль языка SQL, чтобы все его инструкции, как языков DDL и DML, так и языка хранимых процедур, были единообразны. В результате концепция хранимых процедур во всех диалектах SQL одна, а вот их синтаксис очень отличается.

Простейший пример

проще всего объяснять основы построения хранимых процедур на примере. Представьте себе процесс добавления сведений о клиенте в нашу демонстрационную базу данных. Он состоит из такой последовательности действий:

1 Получаем идентификатор и имя клиента, лимит кредита и предполагаемый объем продаж, а также закрепленного за ним служащего и офис.

2. Добавляем в таблицу CUSTOMERS данные о новом клиенте.

3. Обновляем строку закрепленного за клиентом служащего в таблице SALESREPS, увеличивая его плановый объем продаж на заданную сумму.

4 Обновляем строку назначенного клиенту офиса в таблице OFFICES, увеличивая

его плановый объем продаж на заданную сумму. 5- Сохраняем изменения в базе данных, если все они выполнены успешно.

Без использования хранимой процедуры для вьтолнения всех этих действий Потребовалась бы приведенная ниже последовательность инструкций SQL (мы добавим клиента XYZ Coфoratioп с идентификатором 2137, лимитом кредита $30000 и Планируемым на первый год объемом продаж $50000; этот клиент будет контактировать со служащим Полом Крузом, идентификатор которого 103, из чикагского офиса):

INSERT INTO CUSTOMERS {CUST NUM, COMPANY, CUST REP, CREDIT LIMIT) VALUES (2317, XYZ Corporation, 103, 30000.00);

UPDATE SALESREPS

SET QUOTA = QUOTA + 50000.00 WHERE EMPL NUM = 103;

UPDATE OFFICES

SET TARGET = TARGET + 50000.00 WHERE CITY = Chicago;

COMMIT;

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

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

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

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

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

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

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

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

begin

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

insert into customers (cust num, company, cust rep, credit limit) values (c num, c name, c rep, cred liin) ;

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

set quota = quota + tgt sls where eral num - c rep;

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

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

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

end;

Рис. 20.1. Пример хранимой процедуры на диалекте PL/SQL * f

Поскольку процедура хранится прямо в базе данных, ее можно вызвать вот такой простой инструкцией, в которой указаны значения всех шести параметров:

ADD CUSTсXYZ Corporation, 2317, 30000.00, 50000.00, 103, Chicago)

СУБД выполнит указанную вами хранимую процедуру, выполнив одну за друго все входящие в нее инструкции. Если процедура add caST успешно завершит свою работу, все изменения будут зафиксированы в базе данных. В противном случае СУБД вернет код ошибки и сообщение, указывающее, что именно произошло.

Использование хранимых процедур

Приведенная на рис. 20.1 процедура иллюстрирует базовые концепции, общие для всех диалектов языка хранимых процедур. Практически во всех диалектах определение хранимой процедуры создается с помощью инструкции create procedure. Дополняющая ее инструкция drop procedure предназначена для удаления из базы данных процедуры, которая больше не нужна. Инструкция create procedure задает следующие элементы:

имя хранимой процедуры;

количество и типы данных ее параметров;

имена и типы данных всех локальных переменных, используемых процедурой;

последовательность инструкций, которые выполняются при вызове процедуры. В следующих параграфах подробно описываются все эти элементы и рассказывается о специальных инструкциях SQL, управляющих ходом вьтолнения хранимых процедур.

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

Во многих распространенных диалектах SPL для создания хранимой процедуры применяется инструкция create procedure. Эта инструкция назначает новой процедуре имя, по которому в дальнейшем процедуру можно будет вызывать. Имя процедуры должно соответствовать общим правилам для идентификаторов SQL (процедура на рис. 20.1 названа ADD CUST). Хранимая процедура может принимать ноль или более параметров (в нашем примере их шесть: c name, c num, cred lim, tgt sls, C rep и C 0ffc). Обычно значения параметров указываются в виде разделенного запятыми списка, заключенного в скобки и следующего за именем вызываемой процедуры. Заголовок хранимой процедуры содержит имена параметров и типы их данных. Для параметров хранимых процедур могут использоваться те же типы данных, которые поддерживаются в СУБД для столбцов таблиц.

На рис. 20.1 все параметры процедуры являются входными, о чем говорят ключевые слова in, следующие за их именами в заголовке процедуры (сказанное относится к диалекту PL/SQL в Oracle). Когда процедура вызывается, переданные ей аргументы присваиваются ее параметрам и начинается выполнение тела процедуры. Имена параметров могут использоваться в теле процедуры (и, в частности, в составляющих ее стандартных инструкциях SQL) везде, где допускается наличие констант. Встретив имя параметра, СУБД подставляет на его место текущее значение этого параметра. На рис. 20.1 параметры используются в инструкциях insert и update для вычисления значений столбцов и в условиях отбора.

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