Книги, научные публикации Pages:     | 1 |   ...   | 3 | 4 | 5 | 6 | 7 |

ОФИЦИАЛЬНОЕ АВТОРИЗОВАННОЕ ИЗДАНИЕ ORACLE PRESSЩ ЭКСКЛЮЗИВНЫЕ ПРАВА ПРИНАДЛЕЖАТ ИЗДАТЕЛЬСТВУ OSBORNE Программирование на языке PL/SQL Разработка эффективных приложений )мощью PL/SQL OFFICIAL Х Oracle ...

-- [ Страница 5 ] --

7 / Function created SQL> - Несколько допустимых вызовов непосредственно из SQL SQL> CALL CallProd called with Hello!

Call completed.

SQL> CALL CallProd called with Call completed.

SQL> VARIABLE v_0utput SQL> CALL INTO :v_0utput;

CallFunc called with Hello!

Call completed.

SQL> PRINT v_0utput V_OUTPUT Hello!

SQL> CALL CallProc2(:v_Output);

CallProc2 called with Hello!

Call completed.

Создание процедур, функций и модулей SQL> PRINT V_OUTPUT Hello! returned!

SQL -- Это не допустимо SQL> BEGIN 2 CALL 3 END;

4 / CALL ERROR at line ORA-06550: line 2, column 8:

Encountered the symbol when expecting one of the The symbol was substituted for to continue.

SQL> -- А это допустимо SQL> DECLARE 3 BEGIN 4 EXECUTE IMMEDIATE CallProcK "Hello from 5 EXECUTE IMMEDIATE 6 from INTO 7 USING OUT v_Result;

8 END;

9 / CallFunc called with Hello from PL/SQL CallProc called with Hello PL/SQL PL/SQL procedure sucessfully completed.

Этот пример показывает, что:

Х CALL является оператором SQL. Его нельзя использовать внутри блока PL/SQL, но можно использовать в динамическом SQL (см. гла ву 12). (Внутри блока PL/SQL можно вызывать подпрограмму с по мощью синтаксиса PL/SQL.) Х Круглые скобки всегда должны присутствовать, даже если подпрог рамма не имеет аргументов (или имеет используемые по умолчанию значения для всех т Предложение INTO применяется только для выходных перемен ных функций. Параметр IN OUT или OUT определяется как часть списка аргументов.

Совет Версии SQL ранее не принимают оператор CALL как допустимую инструкцию SQL. Можно использовать вместо него команду Plus EXECUTE (см. главу 2).

340 ГЛАВА Процедуры и функции Свойства процедур и функций во многом схожи:

Х Могут возвращать более одного значения, используя параметры OUT.

Х Состоят из раздела объявлений, выполняемого раздела и раздела исключительных ситуаций.

Х Могут принимать значения по умолчанию.

Х Могут вызываться при помощи позиционного или именного пред ставления.

Х Могут принимать параметры (в г и Так когда же использовать функцию, а когда процедуру? Это обычно зависит от ожидаемого числа возвращаемых значений и от того, для чего они будут применяться. Принято руководствоваться следующим прави лом: если возвращается более одного значения, то следует использовать процедуру, а если одно Ч функцию. Функции могут иметь параметры OUT (т.е. возвращать несколько значений), однако это считается некор ректным стилем программирования. Функции можно вызывать также и из SQL-операторов (см. главу 10).

Модули (package) Ч еще одно средство, пришедшее в PL/SQL из языка про граммирования Ada. Модуль Ч это конструкция PL/SQL, которая позволя ет хранить связанные объекты в одном месте. Модуль состоит из двух частей: описания и тела. Они хранятся по отдельности в словаре данных.

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

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

Размещение их в модуле полезно тем, что это позволяет обращаться к ним из других блоков PL/SQL, поэтому в модулях можно описывать гло бальные переменные PL/SQL (внутри одного сеанса работы с базой Описание модуля В описании, или модуля (package specification), называемом также заголовком модуля (package header), содержится информация о со ставе модуля, однако в описание не входит текст процедур. Рассмотрим пример:

Создание процедур, функций и -- Этот пример содержится в файле CREATE OR REPLACE PACKAGE>

PROCEDURE IN p_Department IN IN - Удаляет указанного студента из указанной группы.

IN IN p_Course IN - Исключительная ситуация, устанавливаемая процедурой RemoveStudent.

EXCEPTION;

- Табличный тип, используемый для хранения информации о студентах.

TYPE t_StudentIDTable IS TABLE OF students. id%TYPE INDEX BY BINARY_INTEGER;

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

PROCEDURE IN p_Course IN p_IDs OUT t_StudentIDTable, IN OUT END>

В модуле>

CREATE [OR REPLACE] PACKAGE | \ \ \ \ \ \ END Элементы модуля (описания процедур и функций, переменные и т.д.) аналогичны что указывается в разделе объявлений анонимного бло ка. Для заголовка модуля действуют те же синтаксические правила, что и для раздела за исключением объявлений процедур и функ ций. Перечислим эти правила:

Х Элементы модуля могут указываться в любом порядке. Однако, как и в разделе объявлений, объект должен быть объявлен до того, как на него будут произведены ссылки. Например, если частью предложе ния WHERE курсора является некоторая переменная, она должна быть объявлена до объявления курсора.

342 Х Совсем не обязательно, чтобы присутствовали элементы всех ви дов. К примеру, модуль может состоять только из описаний проце дур и функций и не содержать объявлений исключительных ситуаций или типов.

Х Объявления всех процедур и функций должны быть предваритель ными. В объявлении (forward declaration) описывают ся подпрограмма и ее аргументы (если есть), но не включается программный текст (см. главу 10). В этом отличие модуля от раздела объявлений блока, где могут находиться как предварительные объ явления, так и текст процедур и функций. Программный текст про цедур и функций модуля содержится в теле этого модуля.

Тело модуля Тело модуля (package body) Ч это объект словаря данных, хранящийся от дельно от заголовка модуля. Тело модуля нельзя скомпилировать, если ра нее не был успешно скомпилирован заголовок. В теле содержится текст подпрограмм, предварительно объявленных в заголовке модуля. В нем могут находиться также дополнительные объявления, глобальные для тела модуля, но не видимые в его описании. Тело модуля>

ХХ- Этот пример содержится в файле CREATE OR REPLACE PACKAGE BODY>

PROCEDURE IN IN p_Course IN IS BEGIN INSERT INTO (student_id, course) VALUES END - Удаляет указанного студента из указанной группы.

PROCEDURE RemoveStudent( IN students.

IN p_Course IN IS BEGIN DELETE FROM registered_students WHERE student_id = p_StudentID AND department = AND course = - Проверим, успешно ли была выполнена операция DELETE. Если - указанные строки не найдены, устанавливается ошибка.

IF SQL%NOTFOUND THEN RAISE e_StudentNotRegistered;

END IF;

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

PROCEDURE IN p_Course IN p_IDs OUT IN OUT BINARY_INTEGER) IS - Локальный курсор для выбора зарегистрированных студентов.

CURSOR c_RegisteredStudents IS SELECT student_id FROM WHERE department = AND course = p_Course;

BEGIN /* - индекс таблицы. Он будет начинаться с 0 и увеличиваться с каждым циклом выборки.

В конце цикла в индексе будет содержаться число считанных строк, т.е. число строк, возвращаемых в p_IDs. */ 0;

OPEN LOOP FETCH c_RegisteredStudents INTO EXIT WHEN := + 1;

END LOOP;

END>

END>

В теле модуля содержится программный текст для предварительных объявлений, сделанных в заголовке и могут также находиться до полнительные переменные, курсоры, типы и подпрограммы. На объекты в заголовке, которые не были объявлены предварительно (например, исключение e_StudentNotRegistered), можно ссылаться в теле модуля Тело модуля не является обязательной его частью. Если в заголовке не указаны какие-либо процедуры или функции (а только кур соры, типы и т.д.), тело можно не создавать. Такой способ полезен для объявления глобальных переменных, поскольку все объекты модуля ви димы вне его пределов (область действия и область видимости элементов модуля обсуждаются в следующем Любое предварительное объявление в заголовке модуля должно быть раскрыто в его теле. Описание процедуры или функции должно быть таким же и включать в свой состав имя подпрограммы, имена ее параметров и вид каждого параметра. Например, приведенный ниже заголовок не вует телу модуля, так как в теле используется список параметров функции отличный от списка параметров, указанного в заголовке.

344 -- Этот пример содержится в файле CREATE OR REPLACE PACKAGE PackageA AS FUNCTION IN NUMBER, IN DATE) RETURN VARCHAR2;

END PackageA;

CREATE OR REPLACE PACKAGE BODY PackageA AS FUNCTION IN CHAR) RETURN VARCHAR2;

END PackageA;

Если попытаться создать PackageA, то будут выданы сообщения об ошибках:

Г] A subprogram body be defined for the forward declaration of FUNCTIONA.

(тело подпрограммы должно быть описано так, как это сделано при предварительном объявлении FUNCTIONA) PLS-00323: subprogram or cursor is declared in a package specification and must be defined in the package (подпрограмма (курсор) объявлена в описании модуля и должна быть определена в теле модуля) Модули и область действия Любой объект, объявленный в заголовке модуля, находится в области вия и видим вне этого модуля. При обращении к объекту нужно указать имя модуля. Например, можно вызвать процедуру>

из блока PL/SQL:

BEGIN 101);

END;

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

Кроме того, в модуле можно применять типы данных, определяемые пользователем. Например, чтобы вызвать необходимо объя вить переменную типа>

Этот пример содержится в файле DECLARE v_HistoryStudents t_StudentIDTable;

20;

BEGIN - Заполним таблицу PL/SQL первыми 20 студентами группы History Создание процедур, функций и 101, v_HistoryStudents, - Внесем этих студентов в таблицу FOR v_LoopCounter IN LOOP INSERT INTO char_col) VALUES History END LOOP;

END;

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

Область действия содержащихся в теле модуля Итак, и обновляют таблицу registered_students, но этого недостаточно. Чтобы отразить све дения о только что добавленном (или удаленном) студенте, нужно обно вить еще и таблицы students и>

3 пример содержится в файле CREATE OR REPLACE PACKAGE BODY>

BEGIN - Сначала определим SELECT num_credits INTO FROM>

IF (p_Add) THEN -- Добавим NumCredits к учебной загрузке студента.

UPDATE STUDENTS SET current_credits = + WHERE ID = p_StudentID;

346 ГЛАВА - И увеличим UPDATE>

UPDATE STUDENTS SET current_credits = current_credits WHERE ID = - И уменьшим UPDATE>

END Х- Добавляет нового студента в указанную группу.

PROCEDURE IN IN IN IS BEGIN INSERT INTO (student_id, department, course) VALUES (p_StudentIO, p_Course);

UpdateStudentsAndClasses(TRUE, p_StudentID, END AddStudent;

- Удаляет указанного студента из указанной группы.

PROCEDURE IN IN p_Course IN IS BEGIN DELETE FROM WHERE student_id p_StudentID AND department = AND course = p_Course;

- Проверим, успешно ли была выполнена операция DELETE. Если - указанные строки не найдены, устанавливается ошибка.

IF THEN RAISE e_StudentNotRegistered;

ND IF;

p_StudentID, END END Создание процедур, функций и модулей Процедура объявлена в теле модуля локаль ной, и область ее действия, таким образом, только тело модуля. Следова тельно, ее можно вызывать из других процедур тела модуля (а именно из и но вне тела она невидима.

Перегрузка модульных подпрограмм Внутри модуля процедуры и функции могут быть перегружены (overloaded).

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

Этот пример в файле CREATE OR REPLACE PACKAGE>

PROCEDURE IN IN p_Course - Также добавляет нового студента, но не по - идентификатору этого студента, а по его имени и фамилии.

PROCEDURE IN IN IN p_Course IN END>

CREATE OR REPLACE PACKAGE BODY>

PROCEDURE IN IN p_Course IN IS BEGIN INSERT INTO registered_students (student_id, department, course) VALUES (p_StudentID, p_Course);

END AddStudent;

Добавляет нового студента не по идентификатору, а по имени.

PROCEDURE IN IN IN p_Course IN IS v_StudentID students.

BEGIN /* Сначала получим нужный идентификатор в таблице students. */ SELECT ID INTO v StudentID 348 ГЛАВА FROM students WHERE first_name = AND = p_LastName;

- Теперь добавим студента по его идентификатору.

INSERT INTO department, course) VALUES p_Course);

END END>

Теперь можно внести студента в группу Music 410 так:

BEGIN 410);

END;

или так:

BEGIN 410);

END;

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

Х Нельзя перегружать две подпрограммы, если их параметры отлича ются только именами или видами. К примеру, следующие две проце дуры не являются перегружаемыми:

PROCEDURE IN NUMBER);

PROCEDURE OUT Х Нельзя перегружать две функции, отличающиеся лишь типами воз вращаемых ими данных. К примеру, следующие две функции не яв ляются перегружаемыми:

FUNCTION OverloadMeToo RETURN DATE;

FUNCTION OverloadMeToo RETURN NUMBER;

Х Наконец, типы параметров перегружаемых функций должны при надлежать различным семействам типов. Например, типы CHAR и входят в одно и то же семейство, поэтому не являются перегружаемыми следующие процедуры:

PROCEDURE OverloadChar(p_TheParameter IN CHAR);

PROCEDURE IN VARCHAR2);

Создание процедур, функций и модулей Внимание На самом деле компилятор PL/SQL разрешает создавать модули, в которые входят подпрограммы, нарушающие приведенные выше ограничения. Однако во время выполнения программы ссылки на эти подпрограммы не смогут быть реализованы и всегда будет выдаваться сообщение об ошибке Too many declarations of match this call (этому вызову соответствует слишком много объявлений Объектные типы и перегрузка 1 Модульные подпрограммы можно перегружать и на основе пользователь I ских объектных типов. Для примера предположим, что создаются два объектных типа:

Этот пример содержится в файле CREATE OR REPLACE TYPE t1 AS OBJECT ( f NUMBER );

CREATE OR REPLACE TYPE t2 AS OBJECT ( f NUMBER Теперь создадим модуль и его тело, содержащее процедуры, которые перегружаются в зависимости от объектного типа параметра:

Г) Ч Этот пример содержится в файле CREATE OR REPLACE PACKAGE Overload AS PROCEDURE IN t1);

