Описание данных

Дополнительные возможности DB2

END

BEGIN ATOMIC

FOR EACH ROW

END

ELSE

THEN

ELSE

THEN

BEGIN ATOMIC

FOR EACH ROW

END

BEGIN ATOMIC

FOR EACH ROW

WHEN (N.OnHand < 0.10 * N.MaxStocks)

VALUES (ISSUE_SHIP_REQUEST(N.PartNo, N.MaxStocks – N.OnHand));

Пример 4. Пусть определены следующие таблицы:

GOODS(GID, GName, CurQTY) – содержит информацию о товарах, хранящихся на складе; CurQTY – имеющееся на складе количество товара;

INVOICE(InvID, InvType, InvDate) – накладная, оформляемая при поступлении товара на склад (приходная накладная) или отпуске товара со склада (расходная накладная); атрибут InvType (типа CHAR(3)) определяет тип накладной и имеет значения ‘IN’ для приходной накладной и ‘OUT’ для расходной;

INVITEM(GID (FK1), InvID (FK2), QTY) – товар в накладной; QTY – количество товара с кодом GID, принятого или отпущенного по накладной с номером InvID.

Требуется написать триггер, который при добавлении новой записи в таблицу INVITEM модифицирует соответствующим образом количество товара на складе (атрибут QTY таблицы GOODS): увеличивает для приходной накладной и уменьшает для расходной. Если в расходной накладной указано товара больше, чем имеется на складе, должно быть выдано соответствующее сообщение об ошибке.

CREATE TRIGGER NEW_INVITEM

AFTER INSERT ON INVITEM

REFERENCING NEW AS N

DECLARE IType CHAR(3);

DECLARE CQTY INTEGER;

SET IType = (SELECT InvType FROM INVOICE I WHERE I.InvID = N.InvID);

IF (IType = 'IN')

UPDATE GOODS SET CurQTY = CurQTY + N.QTY WHERE GID = N.GID;

SET CQTY = (SELECT CurQTY FROM GOODS WHERE GID = N.GID);

IF (CQTY > N.QTY)

UPDATE GOODS SET CurQTY = CurQTY – N.QTY WHERE GID = N.GID;

VALUES (COALESCE(RAISE_ERROR('70001','INCORRECT QUANTITY'),0));

END IF;

END IF;

Пример 5. Пусть определены следующие таблицы:

GOODS(GID, GName, CurQTY) – содержит информацию о товарах, хранящихся на складе; CurQTY – количество товара на складе;

GOODSHISTORY(GID, GName, QTY, RecTime) – содержит информацию обо всех изменениях количества товара на складе: когда значение колонки CurQTY таблицы GOODS изменяется, новое значение этой колонки, вместе с указанием времени изменения, заносится в таблицу GOODSHISTORY.

CREATE TRIGGER RECORD_HISTORY

AFTER UPDATE OF CurQTY ON GOODS

REFERENCING NEW AS NEWQTY

INSERT INTO GOODSHISTORY

VALUES (NEWQTY.GID, NEWQTY.GName, NEWQTY.CurQTY, CURRENT TIMESTAMP);

Дополнительные типы данных

Наряду с рассмотренными ранее типами данных, DB2 UDB допускает использование дополнительных типов для представления строковых данных и данных большого объема (LOB – Large Objects).

Строковые данные в DB2 делятся на две категории – текстовые строки и графические строки.

Текстовые строки (или просто строки) – это строки, в которых каждый символ представлен одним байтом. Для представления таких строк, наряду с рассмотренными ранее типами CHAR и VARCHAR, используется еще тип данных LONG VARCHAR. Этот тип данных определяет строки переменной длины, до 32700 байтов. Данные этого типа, в отличие от типов CHAR и VARCHAR, хранятся отдельно от таблицы.

Представление текстовых строк определяется связанными с ними кодовыми страницами (code page), которые определяют интерпретацию значений кодов символов.

Если при использовании строковых типов данных добавляется уточнение FOR BIT DATA, оно определяет битовую строку. Битовые строки не ассоциируются с кодовыми страницами и используются для представления таких данных, как рисунки, аудио и видео данные и т.п.

Графические строки состоят из двухбайтных символов. Для представления графических строк используются типы данных, аналогичные соответствующим типам строковых данных:

GRAPHIC( n ) – аналогичен CHAR(n)

VARGRAPHIC( n ) – аналогичен VARCHAR(n)

LONG VARGRAPHIC – аналогичен LONG VARCHAR.

Числовые ограничения для графических типов в 2 раза меньше, чем для соответствующих строковых типов.

Для представления данных большого объема (до 2-х Гбайт) в DB2 предусмотрены следующие типы данных:

CLOB (размер) – для представления обычных текстов большого объема, в которых каждый символ представлен одним байтом.

DBCLOB (размер) – для представления текстовых данных, в которых один символ занимает 2 байта (UNICODE символы и т.п.).

