Справочные ограничения целостности (Referential integrity constraints)

InterBase позволяет вам определять правила обеспечивающие целостность информации хранящейся в столбцах, эти првавила названы справочными ограничениями целостности (referential integrity constraints). Ограничения целостности управляют связями типа столбец-таблица (column-to-table) и таблица-таблица (table-to-table) а также проверкой ввода данных. Они выпонены через первичные ключи (primary keys), внешние ключи (foreign keys) и проверочные ограничения (check constraints). Обычно первичный ключ это столбец (или группа столбцов), которые используются, чтобы уникально идентифицировать строку таблицы. Внешний ключ это столбец, чьи значения должны соответствовать значениям столбца в другой таблице. Проверочные ограничения - ограничивают ввод данных определенным диапазоном или набором значений.

Например, таблица EMPLOYEE могла бы быть определена имеющей внешний ключ столбец DEPT_NO. Который определен в соответствии со столбцом номера отдела в таблице DEPARTMENT. Это гарантировало бы, что каждый служащий из таблицы EMPLOYEE связан с существующим отделом в таблице DEPARTMENT.

Индексы (Indexes)

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

InterBase автоматически определят уникальные индексы для первичных и внешних ключей таблицы.

Представления (Views)

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

 

Хранимые процедуры (Stored procedures)

Хранимые процедуры (stored procedure) это отдельные программы, написанные на языке процедур и триггеров InterBase, который является расширением SQL. Сохраненные процедуры являются частью метаданных базы данных. Сохраненные процедуры могут получать входные параметры, возвращать значения приложению и могут быть вызваны явно из приложения или подстановкой вместо имени таблицы в инструкции SELECT.

Сохраненные процедуры обеспечивают следующие возможности:

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

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

· Улучшают эффективность работы: Особенно для удаленных клиентов. Сохраненные процедуры выполняются сервером, а не клиентом, что снижает сетевой трафик.

Триггеры (Triggers)

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

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

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

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

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

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

· Завершения операции, возможно с сообщением об ошибке.

· Установки значений в записи к торой вы обращаетесь.

· Добавления, изменения или удаления строк в других таблицах.

Генераторы (Generators)

Генератор (generator) это механизм который создает последовательный уникальный номер, который автоматически вставляется в столбец базой данных, когда выполняются операции INSERT или UPDATE. Генератор обычно применяется для создания уникальных значений, вставляемых в столбец, который используется как PRIMARY KEY. Для базы данных может быть определено любое число генераторов, каждый генератор должен имеет уникальное имя.

Защита (Security)

SQL защита (securite) управляется на уровне таблицы привилегий доступа - списка операций, которые разрешены пользователю над данной таблицей или видом. Инструкция GRANT назначает привилегии доступа к таблице или виду конкретным пользователям или процедурам. Инструкция REVOKE удаляет предварительно предоставленные привилегии доступа.

Операторы SQL для работы с объектами БД

Представления

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

 

Создание

CREATE VIEW имя_представления [(имя_столбца [,имя_столбца...])] AS <select_выражение> [WITH CHECK OPTION]; имя_представления - имя для вида. Должно быть уникальным среди всех имен видов, таблиц и процедур в базе данных.имя_столбца имена столбцов вида. Имена столбцов должны быть уникальны среди всех имен столбцов в виде. Требуется, если вид содержит столбцы, основанные на выражении; иначе факультативен. По умолчанию, имена столбцов берутся из базовой таблицы.<select_выражение> select_выражение.WITH CHECK OPTION – предотвращает использование представления совместно с операциями INSERT и UPDATE. Особенности использования:1. Если в представлении не указаны столбцы – то они берутся из <select_выражение>.2. Количество <имя_столбца> указано явно, то они должны совпадать со столбцами из <select_выражение>3. В <select_выражение> не могут использоваться сортировки ORDER BY

 

Обратите внимание: Любые столбцы, используемые в значении выражения, должны присутствовать раньше чем выражение может быть определено.

Изменение

нельзя

Удаление

DROP VIEW имя_представления; имя_представления Имя существующего представления, для удаления

Примеры

CREATE VIEW "A_UNI_B" (

"ID_SOTR",

"FAM",

"ZAP"

) AS

select * from a

union

select * from b;

 

Хранимые процедуры

 

