Иптышев А.А. Курс лекций по дисциплине базы данных

Подождите немного. Документ загружается.

121

Пример создания домена

CREATE DOMAIN MONTH AS SMALLINT CHECK( VALUE BETWEEN 1 AND 12);

Объявляет тип поля для хранения номера месяца с указанием ограни-

чения для контроля вводимых данных.

15.3.2 Изменение доменов

Изменение доменов осуществляется командой ALTER DOMAIN.

С помощью команды ALTER DOMAIN можно изменить любые харак-

теристики домена, кроме типа данных (включая размеры массива) и устано-

вок NOT NULL. Сделанные изменения воздействуют на атрибуты (поля) всех

таблиц, где использовался измененный домен. Команду ALTER DOMAIN

может выдать либо его создатель, либо пользователь SYSDBA, либо другой

пользователь с правами системного администратора.

Синтаксис команды ALTER DOMAIN выглядит следующим образом:

ALTER DOMAIN name {

[SET DEFAULT { literal / NULL / USER}]

| [DROP DEFAULT]

| [ADD [CONSTRAINT] CHECK { <dom_condition>)]

| [DROP CONSTRAINT]

};

Пример изменения домена:

ALTER DOMAIN DM_INTEGER ADD CHECK (VALUE>0);

Добавление проверки на положительность вводимых чисел.

15.3.3 Удаление домена

Удаление доменов осуществляется командой DROP DOMAIN. С по-

мощью этой команды можно удалить описание домена. Если домен исполь-

зуется в каких-либо таблицах, то удалить его нельзя (соответствующая ко-

манда завершится аварийно).

Синтаксис команды DROP DOMAIN выглядит следующим образом:

DROP DOMAIN name;

Пример удаления домена:

DROP DOMAIN DM_INTEGER;

122

15.4 Таблицы

Таблица является набором информации с жестко заданной структурой,

т.е при создании таблицы определяется сколько места на жестком диске бу-

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

следующие описания столбцов: имя (уникальное в пределах таблицы), тип

данных хранящихся в столбце, является ли данных столбец ключевым (пер-

вичным или внешним), значения по умолчанию, вычисляемые значения и др.

15.4.1 Создание таблицы

Создание таблиц осуществляется командой CREATE TABLE

Команда CREATE TABLE имеет следующий синтаксис:

CREATE TABLE table [EXTERNAL FILE “<filespec>”]