PROCEDURE IN t2);

END Overload;

CREATE OR REPLACE PACKAGE BODY Overload AS PROCEDURE IN t1) IS BEGIN || END Proc;

PROCEDURE IN t2) IS BEGIN || END Proc;

END Overload;

Из приведенного ниже примера следует, что нужная процедура вызы вается исходя из типа аргумента:

Г) Ч Этот пример содержится в файле SQL> DECLARE 2 t 3 t2 t2(2);

350 4 BEGIN 8 / Proc(t1): Proc(t2): PL/SQL procedure completed.

Более подробно об объектных типах и их использовании рассказыва ется в главах 12 и 13.

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

это гарантирует, что два сеан са, выполняющие подпрограммы одного и того же модуля, будут использо вать различные области памяти.

Во многих случаях при создании экземпляра модуля требуется запус кать код инициализации. Для этого к телу модуля нужно добавить раздел инициализации, разместив его после всех объектов:

CREATE OR REPLACE PACKAGE BODY | BEGIN END где Ч это имя модуля, а Ч запускаемый код.

Ниже приведен пример модуля, реализующего функцию генерации слу чайных чисел.

Этот пример содержится в файле CREATE OR REPLACE PACKAGE Random AS - Генератор случайных чисел. Применяется тот же алгоритм, что и в -- функции rand() языка С.

Используется для изменения исходного значения (seed). Из одного - и того же значения будут генерироваться одинаковые - последовательности случайных чисел.

ChangeSeed(p_NewSeed IN NUMBER);

- Возвращает случайное целое число в диапазоне от 1 до 32767.

FUNCTION Rand RETURN NUMBER;

- Аналогична функции Rand, но имеет процедурный интерфейс.

PROCEDURE OUT NUMBER);

- Возвращает случайное целое число в диапазоне от 1 до p_MaxVal.

Создание процедур, функций и модулей FUNCTION IN NUMBER) RETURN NUMBER;

- Аналогична функции RandMax, но имеет процедурный интерфейс.

PROCEDURE NUMBER, IN NUMBER);

END Random;

CREATE OR REPLACE PACKAGE BODY Random AS /* Используется для вычисления следующего числа. */ CONSTANT NUMBER := 22695477;

v_Increment CONSTANT NUMBER := 1;

/* Исходное значение, используемое для генерирования последовательности случайных чисел. */ v_Seed number := 1;

PROCEDURE ChangeSeed(p_NewSeed IN NUMBER) IS BEGIN v_Seed p_NewSeed;

END ChangeSeed;

FUNCTION Rand RETURN NUMBER IS BEGIN v_Seed := * v_Seed + ( RETURN 16), 32767);

END Rand;

PROCEDURE NUMBER) IS BEGIN - Вызовем Rand и возвратим значение.

:= Rand;

END GetRand;

FUNCTION RandMax(p_MaxVal IN NUMBER) RETURN NUMBER IS BEGIN RETURN MOD(Rand, p_MaxVal) + 1;

END RandMax;

PROCEDURE OUT NUMBER, p_MaxVal IN NUMBER) IS BEGIN - Вызовем RandMax и возвратим значение.

:= RandMax(p_MaxVal);

END GetRandMax;

BEGIN /* Инициализация модуля. Инициализируем исходное значение текущим временем в секундах. */ END Random;

352 Для получения случайного числа можно просто вызвать Random. Rand.

Последовательность случайных чисел зависит от исходного значения:

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

Итоги В этой главе рассматривались именованные блоки PL/SQL трех типов:

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

В главе обсуждается четвертый тип именованных блоков Ч триггеры базы данных.

ГЛАВА и модулей 354 предыдущей главе обсуждались вопросы создания процедур, модулей В и функций. В этой главе рассматриваются некоторые их свойства, в том числе отличие хранимых подпрограмм от локальных, взаимодействие хра нимых подпрограмм со словарем данных и вызов хранимых подпрограмм из Исследуется также ряд новых характеристик храни мых подпрограмм в и В главе 11 будут представлены триггеры.

Размещение подпрограмм Подпрограммы можно хранить в словаре данных. Сначала подпрограмма создается при помощи команды CREATE OR REPLACE, а затем вызывает ся из другого блока PL/SQL. Кроме того, подпрограмму можно описать в разделе объявлений блока;

в этом случае она называется подпрог раммой (local subprogram). Модули должны храниться в словаре данных и не могут быть локальными.

Хранимые подпрограммы и словарь данных Если подпрограмма создается при помощи команды CREATE OR REPLACE, она сохраняется в словаре данных, причем хранится не только исходный текст подпрограммы, но и скомпилированная форма, которая называется (p-code). В р- коде содержатся все обработанные ссыл ки подпрограммы, а исходный текст преобразуется в вид, удобный для чтения системой поддержки PL/SQL. При вызове подпрограммы р-код считывается с диска и выполняется. Считанный р-код сохраняется в раз деляемой области пула системной глобальной области (SGA), где при не обходимости к нему могут обращаться несколько пользователей. Как и все другие компоненты разделяемого пула, р-код удаляется из него с тече нием времени в соответствии с LRU.

Р-код аналогичен объектному коду, генерируемому компиляторами дру гих языков третьего поколения, или байт-коду Java, который считывается исполняющей системой Java. В р-коде содержатся обработанные ссылки на объекты (это свойство раннего связывания), поэтому выполнение р-кода является сравнительно недорогой (нересурсоемкой) операцией.

' Внимание позволяет компилировать подпрограмму в код операционной системы, а не в р-код (см. ниже).

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

Рассмотрим следующую простую процедуру:

CREATE OR REPLACE PROCEDURE Simple AS NUMBER;

BEGIN v_Counter 7;

END Simple;

После создания процедуры в представлении можно уз нать о том, что она достоверна (valid), а в представлении Ч ка ков ее исходный текст. В нет сообщений, так как процедура была успешно скомпилирована (рис. 10.1).

File Edit Options Help SQL> SELECT status 2 FROM WHERE OBJECT_TVPE SIMPLE PROCEDURE SQL> SELECT text FROM 2 name = ORDER BV line;

TEXT PROCEDURE Simple AS NUMBER;

BEGIN := 7;

END SQL> SELECT line, position, text 2 FROM 3 WHERE name ORDER BV sequence;

no rows selected Рис. Представления словаря данных после успешной компиляции Теперь изменим текст процедуры Simple так, чтобы возникла ошибка при ее компиляции (пропустим точку с запятой):

CREATE OR REPLACE PROCEDURE Simple AS NUMBER;

BEGIN 356 ГЛАВА v_Counter := END Simple;

Проанализируем те же три представления словаря данных (рис. 10.2).

В по-прежнему содержится исходный текст процедуры, одна ко в ее состояние имеет значение INVALID (недостоверное), a user_errors показывает ошибку компиляции SQL-Plus Edit flptions Help SELECT status 2 FROM user_objects WHERE STATUS SIMPLE PROCEDURE INUALID sqL> SELECT text 2 WHERE - ORDER TEXT PROCEDURE BEGIN :- END SQL> SELECT line, text 2 FROM 3 1 ORDER BY sequence;

LINE TEXT 1 Encountered the "END" when expecting one OF the following:

nod not an exponent () <> or or <- <> and or like between is null is not II is dangling The was substituted For "END" to continue.

..

Рис. 10.2. Представления словаря данных после неуспешной компиляции Совет Б можно обращаться к и форматировать результаты для удобства чтения при помощи команды SHOW ERRORS (показать ошибки). Она возвращает информацию об ошибках для последнего созданного объекта. Использовать SHOW ERRORS можно после получения сообщения "Warning:

Procedure created with compilation Процедура создана с ошибками Более подробно об этом рассказывается в главе 2.

Недостоверные хранимые подпрограммы заносятся в базу данных. Одна ко до устранения ошибок их нельзя вызывать. При вызове недостоверной процедуры возвращается ошибка PLS-905:

Использование процедур, функций и модулей Г) SQL> BEGIN Simple;

END;

2 / BEGIN Simple;

END;

ж ERROR at line ORA-06550: line column object is invalid line column 7:

Statement ignored Словарь данных более подробно рассматривается в приложении С.

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

позволяет компилировать PL/SQL-код в код системы. При этом создается разделяемая библиотека, которая выполняется Oracle в фоновом режиме. Для осуществления такой компиляции вы должны уста новить на своем компьютере компилятор С. будет генерировать код С, который затем будет компилироваться в библиотеку.

Более подробную информацию можно получить в документации Oracle.

Локальные подпрограммы Локальная подпрограмма, описываемая в разделе объявлений блока PL/SQL, приведена в следующем примере:

Г] -- Этот пример содержится в файле SQL> DECLARE 2 CURSOR IS 3 SELECT 4 FROM students;

6 VARCHAR2(50);

8 /* Функция возвращает конкатенированные (связанные) имя и 9 фамилию, разделенные пробелом. */ 10 FUNCTION IN VARCHAR2, 11 IN VARCHAR2) 12 RETURN VARCHAR2 IS 13 BEGIN 14 RETURN II ' ' II p_LastName;

15 END 17 -- Начало основного блока.

358 ГЛАВА 18 BEGIN 19 FOR IN LOOP 24 END LOOP;

25 END;

26 / Scott Smith Margaret Mason Joanne Manish Patrick Poll Timothy Taller Barbara Blues David Dinsmore Ester Elegant Rose Riznit Rita Razmataz Shay Shariatpanahy PL/SQL procedure Функция FormatName описана в разделе объявлений блока. Имя функ ции является идентификатором PL/SQL и поэтому подчиняется тем же самым правилам по ограничению области действия и области видимости, что и другие идентификаторы PL/SQL. Другими словами, она видима то лько в том блоке, в котором описана, а область ее действия Ч от точки ее объявления до конца блока. Из другого блока вызвать FormatName нельзя, поскольку эта функция в другом блоке невидима.

Локальные подпрограммы в хранимых подпрограммах Локальную подпрограмму можно описать в разделе объявлений храни мой подпрограммы, как показано в приведенном ниже примере. В этом случае функцию FormatName можно вызвать только из StoredProc, посколь ку область ее действия ограничена:

Г) Ч Этот пример содержится в файле CREATE OR REPLACE PROCEDURE StoredProc AS /* Локальные курсора, переменной и функции */ CURSOR IS SELECT FROM students;

/* Функция возвращает конкатенированные имя и фамилию, разделенные пробелом. */ FUNCTION IN VARCHAR2, IN RETURN VARCHAR2 IS Использование функций и модулей BEGIN RETURN || ' ' II END - Начало основного блока BEGIN FOR IN LOOP := END LOOP;

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

-- Этот пример содержится в файле BEGIN 2 StoredProc;

3 END;

4 / Scott Smith Margaret Mason Joanne Junebug Manish Murgatroid Patrick Poll Timothy Taller Barbara Blues David Ester Elegant Rose Riznit Rita Shay Shariatpanahy PL/SQL procedure succesfully completed.

Размещение локальных подпрограмм Любую локальную подпрограмму необходимо описывать в конце раздела объявлений. Если перенести FormatName выше описания курсора то будет выдано сообщение об ошибке:

Х- Этот пример содержится в файле SQL> DECLARE 2 /* Сначала объявим FormatName. Это к ошибке 3 компиляции, так как все другие объявления должны быть 4 сделаны до объявления локальных подпрограмм. */ 5 FUNCTION IN VARCHAR2, 6 IN VARCHAR2) 7 RETURN IS 8 BEGIN 9 RETURN | ' ' |l 10 Е ND 12 CURSOR IS 13 last_name 14 FROM students;

17 - Начало основного блока.

18 BEGIN 19 NULL;

20 END;

21 / CURSOR IS Х ERROR at line 12:

ORA-06550: line 12, column 3:

Encountered the symbol "CURSOR" when expecting one of the begin function package pragma procedure form Предварительное объявление Имена локальных подпрограмм PL/SQL являются идентификаторами, поэтому они должны быть объявлены до их использования. Обычно это не вызывает проблем, но когда подпрограммы ссылаются друг на друга, возникают трудности. Рассмотрим пример:

Этот пример содержится в файле SQL> DECLARE 2 BINARY_INTEGER := 5;

3 - Локальная процедура А. Обратите внимание на то, что в тексте 4 - процедуры А вызывается процедура В.

5 PROCEDURE IN OUT BINARY_INTEGER) IS 6 BEGIN 8 IF p_Counter > 0 THEN 9 B(p_Counter);

10 ;

= p_Counter - 1;

11 END IF;

12 END A;

13 - Локальная процедура В. Обратите внимание на то, что в тексте 14 - процедуры В вызывается процедура А.

15 PROCEDURE B(p_Counter IN OUT IS 16 BEGIN p_Counter | 18 p_Counter := p_Counter 20 END B;

21 BEGIN 22 B(v_TempVal);

23 END;

Использование функций и 24 / DECLARE ERROR at line ORA-06550: line 9, column 7:

identifier must be declared ORA-06550: 9, column 7:

PL/SQL: Statement ignored Эту программу скомпилировать невозможно. Процедура А вызывает процедуру В, поэтому В должна быть объявлена раньше А. В свою очередь процедура В вызывает процедуру А, поэтому А должна быть объявлена ра ньше В. Одновременно эти условия не могут быть выполнены. Для того чтобы исправить сложившуюся ситуацию, можно воспользоваться пред варительным объявлением (forward declaration), в котором указывается имя процедуры и формальные параметры. Это позволяет про цедуры, ссылающиеся друг на друга. Предварительное объявление приме няется также в заголовках модулей. Приведем пример:

Этот пример содержится в файле DECLARE BINARY_INTEGER 5;

- Предварительное объявление процедуры В.

PROCEDURE IN OUT BINARY_INTEGER);