BLOB (размер) – для представления двоичных данных (картинки, видео, аудио, …); заменяет уточнение FOR BIT DATA для CLOB данных.

Везде размер задается в виде: целое [ единица_измерения ], где в качестве единицы измерения может быть указано K, M или G, и определяет максимальное количество символов, выделяемое для хранения данных. Для BLOB и CLOB максимальное значение – 2 Гбайта, для DBLOB – 1 Гбайт. Если размер не указан, по умолчанию принимается 1М.

На использование данных типа LONG и LOB (LONG VARCHAR, LONG VARGRAPHIC, BLOB, CLOB, DBLOB) накладываются дополнительные ограничения; в частности, они не могут быть указаны:

• в предложении SELECT, в котором в списке вызова указано DISTINCT;

• в конструкциях GROUP BY и ORDER BY;

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

• в предикатах;

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

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

Общий синтаксис:

CREATE TABLE имя_таблицы описание_структуры_таблицы

[ IN имя_ТП ][ INDEX IN имя_ТП ][ LONG IN имя_ТП ]

Здесь после описания структуры таблицы можно указать, в каком табличном пространстве размещаются соответствующие фрагменты таблицы: сама таблица (IN), индексы, создаваемые для таблицы (INDEX IN) и данные типа LONG (LONG IN). Если табличные пространства не указаны, используются значения по умолчанию.

В DB2 определены таблицы двух типов:

• базовая таблица,

• таблица материализованного запроса.

Базовая таблица

Основной способ создания базовой таблицы был рассмотрен ранее.

Базовую таблицу можно создать и как таблицу, структура которой соответствует структуре другой, уже существующей таблицы:

CREATE TABLE имя_таблицы LIKE имя_существующей_таблицы_или_представления [ опции_копирования ]

В новой таблице колонки имеют такие же имена и типы данных, как и в прообразе; передается также ограничение обязательности NULL / NOT NULL, имеющееся у колонок прообраза. Все остальные ограничения целостности, индексы и триггеры, определенные для таблицы-прообраза, в новую таблицу не передаются.

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

{ INCLUDING | EXCLUDING } [ COLUMNS ] DEFAULTS – копировать (INCLUDING) или нет (EXCLUDING) установленные для колонок в прообразе таблицы значения по умолчанию; если данная опция не указана, предполагается задание INCLUDING;

{ EXCLUDING | INCLUDING } IDENTITY [ COLUMNS ATTRIBUTES ] – копировать или нет установленное для колонки в прообразе таблицы свойство identity; если не указано, предполагается задание

EXCLUDING.

Таблица материализованного запроса

Создается одним из двух способов:

CREATE TABLE имя_таблицы [(имя_колонки, …)] AS (SQL-запрос) WITH NO DATA [ опции_копирования ]

или

CREATE TABLE имя_таблицы [(имя_колонки, …)] AS (SQL-запрос) опции_обновления_таблицы

Пояснения:

WITH NO DATA – запрос используется только для определения структуры таблицы. Указанный в предложении запрос не выполняется, таблица не заполняется результатами выполнения запроса, предложение REFRESH TABLE для такой таблицы использовать нельзя. В дальнейшем работа с такой таблицей реализуется предложениями INSERT, DELETE, UPDATE.

Опции копирования – указывают, нужно ли копировать дополнительные атрибуты исходного определения таблицы или представления; те же, что и рассмотренные выше.

Опции обновления таблицы – определяют опции обновления атрибутов материализованной таблицы. Задаются следующим образом:

DATA INITIALLY DEFERRED способ_обновления [ опции_оптимизации ] [ опции_управления ]

DATA INITIALLY DEFERRED – при выполнении предложения CREATE TABLE запрос не выполняется, данные не вставляются в таблицу на этапе выполнения предложения CREATE TABLE. Для заполнения таблицы (вставки данных в таблицу) нужно использовать предложение REFRESH TABLE:

REFRESH TABLE имя_таблицы

Способ обновления – указывает, как обновляется таблица; допускается указание одного из двух способов обновления: REFRESH DEFERRED или REFRESH IMMEDIATE

REFRESH DEFERRED – данные в таблице обновляются с помощью предложения REFRESH TABLE. Данные в таблице отражают результат выполнения запроса во время выполнения предложения REFRESH

TABLE. Управляемый системой (MAINTAINED BY SYSTEM), такой материализованный запрос не допускает использование для таблицы материализованного запроса предложений INSERT, DELETE, UPDATE; управляемый пользователем (MAINTAINED BY USER) – допускает;

REFRESH IMMEDIATE – изменения, вносимые в исходную таблицу (используемую в запросе) с помощью предложений INSERT, DELETE, UPDATE, сразу же каскадно отображаются в таблице материализованного запроса. Для таких таблиц непосредственное использование предложений INSERT, DELETE, UPDATE не допускается.

