Дополнительные сведения по работе с генераторами
Примеры
Синтаксис
SET TERM string;
Аргумент | Описание |
string | Определяет символ или символы, чтобы использовать для завершения инструкции. По умолчанию: точка с запятой (;). |
Следующий пример показывает текстовый файл, который использует SET TERM при создании процедуры. Первый SET TERM определяет ##, как завершающие символы; соответствующий SET TERM восстанавливает точку с запятой (;), как завершающий символ.
SET TERM ##;
CREATE PROCEDURE ADD_EMP_PROJ (EMP_NO SMALLINT, PROJ_ID CHAR(5))
AS
BEGIN
BEGIN
INSERT INTO employee_project (emp_no, proj_id)
VALUES (:emp_no,:proj_id);
WHEN SQLCODE -530 DO
EXCEPTION unknown_emp_id;
END
RETURN;
END ##
SET TERM; ##
Генератор – это специальный объект базы данных, который генерирует уникальные последовательные числа. Эти числа могут быть использованы в качестве идентификаторов (например код клиента, номер счета и т. п.). Для создания генератора необходимо использовать оператор DDL
CREATE GENERATOR generatorname;
При выполнении такой команды происходит 2 действия:
На специальной странице БД отводится 4 байта для хранения значения генератора
|
|
В системной таблице RDB$GENERATORS заводится запись, куда помещается имя генератора и его номер (фактически смещение на странице генераторов).
После создания генератора его значения можно получать при помощи функции
GEN _ ID ( generatorname , inc _ value ), где inc_value – число, на которое необходимо прирастить значение генератора.
Генераторы возвращают значения (и сохраняют свои значения на диске) вне контекста транзакции пользователя. Это означает, что если генератора было увеличено с 10 до 11 (инкремент 1), то даже при откате транзакции (ROLLBACK) значение генератора не вернется к предыдущему. Вместе с этим гарантируется, что каждому пользователю будет возвращено уникальное значение генератора.
При выборке значения генератора запросом вида select gen_id(genname, x) from... следует учитывать буферизацию выборки на клиенте. Т. е. в многопользовательской среде при выполнении двух таких запросов значения генератора будут увеличиваться «пачками», а не на величину x для каждой выбираемой записи.
Использование генераторов в триггерах и хранимых процедурах
Пример триггера, автоматически присваивающего уникальное значение ключевому полю таблицы:
Создадим генератор для уникальной идентификации клиентов:
CREATE GENERATOR NEWCLIENT;
Создадим триггер для таблицы CLIENTS:
CREATE TRIGGER TBI_CLIENTS FOR CLIENTS
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
NEW.CLIENT_ID = GEN_ID(NEWCLIENT, 1);
END
В результате при создании новой записи полю CLIENT_ID будет автоматически присваиваться новое значение.
Однако при использовании генератора в триггере возникает проблема на клиентской стороне (например в BDE, используемом в Delphi, C++Builder...), когда клиентское приложение пытается перечитать только что вставленную запись. Поскольку триггер меняет значение первичного ключа вставляемой записи, BDE «теряет» такую запись и чаще всего выдает сообщение «Record/Key deleted». Поскольку SQL-сервер не может сообщить клиентскому приложению о новом значении ключевого поля, необходимо сначала запросить уникальное значение с сервера, и только затем использовать его во вставляемой записи. Сделать это можно при помощи хранимой процедуры:
|
|
CREATE PROCEDURE GETNEWCLIENT
RETURNS ( NID INTEGER )
AS
BEGIN
NID = GEN_ID(NEWCLIENT, 1);
END
В Delphi, вы можете поместить компонент TStoredProc на форму, подсоединить его к данной процедуре, и например в методе таблицы BeforePost написать следующее
begin
if DataSource.State = dsInsert then
begin
StoredProc1.ExecProc;
ClientTable.FieldByName('CLIENT_ID').asInteger:=
StoredProc1.Params[0].asInteger;
end;
end;
После этого вышеприведенный триггер TBI_CLIENTS можно либо удалить, либо переписать так, чтобы генератор использовался только когда поле первичного ключа случайно приобрело значение NULL (например когда к таблице CLIENTS доступ осуществляется не через ваше приложение):
ALTER TRIGGER TBI_CLIENTS
AS
BEGIN
IF (NEW.CLIENT_ID IS NULL) THEN
NEW.CLIENT_ID = GEN_ID(NEWCLIENT, 1);
END
Однако использование хранимой процедуры не всегда удобно – BDE может решить, что процедура вероятно изменяет какие-то данные на сервере, и в режиме autocommit завершит текущую транзакцию, что вызовет перечитывание данных TTable и TQuery. Более простым способом является получение значения генератора при помощи запроса:
SELECT GEN_ID(NEWCLIENT, 1) FROM RDB$DATABASE;
При этом, если запрос помещен например в Query2, текст в BeforePost будет следующим:
begin
if DataSource.State = dsInsert then
begin
Query2.Open;
ClientTable.FieldByName('CLIENT_ID').asInteger:=
Query2.Fields[0].asInteger;
Query2.Close;
end ;
end ;
Такой способ более предпочтителен, чем использование хранимой процедуры для получения значения генератора, особенно при большом количестве генераторов.