PROCEDURE A(p_Counter IN OUT BINARY_INTEGER) IS BEGIN IF > 0 THEN p_Counter := - 1;

END IF;

END A;

PROCEDURE IN OUT BINARY_INTEGER) IS BEGIN p_Counter p_Counter := - 1;

A(p_Counter);

END BEGIN END;

Результат выполнения этого блока таков:

А(4) В(4) А(3) В(3) 362 ГЛАВА А(2) В(2) А(0) Перегрузка локальных подпрограмм Как показано в главе 9, подпрограммы, объявленные в модулях, можно перегружать. Это справедливо и для локальных подпрограмм, например:

Г] Ч Этот пример содержится в файле DECLARE Х- Две перегружаемые локальные процедуры PROCEDURE IN NUMBER) IS BEGIN version 1, = |l END LocalProc;

PROCEDURE IN VARCHAR2) IS BEGIN version 2, = ' II END LocalProc;

BEGIN - вариант LocalProc(12345);

- Вызовем вариант 2.

END;

Результат выполнения этого примера таков:

In version 1, = In version 2, p_Parameter1 = abcdef Сравнение хранимых и локальных подпрограмм Хранимые подпрограммы во многом отличаются от локальных. Когда же использовать те или другие? Автор предпочитает работать с хранимыми подпрограммами, помещая их в модули. Велика вероятность того, что со здаваемую подпрограмму придется вызывать из нескольких блоков, для чего она должна храниться в базе данных. На выбор типа подпрограмм оказывают влияние также их размеры и сложность. Единственный вид процедур и функций, которые рекомендуется объявлять локально в бло ке, это короткие подпрограммы, вызываемые только из одного конкрет ного фрагмента программы (блока, их содержащего). Подобные локаль ные подпрограммы, схожие с макросами языка С, обычно применяются для того, чтобы избежать дублирования программного текста в блоке.

Различия между хранимыми и локальными подпрограммами приводятся в таблице 10.1.

функций и модулей Таблица Хранимые и локальные подпрограммы Хранимые подпрограммы Локальные подпрограммы Хранятся в базе данных в скомпилированном Компилируются как фрагменты р-коде;

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

Могут вызываться из любого блока, Могут вызываться только из содержащих запущенного на выполнение пользователем, их который имеет привилегии EXECUTE на подпрограмму.

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

Скомпилированный р-код можно закрепить Непосредственно локальные подпрограммы в разделяемом пуле при помощи модульной нельзя закреплять в разделяемом процедуры Это улучшает функционирование системы.

Автономные хранимые подпрограммы нельзя Можно перегружать в границах одного перегружать, но модульные можно (в границах одного модуля).

Модуль рассматривается ниже в разделе "Закрепление в разделяемом пуле".

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

Зависимости в подпрограммах При компиляции процедуры или функции все объекты Oracle, на кото рые она ссылается, записываются в словарь данных. Говорят, что проце дура зависит (dependent) от этих объектов. Выше было показано, что подпрограмма, при компиляции которой возникли ошибки, помечается в словаре данных как недостоверная. Хранимая подпрограмма может стать недостоверной и в том случае, когда над одним из объектов, от которых она зависит, выполняется некоторая операция DDL. Рассмотрим пример.

Функция (см. главу 9) обращается с запросом к таблице>

Зависимости AlmostFull представлены на рис. 10.3. AlmostFull зависит толь ко от одного объекта Ч>

Рис. 10.3. Зависимости функции AlmostFull Теперь создадим процедуру, вызывающую и резуль таты в таблицу Назовем эту процедуру Г] Ч Этот пример содержится в файле CREATE OR REPLACE PROCEDURE AS CURSOR c_Classes IS SELECT department, course FROM>

BEGIN FOR v_ClassRecord IN c_Classes LOOP - Запишем в таблицу информацию обо всех группах, в которых - осталось слишком мало мест.

IF THEN INSERT INTO (char_col) VALUES || ' ' || ' is almost full!

END IF;

END LOOP;

END RecordFullClasses;

Существующие зависимости показаны стрелками на рис. 10.4. Record FullClasses зависит как от AlmostFull, так и от temp_table. Такие зависимо сти называются непосредственными (direct), поскольку RecordFullClasses ссылается непосредственно на AlmostFull и AlmostFull зависит от поэтому RecordFullClasses имеет косвенную (indirect) зависи мость от>

Функция AlmostFull зависит непосредственно от таблицы>

Рис. 10.4. Зависимости процедуры RecordFullClasses Использование процедур, функций и модулей Если над>

ALTER

SELECT status 2 FROM 3 object name IN OBJECT OBJECT TVPE STflTUS RECORDFULLCLASSES PROCEDURE ALMOSTFULL J Рис. 10.5. В результате выполнения операции DDL объекты становятся недостоверными Автоматическая перекомпиляция Если зависимый объект оказывается недостоверным, система поддержки PL/SQL автоматически пытается перекомпилировать его при следующем вызове. Поскольку ни RecordFullClasses, ни AlmostFull не ссылаются на новый столбец в>

366 Search Options Table altered.

SQL> SELECT object_type, status 2 FROM user_objects 3 WHERE IN OBJECT OBJECT TYPE PROCEDURE FUNCTION INUALID exec PL/SQL procedure successfully completed.

SELECT object_type, status 2 FROM user_objects 3 WHERE IN OBJECT OBJECT TVPE STflTUS RECORDFULLCLASSES PROCEDURE ALMOSTFULL FUNCTION SQL> Рис. 10.6. Автоматическая перекомпиляция недостоверного объекта Осторожно!

Автоматическая перекомпиляция может завершиться неудачей (особенно при изменении описания таблицы). В этом случае вызывающий блок получает сообщение об ошибке компиляции.

Однако эта ошибка происходит во время выполнения подпрограммы, а не во время ее компиляции.

Модули и зависимости Как показано в предыдущем примере, хранимые подпрограммы стано вятся недостоверными при модификации зависимых объектов. Однако для модулей дело обстоит иначе. Рассмотрим зависимости>

Если изменяется заголовок, то тело модуля автоматически становится недостоверным, поскольку оно зависит от заголовка.

Использование процедур, функций и Тело>

Тело>

Рис. 10.7. Зависимости в модуле>

Проиллюстрируем вышесказанное на примере следующего сеанса боты в Этот пример содержится в файле SQL> -- Сначала создадим простую таблицу.

SQL> CREATE TABLE (f1 NUMBER);

Table SQL> -- Теперь создадим модульную процедуру, ссылающуюся на таблицу.

SQL> CREATE OR REPLACE PACKAGE Dependee AS 2 PROCEDURE IN NUMBER);

3 END Dependee;

4 / Package created.

SQL> CREATE OR REPLACE PACKAGE BODY Dependee AS 2 PROCEDURE IN NUMBER) IS 3 BEGIN 4 INSERT INTO VALUES 5 END Example;

6 END Dependee;

7 / Package body created.

368 ГЛАВА SQL> -- Теперь создадим процедуру, ссылающуюся на модуль CREATE OR REPLACE PROCEDURE IN NUMBER) AS 2 BEGIN 3 1);

4 END Depender;

5 / Procedure created.

SQL> -- Обратимся к и посмотрим, все ли объекты достоверны.

SELECT object_type, status 2 FROM user_objects 3 WHERE IN OBJECT NAME OBJECT TYPE STATUS TABLE VALID DEPENDEE PACKAGE VALID DEPENDEE PACKAGE BODY VALID DEPENDER PROCEDURE VALID SQL> -- Изменим лишь тело модуля. Заметьте, что заголовок не -- изменяется.

SQL> CREATE OR REPLACE PACKAGE BODY Dependee AS 2 PROCEDURE IN NUMBER) IS 3 BEGIN 4 INSERT INTO VALUES (p_Val - 1);

5 END Example;

6 END Dependee;

7 / Package body created.

SQL> -- user_objects показывает, что процедура Depender по-прежнему SQL> -- достоверна.

SQL> SELECT object_name, object_type, status 2 FROM user_objects 3 WHERE IN OBJECT_TYPE STATUS TABLE VALID DEPENDEE PACKAGE VALID DEPENDEE PACKAGE BODY VALID DEPENDER PROCEDURE VALID SQL> -- После удаления таблицы становится только SQL> -- тело модуля.

SQL> DROP TABLE Table dropped.

SQL> SELECT object_type, status 2 FROM user_.objects Использование процедур, функций и модулей OBJECT_TYPE STATUS DEPENDEE PACKAGE VALID DEPENDEE PACKAGE BODY INVALID DEPENDER PROCEDURE VALID Внимание 6 представлениях словаря данных и отображаются все взаимосвязи, существующие между объектами схем.

Более подробно об этих представлениях рассказывается в приложении С.

На рис. 10.8 показаны зависимости между объектами, создаваемыми этим сценарием.

(заголовок модуля) (тело модуля) напрямую зависит от заголовка модуля.

Тело модуля зависит от simplejtable и от заголовка модуля.

Рис. 10.8. Зависимости модуля Определение недостоверности При изменении объекта зависимые от него объекты становятся недосто верными. Если это объекты одной базы данных, то зависимые объекты становятся недостоверными сразу же после изменения базового. Это про исходит очень быстро, поскольку зависимости отслеживаются в словаре данных. Предположим, что имеются две процедуры: Р1 и Р2 (рис. 10.9).

Р1 зависит от Р2, т.е. при перекомпиляции Р2 процедура Р1 становится недостоверной. Покажем это на примере сеанса работы в 370 ГЛАВА CREATE OR REPLACE PROCEDURE P1 AS CREATE OR REPLACE PROCEDURE P2 AS BEGIN BEGIN DBMS DBMS P2!

P2 Х P2;

END P1;

Система поддержки Рис. 10.9. P1 и Р2 в одной базе данных Г]..- Этот пример содержится в файле ' SQL> -- Создадим две процедуры. Р1 от Р2.

SQL> CREATE OR REPLACE PROCEDURE P2 AS 2 BEGIN 3 P2! ');

4 END P2;

5 / Procedure SQL> CREATE OR REPLACE PROCEDURE P1 AS 2 BEGIN 3 P1! ');

4 P2;

6 / Procedure created.

SQL> -- Убедимся в достоверности обеих процедур.

SQL> SELECT object_name, object_type, status 2 FROM user_objects 3 WHERE IN STATUS P PROCEDURE VALID PROCEDURE VALID SOL> -- Перекомпилируем Р2, что немедленно сделает Р SQL> ALTER PROCEDURE P2 COMPILE;

Procedure altered.

SQL > -- Вновь сделаем запрос.

SQL> SELECT object_type, status 2 FROM 3 WHERE IN OBJECT TYPE STATUS P2 PROCEDURE VALID P1 PROCEDURE INVALID Использование процедур, функций и CREATE OR REPLACE PROCEDURE P1 AS CREATE OR REPLACE PROCEDURE P2 AS BEGIN BEGIN END P2;

END P1;

Система поддержки PL/SQL Система поддержки PL/SQL Рис. 10.10. P1 и Р2 в разных базах данных Допустим теперь, что Р1 и Р2 находятся в разных базах данных, и Р вызывает Р2, используя соединение баз данных (рис. 10.10). В этом случае перекомпиляция Р2 не влечет за собой немедленной недостоверности как показано в следующем сеансе работы в Г] Этот пример содержится в файле SQL> -- Создадим связь баз данных, указывающую на текущий экземпляр.

SQL> -- Вам придется модифицировать и соответствующим SQL> -- образом настроить SQL*Net в вашей системе.

SQL> CREATE DATABASE LINK loopback 2 USING Database link created.

SQL> -- Изменим R1 так, чтобы Р2 вызывалась по связи.

CREATE OR REPLACE PROCEDURE P1 AS 2 BEGIN 3 Inside P1! ');

5 END P1;

6 / Procedure SQL> -- Убедимся в достоверности обеих процедур.

SQL> SELECT object_type, status 2 FROM 3 WHERE IN OBJECT TYPE STATUS P2 PROCEDURE VALID P1 PROCEDURE VALID SQL> - Теперь после перекомпиляции Р2 процедура Р1 не становится сразу же SQL> SOL> ALTER PROCEDURE P2 COMPILE;

Procedure altered.

SQL> SELECT status 372 ГЛАВА 2 FROM 3 WHERE IN OBJECT NAME OBJECT TYPE STATUS Р2 PROCEDURE VALID P1 PROCEDURE VALID Внимание В этом примере связь баз данных в действительности является обратной (loopback), т.е. указывает на базу данных. Тем не менее выполняются точно такие же действия, как и при размещении Р1 и Р2 в разных базах данных.

Использование петли позволяет запрашивать сведения о состоянии и Р2 в одном операторе SELECT.

Так почему же при использовании удаленной процедуры результат по лучается иным? Дело в том, что в словаре данных не отслеживаются уда ленные зависимости. Было бы слишком дорого определять достоверность всех зависимых объектов, так как они могут находиться в разных базах данных (которые в момент определения достоверности вполне могут ока заться Достоверность удаленных объектов проверяется во время выполне ния программы. При вызове запрашивается удаленный словарь данных и определяется состояние Р2 (если удаленная база данных недоступна, уста навливается ошибка). Р1 и Р2 сравниваются, после принимается реше ние, нужно ли перекомпилировать Р1. Существуют два метода сравнения:

метод временных меток и метод сигнатур.

Внимание Для проверки достоверности на этапе выполнения совсем не обязательно иметь связь баз данных. Если находится в системе поддержки PL/SQL на станции клиента в Oracle Forms), a P2 Ч на сервере, то ситуация аналогична и используется один из указанных методов (см. главу 2).

Модель временных меток В этой модели сравниваются временные метки, соответствующие последним модификациям двух объектов. Вре менная метка (timestamp) содержится в поле представления Если временная метка базового объекта более новая, чем метка зависимого объекта, последний перекомпилируется. При работе с этой моделью нужно учитывать ряд обстоятельств:

Х При сравнении данных не принимается во внимание местонахожде ние систем поддержки PL/SQL. Если они находятся в разных часо вых поясах, сравнение не будет достоверным.

