5.9. Создание представлений

5.9. Создание представлений

Представления (view) - это одно из мощных средств языка SQL, предназначенное для реализации механизм подсхем пользователей базы данных. Представления позволяют скрыть от пользователей схему базы данных. Они представляют собой хранимые в базе данных запросы, выраженные операторами SELECT. На базе одних представлений могут быть созданы новые представления, которые наследуют все свойства базовых представлений. Формировать представления могут пользователи с привилегиями SELECT для используемых в представлениях таблиц (базовых таблиц).

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

обращение к представлениям осуществляется также как и к таблицам;
ко всем представлениям применим оператор SELECT;
для некоторых представлений могут применяться операторы INSERT, UPDATE и DELETE.

Однако в соответствие со стандартом ANSI SQL/89 в SYBASE SQL Anywhere таблицы данных и представления имеют некоторые различия:

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

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

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

Создадим представление Code_books_1_3 для базы данных Dubl_Biblia, содержимое которого соответствует таблице 15. Это представление базируется на операторе SELECT (базовый оператор представления) вида:

// оператор SELECT представления Code_bookы_1_3
SELECT  //список полей оператора SELECT
Books.Code_book,
Books.Author, Books.Number
FROM Stepanov.Books /*данные выбираются 
 			из таблицы Stepanov.Books */
WHERE Code_book>=1 AND //условие 
Code_book<=3 		//выборки.данных

Читателям предлагается для проверки выполнить этот оператор в утилите ISQL.

В утилите SQL Central для определения новых представлений пользователю предлагается шаблон Add View(Template) и мастер Add View(Wizard). Оба они являются элементами папки Views (см.рис. 18). Использование шаблона приводит к вызову текстового редактора утилиты. При этом в его окне будет представлена заготовка (шаблон) оператора CREATE VIEW - оператора, создающего представление (рис. 70).

Рис. 70. Окно текстового редактора утилиты SQL Central с заготовкой (шаблоном ) оператора CREATE VIEW

Для формирования заготовки в полноценный оператор необходимо в соответствии с подсказами добавить название представления, необязательные наименования полей и имя владельца представления, а также базовый оператор SELECT.

На рис. 70 окно текстового редактора утилиты приведено с выпадающим меню File. Это связано с тем, что пункты этого меню наиболее часто используются при работе с текстовым редактором. Последний используется при работе с хранимыми процедурами, триггерами и представлениями. Рассмотрим назначение пунктом этого меню.

При выборе пункта Execute Script осуществляется синтаксическая проверка операторов, содержащихся в окне. Без ее положительных результатов содержимое окна невозможно записать в базу данных.

Использование пункта Save To File позволяет записать содержимое окна в файл. Считывание файла в окно редактора производится при помощи пункта Open From File.

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

Кроме шаблона, в утилите SQL Central для создания представления может применяться еще и соответствующий мастер Add View(Wizard). Рассмотрим шаги, которые предлагает выполнить этот мастер.

Шаг 1. Начальная стадия создания представления (рис. 71).

Рис. 71. Создание представления

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

Вопрос 1. Какое наименование вы хотите определить для нового представления?

Ниже следует поле, в которое следует ввести имя создаваемого представления. В нашем случае - это Code_books_1_3.

Вопрос 2. Кто будет владельцем представления?

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

После заполнения полей окна "нажмите" на кнопку Далее для выполнения следующего шага.

Шаг 2. Выбор таблиц данных, используемых в представлении (рис. 72).

Рис. 72. Выбор таблиц данных, используемых в представлении

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

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

В нашем случае база данных Dubl_Biblia содержит всего две таблицы данных. Для создаваемого представления требуется только таблица Stepanov.Books, выбор которой отмечен установленным флажком.

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

Шаг 3. Выбор полей таблиц данных, на базе которых формируется представление (рис. 73).

Рис. 73. Выбор полей таблиц данных, на базе которых формируется представление

Вопрос. Какие поля будут использоваться при формировании содержимого представления?

