Иптышев А.А., Морозов Д.И., Городилов А.А., Иптышев М.А. Базы данных
Подождите немного. Документ загружается.
101
{BEFORE / AFTER} {DELETE / INSERT / UPDATE
[POSITION number
AS < trigger_body>
< trigger_body> :=
[< variable_declaration_list>;
< block>
< variable_declaration_list> := DECLARE VARIABLE variable datatype;
[< variable_declaration_list>
< blоск> : =
BEGIN
<L_statement>
END
<L_statement> ;=
< compound_statement>
<L_statement>
< compound_statement> = {< block> / statement;}
Таблица 17
Синтаксические конструкции команды CREATE TRIGGER
Описание
Параметр
Name Имя триггера. Имя должно быть уникальным в базе
данных
table | view
Имя таблицы, для которой создается триггер
ACTIVE | INACTIVE Необязательная конструкция. Определяет активность
триггера. ACTIVE - триггер включен, INACTIVE -
триггер отключен.
BEFORE | AFTER
Обязательный параметр. Определяет, когда включается
триггер:
BEFORE - перед операцией, выполняемой надтаблицей.
AFTER - после операции, выполняемой над таблицей
DELETE | INSERT | UPDATE
Указывает, при выполнении какой именно операции с
таблицей будет включаться триггер: DELETE - удаление,
INSERT -вставка, UPDATE – модификация.
POSITION number POSITION number задает порядок, в котором будут
выполняться триггеры (с одной таблицей и одними и
теми же условиями включения может быть связано
несколько триггеров), number должен быть целым числом
между 0 (по умолчанию) и 32767 включительно.
Включение триггеров происходит в порядке возрастания
номеров (number). Нумерация может и не быть
последовательной. Если триггеры имеют один и тот же
номер, то они будут включаться в алфавитном порядке
их имен.
DECLARE
ARIABLE variable datatype;
Объявляет локальные переменные, используемые только
в триггере. Объявление каждой переменной начинается с
ключевых слов DECLARE VARIABLE и заканчивается
";". Имена объявляемых переменных должны быть
уникальными в триггере, datatype задает тип локальной
переменной.
<compound_statement>
Оператор SQL для хранимых процедур и
триггеров. Оператор завершается ";", если это не блок
BEGIN-END. После END ";" не ставится.
102
Рассмотрим триггер для контроля добавления данных в таблицу авторов.
Наличие подобных триггеров обеспечивает полноту и корректность строк таблиц.
CREATE TRIGGER I_TAUTHOR_1 FOR TAUTHOR
ACTIVE BEFORE INSERT POSITION 0
as
begin
if (new.AUTHOR is NULL) then
new.AUTHOR=GEN_ID(sysnumber,1);
if (new.COMMENT is NULL) then
new.COMMENT="
end;
103
16. ХРАНИМЫЕ ПРОЦЕДУРЫ – ИНСТРУМЕНТ ПОВЫШЕНИЯ
ЭФФЕКТИВНОСТИ ПРИЛОЖЕНИЯ БД
Хранимая процедура - отдельная программа, написанная на SQL для процедур
и триггеров InterBase. Сами процедуры хранятся в базе данных. Хранимые
процедуры позволяют вести поиск и обработку данных непосредственно на сервере,
обеспечивая максимальную независимость клиентской части приложений. В них
могут использоваться любые конструкции SQL для процедур и триггеров, кроме
контекстных переменных NEW.co/wmn, OLD.column, применимых только в
триггерах. Они, как обычные программы, могут получать входные параметры и
возвращать значения вызвавшим их приложениям. Кроме того, могут возвращать не
только отдельный набор значений - строку, но и множество строк, которое можно
рассматривать как виртуальную таблицу.
Хранимая процедура может также вызываться непосредственно из
приложения или других хранимых процедур или триггеров. Хранимые процедуры,
возвращающие множество строк, можно использовать в команде SELECT на месте
таблиц или обзоров. Использование хранимых процедур дает ряд преимуществ:
- Модульность проектирования. Приложения, которые обращаются к одной
базе данных, могут совместно использовать хранимые процедуры, устраняя двойной
код, уменьшая размер приложений и устраняя потенциальные ошибки.
- Локализация изменений. Если процедура модифицируется, то все внесенные
изменения автоматически отражаются во всех приложениях, которые используют
процедуру, обеспечивая их согласованность. При этом нет необходимости в
перетрансляции и перекомпоновке приложений.
- Ускорение обработки. Хранимые процедуры выполняются сервером, а не
клиентом, что позволяет ускорить обработку запросов и сократить сетевой трафик.
Последнее особенно важно для удаленного клиентского доступа.
Процедуры по своему назначению разделяются на два вида: выполнимые
процедуры и процедуры выбора.
Выполнимая процедура - это обычная программа, которая получает несколько
(возможно, и ноль) параметров, выполняет какие-либо действия в базе данных и
возвращает несколько (возможно, и ноль) значений.
Процедура выбора - это программа, которая получает несколько (возможно, и
ноль) параметров, выполняет какие-либо действия в базе данных и возвращает
множество (возможно, пустое) наборов значений.
Другими словами, процедура выбора создает вычисляемую таблицу, хотя
такая таблица никуда и не записывается. Это позволяет обращаться к процедуре, как
к таблице, используя команду SELECT.
Синтаксически можно обращаться к выполнимой процедуре, как к процедуре
выбора, получая при этом в ответ в точности одну строку, а к процедуре выбора, как
к выполнимой, получая при этом в ответ первую строку формируемой таблицы.
104
Ценность такой инверсии обращений в лучшем случае нулевая, поэтому, несмотря
на отсутствие прямого запрета ее использования, нет оснований для практического
применения инверсных вызовов хранимых процедур.
16.1.1. Создание хранимых процедур
Хранимые процедуры создаются командой CREATE PROCEDURE в ISQL. Во
внедренном SQL команда недоступна. Хранимая процедура состоит из заголовка и
тела. Заголовок хранимой процедуры содержит:
- Имя процедуры, которое должно быть уникальным среди имен процедур,
таблиц и обзоров в базе данных.
- Необязательный список параметров процедуры, передаваемых процедуре
вызывающими приложениями, с указанием их типов данных.
Для процедур, возвращающих значения вызывающим приложениям,
необходимо указать список возвращаемых параметров с указанием их типов.
Список указывается после ключевого слова RETURNS.
Тело процедуры содержит:
- Необязательный список локальных переменных с указанием их типов.
Блок операторов на языке процедур и триггеров, заключенный в операторные
скобки BEGIN-END. Сам блок может содержать внутри себя другие блоки.
Количество вложений блоков практически не ограничено.
При использовании для ввода команды Script-файла необходимо помнить, что
в соответствии с синтаксисом операторы внутри процедуры отделяются друг от
друга символом ";". В то же время команды в Script-файле также отделяются ";"•
Чтобы устранить неоднозначность разделе команд, необходимо установить какой-
либо иной ограничитель текста команд. Для этого следует непосредственно перед
заданием команды CREATE PROCEDURE выполнить команду SET TERM,
устанавливающую такой ограничитель, а в конце работы той же командой SET
TERM восстановить стандартный (";")•
Синтаксис команды CREATE PROCEDURE:
CREATE PROCEDURE name [(LIST_<params>)]
[RETURNS (LIST_<params>)]
AS
<procedure__body> ;
LIST_<params> : : = param datatype
<procedure_body> :: = [ <variable_declaration_list>J
<block>
<variable_declaration_list> ;:=
DECLARE VARIABLE var datatype;
[<variable_declaration_list>]
< block> ::=
BEGIN
<L_statement>
105
END
<L_statement> ;;= < compound_statement>
[<L_statement>]
< compound_statement> = {< block> / statement;}
Таблица 18.
Синтаксические конструкции команды CREATE PROCEDURE
Конструкция Описание
name Имя процедуры. Должно быть
уникальным среди имен процедур,
таблиц и обзоров в базе данных
param <datatype> Параметр процедуры, передаваемый
ей вызывающими приложениями,
param - имя параметра, уникальное
среди имен параметров и переменных
процедуры, <datatype> - допустимый
в InterBase тип данных.
RETURNS param <datatype> Выходные значения
(параметры) процедуры,
возвращаемые ею вызывающим
приложениям, param - имя выходного
параметра, уникальное среди имен
параметров и переменных
процедуры, <datatype> - допустимый
в InterBase тип данных. Процедура
возвращает значения выходных пара-
метров вызывающим приложениям
по достижении ее конца или по
команде SUSPEND в теле процедуры.
AS Ключевое слово, отделяющее
заголовок процедуры от ее тела.
DECLARE VARIABLE var <datatype>
Объявляет локальные переменные,
используемые в процедуре. Каждое
объявление должно начинаться с
ключевых слов DECLARE
VARIABLE и заканчиваться ";". Один
оператор объявляет одну
переменную, var - имя переменной,
уникальное среди имен параметров и
переменных процедуры. <datatype> -
допустимый в InterBase тип данных.
statement Любой допустимый на языке
процедур и триггеров оператор.
Оператор должен оканчиваться
106
символом ";" (кроме операторных
скобок BEGINEND)
Процедура, которая возвращает список книг, генерирует по таблице авторов
для каждой книги полный список ее авторов через запятую.
CREATE PROCEDURE PBUTHOR(CODE INTEGER)
RETURNS (AUTHORS VARCHAR(250))
AS
declare variable auname varchar(60);
declare variable UNIKEY integer;
declare variable ws integer;
begin
ws=-l;
for select a.UNIKEY, b.auname
from tbook a, tauthor b, tbook_author с
where (a.unikey=:Code and a.unikey=c.bookkey and
с.author=b.author)
into :UNIKEY, :auname
do
begin
if(ws=-l) then authors=auname;
else authors=authors||', '||auname;
WS=UNIKEY;
end
if(ws!=-l) then suspend;
end;
16.1.2. Изменение хранимых процедур
Изменение хранимых процедур осуществляется командой ALTER
PROCEDURE. Команда ALTER PROCEDURE выполняется в ISQL. Во внедренном
SQL команда недоступна.
ALTER PROCEDURE изменяет описание хранимой процедуры при условии
сохранения корректности ссылок на нее со стороны объектов базы данных.
Изменения, внесенные в хранимую процедуру, сразу становятся доступными для
всех использующих ее приложений. Перекомпиляция соответствующих
приложений не требуется.
Команда ALTER PROCEDURE может быть выполнена создателем процедуры,
пользователем SYSDBA или другим пользователем с аналогичными правами.
При изменении хранимой процедуры новое описание замещает
существующее. Необходимо помнить, что изменение состава, последовательности и
типов входных и выходных параметров процедур, как и интерфейса прикладных
107
программ, может повлечь необходимость внесения изменений в вызывающих
программах.
На практике можно рекомендовать предварительно выбрать существующую
хранимую процедуру, затем внести в нее изменения и загрузить командой AFTER
PROCEDURE. Конкретная последовательность действий зависит от того, какие
средства используются для работы с базой.
Синтаксис команды ALTER PROCEDURE аналогичен синтаксису команды
CREATE PROCEDURE:
ALTER PROCEDURE name [(LIST_<params>)]
[RETURNS (LIST_<params>)]
AS
<procedure_body>;
name - имя существующей хранимой процедуры.
Состав параметров и опций команды полностью аналогичен параметрам и
опциям команды CREATE PROCEDURE (см. разд. Создание
хранимых процедур).
ALTER PROCEDURE PBUTHOR(CODE INTEGER)
RETURNS (AUTHORS VARCHAR(250))
AS
declare variable auname varchar(60);
declare variable UNIKEY integer;
declare variable ws integer;
begin
ws=-l;
for select a.UNIKEY, b.auname
from tbook a, tauthor b, tbook_author с
where (a.unikey=:Code and a.unikey=c.bookkey and
с.author=b.author)
into :UNIKEY, :auname
do
begin
if(ws=-l) then authors=auname;
else authors=authors||' - '||auname;
/* в исходном тексте разделителем в списке
была запятая */
ws=UNIKEY;
end
if(ws!=-l) then suspend;
end;
108
16.1.3. Удаление хранимых процедур
Удаление хранимых процедур осуществляется командой DROP PROCEDURE,
которая выполняется в ISQL. Во внедренном SQL команда недоступна.
Команда DROP PROCEDURE удаляет существующую в базе данных
процедуру, и может быть выполнена создателем процедуры, пользователем
SYSDBA или другим пользователем с аналогичными правами. Для успешного
выполнения команды DROP PROCEDURE необходимо выполнение ряда условий.
Нельзя удалить процедуру, если она используется другими процедурами,
триггерами или обзорами базы данных.
Перед удалением процедуры необходимо внести соответствующие изменения
в объекты, использующие удаляемую процедуру. Нельзя удалить
процедуру, если она используется незавершенной транзакцией. Необходимо
завершить транзакцию и только после этого удалить процедуру.
Команда DROP PROCEDURE недоступна во внедренном SQL, необходимо
использовать динамический SQL.
Синтаксис команды:
DROP PROCEDURE name;
name - имя существующей процедуры.
DROP PROCEDURE PBUTHOR;
16.1.4. Использование команд Alter Procedure и Drop Procedure
При внесении изменений в процедуры или их удалении необходимо помнить,
что в базе хранятся ссылки на них, если они используются другими объектами базы.
В этом случае их изменение должно быть запрещено во избежание разрушения
логической целостности базы.
Если процедура используется внешним приложением, то целостность
приложений не может контролироваться средствами базы, поэтому, прежде чем
вносить изменения, необходимо убедиться в отсутствии нежелательных
последствий от таких изменений.
Изменения желательно проводить, когда с базой не работают приложения,
использующие процедуры. В противном случае нельзя быть уверенным, с какой
именно версией процедуры работают приложения.
При выполнении запросов на работу процедуры последняя помещается в кэш
метаданных и остается там до завершения запроса. Изменения в процедуре
становятся видимыми на клиенте после его отсоединения и повторного соединения
с базой.
16.1.5. Обращение к выполнимой процедуре
При обращении к выполнимой процедуре из другой выполнимой процедуры
или триггера используется следующий синтаксис:
EXECUTE PROCEDURE name [LIST_: paramj
109
/•RETURNING_VALUES [LIST_: par am ];
name - имя процедуры,
param - входной параметр или возвращаемое значение.
Символ «:» здесь является синтаксическим элементом.
При обращении к выполнимой процедуре из ISQL используется следующий
синтаксис.
EXECUTE PROCEDURE name [ ( ] [LIST_param] [ ) ];
name - имя процедуры,
param - входной параметр.
ОБРАЩЕНИЕ К ПРОЦЕДУРЕ ВЫБОРА
При обращении к процедуре выбора используется следующий синтаксис:
SELECT < col_list> from name (LIST param)
[WHERE < search_condition>]
[ORDER BY < order_list>];
name - имя процедуры,
param - входной параметр процедуры,
< col_list> - список выбираемых выражений (должен базироваться на списке
возвращаемых значений процедуры),
<search_condition>, < order_list> - условия выборки и способ упорядочения
результатов.
В целом структура команды SELECT не зависит от того, каким образом
выбираются данные (см. разд. 3.1). Синтаксически использование процедуры от
таблицы или обзора отличается только тем, что после имени процедуры могут
указываться ее параметры.
110
17. ОПТИМИЗАЦИЯ РАБОТЫ И СРЕДСТВА РАСШИРЕНИЯ БД
Средством расширения БД являются пользовательские функции позволяющие
расширит базовую функциональность БД. Пользовательские функции (User defined
functions - UDF) - это программы на базовом языке для выполнения в приложениях,
часто используемых при работе с базой данных задач. UDF помогают обеспечить
модульность приложений, выделяя их в отдельные модули многократного
использования.
К UDF и фильтрам BLOB можно обращаться через isql или из программ на
базовом языке. К UDF можно также обращаться из хранимых процедур и триггеров.
Пользовательские функции не являются частью базы. Они помещаются в
отдельную библиотеку (DLL), находящуюся на той же машине, что и база данных.
Чтобы сделать функции доступными в базе, их надо в ней объявить. После этого
обращение к ним осуществляется точно так же, как к стандартным функциям
InterBase. Другими словами, вызов этих функций может производиться везде, где
синтаксисом SQL предусмотрено использование выражений.
Чтобы можно было использовать внешнюю функцию, она должна быть
объявлена в базе как внешняя функция, а реализующий ее программный код
помещен в библиотеку, указанную в объявлении.
17.1. ОБЪЯВЛЕНИЕ ВНЕШНЕЙ ФУНКЦИИ
Как только UDF была написана и откомпилирована в библиотеку, она должна
быть объявлена в базе данных, где предполагается ее использовать. Для этого
предусмотрена команда DELCARE EXTERNAL FUNCTION.
Каждая функция в базе данных должна быть объявлена отдельно. Пока точка
входа и имя модуля (библиотеки) и путь к нему не изменяется, нет необходимости в
переобъявлении функции, даже если сама функция изменяется.
Синтаксис объявления внешней функции следующий:
DECLARE EXTERNAL FUNCTION name
[\LIST_datatypes]
[RETURNS (datatype [BY VALUE] / CSTRING ( int)] [FREE_IT]
ENTRY_POINT 'entryname'
MODULE_NAME 'moduIename';
datatypes ; : = {datatype / CSTRING ( i n t )}
datatype - любой разрешенный в InterBase тип данных кроме массивов и
BLOB.
CSTRING ( int) - специальный тип для представления строк, представляющий
собой последовательность символов, заканчивающуюся двоичным
FREE_IT - специальная конструкция, обеспечивающая указывающая на
необходимость освобождения памяти выделенной пользовательской функцией для