Материал: Язык PL SQL

Рассмотрим создание простого пакета catMgmt, демонстрирующего функции, доступные паке­там PL/SQL. Сначала нужно создать спецификацию пакета:

Далее создайте тело пакета, используя приведенную ниже директиву.

Теперь введите анонимный блок PL/SQL, использующий процедуру insertCat пакета catMgmt для вставки новой строки в таблицу Catalogs:

Обратите внимание на то, что при ссылке на объект типа пакет (на глобальную переменную или подпрограмму) необходимо ис­пользовать точечную нотацию для идентификации объекта пакета по имени самого пакета.

Введите анонимный блок для изменения названия нового каталога на «Разное» с ис­пользованием процедуры updateCat пакета catMgmt.

Теперь введите анонимный блок PL/SQL для вы­полнения процедуры deleteCat пакета catMgmt, которая удаляет новую часть таблицыCatalogs.

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

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

Команда EXECUTE представляет собой другой способ вы­полнения PL/SQL-подпрограмм. Вызывая функцию, необходимо создать переменную для присвоения ей возвращаемого функцией значения.

Встроенные пакеты утилит

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

Триггеры баз данных

Триггером базы данных называется хранимая процедура, которая выполня­ется, когда происходит некоторое событие. Самым распространенным типом триггера, который вы можете использовать, является триггер DML, который связан с конкретной таблицей; когда приложение обраща­ется к таблице с помощью команды SQLDML, удовлетворяющей услови­ям срабатывания триггера, Oracle автоматически запускает триггер для выполнения этой операции. Триггеры используются для настройки реак­ции Oracle на события приложения.

Примечание Oracle также поддерживает триггеры, которые срабатывают в от­вет на события на уровне базы данных и на уровне схемы. Более подробная инфор­мация об этих типах триггеров приведена в Справочнике по командам SQLдля баз данных Oracle.

Для создания триггера DML используется SQL-команда CREATETRIGGER. Упрощенный (неполный) синтаксискоманды CREATE TRIGGER имеетвид:

О CREATE [OR REPLACE] TRIGGER trigger{BEFORE|AFTER} {DELETEfINSERT|UPDATE [OF column [.column] ... ]}

[OR {DELETE|INSERT|UPDATE [OF column [.column] ... J} ] ... ON table

FOR EACH ROW [WHEN condition] ] ... PL/SGL block . . . END [trigger]

Определение триггера DML-включает в себя следующие уникальные части:

• Определение триггера включает в себя список директив триггера, включая команды INSERT, LTPDATE и/или DELETE, вызывающие срабатывание триггера. Триггер связан с одной и только одной таб­лицей. ,

• Срабатывание триггера может происходить до его директивы или после, в зависимости от логики приложения.

• Б определении триггера указывается, является ли он триггером ди­рективы или триггером строки. Триггер директивы срабатывает только один раз, независимо оттого, на сколько строк распростра­няется его действие.

Упражнение 4.21. Создание и использование триггеров базы данных

Введите приведенную ниже директиву CREATETRIGGER для создания триггера DML. который автоматически заносит в журнал некоторую ба­зовую информацию о DML-изменениях, внесенных в таблицу PARTS. Триггер logPartChanges находится за директивой триггера, который срабатывает один раз после директивы срабатывания, независимо от того, на какое количество строк он воздействует.

□ CREATE OR REPLACE TRIGGER logPartChanges AFTER INSERT OR UPDATE OR DELETE ON parts DECLARE

l_statementType CHAR(l); BEGIN IF INSERTING THEN l_stateinentType := 'I'; ELSIF UPDATING THEN l_sta'tementType := 'У ; ELSE l_statementType := ' D' END IF;

INSERT INTO partsLogVALUES (SYSDATE, l_statementType. USER) ENDipgPartChanges; /

Обратите внимание на то, что если в триггере logPartChanges сраба­тывание возможно от различных типов команд, то предикаты INSERTING, UPDATING^ DEIJiTINGпозволяют определить тип команды, от которой в действительности сработал триггер.

Теперь создайте триггер logDetailedPartChanges, который принадле­жит к послестроковому типу и заносит в журнал подробную информацию о DML-изменениях, внесенных в таблицу PARTS. Этот триггер срабаты­вает один раз для каждой строки, на которую распространяется директи­ва срабатывания триггера.

□ CREATE OR REPLACE TRIGGER logDetailedPartChanges AFTER INSERT OR UPDATE OR DELETE ON parts

FOR EACH ROW BEGIN INSERT INTO oetaileapartslog (changedateuserid,

newid, newdescription, newunitprice. newonhand, newreprder, oldid, olddescription, oldumtprice. oldonhand. oldreorder) VALUES (SYSDATE. USER, .new.id, :new.description :new.unitprice :new.onhand, :new.reerder, ;old.id. ;old.descriptipn, :pld.. unitprice, ;old.onhand, :old.reorder); tNDlogDetailedPartChanges;

ПримечаниеКак возможный вариант триггер строки может включать в себя ог­раничение, т.е. логическое условие его срабатывания.

Обратите внимание на то, что в триггере logDetailedPartChangesзначе­ния корреляции :newи soldпозволяют триггеру строки получать доступ к прежнему и новому значениям текущей строки. Если директивой тригге­ра является команда INSERT, то все прежние значения полей не опреде­лены (имеют значение null). Аналогичным образом, если директивой триггера является команда DELETE, то все новые значения полей имеют значение null.

В заключение проверьте как работают ваши новые триггеры и какие операции они выполняют. Введите приведенные ниже SQL-команды, ко­торые вставляют, обновляют и удаляют строки таблицы PARTS и делают запрос к таблицам PARTSLOG и DETAILEDPARTSLOG:

О INSERT INTO parts

(id, description, unitprice, onhand. reorder) VALUES (6. "Mouse' , 49, 1200, 500):

UPDATE parts SET onhand = onhand - 10:

DELETE FROM parts WHERE id = 6:

SELECT - FROM partsLog;

SELECT newid. newonhand, oloid. oldonha.nd FROM detailedpartslog:

Текст результирующих таблиц этих запросов должен быть аналогичен приведенному ниже:

□ CHANGEDATE С USERID

03-DEC-05 I ANONYMOUS 03-DEC-05 U ANONYMOUS 03-DEC-05 D ANONYMOUS

NEWID NEWONHAND 0LDID 0LD0NHAND

6 1200

1. 267 1 277

2. 133 2 143

3. 7621 3 7631

4. 5893 4 5903

5 480 5 490

6 1190 .6 1200

6 1190

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

запросов к таблицам PARTSLOG и DETAILEDPARTSLOG вы увидите со­общение "norowsselected" («строки не выбраны»). Проверьте это само­стоятельно.

Итоги

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