Для ответа на поставленный вопрос представляется полный перечень полей ранее выбранных таблиц базы данных. Указание полей осуществляется посредством флажков. Назначение кнопок All и None такое же как и при выполнении предыдущего шага.

В нашем случае для представления Code_books_1_3 требуется три поля, выбор которых отмечен флажками.

Теперь приступаем к следующему шагу.

Шаг 4. Подтверждение готовности к созданию нового представления (рис. 74).

Рис. 74. Подтверждение готовности к созданию нового представления

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

Ниже располагается поле для ввода комментария для создаваемого представления.

Пояснение 2. Мастер сгенерирует заготовку для следующего представления:

Название Code_books_1_3

Владелец Stepanov

Щелкните по кнопке Готово для завершения формирования текста представления в редакторе.

После "нажатия" на эту кнопку мастер сформирует заготовку оператора CREATE VIEW и отобразит ее в окне текстового редактора утилиты (рис. 75).

Рис. 75. Отображение заготовки CREATE VIEW в окне текстового редактора утилиты SQL Central

Откорректируем текст заготовки в части базового оператора. Тогда окончательный текст оператора CREATE VIEW примет следующий вид:

//Окончательный текст оператора CREATE VIEW
CREATE VIEW 
	Stepanov.Code_books_1_3 
AS SELECT Books.Code_book,
	Books.Author, Books.Number
FROM Stepanov.Books
WHERE Code_book>=1
	AND Code_book<=3

Проверим текст оператора на синтаксис при помощи команды меню File|Execute Script. После ее успешного окончания выходим из текстового редактора. В результате в базе данных появляется новое представление Code_books_1_3. Данный факт выражается в появлении одноименного элемента в папке Views. (см. рис. 76).

Рис. 76. Обновленное содержание папки Views

Для создания представления Code_books_1_3 в утилите ISQL необходимо выполнить оператор CREATE VIEW окончательного вида, приведенный выше. Убедиться в появление нового представления можно, просмотрев список имеющихся в базе данных таблиц и представлений. Он появляется на экране после нажатия на клавишу F7 (см. рис. 22). Другим способом проверки наличия в базе данных нового представления является анализ содержимого системного представления SYS.SYSVIEWS. Текст оператора CREATE VIEW можно получить, выполнив следующие SQL-операторы:

//Получение текста представления
SELECT Viewtext 
	//поле Viewtext содержит текст представления
FROM SYS.SYSVIEWS 
WHERE Viewname = 
'Code_books_1_3' //названия представления
/*AND Vcreator = 
'Stepanov' ; */ /* если имя представления не 
	уникально, то требуется указать имя его 
 	владельца  */
//оператор экспорта результатов оператора SELECT
OUTPUT 
TO d:\Log_db\viewtext.sql /* полное имя файла
 			куда осуществляется экспорт 
 			текста представления */
FORMAT /* в формате */ ASCII

ПРИМЕЧАНИЕ

При выполнении оператора OUTPUT в формате ASCII все переводы на новую строку исходного текста в формируемый текстовый файл записываются в виде последовательности символов \X0D\X0A.

В SYBASE SQL Anywhere представления создаются оператором CREATE VIEW. Для последующей изменения текста представлений применяется оператор ALTER VIEW. Он заменяет в существующим представлении текст базового оператора. В утилите ISQL в операторе ALTER VIEW каждый раз приходится вводить текст базового оператора. В утилите SQL Central полный текст оператора ALTER VIEW выводится в окне текстового редактора утилиты после двойного щелчка мыши по пиктограмме представления (см. рис. 76). В этом случае текст можно отредактировать, используя возможности текстового редактора.

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

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

Рассмотрим на примере как определяются привилегии для представлений. Для этого установим пользователю Popova привилегию SELECT для представления Code_books_1_3.

В SQL Central для этого следует выполнить следующую последовательность действий:

щелкнуть правой кнопкой мыши по пиктограмме представления Code_books_1_3 (см. рис. 76);
в появившемся контекстном меню выбрать пункт меню Properties;
в открывшемся окне Code_books_1_3 Properties - окне свойств представления выбрать закладку Permissions:
в закладке Permissions установить для пользователя Popova привилегию SELECT.