Использование процедур, функций и модулей Даже если две системы поддержки расположены в одном часовом поясе, использование модели временных меток может привести к ненужной перекомпиляции. В предыдущем примере Р2 только пере компилировалась, но на самом деле не изменялась. Р1 в действитель ности не нужно перекомпилировать, но из-за более старой времен ной метки это будет сделано.

Х Проблема становится серьезнее, если Р1 размещена в клиентской системе PL/SQL, например в Oracle Forms. В этом случае существу ет вероятность того, что Р1 не будет перекомпилирована, поскольку ее исходный код может отсутствовать в варианте Oracle Forms, при меняемом во время выполнения программы.

Модель сигнатур В PL/SQL предлагается другой способ определения необходимости перекомпиляции удаленных зависимых объектов. Это ме тод сигнатур. При создании процедуры вместе с р-кодом в словаре дан ных сохраняется сигнатура (signature). Сигнатура кодирует типы и порядок параметров. В этой модели сигнатура Р2 будет изменяться только при изменении параметров. Когда Р1 компилируется в первый раз, испо льзуется сигнатура Р2 (а не временная метка), поэтому Р1 необходимо пе рекомпилировать лишь при изменении сигнатуры Р2.

Для использования модели сигнатур необходимо установить параметр в значение SIGNATURE. Этот параметр содержится в файле базы данных (имя и расположе ние этого файла, называемого обычно га, варьируется в зависимости от применяемой системы). Кроме того, можно установить данный пара метр в диалоговом режиме. Существуют три способа задания этого параметра:

Х Можно добавить в файл базы данных строку REMOTE_DEPENDENCIES_MODE=SIGNATURE. При следующем запуске базы данных режим будет установлен как SIGNATURE для всех сеансов.

Х Можно выполнить команду:

ALTER SYSTEM SET = SIGNATURE;

Это будет воздействовать на всю базу данных (на все начиная с момента выполнения оператора. Эта команда требует наличия систем ной привилегии ALTER SYSTEM.

Х Можно выполнить команду:

ХALTER SESSION SET = SIGNATURE;

Она воздействует только на определенный сеанс. Для объектов, созда ваемых в текущем сеансе после выполнения этой команды, будет исполь зоваться метод сигнатур.

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

374 ГЛАВА Х Если изменяются значения, заданные по умолчанию для формаль ных параметров, сигнатуры не модифицируются. Предположим, что один из параметров процедуры Р2 имеет некоторое значение по умолчанию, и оно используется в Р1. Если изменить это значение в описании Р2, то по умолчанию Р1 не будет перекомпилироваться.

Старое значение параметра, заданное по умолчанию, применяется до тех пор, пока Р1 не будет перекомпилирована вручную. Это спра ведливо только для параметров IN.

Х Если Р1 вызывает модульную процедуру Р2 и если в удаленный мо дуль добавляется новый, перегруженный вариант Р2, то сигнатура не изменяется. Р1 будет использовать старый (не перегруженный) вариант до тех пор, пока Р1 не будет перекомпилирована вручную.

Х Для того чтобы вручную перекомпилировать процедуру, используйте команду:

ALTER PROCEDURE COMPILE;

где Ч это имя компилируемой процедуры. Для пере компиляции функции служит команда:

ALTER FUNCTION COMPILE;

Для перекомпиляции модуля можно использовать одну из команд:

ALTER PACKAGE COMPILE;

ALTER PACKAGE COMPILE SPECIFICATION;

ALTER PACKAGE COMPILE BODY;

Если присутствует ключевое слово SPECIFICATION, то компилиру ется только заголовок модуля. Если указано ключевое слово BODY, компилируется только тело модуля. Если нет ни того, ни другого, компилируется все.

За более подробной информацией о модели сигнатуры обратитесь к руководству "Oracle Server Application Guide".

Состояние модулей на этапе выполнения При первом создании экземпляра модуля его р-код считывается с диска и помещается в разделяемый пул. Однако состояние модуля на этапе ния (runtime state), т.е. сведения о модульных переменных и курсорах, фик сируется в памяти сеанса в пользовательской глобальной области, что гарантирует наличие собственной копии состояния модуля у каждого сеан са. Оно инициализируется при создании экземпляра модуля (в это время вы полняется код инициализации) и не сбрасывается до окончания сеанса.

Даже если сам модуль удаляется со временем из разделяемого пула, состоя ние модуля сохраняется. Как было показано в главе 9, переменные, объявля емые в заголовке модуля, имеют глобальную область действия. Они видимы любому блоку PL/SQL, имеющему привилегию EXECUTE на модуль.

льку состояние на этапе выполнения будет сохраняться в течение всего се анса работы с базой данных, то переменные в заголовке пакета могут использоваться как глобальные переменные. Рассмотрим пример:

Использование функций и модулей Г) -- Этот пример содержится в файле CREATE OR REPLACE PACKAGE AS - Тип, предназначенный для хранения массива идентификаторов студентов TYPE IS TABLE OF students. ID%TYPE INDEX BY - Максимальное число строк, возвращаемых каждый раз.

NUMBER := 5;

- Возвращает до v_MaxRows идентификаторов PROCEDURE OUT OUT END PersistPkg;

CREATE OR REPLACE PACKAGE BODY PersistPkg AS - Обратимся к таблице students с запросом. Он является глобальным - по отношению к телу модуля, поэтому останется вне вызова базы данных.

CURSOR IS SELECT ID FROM students ORDER BY PROCEDURE ReadStudents(p_StudTable OUT OUT NUMBER) IS v_Done BOOLEAN := FALSE;

NUMBER := BEGIN IF NOT THEN - Сначала откроем курсор.

OPEN StudentCursor;

END IF;

- Курсор открыт, так что можно выбрать до строк.

WHILE NOT v_Done LOOP FETCH StudentCursor INTO IF Х- Данных больше нет, поэтому завершаем работу.

CLOSE StudentCursor;

v_Done := TRUE;

ELSE := v_NumRows + IF > THEN v_Done TRUE;

END IF;

END IF;

END LOOP;

- Возвратим число выбранных строк.

:= END ReadStudents;

END PersistPkg;

376 ГЛАВА Процедура выбирает информацию из курсора Он объявлен на уровне модуля (а не внутри поэтому останется вне вызова ReadStudents. Вызовем dents в следующем блоке:

Ч Этот пример содержится в файле DECLARE NUMBER := BEGIN Fetched ' ' FOR v_Count IN LOOP SELECT INTO FROM students WHERE ID = v_StudentTable(v_Count);

' ' |l END LOOP;

END;

File Help Fetched 5 rows:

Barbara Blues Ester Elegant Joanne Junebug Margaret Mason PL/SQL procedure successfully completed.

Fetched 5 rows:

Manish Murgatroid Patrick Poll Rita Rose Riznit Shay Shariatpanahy PL/SQL procedure successfully completed.

SQL> Fetched 2 rows:

Scott Smith Timothy Taller PL/SQL procedure successfully completed.

Рис. 10.11. Вызов ReadStudents Использование функций и модулей Результаты троекратного выполнения этого блока представлены на рис. 10.11. При каждом вызове возвращаются разные данные, поскольку курсор остается открытым между вызовами.

Серийно используемые модули В PL/SQL разрешено помечать модули как серийно используемые. Со стояние на этапе выполнения серийно (serially reusable) моду ля будет существовать только в течение одного обращения к базе данных, а не в течение всего сеанса. Серийно используемый модуль имеет синтак сическую конструкцию PRAGMA в заголовке пакета (а также тело пакета при наличии такового). Если по добным образом модифицировать PersistPkg, результат будет иным (см.

рис. 10.12).

-- Этот пример содержится в файле CREATE OR REPLACE PACKAGE PersistPkg AS PRAGMA SERIALLY_REUSABLE;

- Тип, предназначенный для хранения массива идентификаторов студентов TYPE t IS TABLE OF students. ID%TYPE Edit Search flptiom Help SQL> Fetched 5 rows:

Barbara Blues Dauid Dinsmore Ester Elegant Joanne Junebug Margaret Mason PL/SQL procedure successfully completed.

Fetched 5 rows:

Barbara Blues Dauid Dinsmore Ester Elegant Joanne Junebug Margaret Mason procedure successfully Fetched 5 rows:

Barbara Blues Dauid Dinsmore Ester Elegant Joanne Junebug Margaret Mason PL/SQL procedure successfully completed.

Рис. 10.12. Вызов серийно используемой процедуры 378 INDEX BY - Максимальное число строк, возвращаемых каждый раз.

NUMBER 5;

- Возвращает до идентификаторов студентов.

PROCEDURE OUT NUMBER);

END PersistPkg;

CREATE OR REPLACE PACKAGE BODY PersistPkg AS PRAGMA SERIALLY_REUSABLE;

- Обратимся к таблице students с запросом. Хотя он является глобальным - по отношению к телу модуля, значения будут сбрасываться после каждого - вызова базы данных, так как модуль теперь используется многократно.

CURSOR IS SELECT ID FROM students ORDER BY END PersistPkg;

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

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

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

Зависимости состояния модуля на этапе выполнения Помимо зависимостей между хранимыми объектами, существуют зависи мости между состоянием модуля и анонимными блоками. Для примера рассмотрим следующий модуль:

Этот пример содержится в файле CREATE REPLACE PACKAGE AS Использование функций и модулей NUMBER PROCEDURE UpdateVar;

END CREATE OR REPLACE PACKAGE BODY SimplePkg AS PROCEDURE UpdateVar IS BEGIN v_GlobalVar := 7;

END UpdateVar;

END SimplePkg;

В SimplePkg содержится глобальная переменная модуля Ч v_GlobalVar.

Предположим, что SimplePkg была создана в некотором сеансе базы дан ных. Вызовем во втором сеансе из следующего блока:

END;

Теперь вернемся к первому сеансу и создадим SimplePkg еще раз, снова выполнив сценарий создания. Наконец выполним тот же самый аноним ный блок во втором сеансе. В результате получим:

