Кириллов В.В., Громов Г.Ю. - Введение в реляционные базы данных. Литература для вузов isbn 9785941577705 в книге рассматриваются основные понятия баз данных и систем управления ими
 Скачать 11.62 Mb.
|
|
Последовательность выполнения нескольких триггеров одной таблицы Активация триггеров DML происходит при выполнении предложений INSERT , UPDATE или DELETE . Для каждого из этих предложений может быть создано четыре триггера: табличный BEFORE , табличный AFTER , строковый BEFORE и строковый AFTER . При наличии всех этих триггеров порядок их ак- тивации таков: 1. Табличный триггер BEFORE 2. Строковый триггер BEFORE 3. Строковый триггер AFTER 4. Табличный триггер AFTER Глава 18. Хранимые процедуры 327 Предикаты INSERTING, UPDATING и DELETING В одном описании можно объединить несколько триггеров, если они отно- сятся к одному уровню (строка или предложение) и принадлежат к одной таблице. Если триггеры объединяются в одно описание, то для разделения логики на отдельные сегменты можно использовать в блоке кода PL/SQL три логические функции (предикаты), определяющие тип выполняемой операции. INSERTING — принимает значение TRUE , если активизирующий оператор INSERT ; FALSE в противном случае. UPDATING — принимает значение TRUE , если активизирующий оператор UPDATE ; FALSE в противном случае. DELETING — принимает значение TRUE , если активизирующий оператор DELETE ; FALSE в противном случае. 18.4.2. Изменение описания триггера Синтаксис инструкции ALTER TRIGGER , которая позволяет переименовывать, включать или отключать триггер без его yдaления и повторного создания, имеет вид: ALTER TRIGGER имя_триггера { {ENABLE | DISABLE} | RENAME TO новое_имя | COMPILE [дирекгивы_компилятора][DEBUG] [REUSE SETTINGS]} Здесь: ENABLE Включает ранее деактивированный триггер. В качестве альтернативы можно использовать предложение ALTER TABLE имя_таблицы ENABLE ALL TRIGGERS DISABLE Отключает работающий триггер. В качестве альтернативы можно использо- вать предложение ALTER TABLE имя_таблицы DISABLE ALL TRIGGERS RENAME TO новое_имя Триггер переименовывается в новое_имя , но егосостояние остается неизмен- ным. COMPILE [DEBUG] [REUSE SETTINGS] Триггер компилируется независимо от того, является он допустимым или нет, а также компилируются все объекты, от которых он зависит. Если любой из объектов неработоспособен, триггер будет неработоспособным. Если все объекты работоспособны, в том числе блок_кода триггера, то триггер стано- Часть VI. Создание приложений на SQL 328 вится работоспособным. У предложения COMPILE есть несколько дополни- тельных предложений. DEBUG Компилятор PL/SQL будет генерировать и записывать дополнительную ин- формацию, которую сможет использовать отладчик PL/SQL. REUSE SETTINGS Система Oracle сохранит все настройки компилятора, что поможет сэконо- мить много времени при компиляции. директивы_компилятора Указывается специальное значение, используемое компилятором PL/SQL в формате директива = 'значение' . Существуют следующие директивы: PLSQL_OPTIMIZE_LEVEL , PLSQL_CODE_TYPE , PLSQL_DEBUG , PLSQL _ WARNINGS и PLSQL _ NLSLENGTHSEMANTICS . Для каждой из них в инструкции можно ука- зать значение один раз. Директива действует только в рамках компилируемо- го модуля. Примеры создания триггеров были рассмотрены в разд. 12.3 (листин- ги 12.1—12.7). В разд. 18.4.4 будут рассмотрены более сложные примеры. 18.4.3. Удаление описания триггера DROP TRIGGER имя_триггера; 18.4.4. Использование триггера Приводимые далее примеры созданы для объектов базы данных "UCHEB", подробно рассматриваемой в части VII. Пример 18.1. Создадим триггер для формирования номера нового человека в момент ввода данных о нем в таблицу Н_ЛЮДИ , а также для исправления некорректных значений его фамилии и имени с помощью рассмотренной ра- нее функции fio (см. разд. 18.3.4). Этот триггер запускается не только при выполнении вставки новых строк ( INSERT ) в таблицу Н_ЛЮДИ , но и при изме- нении ее строк ( UPDATE ). Для того чтобы он создавал номер человека только при вставке строк, используется логическая функция INSERTING CREATE OR REPLACE TRIGGER люди_biur BEFORE INSERT OR UPDATE ON н_люди FOR EACH ROW DECLARE Глава 18. Хранимые процедуры 329 err_fam EXCEPTION; err_im EXCEPTION; BEGIN :new.Фамилия := fio(:new.Фамилия); -- фамилия замещается результатом -- работы функции fio :new.Имя := fio(:new.Имя); -- имя замещается результатом работы -- функции fio IF :new.Фамилия = '0' THEN RAISE err_fam; END IF; -- выход по ошибке при неправильном написании фамилии IF :new.Имя = '0' THEN RAISE err_im; END IF; -- выход по ошибке при неправильном написании имени IF INSERTING THEN -- Формирование нового номера человека SELECT н_люди_посл.NEXTVAL INTO :new.ид FROM dual; END IF; EXCEPTION -- начало обработчика исключений основной программы WHEN err_fam THEN RAISE_APPLICATION_ERROR(-20040, 'Фамилия должна состоять только из букв русского алфавита, '|| 'пробела, дефиса и начинаться с заглавной буквы !'); WHEN err_im THEN RAISE_APPLICATION_ERROR(-20041, 'Имя должно состоять только из букв русского алфавита, '|| 'пробела, дефиса и начинаться с заглавной буквы !'); WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20999, 'Какая-то другая ошибка'); END люди_biur; / Пример 18.2. Создадим триггер для формирования номера строки при ее вводе в таблицу Н_УЧЕНИКИ и для проверки правильности ввода значений некоторых ее столбцов. CREATE OR REPLACE TRIGGER учен_BIR BEFORE INSERT ON н_ученики FOR EACH ROW DECLARE plan_gr EXCEPTION; -- План должен быть связан с группой nach_kon EXCEPTION; -- Начало должно быть меньше или равно концу null_val EXCEPTION; -- какое-то из обязательных значений не заполнено test NUMBER; BEGIN -- Если не введены какие-то обязательные значения, выводим сообщение Часть VI. Создание приложений на SQL 330 IF :new.члвк_ид IS NULL OR :new.план_ид IS NULL OR :new.группа IS NULL OR :new.начало IS NULL OR :new.конец IS NULL THEN RAISE null_val; END IF; -- Проверяем, и если такой строки нет, то вставляем ее в н_обучения SELECT COUNT(*) INTO test FROM н_обучения WHERE члвк_ид = :NEW.члвк_ид AND вид_обуч_ид = :NEW.вид_обуч_ид; IF test = 0 THEN INSERT INTO н_обучения(члвк_ид,вид_обуч_ид) VALUES (:NEW.члвк_ид,:NEW.вид_обуч_ид); COMMIT; END IF; -- генерируем ид SELECT н_учен_посл.NEXTVAL INTO :new.ид FROM dual; SELECT COUNT(*) INTO test FROM н_группы_планов WHERE группа = :new.группа AND план_ид = :new.план_ид; -- Если план не связан с группой IF test = 0 THEN RAISE plan_gr; END IF; IF :new.начало > :new.конец THEN RAISE nach_kon; END IF; :new.конец_по_приказу := :new.конец; EXCEPTION WHEN plan_gr THEN RAISE_APPLICATION_ERROR(-20040,'Номер группы не сопоставлен с номером плана !'); WHEN nach_kon THEN RAISE_APPLICATION_ERROR(-20041,'Конец не может быть меньше начала !'); WHEN null_val THEN RAISE_APPLICATION_ERROR(-20042,'Не заполнены обязательные поля (информация о человеке, группа/план, даты) !'); WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20999, 'Какая-то другая ошибка'); END учен_bir; / Пример 18.3. Создадим триггер для изменения ряда значений таблицы Н_УЧЕНИКИ CREATE OR REPLACE TRIGGER учен_bur BEFORE UPDATE OF члвк_ид, начало, конец, план_ид, группа ON н_ученики FOR EACH ROW Глава 18. Хранимые процедуры 331 DECLARE plan_gr EXCEPTION; -- План должен быть связан с группой nach_kon EXCEPTION; -- Начало должно быть меньше или равно концу null_val EXCEPTION; -- какое-то из обязательных значений не заполнено test NUMBER; BEGIN -- Если не введены какие-то обязательные значения, выводим сообщение IF :new.члвк_ид IS NULL OR :new.план_ид IS NULL OR :new.группа IS NULL OR :new.начало IS NULL OR :new.конец IS NULL THEN RAISE null_val; END IF; -- SELECT COUNT(*) INTO test FROM н_группы_планов WHERE группа = :new.группа AND план_ид = :new.план_ид; -- Если план не связан с группой IF test = 0 THEN RAISE plan_gr; END IF; IF :new.начало > :new.конец THEN RAISE nach_kon; END IF; -- Если обновляется конец, то мы старый его вариант сохраняем -- в поле конец_по_приказу IF :old.конец <> :new.конец AND :new.конец <> :new.конец_по_приказу THEN :new.конец_по_приказу := :old.конец; END IF; EXCEPTION WHEN plan_gr THEN RAISE_APPLICATION_ERROR(-20040,'Номер группы не сопоставлен с номером плана ! (ид '||:NEW.ид||')'); WHEN nach_kon THEN RAISE_APPLICATION_ERROR(-20041,'Конец не может быть меньше начала !'); WHEN null_val THEN RAISE_APPLICATION_ERROR(-20042,'Не заполнены обяза- тельные поля (информация о человеке, группа/план, даты) !'); WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20999,'Какая-то другая ошибка'); END учен_bur; / Часть VI. Создание приложений на SQL 332 18.4.5. Изменяющиеся (мутирующие) таблицы Из тела строкового триггера нельзя обращаться к таблицам, которые в момент обращения модифицируются предложениями INSERT , DELETE или UPDATE , так как это может привести к неверным результатам. Такие таблицы получили названия изменяющихся (мутирующих) таблиц. Правда, если используется предложение: INSERT INTO имя_таблицы ... VALUES (скалярное_выражение [,...] ); для вставки одного значения (или по одному значению) в таблицу имя_таблицы , то для строковых триггеров BEFORE и AFTER эта таблица не является изменяющейся. И это единственная ситуация, когда строковый триггер может считывать и модифицировать информацию активизирующей таблицы. Однако при использовании предложения: INSERT INTO имя_таблицы ... предложение_SELECT; таблица имя_таблицы всегда является изменяющейся, несмотря на то, сколько строк возвращается в подзапросе. Рассмотрим достаточно объемный пример, в котором появится мутирующая таблица и ошибки, возникающие при ее появлении, и можно будет подробно обсудить, как избежать этих ошибок. 1. Создадим сначала в схеме COOK таблицу Должности , в которой будут хра- ниться сведения о должностях и должностных окладах сотрудников пан- сионата: CREATE TABLE ДОЛЖНОСТИ ( ИД NUMBER(9) PRIMARY KEY, ДОЛЖНОСТЬ VARCHAR2(25) NOT NULL, ОКЛАД NUMBER(15,2) NOT NULL, НАЧАЛО DATE NOT NULL, КОНЕЦ DATE NOT NULL, СОСТОЯНИЕ VARCHAR2(10) DEFAULT 'Проект' NOT NULL ); COMMENT ON COLUMN ДОЛЖНОСТИ.ИД IS 'Уникальный идентификатор'; COMMENT ON COLUMN ДОЛЖНОСТИ.ДОЛЖНОСТЬ IS 'Наименование должности'; Глава 18. Хранимые процедуры 333 COMMENT ON COLUMN ДОЛЖНОСТИ.ОКЛАД IS 'Значение оплаты в рублях по должности'; COMMENT ON COLUMN ДОЛЖНОСТИ.НАЧАЛО IS 'Начало периода действия оплаты'; COMMENT ON COLUMN ДОЛЖНОСТИ.КОНЕЦ IS 'Актуальный конец периода оплаты'; COMMENT ON COLUMN ДОЛЖНОСТИ.СОСТОЯНИЕ IS 'Состояние (Проект или Утвержден)'; ALTER TABLE ДОЛЖНОСТИ ADD CONSTRAINT "Только Проект или Утвержден" CHECK (СОСТОЯНИЕ IN ('Проект', 'Утвержден')); 2. Заполним эту таблицу данными: insert into ДОЛЖНОСТИ (ИД, ДОЛЖНОСТЬ, ОКЛАД, НАЧАЛО, КОНЕЦ, СОСТОЯНИЕ) values (1, 'Зав_производством', 170, '05.11.1987', '14.11.1987', 'Утвержден'); insert into ДОЛЖНОСТИ (ИД, ДОЛЖНОСТЬ, ОКЛАД, НАЧАЛО, КОНЕЦ, СОСТОЯНИЕ) values (2, 'Директор', 250, '05.01.1987', '04.11.1987', 'Утвержден'); insert into ДОЛЖНОСТИ (ИД, ДОЛЖНОСТЬ, ОКЛАД, НАЧАЛО, КОНЕЦ, СОСТОЯНИЕ) values (3, 'Шеф_повар', 150, '05.11.1987', '09.09.9999', 'Утвержден'); insert into ДОЛЖНОСТИ (ИД, ДОЛЖНОСТЬ, ОКЛАД, НАЧАЛО, КОНЕЦ, СОСТОЯНИЕ) values (4, 'Повар_1_категории', 120, '05.11.1987', '09.09.9999', 'Утвержден'); insert into ДОЛЖНОСТИ (ИД, ДОЛЖНОСТЬ, ОКЛАД, НАЧАЛО, КОНЕЦ, СОСТОЯНИЕ) values (5, 'Повар_2_категории', 100, '05.11.1987', '09.09.9999', 'Утвержден'); insert into ДОЛЖНОСТИ (ИД, ДОЛЖНОСТЬ, ОКЛАД, НАЧАЛО, КОНЕЦ, СОСТОЯНИЕ) values (6, 'Посудомойка', 80, '05.11.1987', '09.09.9999', 'Утвержден'); insert into ДОЛЖНОСТИ (ИД, ДОЛЖНОСТЬ, ОКЛАД, НАЧАЛО, КОНЕЦ, СОСТОЯНИЕ) values (7, 'Уборщица', 80, '05.11.1987', '09.09.9999', 'Утвержден'); insert into ДОЛЖНОСТИ (ИД, ДОЛЖНОСТЬ, ОКЛАД, НАЧАЛО, КОНЕЦ, СОСТОЯНИЕ) values (8, 'Директор', 270, '05.11.1987', '09.09.9999', 'Утвержден'); insert into ДОЛЖНОСТИ (ИД, ДОЛЖНОСТЬ, ОКЛАД, НАЧАЛО, КОНЕЦ, СОСТОЯНИЕ) values (9, 'Зав_производством', 180, '15.11.1987', '09.09.9999', 'Утвержден'); commit; 3. Выполним запрос: select * from должности t ORDER BY должность,начало; Часть VI. Создание приложений на SQL 334 4. Результат запроса: ИД ДОЛЖНОСТЬ ОКЛАД НАЧАЛО КОНЕЦ СОСТОЯНИЕ -- ----------------- ------ ----------- ----------- --------- 2 Директор 250,00 05.01.1987 04.11.1987 Утвержден 8 Директор 270,00 05.11.1987 09.09.9999 Утвержден 1 Зав_производством 170,00 05.11.1987 14.11.1987 Утвержден 9 Зав_производством 180,00 15.11.1987 09.09.9999 Утвержден 4 Повар_1_категории 120,00 05.11.1987 09.09.9999 Утвержден 5 Повар_2_категории 100,00 05.11.1987 09.09.9999 Утвержден 6 Посудомойка 80,00 05.11.1987 09.09.9999 Утвержден 7 Уборщица 80,00 05.11.1987 09.09.9999 Утвержден 3 Шеф_повар 150,00 05.11.1987 09.09.9999 Утвержден Перед выполнением остальных действий создадим последовательность с на- чальным значением 10: CREATE SEQUENCE ДОЛЖН_ПОСЛ START WITH 10 INCREMENT BY 1; А теперь рассмотрим "правила игры": 1. Значение должностного оклада устанавливается с определенной даты ( НАЧАЛО ), а так как не известно, до какой даты оно будет актуальным, то в столбец КОНЕЦ устанавливается запредельная дата: 9 сентября 9999 года. 2. При изменении должностного оклада, с какой-либо даты необходимо вве- сти в таблицу ДОЛЖНОСТИ новую строку с: наименованием этой должности; новым значением оклада; датой, с которой должен выплачиваться этот оклад (эта дата должна быть больше даты начала предшествующего должностного оклада); датой окончания действия этого оклада, равной 09.09.9999; идентификатором этой строки ( ИД ), полученным с использованием соз- данной ранее последовательности ДОЛЖН_ПОСЛ ; состоянием 'Проект' При этом в предложении INSERT достаточно указать только три первых пунк- та, последние пункты должны выполниться триггером и значением по умол- чанию, указанным в описании таблицы ДОЛЖНОСТИ : СОСТОЯНИЕ VARCHAR2(10) DEFAULT 'Проект' NOT NULL CREATE OR REPLACE TRIGGER должн_bir BEFORE INSERT ON должности Глава 18. Хранимые процедуры 335 FOR EACH ROW DECLARE null_val EXCEPTION; -- какое-то из обязательных значений не заполнено nach_nach EXCEPTION; -- новое начало должно быть больше существующего test NUMBER; ID NUMBER; nach DATE; BEGIN -- Если не введены какие-то обязательные значения, выводим сообщение IF :new.должность IS NULL OR :new.оклад IS NULL OR :new.начало IS NULL THEN RAISE null_val; END IF; -- Находим строку с той же должностью и датой конца = '09.09.9999' SELECT COUNT(*) INTO test FROM должности WHERE должность = :NEW.должность AND конец = '09.09.9999'; IF test <> 0 THEN SELECT ид, начало INTO ID, nach FROM должности WHERE должность = :NEW.должность AND конец = '09.09.9999' AND состояние = 'Утвержден'; -- если существующее начало больше нового, выводим сообщение IF nach > :NEW.начало THEN RAISE nach_nach; END IF; END IF; -- Установка даты конца нового оклада :new.конец := '09.09.9999'; -- генерация ид SELECT должн_посл.NEXTVAL INTO :new.ид FROM dual; EXCEPTION WHEN null_val THEN RAISE_APPLICATION_ERROR(-20042,'Не заполнены обязательные поля !'); WHEN nach_nach THEN RAISE_APPLICATION_ERROR(-20045,'Вводимое начало меньше существующего !'); WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20999, 'Какая-то другая ошибка'); END должн_bir; / Проверим работу триггера, введя новый должностной оклад: INSERT INTO ДОЛЖНОСТИ (ДОЛЖНОСТЬ, ОКЛАД, НАЧАЛО) VALUES ('Зав_производством', 181, '15.11.1988'); и получив новую строку в таблице: ИД ДОЛЖНОСТЬ ОКЛАД НАЧАЛО КОНЕЦ СОСТОЯНИЕ -- ----------------- ------ ----------- ----------- --------- 10 Зав_производством 181,00 15.11.1988 09.09.9999 Проект Часть VI. Создание приложений на SQL 336 Строка с состоянием 'Проект' должна быть в дальнейшем либо утверждена, либо удалена. Рассмотрим сначала предложение для удаления введенной строки (строка с ИД=10 ) DELETE должности WHERE ид=10; и триггер, который должен проверить допустимость удаления: CREATE OR REPLACE TRIGGER должн_bdr BEFORE DELETE ON должности FOR EACH ROW DECLARE sost_val EXCEPTION; BEGIN -- Если состояние строки "Утвержден", то удалять нельзя IF :OLD.состояние = 'Утвержден' THEN RAISE sost_val; END IF; EXCEPTION WHEN sost_val THEN RAISE_APPLICATION_ERROR(-20043,'Нельзя удалять утвержденный должностной оклад!'); WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20999,'Какая-то другая ошибка'); END должн_bdr; / Если же требуется утвердить новый должностной оклад, то нужно изменить его состояние на 'Утвержден' . При этом значение даты окончания действия предыдущего оклада по этой должности должно быть заменено датой начала нового оклада, уменьшенной на один день. Изменение даты конца предыду- щего должностного оклада выполняем с помощью триггера: CREATE OR REPLACE TRIGGER должн_bur BEFORE UPDATE OF состояние ON должности FOR EACH ROW DECLARE sost_val EXCEPTION; test NUMBER; ID NUMBER; BEGIN -- Если состояние строки "Утвержден", то изменять нельзя IF :OLD.состояние = 'Утвержден' THEN RAISE sost_val; END IF; -- Ищем строку с той же должностью и датой конца, равной '09.09.9999' SELECT COUNT(*) INTO test FROM должности Глава 18. Хранимые процедуры 337 WHERE должность = :OLD.должность AND конец = '09.09.9999'; IF test <> 0 THEN -- Определяем номер найденной строки SELECT ид INTO ID FROM должности WHERE должность = :OLD.должность AND конец = '09.09.9999' AND состояние = 'Утвержден'; -- Изменяем дату конца найденной строки UPDATE должности SET конец = :OLD.начало-1 WHERE ид = ID; END IF; EXCEPTION WHEN sost_val THEN RAISE_APPLICATION_ERROR(-20044,'Нельзя изменять ут- вержденный должностной оклад!'); END должн_bur; / Теперь можно утвердить должностной оклад: UPDATE должности SET состояние = 'Утвержден' WHERE ид = 10; Результат выполнения этого предложения дал такой результат: ORA-04091: таблица COOK.ДОЛЖНОСТИ изменяется, триггер/функция может не заметить это ORA-06512: на "COOK.ДОЛЖН_BUR", line 11 ORA-04088: ошибка во время выполнения триггера 'COOK.ДОЛЖН_BUR' Вот мы и столкнулись с ошибкой изменяющейся (мутирующей) таблицы (ORA-04091), т. е. попыткой изменить таблицу, с которой связан строковый триггер ДОЛЖН_BUR А нельзя ли строковый триггер заменить табличным, который не подвержен подобным ошибкам? Тем более что для этого достаточно закомментировать в ДОЛЖН_BUR строку FOR EACH ROW Однако при таком изменении триггера и попытки утвердить должностной оклад, возникает новая ошибка: ORA-04082: ссылки на NEW или OLD недопустимы в триггерах уровня таблицы. Проблему можно разрешить с помощью создания вместо ДОЛЖН_BUR двух триггеров — строкового ДОЛЖН_BSUR (BEFORE) и табличного ДОЛЖН_ATUR (AFTER) . В строковом триггере ДОЛЖН_BSUR будут определяться значения :OLD.ид , :OLD.начало , :OLD.должность и :OLD.состояние , но сама таблица ДОЛЖНОСТИ запрашиваться и изменяться не будет. Запрос и изменение будут выполняться в табличном триггере ДОЛЖН_ATUR , где и будут использоваться значения, определенные в строковом триггере. А где же сохранять и как передать найденные значения :OLD.ид , :OLD.начало , :OLD.должность и :OLD.состояние ? Oracle рекомендует исполь- зовать для этого пакет. Поэтому дальнейшее рассмотрение проблемы изме- няющихся таблиц перенесено в разд. 18.5.5. Часть VI. Создание приложений на SQL 338 18.5. Пакеты (модули) 18.5.1. Модули Модуль, или пакет (package) — еще одно средство, пришедшее в PL/SQL из языка программирования Ada. Модуль — это конструкция PL/SQL, которая позволяет хранить связанные объекты в одном месте. Модуль состоит из двух частей: описания и тела. Они хранятся по отдельности в словаре дан- ных. В отличие от процедур и функций, которые могут содержаться локально в блоке или храниться в базе данных, модули могут быть только хранимыми и никогда локальными. Помимо того, что модули позволяют группировать связанные объекты, они полезны еще и тем, что ограничений, налагаемых зависимостями, в них меньше, чем в хранимых подпрограммах. Кроме того, они имеют ряд свойств, улучшающих функционирование системы. В сущности, модуль представляет собой именованный раздел объявлений. Все, что может входить в состав раздела объявлений блока, может входить и в модуль: процедуры, функции, курсоры, типы и переменные. Размещение их в модуле полезно тем, что это позволяет обращаться к ним из других бло- ков PL/SQL, поэтому в модулях можно описывать глобальные переменные PL/SQL (внутри одного сеанса работы с базой данных). 18.5.2. Создание описания пакета Описание спецификации (заголовка) пакета Спецификация (заголовок) пакета(package specification) содержит информа- цию о составе модуля, однако в описание не входит текст процедур. Синтак- сис спецификации пакета имеет вид: CREATE [OR REPLACE] PACKAGE [схема.]имя_пакета [ AUTHID {CURRENT_USER | DEFINER} ] { IS | AS } pl/sql_спецификация_пакета; Здесь: AUTHID CURRENT_USER Служит для того, чтобы указать, что пакет выполняется с привилегиями CURRENT_USER и что внешние названия в предложениях языка соответствуют схеме CURRENT_USER . В противном случае все будет соответствовать схеме, в которой расположен пакет. AUTHID DEFINER Глава 18. Хранимые процедуры 339 Служит для того, чтобы указать, что пакет выполняется с привилегиями вла- дельца схемы, в которой расположен пакет, и что внешние названия в пред- ложениях языка соответствуют этой же схеме. IS | AS Ключевые слова IS | AS идентичны и начинают pl/sql_спецификация_пакета pl/sql_спецификация_пакета Может включать: определение_типа , описание_процедуры , описание_функции , объявление_переменной , объявление_исключителъной_ситуации , объявление_ курсора , объявление_прагмы Элементы пакета (описания процедур и функций, переменные и т. д.) анало- гичны тому, что указывается в разделе объявлений анонимного блока. Для заголовка пакета действуют те же синтаксические правила, что и для раздела объявлений, за исключением объявлений процедур и функций. Перечислим эти правила. Элементы пакета могут указываться в любом порядке. Однако, как и в разделе объявлений, объект должен быть объявлен до того, как на него будут произведены ссылки. Например, если частью предложения WHERE курсора является некоторая переменная, она должна быть объявлена до объявления курсора. Совсем не обязательно, чтобы присутствовали элементы всех видов. К примеру, модуль может состоять только из описаний процедур и функ- ций и не содержать объявлений исключительных ситуаций или типов. Объявления всех процедур и функций должны быть предварительными. В предварительном объявлении (forward declaration) описываются подпро- грамма и ее аргументы (если есть), но не включается программный текст. В этом отличие пакета от раздела объявлений блока, где могут находиться как предварительные объявления, так и текст процедур и функций. Про- граммный текст процедур и функций пакета содержится в теле этого пакета. Описание тела пакета Тело пакета (package body) — это объект словаря данных, хранящийся от- дельно от заголовка пакета. Тело пакета нельзя скомпилировать, если ранее не был успешно скомпилирован заголовок. В теле содержится текст подпро- грамм, предварительно объявленных в заголовке пакета. В нем могут нахо- диться также дополнительные объявления, глобальные для тела пакета, но не видимые в его описании. Часть VI. Создание приложений на SQL 340 Синтаксис тела пакета имеет вид: CREATE [OR REPLACE] PACKAGE BODY [схема.]имя_пакета { IS | AS } pl/sql_тело_пакета; В теле пакета содержится программный текст ( pl/sql_тело_пакета ) для предварительных объявлений, сделанных в заголовке пакета, и могут также находиться дополнительные переменные, курсоры, типы и подпрограммы. На объекты в заголовке, которые не были объявлены предварительно (на- пример, исключение), можно ссылаться в теле пакета непосредственно. Тело пакета не является обязательной его частью. Если в заголовке не указа- ны какие-либо процедуры или функции (а только переменные, курсоры, типы и т. д.), тело можно не создавать. Такой способ полезен для объявления гло- бальных переменных, поскольку все объекты пакета видимы вне его преде- лов (область действия и область видимости элементов пакета обсуждаются в следующем разделе). Любое предварительное объявление в заголовке пакета должно быть раскры- то в его теле. Описание процедуры или функции должно быть таким же и включать в свой состав имя подпрограммы, имена ее параметров и вид ка- ждого параметра. Область действия пакетов и их объектов Любой объект, объявленный в заголовке пакета, находится в области дейст- вия и видим вне этого пакета. При обращении к объекту нужно указать имя пакета. Например, для вызова процедуры p_upd1 , размещенной в пакете pk_долж , надо предварить имя процедуры именем пакета: pk_долж.p_upd1 Внутри тела пакета на объекты, представленные в его заголовке, можно ссы- латься без указания имени пакета. Если процедура объявлена в теле пакета локальной (т. е. она отсутствует в описании заголовка пакета), то область ее действия — только тело пакета. Ее можно вызывать из других процедур тела пакета, но вне тела она невидима. 18.5.3. Изменение описания пакета Для изменения описания пакета используется предложение: ALTER PACKAGE [схема.]имя_пакета COMPILE [DEBUG] [PACKAGE | SPECIFICATION | BODY] Глава 18. Хранимые процедуры 341 [имя_параметра = значение_параметра [имя_параметра = значение_параметра] ... ] [REUSE SETTINGS]; Здесь: COMPILE Ключевое слово перед тем, как указываются объекты перекомпиляции и (или) отладка. PACKAGE Определяет, что нужно перекомпилировать спецификацию и тело пакета, а также всех зависимых объектов. SPECIFICATION Определяет, что нужно перекомпилировать спецификацию. При этом будут лишены законной силы любые местные объекты, которые зависят от специ- фикации, типа процедур, которые называют процедуры или функции в паке- те. Тело пакета также зависит от его спецификации. Если впоследствии дела- ется ссылка на один из этих зависимых объектов (без предварительного их перекомпилирования), то СУБД сама их перекомпилирует. BODY Определяет, что нужно перекомпилировать тело пакета. Перекомпилирова- ние тела пакета не лишает законной силы объекты, которые зависят от спе- цификации пакета. DEBUG Дает указание компилятору генерировать и хранить код для использования отладчиком. Определение этого пункта имеет тот же самый эффект как уста- новка PLSQL_DEBUG=TRUE в compiler_parameters_clause compiler_parameters_clause Используется для определения текущих значений, указанных в перечне па- раметров компилятора PL/SQL. REUSE SETTINGS Не позволяет изменять существующие параметры настройки при перекомпи- ляции. 18.5.4. Удаление пакета Для удаления всего пакета или только его тела ( BODY ) используется син- таксис: DROP PACKAGE [BODY] [схема.]имя_пакета; Часть VI. Создание приложений на SQL 342 18.5.5. Примеры пакетов Продолжим рассмотрение примера разд. 18.4.5. 1. Создадим строковый триггер ДОЛЖН_BSUR , в котором вызывается процеду- ра P_UPD1 пакета PK_ДОЛЖ (см. далее). В нее передаются в качестве вход- ных параметров значения некоторых столбцов обновляемой строки. CREATE OR REPLACE TRIGGER должн_bsur BEFORE UPDATE OF состояние ON должности FOR EACH ROW BEGIN pk_долж.p_upd1(:OLD.ид, :OLD.начало,:OLD.должность,:OLD.состояние); END должн_bsur; / 2. Аналогично создается табличный триггер ДОЛЖН_ATUR , в котором вызыва- ется процедура P_UPD2 того же пакета. CREATE OR REPLACE TRIGGER должн_atur AFTER UPDATE OF состояние ON должности BEGIN pk_долж.p_upd2; END должн_atur; / 3. Теперь создаем спецификацию (заголовок) пакета PK_ДОЛЖ , в которой опи- саны две процедуры, вызываемые в приведенных ранее триггерах: CREATE OR REPLACE PACKAGE pk_долж AS -- Описание структуры пакета процедур для триггеров таблицы ДОЛЖНОСТИ PROCEDURE p_upd1 ( o_ИД ДОЛЖНОСТИ.ИД%TYPE, o_НАЧАЛО ДОЛЖНОСТИ.НАЧАЛО%TYPE, o_ДОЛЖНОСТЬ ДОЛЖНОСТИ.ДОЛЖНОСТЬ%TYPE, o_СОСТОЯНИЕ ДОЛЖНОСТИ.СОСТОЯНИЕ%TYPE ); PROCEDURE p_upd2; END pk_долж; 4. Далее создаем тело этого пакета: CREATE OR REPLACE PACKAGE BODY pk_долж AS -- Тело пакета процедур для триггеров таблицы ДОЛЖНОСТИ. -- Описание переменных, предназначенных для хранения некоторых -- значений строки Глава 18. Хранимые процедуры 343 v_ид должности.ид%TYPE; v_НАЧАЛО ДОЛЖНОСТИ.НАЧАЛО%TYPE; v_ДОЛЖНОСТЬ ДОЛЖНОСТИ.ДОЛЖНОСТЬ%TYPE; v_состояние должности.состояние%TYPE; -- Процедура, запускаемая триггером должн_bsur (BEFORE UPDATE ON -- ДОЛЖНОСТИ FOR EACH ROW). Этот строковый триггер стоит на первом месте -- в последовательности исполнения триггеров, созданных для UPDATE) -- -- Получение значений столбцов обновляемой строки и перепись их -- в глобальные переменные PROCEDURE p_upd1( o_ид должности.ид%TYPE, o_НАЧАЛО ДОЛЖНОСТИ.НАЧАЛО%TYPE, o_ДОЛЖНОСТЬ ДОЛЖНОСТИ.ДОЛЖНОСТЬ%TYPE, o_состояние должности.состояние%TYPE ) IS BEGIN v_ид := o_ид; v_НАЧАЛО := o_НАЧАЛО; v_ДОЛЖНОСТЬ := o_ДОЛЖНОСТЬ; v_состояние := o_состояние; END p_upd1; -- -- Процедура, запускаемая триггером должн_atur (AFTER UPDATE ON -- ДОЛЖНОСТИ). Этот табличный триггер стоит на втором месте -- в последовательности исполнения триггеров, созданных для UPDATE) -- -- Анализ обновляемой строки и, если необходимо, принятие решения -- об отклонении обновления с выдачей сообщения о причине. PROCEDURE p_upd2 IS -- Описание локальных переменных и исключений ID NUMBER; test NUMBER; -- Рабочая переменная sost_val EXCEPTION; -- Нельзя изменять утвержденный должностной оклад BEGIN -- Если состояние строки "Утвержден", то изменять нельзя IF v_состояние = 'Утвержден' THEN RAISE sost_val; Часть VI. Создание приложений на SQL 344 END IF; -- Ищем строку с той же должностью и датой конца, равной '09.09.9999' SELECT COUNT(*) INTO test FROM должности WHERE должность = v_ДОЛЖНОСТЬ AND конец = '09.09.9999' AND состояние = 'Утвержден' AND ид <> v_ид; IF test <> 0 THEN -- Определяем номер найденной строки SELECT ид INTO ID FROM должности WHERE должность = v_ДОЛЖНОСТЬ AND конец = '09.09.9999' AND состояние = 'Утвержден' AND ид <> v_ид; -- Изменяем дату конца найденной строки UPDATE должности SET конец = v_НАЧАЛО-1 WHERE ид = ID; END IF; EXCEPTION WHEN sost_val THEN RAISE_APPLICATION_ERROR (-20044,'Нельзя изменять утвержденный должностной оклад!'); END p_upd2; END pk_долж; / 5. Снова попробуем утвердить должностной оклад: UPDATE должности SET состояние = 'Утвержден' WHERE ид = 10; 6. Результат выполнения этого предложения: 1 row updated 7. Проверка результата обновления, выполненная с помощью предложения select * from должности t ORDER BY должность,начало; ИД ДОЛЖНОСТЬ ОКЛАД НАЧАЛО КОНЕЦ СОСТОЯНИЕ -- ----------------- ------ ----------- ----------- ---------- 2 Директор 250,00 05.01.1987 04.11.1987 Утвержден 8 Директор 270,00 05.11.1987 09.09.9999 Утвержден 1 Зав_производством 170,00 05.11.1987 14.11.1987 Утвержден 9 Зав_производством 180,00 15.11.1987 14.12.1988 Утвержден 10 Зав_производством 185,00 15.12.1988 09.09.9999 Утвержден 4 Повар_1_категории 120,00 05.11.1987 09.09.9999 Утвержден 5 Повар_2_категории 100,00 05.11.1987 09.09.9999 Утвержден 6 Посудомойка 80,00 05.11.1987 09.09.9999 Утвержден 7 Уборщица 80,00 05.11.1987 09.09.9999 Утвержден 3 Шеф_повар 150,00 05.11.1987 09.09.9999 Утвержден Глава 18. Хранимые процедуры 345 18.6. Встроенные пакеты PL/SQL PL/SQL предлагает целый ряд дополнительных функций, которые доступны в поставляемых им пакетах. Эти пакеты принадлежат пользователю SYS , од- нако для них созданы общие синонимы, поэтому пакеты можно вызывать, не используя префикс SYS перед именем пакета. Для вызова процедур и функ- ций этих пакетов пользователь, который не является SYS , должен иметь пол- номочие EXECUTE на конкретный пакет. Дополнительную информацию можно найти в документации Oracle и литературе [10]. Далее кратко описываются некоторые из почти тысячи встроенных пакетов (модулей), доступных для использования в PL/SQL. DBMS_ALERT Применяется для обмена сообщениями между сеансами, соединенными с од- ной и той же базой данных. Оповещения (alerts) синхронны, т. е. они посы- лаются при завершении транзакций. Оповещения отправляет процедура SIGNAL , а получают процедуры WAITONE и WAITANY . Чтобы получить оповеще- ние, сеанс должен быть зарегистрирован с помощью процедуры REGISTER Информацию об оповещениях можно найти в таблице словаря данных dbms_alert_info DBMS_APPLICATION_INFO Применяется для регистрации информации (info) о конкретной программе в системной таблице v$session . С помощью этой информации можно опре- делить, какие приложения (applications) функционируют в данный момент и какие действия они выполняют. В частности, DBMS_APPLICATION_INFO можно использовать для считывания и модификации столбцов module , action и client_info таблицы v$session текущего сеанса. В столбце module хранится имя приложения. Например, SQL*Plus помещает в этот столбец значение 'SQL*Plus' . В столбце action содержится фрагмент приложения, выполняющийся в данный момент. Например, можно задать определенное значение action перед вводом фрагмента программы Рго*С. В столбец client_info можно вносить произвольные строки символов. DBMS_APPLICATION_INFO может применяться также (с помощью процедуры SET_SESSION_LONGOPS ) для обновления таблицы v$session_longop , исполь- зуемой для указания статуса долговременных операций. DBMS_DDL Предоставляет PL/SQL-эквиваленты ряда полезных команд DDL, которые нельзя использовать в PL/SQL непосредственно. Для выполнения таких ко- манд можно применять динамический SQL, но в модуле DBMS_DDL они имеют Часть VI. Создание приложений на SQL 346 другой синтаксис. Среди процедур в DBMS_DLL имеются ALTER_COMPILE , служащая для компиляции пакетов, триггеров, процедур и функций, и ANALYZE_OBJECT , применяемая для анализа таблиц, кластеров и индексов. DBMS_OUTPUT DBMS_OUTPUT (в комбинации с SQL*Plus или SQLDEVELOPER ) предоставляет ограниченные возможности вывода для PL/SQL. Это удобно при тестировании и отладке кода PL/SQL. DBMS_OUTPUT не пред- назначен для написания отчетов — для этого лучше подходит утилита Oracle Reports. DBMS_SQL DBMS_SQL реализует динамический PL/SQL. С его помощью можно создавать операторы SQL и блоки PL/SQL во время выполнения и выполнять их. Кроме того, DBMS_SQL можно использовать для выполнения в PL/SQL операторов DDL. Oracle предлагает внутренний динамический SQL (см. разд. 17.8), бо- лее быстрый и простой, чем DBMS_SQL , использующийся в ранних версиях Oracle. DBMS_STANDARD и STANDARD Совместно DBMS_STANDARD и STANDARD реализуют все встроенные функции PL/SQL. В отличие от других модулей, имена входящих в них подпрограмм не нужно предварять именем модуля. DBMS_STATS Позволяет просматривать и модифицировать статистические показатели оп- тимизатора, хранящиеся в словаре данных или в определенной пользовате- лем таблице. Оптимизатор по стоимости выполнения будет использовать ста- тистику только из словаря данных. |