Внешний вид закладки Permissions очень похож на одноименную закладку окна свойств таблицы (см. рис. 56). Работа с этими закладками осуществляется одним и тем же способом.

Для достижения того же результата в утилите ISQL требуется выполнить следующий SQL-оператор:

/* Установка привилегии SELECT для 
представления Code_books_1_3 
пользователю Popova */
GRANT SELECT ON 
Stepanov.Code_books_1_3 
TO Popova

Теперь пользователь Popova может использовать оператор SELECT для представления Stepanov.Code_books_1_3.

После применения к представлениям операторов INSERT и DELETE количество записей в них изменяется. Тоже самое может произойти при выполнении оператора UPDATE для представлений, использующих указание выборки данных WHERE в базовом операторе. Это может произойти в том случае, когда в результате модификации записей их содержимое перестанет удовлетворять условию WHERE - условию включения данных записей в представление.

Изменение числа записей после операторов INSERT и DELETE очевидно, а после WHERE нет. Пользователь может не иметь никакой информации об условии WHERE. Для контроля за такой ситуации в тексте оператора в конец текст оператора CREATE/ ALTER VIEW следует добавить фразу WITH CHECK OPTION. Тогда при попытке модификации записей представления, которые могут привести к их исключению из ее состава, выдается сообщение о возникновении исключительной ситуации. Покажем это на примере.

Проведем модификацию представления Code_books_1_3. Для этого выполним оператор:

// Модификация представления
UPDATE Stepanov.Code_books_1_3
SET Code_book = 100
WHERE Code_book = 2

Исходное и модифицированное содержимое этого представления приведено в табл. 21, 22.

Таблица 21. Исходное содержимое представления Stepanov.Code_books_1_3

Code_book Author Number

1 Коршунов Ю.М. 49

2 Лавров И.А. 2

3 Трауб Дж 38

Таблица 22. Модифицированное содержимое представления Stepanov.Code_books_1_3

Code_book Author Number

1 Коршунов Ю.М. 49

3 Трауб Дж. 38

Выполните теперь оператор ROLLBACK для возврата базы данных в исходное состояние. После этого отредактируем текст представления путем введения нее фразы WITH CHECK OPTION:

//Изменение текста представления 
ALTER VIEW 
	Stepanov.Code_books_1_3 
AS SELECT Books.Code_book,
	Books.Author, Books.Number
FROM Stepanov.Books
WHERE Code_book>=1
	AND Code_book<=3
WITH CHECK OPTION

Теперь выполним тот же оператор UPDATE. При этом оператор завершится аварийно с выдачей сообщения "WITH CHECK OPTIPN violated for view 'Books'". Оно информирует пользователя о нарушения условия WITH CHECK OPTION для представления, созданного на базе таблицы Books.

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

//представление на базе таблицы и представления
CREATE VIEW Stepanov.Table_View
AS SELECT 
Code_books_1_3.Code_book //для этого поля
AS b_code, 	//вводися псевдоним b_code 
Copies.N_books FROM 
Stepanov.Copies NATURAL JOIN 
Stepanov.Code_books_1_3
WHERE b_code=2

Следующим является представление на базе двух таблиц с использованием арифметической функции Count (подсчет числа записей):

//представление на базе двух таблиц
CREATE VIEW Stepanov.Table_table AS 
SELECT Books.Code_book,
	"COUNT"(Copies.Code_book)
FROM Stepanov.Books NATURAL 
	JOIN Stepanov.Copies
GROUP BY Books.Code_book

Это представление выбирает данные о количестве экземпляров каждой книги. Тот же результат можно получить, если при помощи оператора ALTER VIEW заменить в представление Table_table базовый оператор на следующий:

/* Новый базовый оператор 
представления Table_table */
SELECT Code_book, Number 
FROM Stepanov.Books

Читателям предлагается проверить работу приведенных выше представлений.

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

Назад | Содержание | Вперед