Хранимая процедура представляет собой подпрограмму, расположенную на сервере и вызываемую из приложения клиента. Использование этих объектов увеличивает скорость доступа к БД по следующим причинам:
1. вместо текста запроса, который может быть достаточно длинным, серверу передается по сети относительно короткое обращение к хранимой процедуре;
2. хранимая процедура, в отличие от запроса, не требует предварительной синтаксической проверки.
Еще одним преимуществом при обращении к хранимым процедурам является то, что будучи общими для всех приложений-клиентов, они реализуют единые для них правила работы с БД.
Язык хранимых процедур
Для написания хранимых процедур и триггеров используется язык хранимых процедур. Язык хранимых процедур сервера InterBase представляет собой процедурный алгоритмический язык, а его синтаксис имеет много общего с синтаксисом языка Pascal, который составляет основу Delphi. Язык хранимых процедур включает в свой состав инструкции, позволяющие управлять ходом вычислительного процесса (условную инструкцию и инструкцию цикла). Кроме того, язык хранимых процедур обладает рядом возможностей языка SQL.
Рассмотрим кратко основные составляющие языка хранимых процедур.
В текст процедуры (триггера) можно вставлять комментарии.
Для ограничения комментариев используются символы /* и */.
В язык хранимых процедур включены инструкции:
1. объявления переменных;
2. присваивания;
3. условные;
4. составные;
5. цикла;
6. выбора записи;
7. выбора нескольких записей (набора данных);
8. возврата значений;
9. выхода из процедуры;
10. вызова процедуры;
11. посылки сообщения.
Инструкции должны заканчиваться точкой с запятой (кроме составной инструкции).
Инструкция объявления переменных имеет следующий формат: DECLARE VARIABLE <Имя переменной> <Тип переменной>
Виды хранимых процедур
По числу строк, возвращаемых в качестве результата, можно выделить следующие виды хранимых процедур:
1. возвращающие одну строку;
2. возвращающие несколько строк.
Процедуры, возвращающие одну строку, практически не отличаются от процедур языка Pascal и обеспечивают возврат значений выходных параметров. Такие хранимые процедуры также называют процедурами действия.
Процедуры, возвращающие несколько строк, передают набор данных, записями в котором являются строки результатов. Такие хранимые процедуры также называют процедурами выбора. В их теле размещаются совместно используемые инструкции выбора нескольких записей и возврата значений — FOR SELECT ... DO ... SUSPEND, которые и обеспечивают отбор требуемых записей и построчную передачу значений их столбцов в точку вызова.
Вызов хранимой процедуры представляет собой обращение к процедуре с передачей исходных данных для обработки и последующим получением результатов. Исходные данные передаются через входные параметры, а результаты возвращаются через выходные параметры. В зависимости от вида хранимой процедуры различаются способы ее вызова.
Вызов хранимой процедуры выбора
Хранимая процедура выбора возвращает набор данных, состоящий в общем случае из нескольких записей. Поэтому при ее вызове нужно обеспечить возможность приема совокупности записей. В связи с этим хранимая процедура выбора, как правило, вызывается с помощью инструкции выбора SELECT, внутри которой размещается вызов процедуры.
Например: SELECT * FROM pSalary2 (2000, 3000)
Здесь вызывается хранимая процедура psalary2, возвращающая фамилии и оклады сотрудников, значение оклада которых принадлежит заданному диапазону.
Замечание
Хранимую процедуру выбора в программе IBConsole также можно вызвать инструкцией EXECUTE PROCEDURE, например, так: EXECUTE PROCEDURE pSalary2 (2000, 3000)
В этом случае будет получена только первая запись набора данных. Напомним, что на практике такая инструкция вызова процедуры обычно используется только при отладке хранимых процедур выбора.
В приложении инструкция SELECT, вызывающая хранимую процедуру выбора, выполняется с помощью набора данных Query, для чего используется его свойство SQL.
Рассмотрим пример:
procedure TForml.ButtonlClick(Sender: TObject);
begin
Query1.Close;
Query1.SQL.Clear;
Query1.SQL.Add('SELECT * FROM pSalary2 (2000, 3000)');
Query1.Open;
end;
При нажатии кнопки Button1 свойству SQL компонента Query1 присваивается код вызова хранимой процедуры pSalary2, затем запрос выполняется. В результате набор данных Query1 содержит записи, удовлетворяющие заданному в процедуре условию отбора. Отметим, что столбцы набора данных образуют выходные параметры процедуры, в данном случае ими являются столбцы opName и opSalary.
Замечание
Для соединения с удаленной БД в форме, кроме набора данных Query, следует разместить компонент Database.
В приведенном примере SQL-запрос является статическим, т. к. входные параметры, ограничивающие диапазон оклада, передаются процедуре через явно указанные значения 2000 и 3000. Чтобы менять границы диапазона, можно считывать их значения, например, из компонентов Edit (полей редактирования).
Например: Query1.SQL.Add('SELECT * FROM pSalary2 (' + Edit1.Text + ',' + Edit2.Text + ')');
Значения нижней и верхней границ диапазона для оклада вводятся в поля редактирования Editl и Edit2 соответственно.
Значения входных параметров процедуры также можно передавать через параметры компонента Query1 (свойство Params). При этом запрос компонента Query1 будет иметь вид:
'SELECT * FROM pSalary2 (:pMin, :pMax)'
Здесь границы диапазона для оклада задаются двумя параметрами: pMin — для нижней, а рMах — для верхней границы. Эти параметры являются входными и предназначены для передачи вещественных значений. Поэтому для них нужно задать следующие значения свойств:
1. DataType (тип данных) — ftFloat;
2. ParamType (тип параметра) — ptInput.
Управление свойствами параметров осуществляется с помощью Инспектора объектов и Редактора параметров SQL-запроса, который вызывается щелчком в области значения свойства Params в окне Инспектора объектов.
Вызов хранимой процедуры действия
Хранимая процедура действия вызывается инструкцией следующего формата:
EXECUTE PROCEDURE <Имя процедуры> [(<Список входных параметров>)]
Инструкция вызывает указанную процедуру, передавая ей значения входных параметров. Данная инструкция используется в программе IBConsole, при этом результаты работы процедуры (значения выходных параметров) выводятся в окне результатов выполнения запроса.
В программе IBConsole с помощью инструкции EXECUTE PROCEDURE также можно отлаживать хранимые процедуры выбора. Однако в этом случае в качестве их результатов возвращается только первая строка (первая запись набора данных).
Для вызова хранимой процедуры действия из приложения предназначен компонент storedProc. Рассмотрим свойства и особенности использования этого компонента.
Свойство DatabaseName типа String указывает на компонент Database, используемый для соединения с БД.
Свойство storedProcName типа string определяет вызываемую хранимую процедуру. Имя процедуры выбирается через список Инспектора объектов. Если при попытке выбрать процедуру соединение с БД еще не установлено (свойство Connected компонента Database имеет значение False), то выдается запрос на его установление. После ввода имени и пароля пользователя происходит соединение с БД, а свойство connected компонента Database устанавливается в значение True.
Когда имя хранимой процедуры задано, становятся доступными ее входные и выходные параметры, определяемые значением свойства Params типа TParams. Это свойство определяет коллекцию (массив) параметров компонента storedProc, работа с которыми аналогична работе с параметрами SQL-запроса компонента Query.
Свойство paramBindMode типа TParamBindMode определяет, каким образом устанавливается соответствие между параметрами компонента storedProc и параметрами процедуры. Оно может принимать следующие значения:
1. pbByName (соответствие устанавливается по именам) — по умолчанию; имена параметров компонента storedProc и соответствующих параметров процедуры должны совпадать;
2. pbByNumber (соответствие устанавливается по номерам); первый параметр компонента storedProc соотносится с первым параметром процедуры, второй — со вторым и т. д.
Выполнение выбранной хранимой процедуры осуществляется последовательным вызовом методов Prepare и ЕхесРгос. Метод prepare подготавливает хранимую процедуру к выполнению путем связывания параметров компонента storedProc и параметров процедуры. Метод ЕхесРгос выполняет процедуру.
При необходимости перед выполнением процедуры можно установить или изменить значения ее входных параметров аналогично тому, как это делается для параметров SQL-запроса. Результаты работы процедуры возвращаются в выходных параметрах. Для доступа к параметрам при выполнении приложения удобно использовать метод ParamByName.
Рассмотрим пример, иллюстрирующий вызов хранимой процедуры действия:
procedure TForml.ButtonlClickfSender: TObject);
begin
StoredProc1.StoredProcName := 'pSalary';
StoredProc1.Prepare;
StoredProc1.ЕхесРгос;
Edit1.Text := StoredProcl.ParamByName('opSum').Value;
Edit2.Text := StoredProcl.ParamByName('opAvg').Value;
end;
Здесь вызывается процедура действия pSalary. Ее результаты — значения выходных параметров opSum и opAvg — выводятся в компонентах Editi и Edit2 соответственно. Для вызова процедуры используется компонент StoredProcl.
Использование триггеров
Триггер представляет собой процедуру, которая находится на сервере БД и вызывается автоматически при модификации записей БД, т. е. при изменении столбцов или при их удалении и добавлении. В отличие от хранимых процедур, триггеры нельзя вызывать из приложения клиента, а также передавать им параметры и получать от них результаты.
Триггер цо своей сути похож на обработчики событий BeforeEdit, AfterEdit, Beforelnsert, Afterlnsert, BeforeDelete и AfterDelete, связанных с модификацией таблиц. Триггер может вызываться при редактировании, добавлении или удалении записей до и/или после этих событий.
Замечание
Изменения, внесенные триггером в транзакции, которая оказалась отмененной, также отменяются.
Триггеры обычно используются для программной реализации так называемых бизнес-правил. С их помощью удобно реализовывать различные ограничения, например, ограничения на значения столбцов или ограничения ссылочной це
лостности, а также выполнять такие действия, как накапливание статистики, работы с БД или резервное копирование записей.
Создание и изменение триггера
Создание триггера выполняется инструкцией CREATE TRIGGER, имеющей формат:
CREATE TRIGGER <Имя триггера> FOR <Имя таблицы>
[ACTIVE I INACTIVE]
{BEFORE | AFTER}
{UPDATE | INSERT | DELETE} [POSITION <Число>]
AS <Тело триггера>
Описатели ACTIVE и INACTIVE определяют активность триггера сразу после его создания. По умолчанию действует ACTIVE, и созданный триггер активен, т. е. при наступлении соответствующего события будет выполняться. Если триггер неактивен, то при наступлении соответствующего события он не вызывается. Ранее созданный триггер можно активизировать или, наоборот, деактивизировать.
Описатели BEFORE и AFTER задают момент начала выполнения триггера до или после наступления соответствующего события, связанного с изменением записей.
Описатели UPDATE, INSERT и DELETE определяют, при наступлении какого события вызывается триггер — при редактировании, добавлении или удалении записей соответственно.
Для одного события можно создать несколько триггеров, каждый из которых будет автоматически выполнен (если находится в активном состоянии). При наличии нескольких триггеров порядок их вызова (выполнения) определяет число, указанное в операнде POSITION. Триггеры выполняются в порядке возрастания этих чисел.
Созданный триггер можно удалить или изменить. Удаляется триггер инструкцией DROP TRIGGER <Имя триггера>.
Изменение триггера выполняется инструкцией ALTER TRIGGER, формат которой не отличается от формата инструкции создания триггера. После выполнения инструкции ALTER TRIGGER предыдущее описание триггера с указанным именем заменяется на новое.
Программирование триггера аналогично программированию хранимой процедуры, для чего используется специальный язык, позволяющий также создавать хранимые процедуры.
Отметим, что для доступа к значениям столбца используются конструкции формата:
OLD.<Имя столбца>
NEW.<Имя столбца>
Первая из них позволяет обратиться к старому (до внесения изменений), а вторая — к новому (после внесения изменений) значениям столбца.
Значения OLD и NEW
Значение OLD.ИмяСтолбца позволяет обратиться к состоянию столбца, имевшему место до внесения изменений, а значение NEW.ИмяСтолбца — к состоянию столбца после внесения изменений.
Например, механизм обеспечения ссылочной целостности "cascade", в случае изменения значения первичного ключа родительской таблицы, на мнемоническом языке можно описать так:
IF (OLD.PrimaryKeyPoдитeля <> NEW.РгimагуКеуРодителя) THEN
UPDATE ДочерняяТаблица
SET ForeignКеуДочернейТаблицы = NEW.РгimагуКеуРодителя
WHERE ForeignКеуДочернейТаблицы = OLD.PrimaryKeyРодителя;
В качестве примера приведем код триггера для родительской таблицы Realty, который при изменении значения ее первичного ключа будет автомагически изменять значение внешнего ключа дочерней таблицы Lease. Другими словами, если в таблице Realty изменилось значение поля Adr, то триггер изменит значение поля Adr в соответствующей записи таблицы Lease.
CREATE TRIGGER UPDAT_REALTY FOR Realty
ACTIVE
BEFORE UPDATE
AS
BEGIN
IF (OLD.Adr <> NEW.Adr)
THEN UPDATE Lease
SET Adr = NEW.Adr
WHERE Adr = OLD.Adr;
END
В том случае, если значение в столбце не изменилось, то OLD.ИмяСтолбца будет равно NEW.ИмяСтолбца.
Проверим вышеизложенное.
1. Запустите утилиту Interactive SQL.
2. Создайте триггер UPDAT_REALTY.
3. Попробуйте изменить значение адреса в таблице Realty. Это не удается. Дело в том, что сервер InterBase поддерживает механизм обеспечения ссылочной целостности "prohibit". В данном случае он реализован на основании декларации связи таблиц FOREIGN KEY (AdR) REFERENCES Realty (AdR) в скрипте tables.sql при создании таблицы Lease.
4. Удалите эту связь (ограничение INTEG_7):
ALTER TABLE Lease
DROP CONSTRAINT INTEG_7
5. Можете убедиться, что она отсутствует: теперь таблица Lease не связана с Realty.
6. Измените значение адреса в таблице Realty.
Как видите, теперь при изменении значения ее первичного ключа триггер автоматически изменяет значение внешнего ключа дочерней таблицы Lease. Другими словами, реализован механизм обеспечения ссылочной целостности "cascade".
Примеры использования триггера
Рассмотрим использование триггера для реализации ограничений ссылочной целостности и для занесения в ключевые столбцы уникальных значений.
В связи с тем, что InterBase не поддерживает автоинкрементные поля, при создании ключевого столбца, требующего уникальности значений, рекомендуется поступать так:
1. При создании таблицы задать ключевой столбец целочисленного типа.
2. Создать генератор, который при обращении к нему возвращает уникальное целочисленное значение.
3. Создать триггер, который при добавлении к таблице новой записи обращается к генератору и заносит возвращаемое им значение в ключевое поле.
Следующий пример иллюстрирует описанную последовательность действий:
/* Создание таблицы */
CREATE TABLE Store
(S_Code INTEGER NOT NULL,
...
PRIMARY KEY (S_Code));
/* Создание генератора */
CREATE GENERATOR GenStore;
SET GENERATOR GenStore TO 1;
/* Создание триггера */
CREATE TRIGGER CodeStOre FOR Store
ACTIVE
BEFORE INSERT
AS
BEGIN
NEW.S_Code = GEN_ID(GenStore, 1);
END
После добавления к таблице store новой записи ключевому столбцу s_Сode этой записи автоматически присваивается уникальное значение. Это обеспечивается обращением GEN_ID к генератору GenStore, который создается отдельно от триггера (работа с генераторами рассмотрена ниже).
Ограничения ссылочной целостности для связанных таблиц включают в себя:
1. каскадное удаление записей;
2. запрет на редактирование ключевых столбцов.
Рассмотрим, как можно реализовать каскадное удаление записей с участием триггера.
Пусть имеются две таблицы: главная Store и подчиненная Cards, связанные по полям кода s_code и c_code2 соответственно:
CREATE TABLE Store
(S_Code INTEGER NOT NULL,
...
PRIMARY KEY (S_Code));
CREATE TABLE Cards
(C_Code INTEGER NOT NULL,
C_Code2 INTEGER NOT NULL,
PRIMARY KEY (C_Code) ) ;
Каскадное удаление записей для связанных таблиц заключается в том, что если из главной таблицы удаляется запись, то и в подчиненной таблице должны быть удалены все соответствующие ей записи.
В нашем случае это выполняется следующим образом:
CREATE TRIGGER DeleteStore FOR Store
ACTIVE
AFTER DELETE
AS
BEGIN
DELETE FROM Cards WHERE Store.S_Code = Cards.C_Code2;
END
После удаления записи в таблице store (склад) будут автоматически удалены все соответствующие записи в таблице Cards (движение товара).
Замечание
Для таблиц не должны действовать ограничения ссылочной целостности, заданные на физическом уровне, например, так:
CONSTRAINT rStoreCards
FOREIGN KEY (C_Code2) REFERENCES Store
В противном случае при попытке удалить запись из главной таблицы генерируется исключение. Если есть такие ограничения, то их можно удалить, например, с помощью программы SQL Explorer.
Аналогичным способом можно реализовать и обновление столбцов связи (ключевых столбцов) связанных таблиц, заключающееся в том, что при изменении значения столбца связи главной таблицы соответственно изменяются значения столбца связи всех связанных записей подчиненной таблицы.
Например:
CREATE TRIGGER ChangeStore FOR Store
ACTIVE
BEFORE UPDATE
AS
BEGIN
IF (OLD.S_Code <> NEW.S_Code)
THEN UPDATE Cards
SET C_Code2 = NEW.S_Code
WHERE C_Code2 = OLD.S_Code;
END
При изменении столбца S_code, используемого для связи главной таблицы store с подчиненной таблицей cards, автоматически изменяются значения столбца связи C_code2 соответствующих записей подчиненной таблицы.
Замечание
Чтобы столбец связи главной таблицы можно было редактировать, по нему не должен быть создан ключ. Если ключ создан, то его следует удалить, например, с помощью программы SQL Explorer. Более кардинальным решением проблемы является создание таблицы заново с помощью инструкции ALTER TABLE.
Поскольку использование триггеров не допускает существования ограничений на физическом уровне (при определении структуры таблиц), необходимо обеспечить также занесение значения в столбец связи подчиненной таблицы при добавлении в нее новой записи.
Создание генераторов
Напомним, что, в отличие от базы данных Paradox, для таблиц InterBase отсутствует автоинкрементный тип, обеспечивающий автоматическую установку уни^ кальных значений. Поэтому для обеспечения уникальности значений ключевых столбцов совместно с триггерами используются генераторы. Генератор возвращает уникальное целочисленное значение.
С помощью языка SQL-сервера можно создать генератор и установить для него начальное значение.
Генератор создается следующией инструкцией:
CREATE GENERATOR <Имя генератора>;
Начальное значение задается инструкцией:
SET GENERATOR <Имя генератора> ТО <Начальное эначение>;
Начальное значение представляет собой целое число, начиная с которого формируется числовой ряд.
Обращение к созданному генератору выполняется с помощью функции: GET_ID (<Имя генератора>,<Шаг>);
Эта функция озвращает значение, увеличенное на целочисленный шаг относительно предыдущего значения генератора.
|