Хранимые процедуры (stored procedure) – это отдельные программы, написанные на языке SQL процедур и триггеров InterBase, который является расширением SQL. Сохраненные процедуры являются частью метаданных базы данных. Сохраненные процедуры могут получать входные параметры, возвращать значения приложению и могут быть вызваны явно из приложения или подстановкой вместо имени таблицы в инструкции SELECT.

Сохраненные процедуры обеспечивают следующие возможности:

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

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

3. Улучшают эффективность работы: Особенно для удаленных клиентов. Сохраненные процедуры выполняются сервером, а не клиентом, что снижает сетевой трафик.

 

Важно: Т.к. каждая инструкция в теле сохраненной процедуры должна завершатся точкой с запятой, вы должны определить другой символ для завершения инструкции CREATE PROCEDURE в SQL. Используйте SET TERM пред CREATE PROCEDURE чтобы определить терминатор отличный от точки с запятой. После инструкции CREATE PROCEDURE, включите SET TERM, что бы изменить терминатор обратно к точке с запятой

Создание

CREATE PROCEDURE имя_проц[(имя_параметра <тип> [,имя_параметра <тип>...])] [RETURNS имя_параметра <тип> [,имя_параметра <тип>...])]

AS <тело_процедуры> [terminator]

имя_проц Имя процедуры.param <datatype> Входные параметры, используемые процедурой и тип данныхRETURNS param <datatype> Выходные параметры, используемые процедурой. Допустимые типы данных перечислены в Типы данных.<тело_процедуры> Тело процедуры. Включает: ·Объявления локальных переменных. · Блок инструкций на языке процедур и триггеров.

terminator Терминатор, определенный для SQL командой SET TERM, указывающий конец тела процедуры

Изменение

ALTER PROCEDURE имя_проц [(имя_параметра <тип> [,(имя_параметра <тип>...])] [RETURNS ((имя_параметра <тип> [,(имя_параметра <тип>...])] AS <тело_процедуры> [terminator]

Удаление

DROP PROCEDURE имя_проц;

Примеры

SET TERM ^;

 

CREATE PROCEDURE "MyProc"(

"ID" INTEGER)

AS

BEGIN EXIT; END ^

 

ALTER PROCEDURE "MyProc"

("LID" INTEGER,

"LFAM" CHAR(30),

"LYEARS" INTEGER,

"LSTATUS" CHAR(10))

AS

BEGIN

INSERT INTO sotrudnik(id_sotr, fam, years, status)

VALUES(:lid,:lfam,:lyears,:lstatus);

END^

SET TERM; ^

 

Чтобы выполнить эту процедуру необходимо выполнить команду:

execute procedure MyProc(123, 'Ленин', 35, 'Вождь');

commit;

А теперь удалим процедуру:

DROP PROCEDURE MyProc;

 

Еще один пример в котором используются циклы FOR:

CREATE PROCEDURE “SET_DEF_USER”(

“NewDefValue” CHAR(10),

“OldDefValue” CHAR(10))

RETURNS (

“Number” INTEGER)

AS

DECLARE VARIABLE Count INTEGER;

DECLARE VARIABLE TempID INTEGER;

BEGIN

:Count = 0;

FOR

SELECT “Status”, “ID_Status”

FROM “StatusTable”

WHERE “Status” =:OldDefValue

INTO:NewDefValue,:TempID

DO

BEGIN

UPDATE “StatusTable”

SET “Status” =:NewDefValue

WHERE “ID” =:TempID;

:Count =:Count + 1;

END;

:Number =:Count;

END^

 

Генераторы

Создание

CREATE GENERATOR имя_генератора;

Генератор можно проинициализировать начальным значением. Это бывает нужно тогда, например когда удаляются все записи и таблица наполняется по новому. Значения генератора могут быть в диапазоне от -2^31 до 2^31 - 1.

SET GENERATOR имя_генератора TO число;

 

Генератор используется совместно с функцией GEN_ID:

GEN_ID (имя_генератора, шаг);

 

Изменение

Удаление

Примеры

insert into street(id_street, street)

values(gen_id(gnstreetid, 1), 'Куба')

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

Триггеры

Создание

CREATE TRIGGER имя_триггера FOR имя_табл [ACTIVE | INACTIVE] {BEFORE | AFTER} {DELETE | INSERT | UPDATE} [POSITION number] AS <тело_триггера> terminator

имя_триггера Имя существующего триггера.

ACTIVE Определяет, что действие триггера дает эффект, когда срабатывает (по умолчанию).

INACTIVE Определяет, что действие триггера не дает эффекта.

BEFORE Определяет, что триггер срабатывает перед ассоциированной операцией.

AFTER Определяет, что триггер срабатывает после ассоциированной операцией.

DELETE | INSERT | UPDATE Определяет операцию над таблицей, с которой связывает триггер to fire.

POSITION число Определяет порядок в котором срабатывают триггеры перед или после того же самого действия. число должно быть целым от 0 до 32767. Триггер с меньшим номером срабатывает первым. Триггеры для того же самого действия, с тем же самым позиционным номером, буду fire в случайном порядке.

тало_триггера Тело триггера, блок инструкций на языке процедур и триггеров.

Изменение

ALTER TRIGGER имя_триггера[ACTIVE | INACTIVE] [{BEFORE | AFTER} {DELETE | INSERT | UPDATE}] [POSITION number] [AS <тело_триггера>] [terminator]

Удаление

DROP TRIGGER имя_триггера;

Примеры

SET TERM ^;

CREATE TRIGGER "ADDSTREET" FOR "STREET"

ACTIVE BEFORE INSERT POSITION 0

as

begin

street.id_street=gen_id(gnstreetid, 1);

end

^

 

CREATE TRIGGER "DELSTREET" FOR "STREET"

ACTIVE BEFORE DELETE POSITION 0

as

begin

delete from person

where person.id_street = street.id_street;

end

^

 

SET TERM;^

 

Индексы

Создание

CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]]INDEX index ON table (col [, col...]); UNIQUE Предотвращает вставку или модификацию данных, приводящих к дублированию значений в индексированных столбцах.ASC[ENDING]/DESC[ENDING] Сортирует столбцы в возрастающем порядке/ в убывающем порядке. По умолчанию – сортирует по возрастанию.index Уникальное имя для индекса.table Имя таблицы для которой индекс определен.col Столбец в индексируемой таблице, собственно для которой и строится индекс.

Важно:

1) Столбцы типов BLOB и массивы не могут быть индексированы;