Опции оптимизации задаются следующим образом:

{ ENABLE | DISABLE } QUERY OPTIMISATION

Опции оптимизации указывают, может ли (ENABLE) или не может (DISABLE) таблица материализованного запроса использоваться для оптимизации запроса. Если опция не указана, по умолчанию принимается ENABLE.

Опции управления указывают, кто управляет данными в таблице материализованного запроса, и задаются одним из трех способов:

MAINTAINED BY SYSTEM – данными управляет система; значение, устанавливаемое по умолчанию;

MAINTAINED BY USER – данными управляет пользователь; он может выполнять для таблицы материализованного запроса предложения INSERT, DELETE, UPDATE. Для таких таблиц нельзя использовать предложение REFRESH TABLE, используемое для таблиц, управляемых системой; в качестве способа обновления допускается указание только REFRESH DEFERRED;

MAINTAINED BY FEDERATED TOOLS – данные обновляются средствами репликации; в качестве способа обновления допускается указание только REFRESH

Опции IDENTITY

При определении колонки в таблице для нее можно указать свойство identity – автоматическая генерация последовательности значений. В полном объеме свойство identity задается следующим образом:

GENERATED ALWAYS AS IDENTITY [ ( опции-identity ) ]

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

START WITH целая_константа – указывает первое значение для колонки identity. Может быть и положительным, и отрицательным. Если данная опция не указана, по умолчанию используется START WITH MINVALUE для возрастающей последовательности и START WITH MAXVALUE для убывающей.

INCREMENT BY целая_константа – указывает шаг изменения автоинкрементного значения. Может быть и положительным, и отрицательным. Положительное значение определяет возрастающую последовательность, отрицательное – убывающую. Если данная опция не указана, по умолчанию используется

INCREMENT BY 1.

NO MINVALUE или MINVALUE целая_константа – указывает минимальное значение, используемое при генерации последовательности. Если указано NO MINVALUE, тогда в качестве минимального значения используется значение, зависящее от типа последовательности. Для возрастающей последовательности используется значение, указанное в конструкции START WITH, или 1, если START WITH не указано. Для убывающей последовательности используется минимальное значение, соответствующее типу данных колонки.

NO MAXVALUE или MAXVALUE целая_константа – указывает максимальное значение, используемое при генерации последовательности. Если указано NO MAXVALUE, тогда в качестве максимального значения используется значение, зависящее от типа последовательности. Для возрастающей последовательности используется максимальное значение, соответствующее типу данных колонки. Для убывающей последовательности используется значение, указанное в конструкции START WITH, или -1, если START WITH не указано.

[ NO ] CYCLE – указывает, будет ли при вычислении значений использован циклический принцип: при достижении последнего значения (максимального для возрастающей последовательности или минимального для убывающей) будет получено следующим (CYCLE) или нет (NO CYCLE) первое значение последовательности (минимальное для возрастающей или максимальное для убывающей). Если указано CYCLE, DB2 может генерировать для identity колонки дублирующиеся значения. Если опция не указана, по умолчанию принимается NO CYCLE.

NO CACHE или CACHE число – указывает, будут ли (CACHE число) или нет (NO CACHE) храниться в некоторой кэш-области предварительно вычисленные значения. Число указывает, сколько значений могут храниться в кэш области. Если опция не указана, по умолчанию принимается CACHE 20.

[ NO ] ORDER – указывает, должна ли генерироваться упорядоченная последовательность (ORDER) или нет (NO ORDER). Если опция не указана, по умолчанию принимается NO ORDER.

Последовательность

Последовательность представляет собой некоторый объект базы данных, хранящий последовательный набор значений. Каждое обращение к последовательности позволяет получить очередное значение.

Последовательность создается с помощью предложения CREATE SEQUENCE, имеющего следующий синтаксис:

CREATE SEQUENCE имя_последовательности [ AS тип_данных ] опции_identity

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

Тип данных – указывает тип данных, используемый для значений последовательности. Допускаются только целочисленные типы. Если не указано, используется тип INTEGER.

Опции identity – имеют такой же синтаксис и такой же смысл, как и при задании identity-колонок в таблице (см. выше).

Для каждой последовательности в некоторый момент времени могут быть доступны два значения: следующее (или очередное) значение и предыдущее (или текущее).

Следующее значение последовательности: NEXT VALUE FOR имя_последовательности

Предыдущее значение последовательности: PREVIOUS VALEW FOR имя_последовательности

Если в одном SQL-предложении встречается несколько обращений к PREVIOUS VALUE одной и той же последовательности, все они возвращают одно и то же значение.

Ссылка на последовательность может быть использована в предложениях SQL SELECT, INSERT, UPDATE, SET, VALUES INTO, CREATE PROC, CREATE TRIGGER (только NEXT VALUE), CREATE VIEW.

Пример:

CREATE SEQUENCE S1

START WITH 1

INCREMENT BY 1