его под именем, скажем, SP_CI.sql. Если в последней строке файла вы поместите косую черту, то процедура будет скомпилирована и сохранена в базе данных автоматически после ввода команды
Start SP_CI
Если вы что-то ввели неправильно, у нас могут возникнуть ошибки компиляции. К сожалению, SQL Plus не покажет вам эти ошибки автоматически, а выдаст сообщение: «Warning: Procedure created with compilation errors» («Предупреждение: при компиляции процедуры обнаружены ошибки»).
Чтобы увидеть ошибки, введите команду
Show errors;
Если синтаксических ошибок не было, вы получите сообщение «Procedure created» («Процедура создана»). Теперь вы можете вызывать эту процедуру с помощью команд EXECUTE или ЕХЕС:
Exec Customer_Insert(‘Selma Warning', '206', '555-0099', 'US');
Если возникнут ошибки на этапе выполнения процедуры, номера строк в отчете об ошибках не будут совпадать с номерами строк, которые вы можете видеть в своем текстовом редакторе. Можно настроить SQL Plus так, чтобы выводимые номера строк соответствовали вашим (но мы это не рассматриваем!) Главное, имейте в виду, что номера строк могут не совпадать.
Приведем листинг 2 хранимой процедуры, реализующей процесс создания представления
Листинг 2 Процедура NewCustomerWithTransaction
CREATE OR REPLACE PROCEDURE NewCustoirierWithTransaction
(
newnawe IN char, newareacode IN char, newphone IN char, artistname IN char,
36
worktit1e IN char, workcopy IN char, price IN number
)
AS
rowcount integer (2) :
tid |
int: |
aid |
int; |
|
CURSOR transcursor IS |
|
SELECT TransactionID, ARTIST.ArtlstID |
|
FROM ARTIST, WORK, TRANSACTION |
|
WHERE Name=artistname AND Tit1e=worktit1e AND |
|
Copy=workcopy AND |
TRANSACTION.CuStomerID IS NULL AND
ARTIST.ArtlstID = WORK.ArtistID AND
WORK.WorkID = TRANSACTION.WorkID;
BEGIN
/* Клиент уже есть в базе данных? */
SELECT |
Count (*) INTO rowcount |
FROM |
CUSTOMER |
WHERE |
Name=newname AND Area_Code=newareacode AND |
|
Phone_Number = newphone |
IF rowcount > 0 THEN BEGIN
DBMS_OUTPUT.PUT_LINE ('Customer Already Exists – No Action Taken');
RETURN;
END; END IF;
/* Клиента нет в базе данных, добавляем нового клиента */
INSERT INTO CUSTOMER
37
(CustomerID, Name, Area_Code, Phone_Number) VALUES
(CustID.NextVal, newname, newareacode, newphone);
/* Ищем одну и только одну свободную строку в таблице TRANSACTION. */ rowcount :5 0;
FOR trans In transcursor LOOP
tid := trans.TransactionID; aid := trans.ArtistID; rowcount := rowcount + 1;
END LOOP; IF rowcount > 1 Then
BEGIN
/* Слишком много свободных строк -- выдаем сообщение об ошибке, отменяем изменения и выходим из процедуры */
ROLLBACK;
DBMS_OUTPUT.PUT_LINE (‘ Неверные данные в таблицах ARTIST/WORK/TRANSACTION -- никаких действий не предпринято ‘);
RETURN;
END; END IF;
IF rowcount 5 0 Then BEGIN
/* Нет ни одной свободной строки – выдаем сообщение об ошибке, отменяем изменения и выходим из процедуры */
ROLLBACK;
DBMS_OUTPUT.PUT_LINE (' Ни одной свободной строки в таблице TRANSACTION -- никаких действий не предпринято ');
RETURN;
END; END IF:
38
/* Есть ровно одна строка -- используем ее. Идентификатор транзакции. полученный из transcursor, находится в переменной ttd */
DBMS_OUTPUT.PUT_LINE (t1d); UPDATE TRANSACTION
SET CustomerID = CustID.Currvat , Salesprtce = price, PurchaseDate = SysDate WHERE TransactionID – tid;
DBMS OUTPUT.PUT_LINE (‘ Клиент добавлен в базу данных, данные транзакций обновлены ');
/* Теперь регистрируем интерес данного клиента к данному художнику */
/* Используем идентификатор художника, находящийся в переменной aid. и текущее значение последовательности
CurrVal */
INSERT INTO CUSTOMER_ARTIST INT (ArtistID, CustomerID)
VALUES (aid, CuitID.CurrVal);
END;
/
Логика этой процедуры, носящей имя NewCustomerWithTransaction, такова. Сначала создаются данные нового клиента, и в таблице TRANSACTION, где регистрируются купленные произведения, ведется поиск строк, и которых столбец CustomerID имеет пустое значение. Этот поиск ведется по соединению таблиц ARTIST, WORK и TRANSACTION, так как имя художника (Name) хранится в таблице ARTIST, а название произведения (Title) и номер копии (Copy) хранятся в таблице WORK. Если найдена ровно одна такая строка, в ней обновляются столбцы CustomerID (идентификатор клиента), SalesPrice (цена продажи) и PurchaseDate (дата приобретения). Затем добавляется запись в таблицу пересечения, чтобы зарегистрировать интерес клиента к данному художнику. В противном случае, если число найденных строк больше или меньше 1, никаких изменений в базе данных не производится.
Параметры процедуры NewCustomerWithTransaction содержат информацию о клиенте и приобретенном произведении. В процедуре объявлены несколько переменных и курсор. Курсор определен на соединении таблиц ARTIST,
39
WORK и TRANSACTION, Он выделяет столбцы TransactionID и ARTIST. ArtistID из строк, содержащих данные об искомом художнике и произведении и имеющих нулевое значение столбца CustomerID.
Прежде всего процедура выполняет проверку, нет ли уже в базе данных информации о данном клиенте. Если нет, данные нового клиента добавляются в базу. В PL/SQL нет оператора BEGIN TRANSACTION1 — первое действие с базой данных автоматически начинает транзакцию. Здесь новая транзакция начнется при добавлении данных о покупателе.
После того как данные о новом покупателе добавлены в базу, обрабатывается курсор TransCursor. Переменная rowcount используется для подсчета строк, в переменную tid записывается значение TransactionID, а в переменную aid — значение ArtistID. Заметьте, что оператор присваивания в Oracle выглядит так: «:=». Например, tid := trans.transactionID обозначает, что в переменную tid записывается значение trans.TransactionID.
В соответствии с логикой процедуры, если найдена одна и только одна строка, удовлетворяющая критериям, то в переменных tid и aid будут содержаться значения, нужные нам для успешного завершения транзакции. Если же таких строк не найдено или найдено более одной, то транзакция будет прервана, и ни tid, ни aid не будут использованы.
Для подсчета строк мы могли бы использовать выражение Count(*), и затем, если Count(*) = 1, запустить другой оператор, который бы получал нужные нам значения aid и tid. При той логике, которая реализована и листинге 2, необходимость во втором SQL-операторе отпадает.
Если число строк RowCount больше единицы или равно нулю, то выдается сообщение об ошибке и выполняется откат транзакции, отменяющий вставку данных в таблицу CUSTOMER. Если RowCount равно 1, обновляется соответствующая строка в таблице TRANSACTION. Обратите внимание на использование функции SysDate, с помощью которой записывается текущая дата. Наконец, в таблицу пересечения добавляется строка с информацией о покупателе и авторе купленного произведения (aid).
Эта хранимая процедура может быть вызвана командой наподобие
Exec NewCustorTierWithTransact1on( 'Susan Wu', '206', '555-1000', 'Miro', 'Mi Vida', 79/122', '65000');
Следующие два оператора отображают данные после обновления:
40