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

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

Представление – это фактически тот же запрос, который выполняется всякий раз, когда представление участвует в какой-либо команде. Вывод этого запроса при этом в каждый момент времени становится содержанием представления.

Имеются две возможности реализации представления:

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

2. сложное определение, то есть СУБД приходится сначала выполнить такую операцию, как материализация представления, т.е. сохранение информации, из которой состоит представление во временной таблице. Затем система приступает к выполнению пользовательской команды и формированию ее результатов, а после этого временная таблица удаляется.

18.1. Представление создается командой CREATE VIEV, после которой указывается его имя, а далее следует запрос, формирующий тело представления.

Пример 1. Создать представление OTLSTUD, которое содержит информацию о студентах, получающих стипендию в размере 25.50.

CREATE VIEV OTLSTUD

AS SELECT*

FROM STUDIENTS

WHERE STIP=25.50;

Теперь полученное представление можно использовать как любую другую таблицу: она может быть запрошена, модифицирована, в нее могут быть ставлены записи, она может быть удалена из БД или соединена с другими таблицами и представлениями.

Пример 2.Вывести результат запроса

SELECT *

FROM OTLSTUD

Результат запроса:

SNUM SFAM SIMA SOTCH STIP

-------------------------------------------------------------------------------------------

3412 Поляков Анатолий Алексеевич 25.50

3416 Нагорный Евгений Васильевич 25.50

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

Пример 3. Создать представление, в котором скрывается от пользователя размер стипендии.

CREATE VIEV STIPOFF

AS SELECT SNUM, SFAM, SIMA, SOTCH

FROM STUDIENTS

Пример 4. Вывести содержимое таблицы STIPOFF

SELECT *

FROM STIPOFF

Результат запроса:

SNUM SFAM SIMA SOTCH

-------------------------------------------------------------

3412 Поляков Анатолий Алексеевич

3413 Старова Любовь Михайловна

3414 Гриценко Владимир Николаевич

3415 Котенко Анатолий Николаевич

3416 Нагорный Евгений Васильевич

Представление теперь может изменяться командами модификации DML, но модификация не будет воздействовать на само представление. Фактически команды будут перенаправлены к базовой таблице.

Пример 5. какая из команд будет отвергнута системой.

UPDATE STIPOFF UPDATE STIPOFF

SET SIMA=’Василий’ SET STIP=25.50

WHERE SNUM=3415; WHERE SNUM=3415;

В представлениях нередко возникает необходимость называть столбцы новыми именами. Это может потребоваться в случае, если столбцы являются вводимыми, и поэтому не имеющими имен, или если два или более столбцов в объединении имеют те же имена, что и в базовой таблице. Имена, которые необходимо присвоить полям, записываются в круглых скобках после имени таблиц. Они могут не указываться, если совпадают с именами полей запрашиваемой таблицы.

Пример 6. Объясните, почему данные команды являются единичными:

SELECT * SELECT *

FROM OTLSUD FROM STUDIENTS

WHERE SNUM>3412; WHERE STIP=25.50

AND SNUM>3412;

Пример 7. Создать представление, которое содержит данные о количестве студентов, получающих стипендию.

CREATE VIEV STIPCOUNT (STIP, COL)

AS SELECT STIP, COUNT(*)

FROM STUDIENTS

GROUP BY STIP;

18.2. Групповые представления – это представления которые содержат предложение GROUP BY или которые основаны на других групповых представлениях. Представления могут быть основаны сразу на нескольких базовых таблицах.

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

CREATE VIEV STUDOSEN

AS SELECT THIRD.UNUM, FIRST.SEAM, SECOND.PNAME, THIRD.OCENCA

FROM STUDIENTS FIRST, PREDMET SECOND, USP THIRD

WHERE FIRST.SNUM=THIRD.SNUM

AND SECOND.PNUM=THIRD.PNUM;

После этого легко ориентироваться в полученных оценках.

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

SELECT *

FROM STUDOSEN

WHERE SFAM=’Поляков’;

Результат запроса:

UNUM SFAM PNAME OCENKA

--------------------------------------------------------------------

1001 Поляков Физика 5

1004 Поляков Математика 4

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

Пример 3. Вывести информацию об оценках и дате их получения студента Полякова.

SELECT SFAM, PNAME, OCENCA,UDATE

FROM STUDOSEN FIRST, USP SECOND

WHERE FIRST.SFAM=’Поляков’

AND FIRST.UNUM=SECOND.UNUM;

Результат запроса:

SFAM PNAME OCENCA UDATE

-----------------------------------------------------------------

Поляков Физика 5 10/06/1999

Поляков Математика 4 12/06/1999

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

Пример 4. создать представление, в котором выводятся оценки по дисциплине выше средней по той же дисциплине.

CREATE VIEV AVGOC

AS SELECT *

FROM USP FIRST

WHERE OCENKA >