(LIST_<col_def> [, LIST_<tconstraint>J);

table - имя создаваемой таблицы. Имя таблицы внутри базы должно

быть

уникальным,

EXTERNAL [FILE] "<filespec>" задает таблицу, данные которой раз-

мещаются во внешней (не InterBase) таблице или файле,

<col_def> - описание поля (атрибута в терминах отношений) таблицы,

<tconstraint> - описание ограничений логической целостности для таб-

ли-

цы в целом.

Описание полей таблицы

Для описания полей <col_def> используется следующий синтаксис:

<col_def>:= col {datatype | COMPUTED [BY] (< expr>) / domain}

[DEFAULT {literal \ HULL \ USER}]

[NOT NOLL] [<col_constraint>]

[COLLATE collation]

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

(поля):

col - имя столбца; должно быть уникальным в пределах таблицы;

( datatype \ COMPUTED [BY] (< expr>) / domain) задают тип данных в

столбце, где datatype - любой допустимый в InterBase тип данных.

123

domain — имя определенного ранее домена (описание домена, в свою

очередь, содержит описание типа данных);

COMPUTED [BY] (< expr>) - выражение для вычисляемого столбца.

Значения вычисляемых столбцов рассчитываются в соответствии с задавае-

мым выражением всякий раз при обращении к ним. Выражение может быть

любым допустимым в InterBase выражением, возвращающим единственное

значение простого типа (не массив).

При создании таблицы должны быть указаны название таблицы и хотя

бы одно поле.

В следующем примере создается таблица с первичным ключом, и на-

званием университета.

CREATE TABLE "UNIVER"

// Создается таблица с названием «UNIVER”

("ID" INTEGER NOT NULL,

// Числовое поле, значение которого не должно быть равно

NULL

"CAPTION" VARCHAR(50),

// Строковое поле, длинной 50 символов

PRIMARY KEY ("ID")

// Поле «ID» является первичным ключом);

Пример создания таблицы с внешним ключом:

CREATE TABLE "STUDENT"

("ID" INTEGER NOT NULL,

//Числовое поле, значение которого не должно быть равно

NULL

"NAME" VARCHAR(30),

// Строковое поле, длинной 30 символов

"SECOND_NAME" VARCHAR(30),

// Строковое поле, длинной 30 символов

"FAMILY" VARCHAR(30),

// Строковое поле, длинной 30 символов

"BIRTHDAY" DATE,

// Поле хранящее даты

“ID_UNIVER” INTEGER,

// Определяем поле которое будет внешним ключом

PRIMARY KEY ("ID")

//Поле «ID» является первичным ключом);

В этом примере создается таблица, в которой имеется внешний ключ к

таблице предыдущего примера.

124

15.4.2 Модификация таблицы

При создании приложения БД, обнаруживаются новые требования к

таблице БД. Для изменения существующих таблиц, используется команда

ALTER TABLE. Эта команда позволяет добавлять, удалять, изменять столб-

цы таблицы. Также этот оператор используется для удаления и добавления

ключей и индексов.

Рассмотрим добавление в таблицу столбца:

ALTER TABLE "STUDENT"

// Изменяется таблица "STUDENT"

ADD “course” INTEGER NOT NULL;

// Добавляется поле “COURSE”, значение которого не может быть равным нулю

Этот скрипт добавляет в таблицу student столбец course, в случае если в

таблице уже есть столбец с таким именем, произойдет исключительная си-

туация.

Удаление столбца из таблицы:

ALTER TABLE "STUDENT"

// Изменяется таблица "STUDENT"

DROP “COURSE”;

// Удаляется поле “COURSE”

При проектировании базы данных возникает потребность переимено-

вать столбцы таблицы, для этого также используется оператор ALTER TA-

BLE.

Alter table "STUDENT"

ALTER “course” TO “course_”

15.4.3 Удаление таблицы

Для удаления таблицы используется оператор Drop Table. Перед удале-

ние таблицы необходимо удалить все зависимые от нее компоненты (табли-

цы, триггеры, хранимые процедуры, представления). Пример удаления таб-

лицы:

DROP TABLE “STUDENT”;

125

15.5 Индексы

Индексы предназначены для ускорения поиска данных на запросы в

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

ваться для контроля уникальности ключевых выражений. В определенном

смысле индекс является аналогом оглавления или алфавитного указателя

(дающих номера страниц), то есть по значению индекса мы сразу находим

место, где находятся интересующие строки таблиц.

В индексе хранятся значения индексированного столбца или столбцов

наряду с указателями на все дисковые блоки, которые содержат строки с со-

ответствующими значениями.

15.5.1 Создание индексов

Создание индексов происходит с помощью команды CREATE INDEX.

Синтаксис команды CREATE INDEX:

CREATE INDEX [ASC/ENDING / DESC/ENDING]

INDEX index ON t a b l e (LIST_col);

ASC/ENDING/ или DESC/ENDING/ - задает способ упорядочения

данных.

ASC или ASCENDING - индекс создается по возрастанию ключей.

DESC или DESCENDING - индекс создается по убыванию ключей.

index задает имя индекса.

table задает имя индексируемой таблицы.

col задает имя столбца, по значениям которого строится индекс; таких

столбцов может быть несколько.

UNIQUE задает режим уникального индекса. При задании этого режи-

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

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

является и уникальным. В терминах теории отношений уникальный ключ яв-

ляется потенциальным ключом отношения и так же, как и первичный, может

использоваться для идентификации строк в таблице.

Пример создания индекса:

CREATE UNIQUE ASCENDING

INDEX SCLAD_PRODUCT ON SCLAD (PRODUCT_NAME);

Создается уникальный индекс по возрастанию.

126

15.5.2 Изменение индексов

В БД InterBase индексы нельзя изменять их можно только активировать

и деактивировать.

Синтаксис команды:

ALTER INDEX name {ACTIVE / INACTIVE} ;

Пример деактивации индекса.

ALTER INDEX SCLAD_PRODUCT INACTIVE;

15.5.3 Удаление индексов

Удаление индексов осуществляется командой DROP INDEX. Синтак-

сис команды:

DROP INDEX name;

Пример удаления индекса:

DROP INDEX SCLAD_PRODUCT;

15.6 Триггеры

Триггер - это отдельная хранимая в базе подпрограмма, связанная с

таблицей, которая автоматически включается, когда в таблицу вставляется

(триггер добавления), модифицируется (триггер модификации) или удаляется

(триггер удаления) строка.

Триггер никогда не вызывается непосредственно. Он выполнятся вся-

кий раз, когда приложение или пользователь пытаются вставлять, модифици-

ровать или удалять строку в таблице. Другими словами триггер жестко свя-

зан с данными и выполняется тогда и только тогда, когда делается попытка

изменить данные.

15.6.1 Создание триггера

Триггер создается командой CREATE TRIGGER

CREATE TRIGGER name FOR {table / view}

[ACTIVE / INACTIVE]

{BEFORE / AFTER} {DELETE / INSERT / UPDATE

[POSITION number

127

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>

Рассмотрим триггер для контроля добавления данных в таблицу авто-

ров. Наличие подобных триггеров обеспечивает полноту и корректность

строк таблиц.

CREATE TRIGGER I_TAUTHOR_1 FOR TAUTHOR

ACTIVE BEFORE INSERT POSITION 0

begin

if (new.AUTHOR is NULL) then

new.AUTHOR=GEN_ID(sysnumber,1);

if (new.COMMENT is NULL) then

new.COMMENT="

end;

< compound_statement> = {< block> / statement;}

128

Таблица 17

Синтаксические конструкции команды CREATE TRIGGER

Описание Параметр

Name Имя триггера. Имя должно быть уникальным в базе данных

table | view Имя таблицы, для которой создается триггер

ACTIVE | INACTIVE Необязательная конструкция. Определяет активность тригге-

ра. ACTIVE - триггер включен, INACTIVE - триггер отклю-

чен.

BEFORE | AFTER

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

гер:

BEFORE - перед операцией, выполняемой надтаблицей.

AFTER - после операции, выполняемой над таблицей

DELETE | INSERT | UP-

DATE

Указывает, при выполнении какой именно операции с табли-

цей будет включаться триггер: DELETE - удаление, INSERT -

вставка, UPDATE – модификация.

POSITION number POSITION number задает порядок, в котором будут выпол-

няться триггеры (с одной таблицей и одними и теми же усло-

виями включения может быть связано несколько триггеров),

number должен быть целым числом между 0 (по умолчанию) и

32767 включительно. Включение триггеров происходит в по-

рядке возрастания номеров (number). Нумерация может и не

быть последовательной. Если триггеры имеют один и тот же

номер, то они будут включаться в алфавитном порядке их

имен.

DECLARE ARIABLE va-

riable datatype;

Объявляет локальные переменные, используемые только в

триггере. Объявление каждой переменной начинается с клю-

чевых слов DECLARE VARIABLE и заканчивается ";". Имена

объявляемых переменных должны быть уникальными в триг-

гере, datatype задает тип локальной переменной.

<com-

pound_statement>

Оператор SQL для хранимых процедур и триггеров.

Оператор завершается ";", если это не блок BEGIN-END. По-

сле END ";" не ставится.

129

Тема 16. Хранимые процедуры – инструмент повышения

эффективности приложения БД

Хранимая процедура - отдельная программа, написанная на SQL для

процедур и триггеров InterBase. Сами процедуры хранятся в базе данных.

Хранимые процедуры позволяют вести поиск и обработку данных непосред-

ственно на сервере, обеспечивая максимальную независимость клиентской

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

процедур и триггеров, кроме контекстных переменных NEW.co/wmn,

OLD.column, применимых только в триггерах. Они, как обычные программы,

могут получать входные параметры и возвращать значения вызвавшим их

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

чений - строку, но и множество строк, которое можно рассматривать как вир-

туальную таблицу.

Хранимая процедура может также вызываться непосредственно из

приложения или других хранимых процедур или триггеров. Хранимые про-

цедуры, возвращающие множество строк, можно использовать в команде

SELECT на месте таблиц или обзоров. Использование хранимых процедур

дает ряд преимуществ:

- Модульность проектирования. Приложения, которые обращаются к

одной базе данных, могут совместно использовать хранимые процедуры,

устраняя двойной код, уменьшая размер приложений и устраняя потенциаль-

ные ошибки.

- Локализация изменений. Если процедура модифицируется, то все вне-

сенные изменения автоматически отражаются во всех приложениях, которые

используют процедуру, обеспечивая их согласованность. При этом нет необ-

ходимости в перетрансляции и перекомпоновке приложений.

- Ускорение обработки. Хранимые процедуры выполняются сервером,

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

вой трафик. Последнее особенно важно для удаленного клиентского доступа.

Процедуры по своему назначению разделяются на два вида: выполни-

мые процедуры и процедуры выбора.

130

Выполнимая процедура - это обычная программа, которая получает не-

сколько (возможно, и ноль) параметров, выполняет какие-либо действия в

базе данных и возвращает несколько (возможно, и ноль) значений.

Процедура выбора - это программа, которая получает несколько (воз-

можно, и ноль) параметров, выполняет какие-либо действия в базе данных и

возвращает множество (возможно, пустое) наборов значений.

Другими словами, процедура выбора создает вычисляемую таблицу,

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

процедуре, как к таблице, используя команду SELECT.

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

процедуре выбора, получая при этом в ответ в точности одну строку, а к про-

цедуре выбора, как к выполнимой, получая при этом в ответ первую строку

формируемой таблицы. Ценность такой инверсии обращений в лучшем слу-

чае нулевая, поэтому, несмотря на отсутствие прямого запрета ее использо-

вания, нет оснований для практического применения инверсных вызовов

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

16.1 Создание хранимых процедур

Хранимые процедуры создаются командой CREATE PROCEDURE в

ISQL. Во внедренном SQL команда недоступна. Хранимая процедура состоит

из заголовка и тела. Заголовок хранимой процедуры содержит:

- Имя процедуры, которое должно быть уникальным среди имен про-

цедур, таблиц и обзоров в базе данных.

- Необязательный список параметров процедуры, передаваемых проце-

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

Для процедур, возвращающих значения вызывающим приложениям,

необходимо указать список возвращаемых параметров с указанием их типов.

Список указывается после ключевого слова RETURNS.

Тело процедуры содержит:

- Необязательный список локальных переменных с указанием их типов.

Блок операторов на языке процедур и триггеров, заключенный в опера-

торные скобки BEGIN-END. Сам блок может содержать внутри себя другие

блоки. Количество вложений блоков практически не ограничено.