2) UNIQUE индекс не может быть создан на столбце или наборе столбцов, которые уже содержат повторяющиеся или NULL значения.

Изменение

ALTER INDEX name {ACTIVE | INACTIVE}; name Имя существующего индекса.

ACTIVE Включает индекс/выключает индекс

Удаление

Важно: Индексы определенные системой, как UNIQUE, PRIMARY KEY и FORGIN KEY ограничений целостности, не могут быть удалены.

DROP INDEX name;name Имя существующего индекса

Примеры

В принципе все просто, поэтому их пока нет.

Инструкции SQL

Определение переменных

DECLARE VARIABLE var <тип_данных>;[DECLARE VARIABLE var <datatype>;...] <тип_данных> может быть числового, строкового типа или дата.Используются переменный обычным образом. Перед именем переменной ставится «:»

FOR <select_выражение> DO <инструкция>	Повторение инструкции или блока, который следует за DO, для каждой допустимой строки возращенной <select_выражение>. · <select_выражение>: нормальный оператор SELECT, за исключением, INTO предложение требуется и должно быть последним. · <инструкция>: или одиночная инструкция (оператор) на языке процедур и триггеров или блок инструкций заключенный в BEGIN и END.
IF (<условие>) THEN <инструкция> [ELSE <инструкция>]	Проверяет <условие>, и если оно TRUE, выполняет инструкцию или блок, следующий за THEN; иначе, выполняет инструкцию или блок следующий за ELSE, если он существует.
WHILE (<условие>) DO <инструкция>	Пока <условие> TRUE, выполняется <инструкция> Первое <условие> проверяется и если оно TRUE, то выполняется <инструкция>.

Пример использования инструкций:

Следующая процедура возвращает количество сотрудников, работающих в должности переданной строковым параметром (но не ее идентификатором), например «Доцент» и общее количество денег выплачиваемых им в качестве зарплаты.

SET TERM ^;

ALTER PROCEDURE num_sotr(

dolg CHAR(10)

)

RETURNS(

num integer,

sum_money integer

)

AS

DECLARE VARIABLE id_d INTEGER;

BEGIN

num=0;

FOR SELECT id_d

FROM dolgnost

WHERE dolg=:dolg

INTO:id_d

DO

BEGIN

SELECT COUNT(*)

FROM a

WHERE

a.id_d=:id_d

INTO num;

END

 

sum_money=0;

IF (num>0) THEN

BEGIN

sum_money = sum_money * num;

END

END ^

SET TERM; ^