(SELECT AVG (OCENKA)

FROM USP SECOND

WHERE SECOND.PNUM=FIRST.PNUM);

Пример 5. извлечь данные из таблицы AVGOC.

SELECT *

FROM AVGOC;

Ограничения:

1. Представление должно основываться на одиночном запросе, поэтому объединение UNION не разрешается.

2. В представлениях не используется упорядочение ORDER BY.

18.3. Представление удаляется командой

DROP VIEV <VIEV NAME>

При удалении представления пользователь должен являться его владельцем.

18.4. Модификация представления происходит под управлением команд модификации языка DML – INSERT, UPDATE и DELETE. Если команды модификации языка представлении, то оно считается модифицируемым; в противном случае оно предназначено только для чтения при запросе.

Модифицируемое представление – это представление, в котором команда модификации может выполниться так, чтобы изменить только одну запись основной таблицы в каждый момент времени, не воздействуя на другие строки любой таблицы

Критерии, по которым определяют, является ли представление модифицируемым в SQL, следующие:

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

§ Оно должно содержать первичный ключ этой таблицы;

§ Оно не должно иметь никаких полей, которые бы являлись агрегатными функциями;

§ Оно не должно содержать DISTINCT в своем определении;

§ Представление не должно использовать GROUP BY или HAVING в своем определении;

§ Желательно, чтобы оно не использовало в своем определении подзапросы;

§ Оно не может быть использовано в другом представлении, но это представление должно также быть модифицируемым;

§ Оно не должно использовать константы, строки или выражения значений среди выбранных полей вывода;

§ Для команды INSERT оно может содержать любые поля основной таблицы, которые имеют ограничение NOT NULL, если другое ограничение по умолчанию не определенно.

Пример 1. определите, почему следующее представление является представлением только для чтения. Определите содержание таблицы.

CREATE VIEV PRCOUNT (UDATE, COL)

AS SELECT UDATE, COUNT (*)

FROM USP

GROUP BY UDATE;

Пример 2. Определите, почему следующее представление является модифицируемым представлением. Определите содержание таблицы.

CREATE VIEV MATEMUSP

AS SELECT *

FROM USP

WHERE PNUM=2003;

Пример 3. Определите характер представления и содержание таблицы.

CREATE VIEV IDXSTIP (SNUM, SFAM, NEWSTIP)

AS SELECT SNUM, SFAM, STIP*2

FROM STUDIENTS

WHERE STIP=25.50;

Пример 4. Определите характер представления и содержание таблицы.

CREATE VIEV DATEOS

AS SELECT *

FROM STUDENTS

WHERE SNUM IN

(SELECT SNUM

FROM USP

WHERE UDATE=10/06/1999);

Пример 5. Определите характер представления и содержание таблицы.

CREATE VIEW DATEOC2

AS SELECT *

FROM USP

WHERE UDATE IN (10/06/1999,11/06/1999);

Пример 6. Определите характер представления и содержание таблицы.

CREATE VIEV ONLY 5

AS SELECT SNUM, OCENKA

FROM USP

WHERE OCENKA=5;

18.5. Фраза WITH CHCK OPTION производит действие в режиме «все или ничего», так как она размещается в определении представления, и все команды модификации будут подвергаться проверке. Таким образом можно регулировать процесс ввода значений, которые пользователь впоследствии сам не в состоянии корректировать. Эта фраза применяется только в представлениях, которые основаны на базовых таблицах, а не на других представлениях.

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

CREATE VIEV ONLY5

AS SELECT SNUM, OCENCA

FROM USP

WHERE OCENKA=5

WITH CHECK OPTION;

18.6. Курсор – это вид переменной, которая связана с запросом. Значением этой переменной может быть каждая строка, которая выводится при запросе и он должен быть объявлен командой DECLARE CURSOR.

Например, запрос

EXEC SQL DECLARE CURSOR STIPCUR

FOR SELECT SNUM, SFAM, STIP

FROM STUDENTS

WHERE STIP=25.50;

Не выполняется немедленно, так как в данном случае представлено только его определение. В отличии от базовых таблиц и представлений, строки курсора упорядочены и имеются первая, вторая и т.д., а так же последняя строка. Этот порядок может быть произвольным с явным управлением при помощи фразы ORDER BY в запросе. Когда в программе необходимо выполнить запрос, то открывается курсор с помощью команды.

EXEC SQL OPEN CURSOR STIPCUR;

И только тогда передаются значения в курсор.

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

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

EXEC SQL FETCH STIPCUR INTO STUDENTSID,: STUDENTSFAM,: STUDENTSSTIP;

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

Оператор CLOSE CURSOR освобождает курсор значений, поэтому после него запрос нужно будет выполнить повторно с оператором OPEN CURSOR, прежде чемперейти квыбору следующих значений.

В нашем случае этот оператор имеет вид:

EXEC SQL CLOSE CURSOR STIPCUR;