Цель занятия:Изучение видом и методов использования структурных ограничений в языке SQL. Создание таблиц на основе SQL-выборок, временные таблицы. Создание и использование представлений. Использование последовательностей и синонимов.
Литература:
1. Бобровский, С. Oracle Database XE для Windows. Эффективное использование/ С. Бобровский. – М. : Лори. 2009. – 512 с.
2. Джейсон Прайс. SQL для Oracle 10g. Изд. Лори. 2010 г. – 556 с.
№
п/п
Содержание занятия
Отводимое учебное время, мин.
Применяемые наглядные пособия и ТСО
1.
Организационная часть
Проверка наличия студентов (по журналу)
15 мин.
2.
Вводная часть
1. кратко напомнить материал предыдущего занятия;
2. объявить тему лекции;
3. довести учебные вопросы;
4. определить место темы в учебном курсе, указать связь с предыдущими темами и междисциплинарные связи;
5. провести краткий опрос по предыдущему материалу;
6. отметить актуальность темы и ее практическое значение;
7. довести цель занятия;
8. сообщить литературные источники по теме занятия.
Интерактивная доска, проектор, презентация
3.
Основная часть (учебные вопросы)
65 мин.
Интерактивная доска, проектор, презентация
Структурные ограничения при определении таблиц
25мин.
Создание и использование представлений
25 мин.
Создание и использование
последовательностей и синонимов
10 мин.
4.
Заключительная часть
9. сделать выводы по теме;
10. ответить на вопросы студентов;
11. дать задание на самостоятельную работу;
12. объявить тему следующего занятия.
10 мин.
Интерактивная доска, проектор, презентация
Старший преподаватель кафедры
информационных таможенных технологий и информатики Заставной.Д.А.
1. Ограничения
На поля таблицы могут быть наложены ограничения – специальные требования, или условия, которые ограничивают множество значений, которые могут быть присвоены полям таблицы. Эти условия обычно используются для того, чтобы запретить помещать в базу данных значения, которые являются логически неправильными или противоречивыми. Например, поле “Возраст”, объявленное как целочисленное, не может содержать отрицательные значения, или, например, значение менее 18, и т.д. В целом, ограничения отражают семантические свойства данных предметной области, и выявляются при ее анализе. Использовать ограничения не обязательно, более того, их определение в схеме базы данных потенциально снижает скорость работы системы, однако их использование эффективно предотвращает накапливание в БД неправильных данных и откровенного “мусора” и повышает качество и эксплуатационные свойства системы.
Проверка выполнения ограничения производится при выполнении SQL-сервером команд обновления данных (INSERT, UPDATE и DELETE), а так же при изменении структуры таблицы. Если ограничение при конкретном обновлении не выполняется, генерируется ошибка.
Объявление ограничения в команде CREATE TABLE может помещаться непосредственно после определения поля (эти ограничения так же называются ограничениями на уровне полей, или in-line-ограничениями), или отдельным описанием (в этом случае их называют ограничениями на уровне таблицы, или out-line-ограничениями). Для одного поля может быть определено несколько in-line-ограничений. Ограничение на уровне таблицы обычно используют для накладывания ограничения на несколько полей, а ограничение на уровне поля затрагивает, естественно, только одно это поле.
Полное определение ограничения имеет следующий синтаксис:
CONSTRAINTимя_ограничения описание_ограничения
Здесь имя_ограничения - имя, которое присваивается данному ограничению (должно быть уникальными среди всех использованных имен ограничений в данной схеме); явное присваивание имени ограничению полезно для дальнейших действий с этим ограничением. Для in-line-ограничений служебное слово CONSTRAINT и имя ограничения необязательны и могут быть пропущены, при этом имя для ограничения создается автоматически.
Если на поле наложено ограничение NOT NULL, это означает, что в данном поле любой записи не может храниться значение NULL. Данное ограничение – единственное, которое может быть наложено на поля “длинных” типов BLOB и CLOB.
Это ограничение полезно, если в поле планируется хранить информацию, которая является обязательной и не может быть опущена или вставлена позднее.
Ограничение уникальности UNIQUE используется для того, чтобы в данном поле значения во всех записях были различными, или, иными словами, в таблице не должно существовать две записи с одинаковыми значениями этого поля. Это ограничение может быть наложено на одно поле или на группу полей; однако нельзя на одни и те же поля накладывать ограничение UNIQUE дважды.
Следует иметь в виду, что, если на группу полей наложено ограничение уникальности, из этого не следует, что каждой поле из набора само так же является уникальным. Например, если на пару полей “Имя” и “Фамилия” наложено это ограничение, конечно, сами по себе имена и фамилии, которые можно хранить в записях, могут повторяться. Кроме того, могут встречаться и пустые значения.
Частными случаем ограничения уникальности является ограничение первичного ключа PRIMARY KEY, которое требует дополнительно отсутствие пустых значений в группе полей. В дополнении к этому, ограничение PRIMARY KEY может встречаться только один раз среди ограничений на таблицу; впрочем, эквивалентная комбинация NOT NULL UNIQUE может быть наложена на любые другие поля.
Термин “первичный ключ” восходит к терминологии теории реляционных баз данных, согласно которой было принято разделять все возможные ключи на первичный и прочие, называемые вторичными, альтернативными и т.д. По существу, ключ таблицы – это группа полей с наложенным на них ограничением уникальности.
Ограничение доменной целостности (CHECK) позволяет контролировать присваиваемое значение данному полю при помощи логического выражения, которое проверяется при присваивании значения и, в случае если значение равно FALSE, ограничение считается нарушенным и возбуждается ошибка. В этом выражении можно использовать различные операции, содержащие присваиваемое значение поля и значения других полей данной записи, если ограничение объявлено как out-of-line. Подзапросы, однако, использовать нельзя, что означает, что при проверке нельзя анализировать данные из других строк этой же таблицы или других таблиц.
Наконец, последним из рассматриваемых ограничений является ограничение внешнего ключа, или ограничение референциальной целостности. Данное ограничение требует, чтобы в качестве значений поля или группы полей можно было использовать только значения полей какой-либо таблицы. Таблицу, на которую ссылается ограничение, называют часто master-таблицей, или родительской, а таблицу, в которой это ограничение определено – slave-таблицей, или подчиненной. Описание ограничений FOREIGN KEY, соответственно, содержит имя родительской таблицы и список ее полей, причем в родительской таблице на эту группу полей должно существовать наложенное ограничение UNIQUE или PRIMARY KEY.
Наконец, пре определении ограничения референциальной целостности можно определить поведение системы при удалении или изменении полей, на которые ссылается вешний ключ. Действительно, что должно происходить, если удалить запись в master-таблице, на которую ссылаются записи в подчиненной таблице? Данный вопрос регламентируется следующим образом. По умолчанию изменения полей референциального ключа и удаление содержащей его записи запрещено; однако существует две дополнительных опции – SET NULL для принудительного присваивания полям внешнего ключа значений NULL при удалении “родительской” записи, и опция CASCADE для удаления “подчиненных” записей. Перенос изменений от “родительских” записей к “подчиненным” называется каскадным обновлением.
2. Создание таблиц при помощи команды SELECT
Таблицы, создание которых рассматривалось выше, после исполнения соответствующих команд CREATE TABLE не содержат, естественно, строк с данными. Другим вариантом использования этой команды является создание таблиц из данных, взятых из других таблиц. Для этого необходимо при определении таблицы указать SELECT-запрос, который будет вычислен, и его значение – множество строк – образует строки новой таблицы. При этом структура новой таблицы – имена полей и их типы – будет создана в соответствии со структурой выборки запроса. Определения полей в таких командах
CREATE TABLE AS SELECT,
могут быть опущены, однако, если эти определения присутствуют, они должны соответствовать структуре фактической выборки SELECT-запроса. Ограничения на данные исходных таблиц в новую таблицу не переносятся.
Можно так же создавать временные таблицы (CREATE TEMPORARY TABLE), которые, как следует из названия, не хранятся в базе данных постоянно, а существует ограниченное время. Структура и данные временных таблиц так же формируются как значение запроса, извлекающего данные из других таблиц.
3. Последовательности
Полезными объектами схемы являются так называемые генераторы последовательностей (англ. SEQUENCE), называемые так же просто последовательностями. Эти объекты предназначены для создания последовательности чисел, которые обычно используются в качестве значений суррогатных ключей таблиц; функциональным аналогом последовательностей являются поля с автонумерацией, как в системах Microsoft SQL Server и т.д. Отличительными особенностями последовательностей является, во-первых, их независимость от какой-либо одной таблицы, и, во-вторых, их глобальность (потенциальная доступность всем пользователям).
Последовательность можно представить как некоторую псевдотаблицу с двумя полями NEXTVAL и CURRVAL. Обращение к первому полю создает новое целое число и возвращает его, а обращение ко второму полю возвращает последнее сгенерированное число. Для корректной работы с последовательностями необходимо в рамках одной транзакции сначала обратиться к полю NEXTVAL, а затем можно получать этот номер по полю CURRVAL. Обращение любого пользователя к полю NEXTVAL генерирует следующий номер, а обращение к полю CURRVAL всегда возвращает текущий.
Следует иметь в виду, что, поскольку к последовательности могут обращаться одновременно несколько пользователей, значения, которые поле NEXTVAL возвращает конкретному пользователю, могут идти не подряд.
При создании последовательности обычно указывается, помимо имени, начальное значение, шаг, и некоторые другие параметры. Ниже приведен пример создания последовательности и обращение к полям NEXTVAL и CURRVAL
CREATE SEQUENCE "Клиент_sec" MINVALUE 0
START WITH 0 INCREMENT BY 1
SELECT "Клиент_sec". NEXTVAL,
"Клиент_sec". CURRVAL FROM Dual
Команды ALTER SEQUENCE позволяет изменить параметры последовательности (кроме начального значение)
Если необходимо изменить начальное значение последовательности, следует ее удалить и создать заново. Удаление последовательности, конечно же, не влияет на значения полей записей, которым значения последовательности были присвоены ранее.
DROP SEQUENCE "Клиент_sec"
4. Представления
Представление (англ. VIEW), называемое иногда так же виртуальной таблицей, является объектом базы данных, который можно использовать, наряду с таблицами, в запросах как источник данных, и, с рассматриваемыми далее ограничениями, в командах обновления. В отличие от таблицы, представление на содержит записей, и представляет собой, по существу, именованный SELECT-запрос, который “подставляется” в SQL-команды при обращении к этому представлению. Таблицы, которые используются в запросе представления, называются базовыми.
Представления могут быть полезны в следующих случаях. Прежде всего, представления используются для именования или сокрытия сложных, громоздких и часто используемых SQL-выражений. Вернемся снова к следующему запросу:
WHERE "Клиент"."Код Клиента" = "Заказ"."Клиент" AND "Заказ"."Код заказа"="СоставЗак"."Код заказа" AND "Товар"."Код товара"="СоставЗак"."Код товара"
Этот неоднократно рассматриваемый запрос является, конечно, широко употребительным и вместе с тем очень громоздким; следует обратить так же внимание на отсутствие какой-либо селекции. На основании этого “общего” запроса целесообразно определить представление, которое далее использовать в многочисленных запросах. Ниже приведено определение такого представления.
AND "Заказ"."Код заказа"="СоставЗак"."Код заказа" AND "Товар"."Код товара"="СоставЗак"."Код товара"
Другое использование представлений связано с представление данных базовых таблиц в более удобном для пользователя виде; например, с содержательными заголовками на русском языке, и т.д. Типичным примером таких представлений являются представления системного словаря типа ALL_TABLES, USER_TABLES и т.д., извлекающие данные из системной таблицы OBJ$, которая является малоподходящей для непосредственной работы.
Третьим типичным использованием представлений является сокрытие каких-либо столбцов и строк базовых таблиц. Представления типа USER_TABLES из системного каталога снова оказываются типичным примером. Приведем, однако, еще один пример построения представления, скрывающего некоторые строки базовой таблицы.
Сначала создадим некоторую базовую таблицу:
CREATE TABLE "Все записи"
( username CHAR(20) DEFAULT USER
NOT NULL UNIQUE,
name CHAR(20),
birth DATE )
Обратите внимание, что поле username содержит присваиваемое по умолчанию значение – название учетной записи пользователя, доступное через системную функцию USER; прочие поля содержат ползовательскую информацию.
Теперь создадим представление со следующим определением:
CREATE OR REPLACE VIEW "Мои записи"
AS SELECT name "Имя", birth "Дата рождения"
FROM "Все записи"
WHERE username = USER
Обратите внимание, что поля username в списке извлекаемых полей в запросе не присутствует, и поэтому пользователю будет недоступно. Далее, условие селекции предоставляет доступ только к тем записям, которые были добавлены пользователем с той же учетной записью. Если пользователь не имеет доступа на чтение и обновление к таблице "Все записи" (такое ограничение доступа может быть реализовано), он не сможет через представление "Мои записи" получить доступ к чужим записям.
Обратите внимание, что по имени источника данных невозможно определить, является ли он таблицей или представление, или каким-либо другим объектом схемы:
SELECT * FROM "Мои записи"
Это представление можно так же использовать и для удаления, добавления и изменения записей, но с некоторыми ограничениями, например:
INSERT INTO "Мои записи" VALUES ( 'Ада Вонг', NULL )
При добавлении строки в представление, при этом, естественно, запись будет добавлена в базовую таблицу, причем поле username будет инициализирована значеним по умолчанию:
USERNAME
NAME
BIRTH
CUSTOMER
Ада Вонг
-
Здесь значение CUSTOMER – имя учетной записи.
Теперь рассмотрим вопрос о модификации данных базовых таблиц через обращение к ним через представления; пример добавления новой записи приведен выше. В целом, добавление новых записей и изменение и удаление существующих возможно, если, говоря неформально, между строками представления и строками базовых таблиц существует прямое соответствие, равно как и между полям представления и таблицы. Такое соответствие присутствует в представлении "Мои записи". Данные нельзя модифицировать, если запрос представления содержит, в том числе, выражения, вычисляемые на основании полей исходный таблицы, опцию DISTINCT, упорядочивание, группировку и вычисление агрегатных функций, операцию UNION, а так же многотабличные запросы с соединением (есть исключения). Модифицировать данные рассматриваемого выше представления "Все заказы", конечно же, нельзя.
Можно явным образом запретить изменение данных любого представления, используя опцию WITH READ ONLY, например:
CREATE OR REPLACE VIEW "Мои записи" AS
SELECT name "Имя", birth "Дата рождения"
FROM "Все записи"
WHERE username = USER
WITH READ ONLY
Однако эта запись, естественно, не будет содержаться в значении выборки SELECT * FROM "Мои записи", поскольку не сответствует условию селекции запроса. Чтобы устранить подобные противоречивые ситуации, следует к определению запроса добавить опцию WITH CHECK OPTION, при использовании которой происходит проверка изменений условиям запроса:
CREATE OR REPLACE VIEW "Мои записи" AS
SELECT name "Имя", birth "Дата рождения",
User “Пользователь”
FROM "Все записи"
WHERE username = USER
WITH CHECK OPTION
Команда ALTER VIEW предназначена для изменения наложенных ограничений на представление (в данном пособии не рассматриваемых); для изменения запроса необходимо представление создать заново. Удаление представления при помощи команды DROP VIEW на данные базовых таблиц не влияет.
