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

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

В листинге 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. В случае вызова процедуры иным путем, например с помощью браузера через Интернет, пользователь не увидит этого сообщения. Чтобы сообщить пользователю об ошибке, разработчик должен воспользоваться выходным параметром или сгенерировать исклю чение.

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