4.4 Хранимые процедуры

Хранимая процедура (stored procedure) — это программа, которая вы­полняет некоторые действия с информацией в базе данных и при этом сама хранится в базе данных. В Oracle хранимые процедуры можно мож­но писать на языках PL/SQL и Java.

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

Хранимые процедуры используются для многих целей. Хотя админи­страторы баз данных используют их для выполнения рутинных задач ад­министрирования, главной областью их применения являются все же при­ложения баз данных. Эти процедуры могут вызываться из прикладных программ, написанных на таких языках, как Java, С#, С++ или VB.Net, а также из веб-сценариев, написанных на VBScript или JavaScript. Кроме того, эти процедуры можно вызывать в интерактивном режиме из команд­ной оболочки SQL*Plus.

Можно выделить следующие преимущества хранимых процедур:

В отличие от кода приложений, хранимые процедуры никогда не пере­даются на клиентские компьютеры. Она всегда находятся в базе данных и выполняются СУБД на том компьютере, где располагается сервер базы данных. Таким образом, они более безопасны, чем распространяемый код приложения, а кроме того, снижают сетевой трафик. Хранимые процеду­ры постепенно становятся предпочтительным режимом реализации логи­ки приложения в сети Интернет и корпоративных интрасетях. Еще одно преимущество хранимых процедур заключается в том, что SQL-операторы в них могут быть оптимизированы компилятором СУБД.

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

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

В листинге 4.6 изображена хранимая процедура, выполняющая эту за­дачу. Процедура, которая называется Customer_Insert, принимает четы­ре параметра: newname (имя нового клиента), newareacode (код региона), newphone (телефон) и artistnationality (национальность художника). Клю­чевое слово IN указывает на то, что все эти параметры являются входны­ми. Выходные параметры (которых у этой процедуры нет) обозначаются ключевым словом OUT, а параметры, играющие роль и входных и вы­ходных, — сочетанием IN OUT. Следует обратить внимание, что для па­раметра указывается только тип данных, а длина не указывается. Oracle определит длину из контекста.

Листинг 4.6.

CREATE OR REPLACE PROCEDURE Customer_Insert (
newname IN char, newareacode IN char, newphone IN char,
artistnationality IN char
)
AS
rowcount integer(2);
CURSOR artistcursor IS SELECT ArtistID FROM ARTIST
WHERE Nationality = artistnationality;
BEGIN
SELECT Count(*) INTO rowcount FROM  CUSTOMER
WHERE Name = newname AND AreaCode = newareacode AND PhoneNumber = newphone;
IF rowcount > 0 THEN BEGIN
DBMS_OUTPUT.PUT_LINE ('There is client in DB! Count is ' I I rowcount); RETURN;
END; END IF;
INSERT INTO CUSTOMER
(CustomerlD, Name, AreaCode, PhoneNumber)
VALUES (CustID.NextVal, newname, newareacode, newphone);
FOR artist IN artistcursor LOOP
INSERT INTO CUSTOMER_ARTIST_INT (CustomerlD, ArtistID)
VALUES (CustID.CurrVal, artist.Artist ID); END LOOP;
DBMS_OUTPUT.PUT_LINE ('Client is added!');
END;
/

Раздел объявления переменных следует за ключевым словом AS. Опе­ратор SELECT определяет переменную-курсор (cursor variable) с именем artistcursor. Этот курсор выделяет из таблицы ARTIST для обработки строки всех художников заданной национальности.

В первой части процедуры проверяется, есть ли в базе информация о данном клиенте. В этом случае никакие действия не предпринимаются, а пользователю с помощью пакета Oracle DBMS_OUTPUT выводится со­ответствующее сообщение. Следует обратить внимание, что для вывода строки и значения переменной используется следующий синтаксис:

DBMS_OUTPUT.PUT_LINE ('<строка>' И <переменная>);

Пользователь получит это сообщение только в том случае, если проце­дура будет вызвана из SQL*Plus. В случае вызова процедуры иным путем, например с помощью браузера через Интернет, пользователь не увидит этого сообщения. Чтобы сообщить пользователю об ошибке, разработчик должен воспользоваться выходным параметром или сгенерировать исклю­чение.

Кроме того, чтобы такие сообщения стали видимыми, следует выпол­нить команду

Set serveroutput on;

Если при работе в SQL*Plus вы не видите сообщений, выводимых ва­шими процедурами, то, скорее всего, вы не выполнили этот оператор.

Оставшаяся часть процедуры в листинге 4.6 вставляет данные о новом клиенте и затем перебирает всех художников выбранной национальности. Обратите внимание на использование специальной конструкции PL/SQL FOR artist IN artistcursor. Эта конструкция выполняет несколько задач. Прежде всего, она открывает курсор и считывает первую строку. Затем она последовательно обрабатывает все строки под курсором и по оконча­нии обработки передает управление следующему оператору после FOR. Заметьте также, что обращение к столбцу ArtistID текущей строки про­исходит с использованием синтаксиса artist.ArtistID, где artist — это имя переменной цикла FOR, а не курсора.

После того как процедура написана, ее необходимо скомпилировать и сохранить в базе данных. Если текст процедуры сохранен в файле, то про­цедура будет скомпилирована и сохранена в базе данных автоматически после ввода команды

start Имя_файла_процедуры

Если вы что-то ввели неправильно, у вас могут возникнуть ошибки компиляции. К сожалению, SQL*Plus не покажет вам эти ошибки автома­тически, а выдаст сообщение "Warning: Procedure created with compilation errors "(Предупреждение: При компиляции процедуры обнаружены ошиб­ки). Чтобы увидеть ошибки, введите команду:

Show errors;

Если синтаксических ошибок не было, вы получите сообщение "Procedure created "(Процедура создана). Теперь вы можете вызвать эту процедуру с помощью команды EXECUTE или EXEC:

Exec Customer_Insert('Michael Bench', '203', '555-2014', 'US');

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