Язык определения данных

Удаление индекса с  заданным именем производится с помощью  запроса DROP INDEX, который имеет следующий  формат:

DROP INDEX имя_индекса;.

4.4. Временные таблицы

Ранее были описаны запросы DDL применительно к постоянным (базовым) таблицам, которые характеризуются  тем, что их определение и содержимое существуют в БД до тех пор, пока они не будут удалены явно с  помощью соответствующих запросов. Часто в процессе работы возникает  необходимость сохранения временных  данных (например, промежуточных результатов  вычислений в хранимых процедурах). Если хранить такие временные  данные в обычных таблицах, то требуются  постоянное слежение за содержимым таблиц, а также специфическая организация  работы с данными. Более удобно в  таком случае использовать временные  таблицы.

Рассмотрим, что представляют собой временные таблицы. Сами по себе временные таблицы на самом  деле постоянные, то есть при их создании информация сохраняется в системной  таблице RDB$RELATIONS, как и для обычных  таблиц. Определение временной таблицы  может быть удалено только явно, однако ее содержимое может удаляться  или становиться невидимым (недостижимым) автоматически при достижении определенных условий. Временная таблица определяется следующим образом:

CREATE GLOBAL TEMPORARY TABLE имя_временной_таблицы  (<определение_столбца> [, <определение_столбца>...] [,<тип_ограничения> ..... ]) [ON COMMIT {DELETE | PRESERVE} ROWS];

Таким образом, данный синтаксис  отличается от синтаксиса создания обычных  таблиц фразой GLOBAL TEMPORARY и предложением ON COMMIT.

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

Тип временной таблицы  устанавливается с помощью предложения ON COMMIT.

Если используется ON COMMIT DELETE ROWS, то данные таблицы будут удаляться  из БД сразу же после окончания  транзакции. Таким образом, таблицы GLOBAL TEMPORARY DELETE хранят записи только до ближайшей команды COMMIT или ROLLBACK, причем не только транзакции, которая их создала, но и любой другой транзакции в  этом же подключении. При этом созданные  в таблице записи не видны нигде  кроме текущей транзакции. После  использования как ROLLBACK, так и COMMIT записи во временных таблицах "исчезнут", однако в случае COMMIT все изменения, произведенные над обычными таблицами, будут подтверждены.

Следует отметить, что ON COMMIT DELETE ROWS принимается по умолчанию, если предложение ON COMMIT не задано.

Если создадать временную  таблицу с помощью следующего запроса:

CREATE GLOBAL TEMPORARY TABLE TmpTrans (ID INTEGER not null, NAME VARCHAR (20), CONSTRAINT PK_TmpTrans PRIMARY KEY (ID))

ON COMMIT DELETE ROWS;,

а затем добавить в нее  запись

INSERT INTO TmpTrans VALUES (1, 'Запись №1');,

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

Если теперь выполнить  следующий запрос:

SELECT * FROM TmpTrans;,

то в результате можно  получить записи, внесенные во временную  таблицу (рис. 4.3).

ID	NAME
1	Запись №1

Рис. 4.3. Результат выборки данных из временной таблицы

После выполнения COMMIT повтор последнего запроса вернет в качестве результата пустую таблицу.

Если используется ON COMMIT PRESERVE ROWS, то данные таблицы после  окончания транзакции остаются в  БД до конца соединения. Т.е. таблицы GLOBAL TEMPORARY PRESERVE хранят записи до отсоединения подключения, в котором они были добавлены, причем их видимость ограничена только этим подключением.

Например, если создадать  временную таблицу с помощью  следующего запроса:

CREATE GLOBAL TEMPORARY TABLE TmpConn (Id INTEGER NOT NULL, Name VARCHAR (20), CONSTRAINT PK_TmpConn PRIMARY KEY (ID))

ON COMMIT PRESERVE ROWS;,

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

INSERT INTO TMPCONN

VALUES (1, 'Запись № 1 для  текущего соединения');