Г] BEGIN * ERROR at ORA-04068: existing state of packages has been discarded existing state of package "EXAMPLE. SIMPLEPKG" has been invalidated not executed, altered or dropped package ORA-06508: PL/SQL: could not find program unit being called at line (Существующее состояние модулей сброшено. Существующее состояние модуля признано Не исполнен, изменен или удален модуль Невозможно найти вызываемый программный Что же произошло? Картина зависимостей для этой ситуации пред ставлена на рис. 10.13. Анонимный блок зависит от SimplePkg так же, как и ранее. Это зависимость этапа компиляции, поскольку определяется при первой компиляции анонимного блока. Однако здесь присутствует и за висимость этапа выполнения от модульной переменной, так как у каждо го сеанса есть своя собственная копия модульных переменных. Таким образом, при перекомпиляции SimplePkg зависимость этапа выполнения сохраняется, что делает блок недостоверным и устанавливает ошибку Зависимости этапа выполнения определяются только состоянием мо дуля: объявленными в нем переменными и курсорами. Если бы в модуле не было глобальных переменных, то второе исполнение анонимного бло ка прошло бы успешно.

Анонимный блок зависит от и содержит экземпляр v_GlobalVar в своем состоянии этапа выполнения.

Рис. Глобальные зависимости модуля Привилегии и хранимые подпрограммы Хранимые подпрограммы и модули являются объектами словаря данных, и поэтому они принадлежат конкретным пользователям базы данных или схемам. Другие пользователи могут обращаться к этим объектам, если им предоставлены необходимые привилегии. Привилегии и роли начинают действовать при создании хранимого объекта, они отвечают за доступ, обеспечиваемый в подпрограмме.

Привилегия EXECUTE Чтобы разрешить доступ применяются объектные привилегии SELECT, INSERT, UPDATE и DELETE. Эти привилегии предоставляются пользователю или роли базы данных при помощи оператора GRANT. Для хранимых подпрограмм и модулей необходима привилегия EXECUTE.

Обратимся вновь к процедуре Ч Этот пример содержится в файле CREATE OR REPLACE PROCEDURE RecordFullClasses AS CURSOR c_Classes IS SELECT department, course FROM>

BEGIN FOR v_ClassRecord IN c_Classes LOOP - Запишем в таблицу информацию обо всех группах, в которых - осталось слишком мало места.

IF THEN INSERT INTO (char_col) VALUES ' ' ' is almost full!

END IF;

END LOOP;

END RecordFullClasses;

Использование процедур, функций и модулей Внимание В sql сначала создаются пользователи UserA и а затем объекты, используемые в примерах этого раздела.

Чтобы пример выполнялся в вашей системе, возможно, придется изменить пароль учетной записи DBA. Результат выполнения sql находится в файле out, Предположим, что владельцем объектов, от которых зависит Record FullClasses (функция AlmostFull и таблицы>

Г] ХХ- Этот пример содержится в файле GRANT EXECUTE ON RecordFullClasses TO UserB;

Тогда UserB сможет выполнить RecordFullClasses при помощи следую щего блока (для указания схемы используется запись через ХХ- Этот пример содержится в файле BEGIN END;

В данном случае владельцем всех объектов базы данных является поль зователь UserA (см. рис. 10.14). Пунктирная линия на рисунке означает предоставление полномочия (оператор GRANT) пользователем UserA поль зователю UserB, а непрерывная линия Ч зависимости, существующие между объектами. Результаты исполнения программного блока вводятся в таблицу.

>

в Рис. Пользователи UserA и UserB являются Теперь предположим, что UserB владеет которая также назы вается (рис. 10.15). Если UserB вызывает UserA. RecordFullClasses (исполняя представленный выше анонимный блок), то какая из таблиц бу дет модифицирована? Таблица пользователя UserA. Сформулируем правило:

По умолчанию подпрограмма выполняется на основании набора при вилегий своего владельца.

Хотя UserB вызывает RecordFullClasses, этой процедурой владеет UserA.

Поэтому идентификатор будет поставлен в соответствие таб лице, принадлежащей пользователю UserA, а не UserB.

содержит новое средство, называемое "правами вызывающе го". Оно разрешает указать набор привилегий для исполнения процеду ры: набор владельца или набор вызывающего (см. ниже раздел "Права вызывающего и Хранимые подпрограммы и роли Теперь немного изменим ситуацию, показанную на рис. 10.15. Предполо жим, что UserA не владеет ни ни RecordFullClasses, а оба эти объекта принадлежат пользователю UserB. Кроме того, изменим Record FullClasses так, чтобы эта процедура явно ссылалась на объекты пользо вателя UserA (см. рис. 10.16).

Х- Этот пример содержится в файле CREATE OR REPLACE PROCEDURE RecordFullClasses AS CURSOR c_Classes IS SELECT department, course Использование процедур, функций и модулей Результаты сохраняются В Рис. 10.16. Пользователь UserB является владельцем процедуры FROM UserA.>

BEGIN FOR v_ClassRecord IN c_Classes LOOP - Запишем в таблицу информацию обо всех группах, в которых - осталось слишком мало мест.

IF THEN INSERT INTO (char_col) VALUES ' ' ' is almost full!

END IF;

END LOOP;

END RecordFullClasses;

Для того чтобы процедура RecordFullClasses была корректно скомпи лирована, пользователь UserA должен предоставить привилегию SELECT на таблицу>

GRANT EXECUTE ON AlmostFull TO UserB;

Если же воспользоваться промежуточной ролью:

-- Этот пример содержится в файле sql 384 ГЛАВА CREATE ROLE GRANT SELECT ON>

GRANT EXECUTE ON TO GRANT TO то привилегии предоставлены не будут. Использование роли показано на рис. 10.17.

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

Рис. 10.17. Предоставление привилегий через роль Итак, уточним правило, сформулированное в предыдущем разделе:

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

Если предоставить привилегии через роль, то при попытке компиля ции RecordFullClasses будет выдано сообщение об ошибке identifier must be declared identifier must be declared Это правило распространяется на триггеры и модули, которые хра нятся в базе данных. Таким образом, в хранимых процедурах, функциях, мо дулях и триггерах доступны те объекты, которые принадлежат этих подпрограмм или пользователям, которым явно предостав лены привилегии на применение этих Почему же установлено такое ограничение? Для ответа на этот вопрос необходимо обратиться к процессу связывания. В PL/SQL используется раннее ссылки определяются во время компиляции, а не вы полнения подпрограммы. Операторы GRANT и REVOKE являются операто рами DDL. Они начинают действовать немедленно, и новые Использование функций и модулей записываются в словарь данных. Во всех соединениях, установленных с базой данных, будет виден новый набор привилегий. Однако это не все гда справедливо для ролей. Роль можно предоставить пользователю, а этот пользователь затем вправе запретить ее при помощи команды SET ROLE. Отличие заключается в том, что команда SET ROLE действует только в одном сеансе базы данных, в то время как операторы GRANT и REVOKE применяются для всех сеансов. Роль может быть запрещена в одном сеансе, но разрешена в других.

Чтобы использовать в хранимых подпрограммах и триггерах привиле гии, предоставляемые через роль, эти привилегии нужно проверять вся кий раз при запуске процедуры. Проверка привилегий является этапом процесса связывания. Но раннее связывание означает, что привилегии проверяются во время компиляции, а не выполнения. Для того чтобы раннее связывание было осуществимо, роли внутри хранимых функций, и триггеров запрещаются, Права вызывающего и определяющего Проанализируем ситуацию, представленную на рис. 10.15. Здесь оба поль зователя UserA и владеют копиями таблицы a lasses принадлежит UserA и вводит данные в UserA.

RecordFullClasses называется процедурой с правами определяющего (definer's rights), поскольку неквалифицированные внешние ссылки внутри нее разрешаются на основании набора привилегий ее владельца, т.е. определяющего.

В возможен и другой способ разрешения внешних ссылок. В подпрограмме с правами вызывающего rights) внешние ссылки разрешаются на основании набора привилегий не владельца, а вызываю щего. Подобная подпрограмма создается с помощью предложения AUTHID. Это разрешено только для автономных подпрограмм, для опи саний модулей и для описаний объектных типов (см. главу 12).

Все отдельные подпрограммы модуля должны иметь права вызывающе го или их комбинация не допустима. Синтаксис AUTHID:

CREATE [OR REPLACE] FUNCTION RETURN [AUTHID | DEFINER}] | CREATE PROCEDURE [ [AUTHID | | CREATE [OR REPLACE] PACKAGE [AUTHID | | 386 ГЛАВА Если в предложении AUTHID указано (текущий по льзователь), то объекту предоставляются права вызывающего, а если DEFINER Ч то права определяющего. По умолчанию (если конструкция AUTHID отсутствует) устанавливаются права определяющего.

Например, следующий вариант процедуры обладает правами вызывающего:

Ч Этот пример содержится в файле invokers.sql CREATE OR REPLACE PROCEDURE RecordFullClasses AUTHID CURRENT_USER AS - Обратите внимание: для>

CURSOR IS SELECT department, course FROM BEGIN FOR v_ClassRecord IN c_Classes LOOP - Запишем в таблицу обо всех группах, в которых -- осталось слишком мало мест.

IF THEN INSERT INTO temp_table (char_col) VALUES ' ' | | ' is almost full!

END IF;

END LOOP;

END RecordFullClasses;

Внимание В сначала создаются пользователи UserA и а затем объекты, используемые в примерах этого раздела.

Чтобы пример выполнялся в вашей системе, возможно, придется изменить пароль учетной записи DBA. Результат выполнения invokers. sql находится в файле invokers.

Если RecordFullClasses выполняется пользователем UserB, данные вво дятся в таблицу UserB. а если пользователем UserA Ч то в табли цу Это демонстрируется следующим сеансом и рис. 10.18.

- Этот пример содержится в файле SQL> connect UserA/UserA SQL> -- Вызовем процедуру как UserA;

данные вводятся в таблицу SQL> - SOL> 2 RecordFullClasses;

3 COMMIT;

4 END;

5 / PL/SQL procedure successfully completed.

Использование процедур, функций и модулей RecordFullClasses (права вызывающего) UserB Если UserA вызывает Если вызывает RecordFullClasses, RecordFullClasses, результаты сохраняются здесь. результаты сохраняются здесь.

Рис. 10.18. вызывающего для RecordFullClasses SQL> -- Обратимся к temp_table с запросом. Должна быть выдана одна строка.

SELECT * FROM COL CHAR COL MUS 410 is almost full!

SQL> -- Установим соединение как UserB.

SQL> -- Теперь при вызове RecordFullClasses данные вводятся в SQL> - SQL> BEGIN 3 COMMIT;

4 END;

5 / PL/SQL procedure successfully completed.

SQL> -- Здесь тоже должна быть выдана одна строка.

SQL> SELECT * FROM CHAR_COL MUS 410 is almost full!

Разрешение ссылок с правами вызывающего В подпрограмме с права ми вызывающего внешние ссылки в SQL-операторах разрешаются с помо щью набора привилегий вызывающего эту подпрограмму.

Однако ссылки в операторах PL/SQL (например, в операциях присваива ния и в вызовах процедур) по-прежнему разрешаются на основании набо ра привилегий владельца. Именно поэтому необходимо предоставлять привилегии (GRANT) только на RecordFullClasses и на таблицу>

Теперь предположим, что привилегия на>

Ч Этот пример содержится в файле SQL> connect UserB/UserB SQL> BEGIN 3 END;

4 / BEGIN ERROR at line table or view does not exist at "USERA. line at "USERA. line ORA-06512: at line RecordFullClasses (права вызывающего) AlmostFull 1 r>

возвращается ORA-942, Рис. 10.19. Привилегия SELECT на>

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

Роли и права вызывающего Предположим, что привилегия на>

-- Этот пример содержится в файле invokers.sql SQL> connect Connected.

SQL> CREATE ROLE UserA_Role;

Role SQL> GRANT SELECT ON>

Grant succeeded.

SQL> GRANT UserA_Role TO UserB;

Grant succeeded.

SQL> -- Установим соединение как UserB и вызовем процедуру.

SQL> connect SQL> -- Теперь вызов RecordFullClasses будет SQL> BEGIN RecordFullClasses (права вызывающего) UserA Role UserA UserB Если UserA вызывает Если UserB вызывает RecordFullClasses, RecordFullClasses, результаты сохраняются здесь. результаты сохраняются здесь.

Рис. 10.20. Роли и права вызывающего 390 3 COMMIT;

4 END;

5 / PL/SOL procedure successfully completed.

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

С помощью роли можно предоставлять привилегии только на те ссылки, которые разрешаются на этапе выполнения программы. При этом подразумевается, что для ссылок этапа выполнения можно использовать команду SET (при исполнении программ посредством динамического SQL).

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

Использование хранимых функций в SQL-операторах Вызовы подпрограмм по сути своей реализуются в виде процедур, поэто му их нельзя выполнять в Однако если автономная или модульная функция отвечает определенным условиям, ее можно вызы вать во время выполнения SQL-оператора. Эта возможность стала впер вые доступна в PL/SQL версии 2.1 редакции 7.1), а в она была расширена.

Функция, созданная пользователем, вызывается точно так же, как и встроенные функции (например, UPPER или В зависимости от версии Oracle и от области применения функции она должна отвечать определенным условиям. Эти условия определяются так называемыми уровнями строгости.

Уровни строгости Для функций существуют четыре различных уровня строгости.

строгости (purity level) определяет структуры данных, которые может счи тывать или модифицировать функция. Эти уровни представлены в табли це 10.2. В зависимости от уровня строгости функции на нее налагаются следующие ограничения:

Х Функция, вызываемая из SQL-оператора, не может модифициро вать таблицы базы данных (В функция, вызывае мая из отличного от SELECT, может модифицировать таблицы базы данных;

см. ниже раздел "Вызов хранимых функций Использование процедур, функций и модулей Х Для того чтобы функция могла быть выполнена дистанционно (с помощью соединения баз данных) или параллельно, она не дол жна считывать или записывать значения модульных переменных Х Функции, вызываемые из предложений SELECT, VALUES или SET, могут записывать модульные переменные. Функции во всех осталь ных предложениях должны иметь уровень строгости Х Функция строга настолько, насколько строги вызываемые ею под программы. Если функция вызывает хранимую процедуру, которая, к примеру, обновляет информацию (оператор UPDATE), то эта функция не имеет уровня строгости и, следовательно, не мо жет быть использована в операторе SELECT.

Х Независимо от уровня строгости хранимые функции PL/SQL нельзя вызывать из предложения ограничения CHECK команды CREATE TABLE или ALTER TABLE, а также использовать для указания значе ния по умолчанию для столбца, так как в этих ситуациях требуется, чтобы определения не изменялись.

Таблица Уровни строгости функций Уровень строгости Значение Описание WNDS Не записывает Функция не модифицирует никакие таблицы состояние базы данных базы данных (при помощи операторов RNDS Не читает состояние Функция не читает никакие таблицы базы данных базы данных (при помощи оператора SELECT).

WNPS He записывает Функция не модифицирует модульные состояние модуля переменные (модульные переменные не указываются с левой стороны операции присваивания или в операторе FETCH).

RNPS Не читает Функция не читает модульные переменные состояние модуля (модульные переменные не располагаются с правой стороны операции присваивания и не указываются в процедурных или SQL-выражениях).

Кроме приведенных ограничений, функция, созданная пользовате лем, должна отвечать также дополнительным требованиям, чтобы ее можно было вызывать из SQL-операторов. Заметим, что все встроенные функции тоже должны отвечать этим требованиям.

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

Х Функция может принимать только параметры IN, но не IN OUT или OUT.

392 ГЛАВА Х Для формальных параметров должны использоваться только те типы, которые применяются в базе данных, но не типы PL/SQL, та кие как BOOLEAN или RECORD. Типы базы данных Ч это CHAR, LONG, RAW, LONG RAW и DATE, а также новые типы i и Х Тип, возвращаемый функцией, также должен быть типом базы дан ных.

Х Функция не должна заканчивать текущую транзакцию оператором COMMIT или ROLLBACK, либо выполнять откат к точке сохране ния до своего выполнения.

Х В функции не должны вызываться команды ALTER SESSION и ALTER SYSTEM.

В качестве примера рассмотрим функцию входным пара метром которой является идентификатор студента и которая возвращает конкатенированные имя и фамилию.

О Ч пример содержится в файле CREATE OR REPLACE FUNCTION FullName ( RETURN VARCHAR2 IS v_Result BEGIN SELECT first_name || ' ' || INTO v_Result FROM students WHERE ID = p_StudentID;

RETURN END FullName;

Функция FullName удовлетворяет всем ограничениям, поэтому ее мож но вызвать из Этот пример содержится в файле FullName. sql SQL> SELECT ID, "Full Name" 2 FROM students;

ID Full Name 10000 Scott Smith 10001 Margaret Mason 10002 Joanne Junebug 10003 Manish Murgratroid 10004 Patrick Poll 10005 Timothy Taller 10006 Barbara Blues 10007 David 10008 Ester Elegant 10009 Rose Riznit Использование процедур, функций и модулей 10010 Rita 10011 Shay 12 rows SQL> INSERT INTO 2 VALUES 1 row RESTRICT REFERENCES В PL/SQL можно устанавливать уровни строгости для автономных функ ций. Когда функция вызывается из SQL-оператора, проверяется уровень строгости. Если он не удовлетворяет предъявляемым требованиям, воз вращается сообщение об ошибке. Однако для модульных функций (до требуется прагма (указание) (ограни чить Она устанавливает уровень строгости для конкретной фун кции. Синтаксис прагмы:

PRAGMA RESTRICT_REFERENCES( [, RNDS] [, Ч имя модуля или модульной подпрограм мы (в и выше можно применять еще и ключевые слова DEFAULT и TRUST;

см. ниже). Уровень обязателен для всех функций, вызывае мых в SQL-операторах, поэтому он обязателен и для прагмы (в это ограничение смягчено). Уровни строгости можно указывать в любом по рядке. Прагма помещается в заголовок модуля вместе с описанием функ ции. К примеру, в модуле прагма используется дважды:

Ч Этот пример содержится в файле OR REPLACE PACKAGE StudentOps AS. FUNCTION IN students.

- flETURN VARCHAR2;

WNDS, WNPS, RNPS);

/*. Возвращает число студентов, профилирующей дисциплиной которых является история. */ -V RETURN NUMBER;

PRAGMA RESTRICT_REFERENCES(NumHistoryMajors, WNDS);

END StudentOps;

OR REPLACE PACKAGE BODY StudentOps AS Модульная переменная для хранения числа :. NUMBER;

FUNCTION VARCHAR v_Result BEGIN SELECT И ' ' last_name INTO v_Result FROM students 394 ГЛАВА WHERE ID = RETURN v_Result;

END FullName;

FUNCTION NumHistoryMajors RETURN NUMBER IS NUMBER;

BEGIN IF IS NULL THEN /* Определим */ SELECT INTO FROM students WHERE major = /* и сохраним его для будущего */ ELSE END IF;

RETURN END NumHistoryMajors;

END Внимание В и выше эта прагма не является обязательной.

При необходимости система поддержки PL/SOL может проверить уровень строгости всех функций на этапе их выполнения (см. ниже раздел "Вызов функций из SQL в Обоснование использования Почему прагма обя зательна для модульной функции, но не нужна для автономной? Для ответа на этот вопрос следует проанализировать взаимосвязи, существующие меж ду заголовком и телом модуля. Напомним, что блоки PL/SQL, вызывающие модульную функцию, зависят только от заголовка модуля, но не от его тела.

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

Всякий раз при последующей модификации (или при первом создании) тела модуля код функции проверяется на соответствие заданной прагме.

Прагма проверяется во время компиляции, а не выполнения.

V Использование процедур, функций и модулей Совет Система поддержки PL/SQL может проверить уровень строгости и на этапе выполнения, как это делается для автономных функций в версиях, предшествующих Однако применение прагмы означает, что системе поддержки не нужно проверять уровень на этапе выполнения, а это повышает производительность всей системы. К же гарантируется успешный вызов данной подпрограммы из SQL Поэтому предпочтительно использовать в хотя это больше и не требуется.

Раздел инициализации Код в разделе инициализации модуля также мо жет иметь уровень строгости. При первом вызове любой функции в моду ле выполняется раздел инициализации. Следовательно, уровень строгости модульной функции соответствует уровню строгости раздела инициализации модуля. Уровень строгости модуля также задается с помощью RESTRICT _ REFERENCES, но указывается имя модуля, а не функции.

- CREATE OR REPLACE PACKAGE AS PRAGMA (StudentOps, WNDS);

StudentOps;

Ключевое слово Если с какой-то модульной функцией не связана прагма то для этой функции уровень строгости не устанавливается. Тем не менее в и выше можно из менить уровень строгости, заданный для модуля по умолчанию. Вместо имени подпрограммы в прагме используется ключевое слово DEFAULT:

PRAGMA DEFAULT, [, RNDS] [, Все последующие подпрограммы модуля должны соответствовать ука занным уровням строгости. В качестве примера рассмотрим модуль Этот пример содержится в файле DefaultPragma.

CREATE OR REPLACE PACKAGE DefaultPragma AS FUNCTION F1 RETURN NUMBER;

PRAGMA RNDS, RNPS);

PRAGMA RESTRICT_REFERENCES(DEFAULT, WNDS, WNPS, RNDS, RNPS);

FUNCTION F2 RETURN NUMBER;

FUNCTION F3 RETURN NUMBER;

END CREATE OR REPLACE PACKAGE BODY DefaultPragma AS FUNCTION F1 RETURN NUMBER IS BEGIN INSERT INTO temp_table VALUES (1, 396 ГЛАВА RETURN END f1;

FUNCTION F2 RETURN NUMBER IS BEGIN RETURN 2;

END F2;

- функция нарушает прагму по умолчанию.

FUNCTION F3 RETURN NUMBER IS BEGIN INSERT INTO char_col) VALUES (1, RETURN 3;

END F3;

END Прагма по умолчанию (устанавливающая все четыре уровня строго сти) будет применяться и по отношению к F2, и по отношению к F3. По скольку функция F3 вводит данные в она нарушает прагму.

При компиляции этого модуля возвращаются следующие сообщения об ошибках:

PL/SQL: Compilation unit analysis terminated.

Subprogram violates its associated pragma (Анализ единицы компиляции прекращен. Подпрограмма нарушает связанную с ней Перегруженные функции Прагма может рас полагаться в любом месте описания модуля после объявления функции, но относиться она может только к одному определению функции. Поэто му в случае с перегруженными функциями прагма относится к функции, чье определение предшествует этой прагме и расположено к ней ближе всего. В следующем примере каждая прагма относится к предшествующе му ей варианту функции с:

-- Этот пример в файле CREATE PACKAGE Overload AS FUNCTION IN NUMBER) RETURN VARCHAR2;

PRAGMA RNDS, RNPS);

FUNCTION IN VARCHAR2, IN DATE) RETURN VARCHAR2;

PRAGMA RESTRICT_REFERENCES(TestFunc, WNDS, RNDS, WNPS, RNPS);

END Overload;

CREATE OR REPLACE PACKAGE BODY Overload AS FUNCTION IN NUMBER) RETURN VARCHAR2 IS BEGIN Использование процедур, функций и модулей RETURN END TestFunc;

FUNCTION IN VARCHAR2, IN DATE) RETURN VARCHAR2 IS BEGIN RETURN END TestFunc;

END Overload;

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

-- Этот пример содержится в файле SQL> SELECT FROM dual;

Version SQL> SELECT SYSDATE) FROM dual;

SYSDATE) Version Совет Лучше указывать прагму RESTRICT_REFERENCES сразу же после каждой функции, чтобы было ясно, к какому варианту она относится.

Встроенные модули Процедуры встроенных модулей PL/SQL не явля ются строгими в том смысле, как это определено в Oracle7.3 и PL/SQL 2.3.

Это относится к модулям DBMS_OUTPUT, DBMS_PIPE, DBMS_SQL и Однако в последующих версиях в некоторые мо дули добавлены необходимые прагмы (см. таблицу 10.3). Поскольку, начиная с прагма не является обязательной, все функции встро енных модулей, отвечающие поставленным требованиям, можно исполь зовать в SQL-операторах. Если функция встроенного модуля, вызываемая в не отвечает требованиям, на этапе ее выполнения порождается ошибка.

Внимание Прагмы добавлялись к некоторым из этих модулей в составе корректирующих или "заплат"), поэтому они могут быть доступны и в версиях более ранних, чем те, что указаны в таблице Для проверки уровня строгости конкретной версии PL/SQL проанализируйте заголовок модуля (обычно находящийся в в каталоге 398 Таблица RESTRICT_REFERENCES во встроенных модулях Модуль Версия добавления прагмы Недоступна - в REGISTER содержится COMMIT DBMS_JOB Недоступна Ч задания выполняются отдельным процессом, и поэтому их нельзя вызывать из SQL DBMS_OUTPUT 7.3. 7.3. DBMS_SQL Недоступна Ч EXECUTE и PARSE можно использовать для выполнения операторов DDL, что может вызвать неявный COMMIT STANDARD 7.3.3 (включая процедуру 8.0. UTL HTTP 7.3. Параметры по умолчанию При вызове функции из процедурного оператора можно использовать для формальных параметров (при их наличии) значения по умолчанию.

Однако при вызове функции из SQL-оператора все параметры должны быть указаны. Кроме того, нужно применять позиционное представле ние;

именное представление недопустимо. Следующий вызов FullName неверный:

SELECT => 10000) FROM dual;

Вызов хранимых функций из SQL в Итак, с помощью прагмы реализуются уровни строгости этапа компиляции. До для вызова модульных функ ций из SQL прагма была обязательна. В г это ограничение смяг чено. Если прагма не база данных проверяет уровень строгости функции на этапе ее выполнения.

Это особенно полезно для внешних подпрограмм (написанных, на пример, Java или на С). В этом случае компилятор PL/SQL не может проверить уровень строгости, так как не он компилирует функцию (см.

главу 12). Следовательно, проверка должна производиться на этапе вы полнения подпрограммы.

Проверка происходит только тогда, когда функция вызывается из ис полняющего ее SQL-оператора. При наличии прагмы проверка не произ водится. Следовательно, прагмы позволяет сократить время исполнения функции, а также служит для документирования ее поведения.

Предположим, что из удалены прагмы:

Ч Этот пример содержится в файле CREATE OR REPLACE PACKAGE StudentOps AS FUNCTION IN students. ID%TYPE) Использование процедур, функций и модулей RETURN VARCHAR2;

/* Возвращает число студентов, профилирующей дисциплиной которых является история. */ FUNCTION RETURN NUMBER;

END Можно, как и раньше, вызывать эти функции из SQL:

SQL> SELECT 2 FROM students 3 WHERE major = Margaret Mason Patrick Poll Timothy Taller SQL> INSERT INTO 2 VALUES 1 row SQL> SELECT * FROM NUM_COL CHAR_COL Если попытаться вызвать в SQL-операторе неверную функцию, г выдаст сообщение об ошибке "ORA-14551: Cannot perform a DML ope ration inside a query" (невозможно выполнить операцию DML в запросе).

Рассмотрим функцию InsertTemp:

Ч Этот пример содержится в файле CREATE OR REPLACE FUNCTION InsertTemp( IN IN RETURN NUMBER AS BEGIN INSERT INTO VALUES p_Char);

RETURN 0;

END InsertTemp;

Ее вызов в операторе SELECT дает такой результат:

-- Этот пример содержится в файле SQL> SELECT 2 FROM SELECT InsertTempd, ERROR at line cannot perform a DML operation inside a query at "EXAMPLE. line 400 ГЛАВА at line Ключевое слово TRUST Хотя прагма теперь не обязательна (и на прак тике не может использоваться для внешних подпрограмм), в программах, созданных до г, применять ее можно. Она позволяет также уско рить обработку данных (см. выше). Таким образом, можно вызвать функ цию, не имеющую прагмы, из функции, объявленной как строгая. Для этого в i предусмотрено вспомогательное ключевое слово TRUST, задаваемое в прагме в дополнение к уровням строгости или вместо них.

Если ключевое слово TRUST присутствует, ограничения, указанные в прагме, не реализуются и считаются истинными априори. Это позволяет создавать новые программные конструкции, в которых не используется и вызывать их из функций, объявленных как строгие. Для примера рассмотрим следующий модуль:

ХХ- Этот пример содержится в файле CREATE OR REPLACE. PACKAGE TrustPkg AS FUNCTION ToUpper (p_a RETURN VARCHAR2 IS LANGUAGE JAVA NAME return PRAGMA RESTRICT_REFERENCES(ToUpper, TRUST);

PROCEDURE IN VARCHAR2, OUT PRAGMA END TrustPkg;

CREATE OR REPLACE PACKAGE BODY TrustPkg AS PROCEDURE IN OUT VARCHAR2) IS BEGIN p_out := ToUpper(p_in);

END Demo;

END TrustPkg;

Ч это внешняя тело функции написа но на Java, она возвращает свой входной параметр в символах верхнего регистра. Поскольку тело написано не на PL/SQL, для прагмы необходи мо ключевое слово TRUST. Затем можно вызывать ToUpper из процедуры Demo, поскольку предполагается, что эта функция обладает уровнем стро гости WNDS.

Внимание \ Хотя TrustPkg можно компилировать, не имея хранимой процедуры Java, выполнить ее невозможно, не создав сначала Вызов функций из операторов DML До г функция, вызываемая из оператора DML, не могла обновлять базу данных (т.е. ее уровень строгости должен был задаваться как В это ограничение смягчено. Теперь функция, вызываемая из процедур, функций и модулей оператора DML, не должна читать или модифицировать таблицы, изме няемые оператором DML, но может обновлять другие таблицы. Для при мера рассмотрим функцию -- Этот пример содержится в файле CREATE OR REPLACE FUNCTION IN RETURN students. ID%TYPE AS BEGIN INSERT INTO temp_table char_col) VALUES(p_ID, RETURN p_ID;

END UpdateTemp;

До исполнение следующего оператора обновления привело бы к ошибке:

-- этот пример содержится в файле SQL> UPDATE students 2 SET major = 3 WHERE = ID;

WHERE = ID ERROR at Function UPDATETEMP does not guarantee not to update database Однако в он разрешен, так как UpdateTemp не модифицирует таблицу students, лишь Внимание Функция, вызываемая из распараллеленного оператора не должна модифицировать базу данных, даже если таблицы в тот момент не изменяются.

Закрепление в разделяемом пуле Разделяемый пул (shared pool) Ч это часть SGA, где помимо всего прочего содержится р-код скомпилированных подпрограмм при их выполнении.

При первом вызове подпрограммы ее р-код загружается с дис ка в разделяемый пул. После того как объект перестает использоваться, информация о нем может быть удалена. Для удаления объектов из разде ляемого пула применяется алгоритм LRU (least recently used Ч использо вавшийся наиболее давно). Более подробно о разделяемом пуле и его работе можно узнать в руководстве "Oracle Concepts".

Модуль позволяет закреплять (pin) объекты в разделяемом пуле. Закрепленный объект не удалится из пула до тех пор, пока не будет выдана соответствующая команда. При этом неважно, на сколько заполнен пул и как часто происходит обращение к объекту. За крепление повышает производительность системы, так как загрузка модуля с диска занимает определенное время. Кроме того, закрепление объектов помогает снизить фрагментацию разделяемого пула. В модуле 402 ГЛАВА содержатся четыре процедуры:

KEEP Процедура используется для закрепления объектов в пуле. Можно закреплять модули, триггеры, последовательно сти, объектные типы и Java (в и выше), KEEP определяется следующим образом:

PROCEDURE flag DEFAULT Параметры этой процедуры описаны в таблице ниже. После того как объект закреплен, он не будет удален из пула до остановки базы данных или до выдачи команды Заметим, что не загружает модуль в разделяемый пул не медленно, а закрепляет его при первой последующей загрузке.

Параметр Тип Описание name Имя объекта или идентификатор, связанный с SQL-оператором. Идентификатор ратора Ч это конкатенация значений полей address и представления v$sqlarea (по умолчанию доступного только пользователю возвращается процедурой SIZES.

flag CHAR Определяет тип объекта. Возможные значения:

Р Ч пакет, функция или процедура Ч последовательность R Ч триггер Т Ч объектный тип и выше) JS Ч исходный Java и выше) JC Ч класс Java i и выше) JR Ч ресурс Java г и выше) JD Ч разделяемые Java и выше) С Ч курсор SQL UNKEEP Процедура UNKEEP Ч единственное средство, позволяющее удалить объ ект из разделяемого пула без перезапуска базы данных. Закрепленные объекты никогда не удаляются автоматически. Определение этой проце дуры выглядит следующим образом:

PROCEDURE flagCHAR DEFAULT Использование процедур, функций и модулей Аргументы аналогичны аргументам процедуры KEEP. Если указанного объекта нет в разделяемом пуле, возвращается сообщение об ошибке.

SIZES С помощью этой процедуры содержимое разделяемого пула выводится на экран. Определение процедуры SIZES выглядит следующим образом:

PROCEDURE SIZES( Возвращаются объекты, размер которых превышает значение minsize.

Для вывода информации процедура SIZES использует модуль DBMS_OUT PUT, поэтому перед ее вызовом обязательно выполните команду SET ON в или Server Manager.

Когда база данных определяет, что в разделяемом пуле не хватает памяти для удовлетворения запроса, она начинает удалять объекты, пока памяти не станет достаточно. Удаление большого числа объектов может повли ять на производительность других сеансов базы данных. Для решения этой проблемы служит Определе ние этой процедуры выглядит следующим образом:

NUMBER);

После вызова этой процедуры Oracle не будет запускать устаревшие объекты из пула, если им требуется больше байтов.

Итоги В этой главе обсуждались такие именованные блоки PL/SQL, как проце дуры, функции и модули. Рассказывалось о различиях между локальными и хранимыми подпрограммами, о зависимостях в хранимых подпрограм мах и о вызове хранимых подпрограмм из SQL-операторов. Был описан пакет В следующей главе рассматривается чет вертый вид именованных блоков PL/SQL Ч триггеры.

ГЛАВА Триггеры данных видом именованных блоков PL/SQL являются триггеры.

Триггеры во многом схожи с подпрограммами, но некоторые их характе ристики отличаются. В этой главе рассматривается создание различных триггеров и приводятся примеры их применения.

Типы триггеров Триггеры похожи на процедуры и функции тем, что также являются име нованными блоками PL/SQL и имеют раздел объявлений, выполняемый раздел и раздел обработки исключительных ситуаций. Подобно модулям, триггеры хранятся как автономные объекты в базе данных и не могут хра ниться локально в блоке или модуле. Процедура вызывается явным обра зом из другого блока, при вызове ей могут передаваться различные аргу менты. Триггер же выполняется неявно всякий раз, когда происходит запускающее его событие, и триггер не имеет аргументов. Акт выполне ния триггера называется его (firing). Событием, запускаю щим триггер, является операция DML (INSERT, UPDATE или DELETE), выполняемая над таблицей или представлением базы данных. В эти функции расширены: триггер может срабатывать на системное собы тие, например на запуск или останов базы данных, а также на определен ные виды операций DDL.

Триггеры можно использовать:

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

Х Для контроля за информацией, хранимой в таблице, посредством регистрации вносимых изменений и пользователей, производящих эти изменения.

Х Для автоматического оповещения других программ о том, что необ ходимо делать в случае информации, содержащейся в таблице.

Х Для публикации информации о различных событиях в среде "публи кация- подписка".

Триггеры делятся на три основных типа: триггеры DML, триггеры за мещения и системные триггеры.

Внимание В и выше можно создавать триггеры на PL/SQL или на других языках программирования, вызываются как внешние подпрограммы (см. ниже раздел триггера" и главу 12).

Триггеры базы данных Триггеры DML Триггер активизируется оператором DML, и тип триггера определя ется типом этого оператора. Триггеры DML задаются для операций вво да, обновления и удаления информации (INSERT, UPDATE, DELETE).

Они активизируются до или после операции, на уровне строки или оператора.

Предположим, что требуется отслеживать статистические показате ли, касающиеся различных профилирующих дисциплин студентов, в том числе количество зарегистрированных студентов и общее число получен ных зачетов. Результаты будут храниться в таблице Этот пример содержится в файле CREATE TABLE ( major VARCHAR2(30) NUMBER, NUMBER);

Чтобы информация в таблице была самой свежей, создадим триггер для таблицы students, который будет обновлять всякий раз при изменении students. Назовем этот триггер UpdateMajorStats. Он будет срабатывать после выполнения любой операции DML над students.

Тело триггера обращается к таблице students с запросом и обновляет ста тистические показатели таблицы свежей информацией:

Ч Этот пример содержится в файле sql CREATE OR REPLACE TRIGGER UpdateMajorStats /* major_stats, отслеживая все изменения, вносимые в таблицу students. */ INSERT OR DELETE OR UPDATE ON students DECLARE CURSOR c_Statistics IS SELECT major, total_students, SUM(current_credits) total_credits FROM students GROUP BY major;

BEGIN /* Сначала удалим информацию из очистив статистические данные.

Это необходимо для учета удаления всех студентов данного профиля. */ DELETE FROM /* Теперь в цикле просмотрим информацию по каждой дисциплине и введем соответствующую строку в */ FOR v_StatsRecord in LOOP INSERT INTO (major, total_credits, VALUES END LOOP;

END UpdateMajorStats;

408 ГЛАВА Операторный триггер может активизироваться операторами несколь ких видов. Например, срабатывает на операторы INSERT, UPDATE и DELETE. Активизирующее событие указывает одну или несколько операций DML, вызывающих выполнение триггера.

Триггеры замещения В предлагается еще один вид триггеров. Триггеры замещения (ins tead of) можно создавать только для представлений (либо объектных, либо В отличие от триггеров DML, которые выполняют ся в дополнение к операторам триггеры замещения выполняются вместо операторов DML, вызывающих их срабатывание. Триггеры заме щения должны быть строковыми триггерами. Для примера рассмотрим представление Этот пример содержится в файле CREATE OR REPLACE VIEW AS SELECT department, course, building, FROM rooms,> INSERT INTO (department, course, building, room_number) 2 VALUES 100, 200);

INSERT INTO (department, course, building, * ERROR at line cannot modify more than one base table through a join view Однако можно создать триггер замещения и с его помощью выпол нить обновление базовых таблиц:

Этот пример содержится в файле CREATE TRIGGER INSTEAD OF INSERT ON>

SELECT INTO FROM rooms WHERE building = AND = - А теперь обновим группу.

UPDATE>

Внимание В данном случае триггер>

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

-- Этот пример содержится в файле CREATE TABLE ( object_type VARCHAR2(20), VARCHAR2(30), object_owner creation date После этого можно создать системный триггер для регистрации нуж ных сведений. Триггер LogCreations регистрирует в таблице сведения о только что созданных объектах после каждой операции CREATE в текущей схеме.

-- Этот пример содержится в файле CREATE OR REPLACE TRIGGER LogCreations AFTER CREATE ON SCHEMA BEGIN INSERT INTO ddl_creations object_type, creation_date) VALUES (USER, END LogCreations;

Создание триггеров Вне зависимости от типа все триггеры создаются одинаково. Общий син таксис создания триггера таков:

CREATE [OR REPLACE] TRIGGER 410 | AFTER | INSTEAD [WHEN ROW] где Ч это имя триггера, указывает событие, которое запускает триггер (может содержать конкретную таб лицу или а Ч основной программный текст триггера. используется для ссылки на данные в мо дифицируемой в конкретный момент строке с помощью другого имени.

Если присутствует в конструкции WHEN (когда), то оно оценивается первым. Тело триггера выполняется только в том слу чае, если это условие истинно.

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

Ограничение размера тела триггеров обусловлено частотой их выполнения.

Создание триггеров DML Триггер DML активизируется операцией INSERT (ввод), UPDATE (об новление) или DELETE (удаление), выполняемой над таблицей базы дан ных. Триггеры могут активизироваться до (BEFORE) или после (AFTER) операции и действовать на уровне строки или оператора. Тип триггера определяется комбинацией этих факторов. Существует 12 возможных ви дов: 3 оператора х 2 момента времени х 2 уровня. Ниже приведены при меры правильных триггеров Х До выполнения операции обновления на операторном уровне Х После выполнения операции ввода на уровне строк Х До выполнения операции удаления на уровне строк Возможные варианты триггеров представлены в таблице Кроме того, триггер может активизироваться несколькими типами операторов DML, выполняемых над конкретной таблицей: например, INSERT и UPDATE. Код триггера выполняется вместе с активизирующим операто ром как часть одной транзакции.

Для таблицы можно создать любое число триггеров каждого вида, в том числе несколько триггеров определенного DML-типа. Например, можно описать два операторных триггера AFTER DELETE. Триггеры одного и того же типа будут срабатывать по очереди. (Порядок активи зации триггеров описывается в следующем разделе.) Триггеры базы данных Таблица Виды триггеров DML Категория Значение Комментарии Оператор INSERT, DELETE или Определяет, какой оператор активизирует триггер.

UPDATE Момент BEFORE или AFTER Определяет момент активизации триггера: до или после времени выполнения оператора.

Уровень Строка или оператор Если триггер является строковым, то он активизируется один раз для каждой из строк, на которые воздействует оператор, вызывающий срабатывание триггера. Если триггер является операторным, то он активизируется один раз до или после оператора. Строковые триггеры идентифицируются предложением FOR EACH ROW (для каждой в описании триггера.

Х Внимание.. '' До PL/SQL для таблицы можно было создать максимум 12 триггеров Ч по одному каждого вида.

Поэтому параметр COMPATIBLE должен быть установлен в значение или выше, чтобы можно было использовать для таблицы повторяющиеся триггеры одного типа.

В активизирующем событии триггера DML указывается имя таблицы (и для которой должен срабатывать триггер. В и выше триггер может активизироваться еще и для столбца вложенной таблицы (см. главу 8).

Порядок активизации триггеров DML Триггеры активизируются при выполнении оператора DML. Алгоритм выполнения оператора DML таков:

Выполняются операторные триггеры BEFORE (при их 2. Для каждой строки, на которую воздействует оператор:

Выполняются строковые триггеры BEFORE (при их Выполняется собственно оператор.

Выполняются строковые триггеры AFTER (при их 3. Выполняются операторные триггеры AFTER (при их наличии).

В качестве примера создадим различные триггеры UPDATE для табли цы>

- Этот пример содержится в файле CREATE SEQUENCE trig_seq START WITH INCREMENT BY 412 CREATE OR REPLACE PACKAGE TrigPackage AS - Глобальный счетчик для использования в триггерах NUMBER;

END TrigPackage;

CREATE OR REPLACE TRIGGER BEFORE UPDATE ON>

:= 0;

INSERT INTO (num_col, char_col) VALUES Statement: counter = ' | I - А теперь увеличим значение для следующего триггера.

:= 1;

END>

CREATE OR REPLACE TRIGGER AFTER UPDATE ON>

:= 1;

END>

CREATE OR REPLACE TRIGGER AFTER UPDATE ON>

:= 1;

END>

CREATE OR REPLACE TRIGGER BEFORE UPDATE ON>

:= 1;

END Триггеры базы данных CREATE OR REPLACE TRIGGER>

:= 1;

END CREATE OR REPLACE TRIGGER BEFORE UPDATE ON>

:= 1;

END CREATE OR REPLACE TRIGGER AFTER UPDATE ON>

:= 1;

END>

Выполним следующий оператор UPDATE:

-- Этот пример содержится в файле UPDATE>

Г) Ч Этот пример содержится в файле SQL> SELECT * FROM 2 ORDER BY 414 ГЛАВА CHAR_COL 1 Before Statement: counter = 2 Before Row 3: counter Before Row 2: counter Before Row 1: counter 5 After Row counter = A Row 6 Before 3: counter 7 Before Row 2 counter G 8 Before Row 1: counter 9 After Row : counter = 10 Before Row 3: counter = 11 Before Row 2: counter = 12 Before Row 1: counter = 13 After Row : counter = 14 Before Row 3: counter = 15 Before Row 2: counter = 16 Before Row 1: counter = 17 After Row : counter = 18 After Statement 2: counter = 19 After 1: counter = При активизации каждого из триггеров будут видны изменения, сде ланные предыдущими триггерами, а также изменения в базе данных, вне сенные оператором. Для этого каждый триггер выводит значение счетчика (о модульных переменных рассказывается в главе Порядок, в котором активизируются триггеры одного вида, не опреде лен. Из приведенного примера следует, что каждый триггер видит изме нения, вносимые более ранними триггерами. Если порядок важен, следует объединить все операции в один триггер.

Внимание При создании журнала моментальных снимков таблицы Oracle автоматически создает для нее строковый триггер ROW, обновляющий этот журнал после каждого оператора Следует учитывать этот факт при создании дополнительного строкового триггера AFTER для данной таблицы. Существуют и другие ограничения на использование триггеров и моментальных снимков как материализованные представления в Дополнительная информация содержится в "Oracle Server Replication".

Идентификаторы корреляции в строковых триггерах Строковый триггер запускается один раз для каждой строки, обрабатывае мой активизирующим оператором. Внутри триггера можно обращаться к данным строки, обрабатываемой в данный момент. Для служат два идентификатора корреляции Ч : old и : new. корреляции (correlation identifier) Ч это переменная привязки PL/SQL особого рода.

Двоеточие перед идентификатором указывает на то, что это переменные Триггеры базы данных привязки (подобны базовым переменным, используемым во встроенном PL/SQL), а не обычные переменные Компилятор рас сматривает их как записи типа где Ч это таблица, для которой создан триггер.

Следовательно, ссылка типа будет достоверна, если только поле является полем активизирующей таб лицы. Назначение : old и : new описано в таблице Хотя синтаксически они рассматриваются в качестве записей, фактически эти идентификато ры записями не являются. Именно поэтому их называют псевдозаписями.

Внимание Псевдозапись : old не определена для операторов INSERT, а для операторов DELETE не определена псевдозапись : new.

В случае использования. в операторе INSERT или new в операторе DELETE компилятор PL/SQL не будет генерировать ошибку, но значения полей обеих записей будут NULL.

В определен еще один идентификатор корреляции Ч :

Если триггер создается для вложенной таблицы, : old и : new ссылают ся на ее строки, а : parent Ч на текущую строку родительской таблицы (см. документацию Oracle).

Таблица 11.2. Идентификаторы корреляции и Активизирующий :old :new оператор INSERT He определено Ч во всех полях Значения, которые будут введены содержится NULL после выполнения оператора UPDATE Исходные значения, содержащиеся Новые значения, которые будут в строке перед обновлением данных введены после выполнения оператора DELETE Исходные содержащиеся Не определено Ч во всех полях в строке перед ее удалением содержится NULL Использование В триггере приведен ном ниже, применяется : new. Это триггер BEFORE, срабатывающий на оператор INSERT, и его назначением является заполнение поля ID табли цы students значением, генерируемым при помощи последовательности student_sequence.

ХХ- Этот пример содержится в файле CREATE OR REPLACE TRIGGER BEFORE INSERT OR UPDATE ON students FOR EACH ROW BEGIN /* Заполним поле ID таблицы students следующим значением из 416 ГЛАВА Поскольку ID - это столбец таблицы students, является допустимой ссылкой. */ SELECT INTO FROM dual;

END GenerateStudentID;

GenerateNewStudentID модифицирует значение ID. Это одно из по лезных свойств : new Ч когда выполнение оператора завершается, исполь зуются те значения, которые содержатся в : new. С помощью триггера GenerateNewStudentID можно выполнить оператор:

Этот пример содержится в файле INSERT INTO students last_name) Ошибка при этом не возникает. Хотя значение для столбца ID, являю щегося первичным ключом, указано не было (что обязательно), оно будет введено триггером. Если указать значение ID, оно будет проигнорирова но, так как триггер его изменит. Если выполнить оператор Этот пример содержится в файле INSERT INTO students (ID, VALUES (-7, то в столбец ID будет введено значение NEXTVAL, а не -7.

Именно поэтому нельзя изменить : new в строковом триггере AFTER, так как оператор будет обработан раньше. Вообще говоря, : new модифи цируется только в строковых триггерах BEFORE;

;

old никогда не моди фицируется, а лишь считывается.

Записи : new и : old разрешается использовать только в строковых триг герах. Если указать из них в операторном будет вы дана ошибка компиляции. Поскольку операторный триггер выполняется лишь однажды (даже в том случае, когда в операторе обрабатывается не сколько строк), псевдозаписи : old и : new не имеют никакого смысла. Дей ствительно, на какую из строк будет ссылаться каждая из них?

Псевдозаписи Хотя : new и : old синтаксически рассматриваются в каче стве записей типа в действитель ности они записями не являются. Поэтому операции, применимые к записям, не могут быть выполнены над : new и : old. Например, эти псевдо записи нельзя присваивать чему-либо в качестве целых записей. Присваи вать можно только отдельные их поля. Приведем пример:

-- Этот пример содержится в файле CREATE OR REPLACE TRIGGER BEFORE DELETE ON FOR EACH ROW DECLARE BEGIN /* Этот оператор неверен, так как не является настоящей записью. */ Триггеры базы данных /* Однако можно сделать то же самое, присвоив поля по отдельности. */ := := END Кроме : old и : new нельзя передавать процедурам или функциям, принимающим аргументы типа Конструкция REFERENCING При желании можно воспользоваться кон струкцией REFERENCING и указать другие имена для : old и : new. Эта кон струкция размещается после активизирующего события, перед условием WHEN:

REFERENCING [OLD AS AS В теле триггера вместо и можно использовать и Отметим, что в предложении REFERENCING идентификато ры указываются без двоеточия. Ниже представлен альтернативный вари ант триггера GenerateStudentID, в котором REFERENCING применяется для ссылки на на Этот пример содержится в файле CREATE OR REPLACE TRIGGER GenerateStudentID BEFORE INSERT OR UPDATE ON students REFERENCING new AS new _student FOR EACH ROW BEGIN /* Заполним поле ID таблицы students следующим значением из Поскольку ID - это столбец таблицы students, является допустимой ссылкой. */ SELECT INTO FROM dual;

END GenerateStudentID;

Предложение WHEN Предложение WHEN можно использовать только для строковых тригге ров. При наличии WHEN тело триггера будет выполняться только для тех строк, которые соответствуют условию, указанному в WHEN. Общий вид предложения WHEN таков:

WHEN где является логическим выражением, которое проверя ется для каждой строки. В условии можно ссылаться на записи : new и : old, но двоеточие в данном случае не применяется. Двоеточие можно указывать только в теле триггера. Например, тело триггера CheckCredits выполняется, если текущее число зачетов, полученных студентом, превы шает 20:

CREATE OR REPLACE TRIGGER CheckCredits 418 BEFORE INSERT OR OF ON students FOR EACH ROW WHEN 20) BEGIN /* Тело триггера */ END;

Триггер можно также написать следующим образом:

Г) CREATE OR REPLACE TRIGGER CheckCredits BEFORE INSERT OR UPDATE OF ON Students FOR EACH ROW BEGIN IF > 20 THEN /* Тело триггера */ END IF;

END;

UPDATING и DELETING Приведенный выше триггер является триггером INSERT, UPDATE и DELETE. Внутри триггера такого типа (который срабатывает на различные виды операторов DML) можно использовать три логические функции, определяющие тип выполняемой операции. Это логические функции (предикаты) UPDATING и DELETING. Их работа описывается в таблице ниже.

Предикат Принимаемое значение INSERTING TRUE, если активизирующий оператор INSERT;

FALSE в противном случае.

UPDATING TRUE, если активизирующий оператор UPDATE;

FALSE в противном случае.

DELETING TRUE, если активизирующий оператор DELETE;

FALSE в противном случае.

Внимание В определены дополнительные функции, вызываемые в теле триггера подобно предикатам (см. ниже раздел "Атрибутные событий").

В триггере LogRSChanges эти предикаты используются для записи всех изменений, вносимых в таблицу Записывается также пользователь, внесший изменение. Записи сохраняются в таблице которая выглядит следующим образом:

Ч Этот пример содержится в файле CREATE TABLE RS_audit ( change_type CHAR(1) NOT NULL, changed_by VARCHAR2(8) NULL, Триггеры базы данных DATE NOT NULL, old_department CHAR(3), CHAR(3), NUMBER(3), );

Создадим триггер LogRSChanges:

-- Этот пример содержится в файле LogRSChanges.

CREATE OR REPLACE TRIGGER LogRSChanges BEFORE INSERT OR DELETE OR UPDATE ON FOR EACH ROW DECLARE v_ChangeType BEGIN /* Используем для INSERT, для DELETE и для UPDATE. */ IF INSERTING THEN v_ChangeType : = ' UPDATING THEN v_ChangeType ELSE END IF;

/* Запишем в таблицу RS_audit все изменения, внесенные в таблицу registered_students. Для генерирования временной метки воспользуемся функцией SYSDATE, а для получения идентификатора текущего пользователя - функцией USER. */ INSERT INTO RS_audit timestamp, old_grade, VALUES (v_ChangeType, USER, SYSDATE, course, :new. course, END LogRSChanges;

Обычно триггеры используются для аудита (контроля) информации, что показано на примере триггера LogRSChanges. В базе данных Oracle имеется специальное средство, позволяющее осуществлять аудит данных, однако с помощью триггеров можно сделать аудит более гибким. К примеру, можно изменить LogRSChanges так, чтобы записывались только те изменения, рые вносят определенные пользователи. Можно также проверять наличие у пользователей полномочий на внесение изменений и устанавливать исклю чительную ситуацию (с помощью в том случае, когда полномочия отсутствуют.

420 Создание замещающих триггеров В отличие от триггеров DML, срабатывающих как дополнение к опера ции INSERT, UPDATE или DELETE (или до, или после них), замещаю щие триггеры активизируются вместо операций DML. К тому же замещающие триггеры создаются только для представлений, в то время как триггеры DML Ч для таблиц. Замещающие триггеры используются в двух случаях:

Х Для того чтобы сделать представление модифицируемым, если иначе это сделать нельзя.

Х Для модификации столбцов в столбце вложенной таблицы пред ставления.

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

Модифицируемые и немодифицируемые представления Модифицируемым (modifiable) называется такое представление, по отно шению к которому можно выполнить оператор DML. Как правило, пред ставление является если оно не содержит:

Х Операций над множествами (UNION, UNION ALL, MINUS) Функций агрегирования (SUM, AVG и Х Конструкций GROUP BY, CONNECT и START WITH Х Операции DISTINCT Х Соединений Существуют, однако, представления, которые содержат соединения и при этом являются модифицируемыми. Обычно представление с соедине нием может быть модифицировано, если операция DML, выполняемая над ним, одновременно модифицирует только одну базовую таблицу и если оператор DML отвечает условиям, приведенным в таблице (см. также "Oracle Concepts"). Если представление является немодифицируемым, то для него можно создать замещающий триггер, выполняющий нужные дей ствия и тем самым разрешающий его модификацию. Замещающий триггер можно создать и для модифицируемого представления, если требуется до полнительная обработка информации.

Таблица 11.3. Модифицируемые представления с соединениями Операция Разрешена, если INSERT Оператор не ссылается, явно или неявно, на столбцы таблицы, не сохраняющей ключи.

UPDATE Обновляемые столбцы отображаются на столбцы таблицы, сохраняющей ключи.

DELETE В соединении только одна таблица, сохраняющая ключи Триггеры базы данных В таблице 11.3 фигурируют таблицы, сохраняющие ключи. Таблица сохраняет ключи в том случае, если после соединения с другой таблицей ключи исходной таблицы становятся ключами результирующего соедине ния (см. руководство "Application Guide Ч Пример замещающего триггера Вновь обратимся к представлению Этот пример содержится в файле insteadOf.sql CREATE OR REPLACE VIEW AS SELECT department, course, building, FROM rooms,>

Операция Значение INSERT Назначить введенной группе указанную аудиторию.

В результате>

UPDATE Изменить аудиторию, назначенную группе. Это жет привести к обновлению или>

DELETE Очистить идентификатор аудитории для удаленной группы. В результате>

Триггер реализует сформулированные выше прави ла и позволяет правильно выполнить операцию DML над Это более полная версия триггера по сравнению с представленной выше:

-- Этот пример содержится в файле CREATE OR REPLACE TRIGGER>

BOOLEAN := FALSE;

- Локальная функция, возвращающая идентификатор аудитории по -- названию здания и номеру аудитории. Если эти сведения не 422 - найдены, функция устанавливает ORA-20000.

FUNCTION IN IN rooms.

RETURN IS BEGIN SELECT INTO FROM rooms WHERE building = p_Building AND = RETURN EXCEPTION WHEN NO_DATA_FOUND THEN matching END - Локальная процедура, проверяющая, существует ли группа, - идентифицируемая и p_Course. Если нет, - устанавливает PROCEDURE IN p_Course IN IS NUMBER;

BEGIN SELECT INTO FROM>

BEGIN IF INSERTING THEN - По существу, назначает группе конкретную аудиторию. Логика - здесь такая же, что и при обновлении аудиторий (см. ниже);

сначала Х- определяется идентификатор := Х- А затем в>

UPDATE>

Триггеры базы данных := != OR != v_UpdatingRooms := != OR != IF THEN - В этом случае изменяется группа для данной аудитории. Сначала - убедимся в том, что новая группа существует.

- Получим идентификатор аудитории.

- Затем очистим аудиторию для старой группы.

UPDATE>

UPDATE>

IF THEN - Здесь изменяется аудитория для данной группы. Логика - такая же. что и при вводе (см. выше), за исключением того, что Х->

old, а не :new.

Х- Сначала определим идентификатор новой аудитории.

- Затем обновим>

UPDATE>

ELSE - Очистим группу, связанную с аудиторией, не удаляя на самом деле - строки базовых таблиц.

UPDATE>

END>

ГЛАВА Внимание Предложение FOR EACH ROW необязательно для замещающего триггера. Эти триггеры являются строковыми независимо от того, указана эта конструкция или нет.

В для определения выполняемой операции и для соответствующей реакции на нее используются триггерные предикаты.

На рис. 11.1 показано исходное содержимое>

Этот пример содержится в файле INSERT INTO VALUES 100, 200);

>

>

Таблица>

Этот пример содержится в файле DELETE FROM WHERE building =>

>

CREATE TRIGGER | \ ON | WHEN] где Ч одно или несколько событий DDL (разделенных ключевым словом OR а Ч одно или несколько событий базы данных (также разделенных ключевым словом OR).

В таблице описаны события DDL и базы данных вместе с указани ем момента их обработки (BEFORE или Системные триггеры замещения создавать нельзя. Для оператора TRUNCATE не предусмотрено событие базы данных.

Таблица Системные события DDL и базы данных Событие Допустимое Описание время выполнения STARTUP AFTER Активизируется при запуске экземпляра базы данных.

SHUTDOWN BEFORE Активизируется при остановке экземпляра базы данных.

Это событие не активизирует если база данных останавливается аварийно.

SERVERERROR AFTER Активизируется при возникновении ошибки.

LOGON AFTER Активизируется после успешного соединения пользователя с базой данных.

LOGOFF BEFORE Активизируется перед отключением пользователя.

CREATE BEFORE, AFTER Активизируется до или после создания объекта схемы.

DROP BEFORE, AFTER Активизируется до или после удаления объекта схемы.

ALTER BEFORE, AFTER Активизируется до или после изменения объекта схемы.

Внимание Для создания системного триггера необходимо иметь системную привилегию ADMINISTER DATABASE TRIGGER (см. ниже раздел "Привилегии на триггеры").

Pages:     | 1 |   ...   | 3 | 4 | 5 | 6 | 7 |    Книги, научные публикации