и подтвердить транзакцию (COMMIT), то в рамках текущего подключения  данная запись будет видна из разных транзакций. Если выполнить еще одно подключение к этой же БД (например, запустив еще один экземпляр IBExpert) и  выполнить тот же самый запрос INSERT, то ошибки "PRIMARY or UNUQIE key constraint" (повтор значения первичного ключа) не возникнет. Как только текущее соединение будет закрыто, вставленные данные пропадут.

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

Однако следует учитывать  следующие ограничения:

•  ссылки (ограничения внешнего ключа REFERENCES) между постоянной и временной таблицей запрещены;

• временная таблица с ON COMMIT PRESERVE ROWS не может иметь ссылку на временную таблицу с ON COMMIT DELETE ROWS.

В заключение можно сказать, что временные таблицы могут  быть достаточно полезны для приложений, которые формируют сложные отчеты или производят промежуточные вычисления на сервере. Однако использование временных  таблиц может замедлять подключение  к БД (если использовались таблицы GLOBAL TEMPORARY DELETE) и отключение от нее (если использовались таблицы GLOBAL TEMPORARY PRESERVE) из-за очистки жесткого диска от версий удаленных записей из таблицы.

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

Представление - это виртуальная  таблица, созданная на основе запроса  из базовой таблицы. Представление, как и реальная (базовая) таблица, содержит строки и столбцы данных, но данные, видимые в представлении, на самом деле являются результатами запроса.

Одной из операций над представлениями  является их непосредственное использование  с запросами модификации DML: INSERT, UPDATE и DELETE. Если к представлению могут  быть успешно применены данные запросы, то оно называется модифицируемым (или обновляемым). В противном случае представление является представлением только для чтения. Обновление представлений будет подробно рассмотрено позднее после изучения языка манипулирования данными.

В запросах SELECT, INSERT, DELETE и UPDATE на представление можно ссылаться  как на обычную таблицу. Это дает возможность определять подмножество данных, необходимых конкретному  пользователю (или группе пользователей) в дополнение к ограничению доступа  к остальной части данных. Представления  используются по следующим причинам:

•  они позволяют сделать так, что разные пользователи БД будут видеть ее по-разному;

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

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

В SQL представления создаются  запросом CREATE VIEW, который имеет следующий  формат:

CREATE VIEW представление [( столбец_представления [, столбец_представления ...])] AS <табличный_подзапрос> [WITH CHECK OPTION];.

Данный запрос создает  представление с именем представление.

Параметр WITH CHECK OPTION предотвращает INSERT- или UPDATE-операции над обновляемым  представлением, если они нарушают условие отбора строк, определенное в предложении WHERE запроса SELECT (<табличный_подзапрос>), используемого при определении  данного представления.

При необходимости в запросе CREATE VIEW можно задать имя для каждого  столбца создаваемого представления, обозначаемое как столбец_представления. Если указывается список имен столбцов, то он должен содержать столько элементов, сколько столбцов возвращается запросом.

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

Например, для создания представления Sred_Summ с полями Month и Summa, показывающего  среднее значение начисленных сумм за месяц, следует выполнить следующий  запрос:

CREATE VIEW Sred_Summ (Mes, Summa) AS SELECT NachislMonth, AVG (NachislSum) FROM NachislSumma GROUP BY 1;.

Результат выполнения запроса 

SELECT * FROM Sred_Summ;

представлен на рис. 4.4.

MES	SUMMA
1	33,58
2	34,58
3	20,61
4	38,38
5	32,09
6	36,98
7	25,26
8	26,45
9	110,24
10	51,36
11	47,92
12	44,23

Рис. 4.4. Результат запроса к представлению Sred_Summ

Ниже приведен пример создания представления с именем Date_Abonent, которое  должно показывать ФИО абонентов  и дату подачи ими ремонтных заявок:

CREATE VIEW Date_Abonent (Abonent_Name, Data) AS SELECT Fio, IncomingDate FROM Abonent, Request WHERE Abonent.AccountCD = Request. AccountCD;.

В СУБД Firebird 2.1 представления  могут использовать все конструкции, допустимые для обычного запроса SELECT. Таким образом, возможно использование  конструкций FIRST/SKIP, ROWS, UNION, ORDER BY. Например, для создания представления Max_Pay с  полями AccountCD, Big_Sum и PayDate, показывающего  пять максимальных значений оплаченных сумм, следует выполнить следующий  запрос:

CREATE VIEW Max_Pay (AccountCD, Big_Sum, PayDate) AS SELECT FIRST 5 AccountCD, PaySum, PayDate FROM PaySumma ORDER BY PaySum DESC;.

Результат выполнения запроса 

SELECT * FROM Max_Pay;

представлен на рис. 4.5.

ACCOUNTCD	BIG SUM	PAYDATE
115705	250,00	06.10.2000
115705	250,00	03.10.2001
080047	80,00	26.11.1998
443069	80,00	03.10.2001
080047	80,00	21.11.2001

Рис. 4.5. Результат запроса к представлению Max_Pay

Физически представление  в БД хранится в виде его определения, т.е. текста того запроса CREATE VIEW, который  был использован при создании представления. Когда СУБД встречает в SQL-запросе ссылку на представление, она отыскивает его определение, сохраненное в БД. Затем преобразует пользовательский запрос, ссылающийся на представление, в эквивалентный запрос к исходным таблицам представления (заданным в запросе SELECT представления) и выполняет этот запрос. Таким образом, СУБД создает иллюзию существования представления в виде отдельной таблицы и в то же время сохраняет целостность исходных данных.

Представление может служить  неким «окном» для просмотра  данных. Любые изменения в исходных данных (т.е. в данных таблиц, на основе которых создано представление) будут автоматически и мгновенно  отображаться в представлении, и  наоборот, все изменения, вносимые в  данные представления, будут автоматически  вноситься в исходные данные и  соответственно отображаться в представлении.

По виду запроса, используемого представлением, различают следующие виды представлений:

•  горизонтальные;

•  вертикальные;

•  смешанные;

•  сгруппированные;

•  соединенные.

Горизонтальное  представление представляет собой горизонтальное подмножество строк одиночной таблицы и предназначено для ограничения доступа к строкам таблицы. Запрос в таком представлении выбирает все столбцы заданной таблицы, но ограничивает выбор строк указанием условия поиска в предложении WHERE запроса SELECT . Например, для создания представления, показывающего все ремонтные заявки по неисправности с кодом, равным 1, следует выполнить следующий запрос:

CREATE VIEW Failure_Req AS SELECT * FROM Request WHERE FailureCD = 1;.

При выполнении следующего запроса 

SELECT * FROM Failure_Req;

из таблицы Request будут  выбраны все ремонтные заявки с кодом неисправности газового оборудования, равным 1. Результат запроса  представлен на рис. 4.6.

REQUESTCD	ACCOUNTCD	EXECUTORCD	FAILURECD	INCOMINGDATE	EXECUTIONDATE	EXECUTED
1	005488	1	1	17.12.2001	20.12.2001	1
2	115705	3	1	07.08.2001	12.08.2001	1
5	080270	4	1	31.12.2001	<null>	0
9	136169	2	1	06.11.2001	08.11.2001	1

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

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

Например, если необходимо создать  представление Abonent_Phone, которое должно содержать ФИО абонента и его  телефон, следует выполнить следующий  запрос:

CREATE VIEW Abonent_Phone (Abon_Fio, Abon_Phone) AS SELECT Fio, Phone FROM Abonent;.

В этом примере Abon_Fio и Abon_Phone - имена столбцов представления. Если не указывать обозначение имен в  скобках после имени представления, то столбцы представления получат  соответственно имена Fio и Phone.

Следующий запрос:

SELECT * FROM Abonent_Phone;

даст результат, представленный на рис. 4.7.

ABON_FIO	ABON_PHONE
АКСЕНОВ С.А.	556893
МИЩЕНКО Е.В.	769975
КОНЮХОВ В.С.	761699
ТУЛУПОВА М.И.	214833
СВИРИНА З.А.	350003
СТАРОДУБЦЕВ Е.В.	683014
ШМАКОВ С.В.	982222
МАРКОВА В.П.	683301
ДЕНИСОВА Е.К.	680305
ЛУКАШИНА Р.М.	254417
ШУБИНА Т.П.	257842
ТИМОШКИНА Н.Г.	321002