|
Юсњхь ш ёђ№ѓъђѓ№р ъѓ№ёр
Объем и структура
курса
По курсу запланировано:
один час лекций и три часа лабораторных занятий в неделю, а также курсовая работа.
В курсовой работе студент (или бригада из двух студентов) должен выбрать любую,
хорошо знакомую ему, предметную область - часть реального мира,
данные о которой он хочет хранить в базе данных на компьютере. Это может быть
небольшой магазин (ларек), торгующий любой продукцией (например, аудио- или
видио-кассетами); пошивочное ателье; пункт проката; зоопарк; склад; коллекция
(монет, марок, вин и др.); справочная вокзала (железнодорожного, речного, морского
или аэро); аптека,
лекарственные растения, гостиница, продажа квартир и т.п. Затем он должен спроектировать
для этой предметной области базу данных и создать ее на SQL-сервере
кафедры ВТ во время лабораторных занятий. Начать же лабораторные занятия он
должен со знакомства с основами языка SQL, являющегося международным
стандартом языка баз данных.
Занятия будут проводиться
с использованием СУБД Oracle8, являющейся лучшей профессиональной
СУБД. Желающие увеличить время на изучение этой СУБД и имеющие дома ПК с достаточными
ресурсами (х486 или выше, не менее 16 МВ ОЗУ, 200 МВ дискового пространства
и установленный Windows 95 или Windows NT) могут переписать на кафедре пакет
Personal Oracle 7.3 или Personal Oracle8.
В следующем семестре,
в рамках дисциплины "Проектирование приложений к базам данных", студент
(бригада) должны создать приложение для своей базы данных, т.е.
спроектировать и построить различные формы для ввода, модификации и просмотра
данных, а также отчеты для вывода разнообразной документации.
Литература
1. Бобровски С.
Oracle7 и вычисления клиент/сервер. - М.: ЛОРИ, 1995. - 652 с.
2. Грабер М. Введение
в SQL. - М.: ЛОРИ, 1996. - 380 с.
3. Дейт К.Дж. Введение
в системы баз данных. - 6-изд. - К.: Диалектика, 1998. - 784 с.
4. Кириллов В.В.
Основы проектирования реляционных баз данных. Учебное пособие. - СПб.: ИТМО,
1994. - 88 с. http://www.cs.ifmo.ru, http://www.citforum.ru.
5. Кириллов В.В., Громов Г.Ю.
Структуризированный язык запросов (SQL). Учебное пособие. - СПб.: ИТМО, 1995.
- 92 с. http://www.cs.ifmo.ru, http://www.citforum.ru.
6. Кириллов В.В., Громов Г.Ю.
Краткий справочник по Oracle7. Электронное учебное пособие. - ИТМО, 1997. http://www.cs.ifmo.ru.
7. Компания Advanced
Information Systems и др. Oracle8. Энциклопедия пользователя.: К. - ДиаСофт,
1998. - 864 с.
8. Мейер М. Теория
реляционных баз данных. - М.: Мир, 1987. - 608 с.
9. Ричардс Майкл
и др. Oracle 7.3. Энциклопедия пользователя. - К: ДиаСофт, 1997. - 832 с.
10. Сингх Лэйв.
Oracle 7.3. Пособие разработчика. - К: ДиаСофт, 1997. - 736 с.
11. Тиори Т., Фрай
Дж. Проектирование структур баз данных. В 2 кн., - М.: Мир, 1985. Кн. 1.- 287
с.: Кн. 2. - 320 с.
12. Урман С. Oracle8:
Программирование на языке PL/SQL.
- М.: ЛОРИ, 1999. - 610 с.
1. Введение
Существует множество
определений понятия "База данных" (БД) иногда совсем не похожих друг
на друга. Воспользуемся одним из них: База данных - это файлы, снабженные описанием
хранимых в них данных и находящиеся под управлением специальных программных
комплексов, называемых "Системы управления базами данных" (СУБД)?.
Сколько должно быть
этих файлов, кто и как должен распределять между ними данные, как создать понятные
СУБД описания данных, как обеспечить безопасность хранимых данных? Эти и многие
другие вопросы будут изучаться в этом курсе. Но сначала будет рассмотрен достаточно
большой пример.
Дело в том, что
человеку, не знакомому с БД, трудно без примеров объяснить, что такое данные
и их описание, как устроена СУБД и как она управляет данными. Конечно, таким
примером может стать описание любой базы данных, функционирующей под управлением
какой-либо СУБД. Однако, как было сказано выше, в этом курсе нам надо познакомиться
и с методами проектирования БД, поэтому целесообразнее потратить время на создание
своей иллюстрационной БД. Для этого спроектируем БД Учебный процесс?, в которой
будут хранится сведения необходимые для Деканатов, Учебной части, Отдела кадров,
Планового отдела (распределение средств за обучение между кафедрами), Бухгалтерии
(стипендия), Выпускающих кафедр и др.
При построении моделей
мы будем опираться в основном на здравый смысл, а не на теоретические положения
и рекомендации, которые будут рассмотрены в следующих разделах курса. Желающие
раньше познакомиться с ними могут воспользоваться, например, учебным пособием
[4].
2. Введение
в проектирование баз данных
2.1. Об этапах
проектирования
Процесс проектирования
состоит из трех основных этапов:
-
получение технического
задания или создание описания предметной области;
-
построение инфологической
модели базы данных;
-
создание даталогической
модели базы данных.
Сначала необходимо
получить от заказчика техническое задание на разработку базы данных или при
инициативной ее разработке (например, в курсовом проекте) подробно описать выбранную
предметную область - часть реального мира, данные о которой нужно хранить в
создаваемой базе данных.
Затем создается
обобщенное, не привязанное к каким-либо компьютерам и СУБД, описание предметной
области (наборы данных, их типов, длин, связей и т.п.), называемое инфологической
моделью.
Наконец, выбирается
СУБД, под управлением которой должна функционировать база данных, и создается
ее даталогическая модель.- инфологическая модель, переведенная на язык
выбранной СУБД.
2.2. Описание
предметной области Учебный процесс?
2.2.1.
Учебные планы и выпускающие кафедры
По инициативе одной
или нескольких кафедр вуза, подготавливающих документы для обоснования открытия
на ее (их) базе новой специальности или бакалаврского направления, Ученый совет
вуза обращается в министерство с просьбой о выдаче лицензии на право подготовки
по этой специальности (направлению). Среди этих документов существует и предлагаемый
вузом учебный план, составленный на основе соответствующего государственного
образовательного стандарта (ГОС), содержащего государственные требования к минимуму
содержания и уровню подготовки выпускников. В шапках? этих планов, например,
указано:
УЧЕБНЫЙ ПЛАН
ПОДГОТОВКИ ИНЖЕНЕРОВ ПО
СПЕЦИАЛЬНОСТИ
220100 "ВЫЧИСЛИТЕЛЬНЫЕ
МАШИНЫ, КОМПЛЕКСЫ, СИСТЕМЫ И СЕТИ"
Специализация 220111 - "Открытые
информационно-вычислительные системы"
или
УЧЕБНЫЙ ПЛАН
ПОДГОТОВКИ БАКАЛАВРОВ ПО
НАПРАВЛЕНИЮ
552800 "ИНФОРМАТИКА
И ВЫЧИСЛИТЕЛЬНАЯ ТЕХНИКА"
или
УЧЕБНЫЙ ПЛАН
ПОДГОТОВКИ МАГИСТРОВ ПО
НАПРАВЛЕНИЮ
552800 "ИНФОРМАТИКА
И ВЫЧИСЛИТЕЛЬНАЯ ТЕХНИКА"
Специализация 552811 - "Базы
данных"
С выполненными по
принятой в вузе форме учебными планами вы можете познакомиться на выпускающих
кафедрах - кафедрах, которым поручен набор абитуриентов и их подготовка по соответствующей
специальности или направлению, по которым получена лицензия.
Учебные планы и
программы обучения по специальностям, бакалаврским и магистерским направлениям
непрерывно модифицируются с целью их улучшения и привязки к текущему моменту.
Выпускающие кафедры пытаются внедрить новые (модифицированные) дисциплины и
другие нововведения по мере их появления и нередко студент начинает обучение
по учебному плану, который действовал на момент его поступления в университет,
затем плавно переходит на смесь этого и нового планов (так называемый, переходной
учебный план), затем на новый переходной учебный план и т.д., заканчивая обучение
совсем по другому учебному плану. Кроме того, в учебных планах специально оставлено
место для ежегодной модификации (Дисциплины по выбору студентов или выпускающих
кафедр, Факультативы и т.п.) и нередко студенты изучают дисциплины, названия,
объемы и другие атрибуты которых отличаются от дисциплин утвержденных учебных
планов.
Поэтому планирование
учебного процесса в студенческих потоках и группах осуществляется с помощью
рабочих учебных планов, ежегодно составляемых и утверждаемых на основании старых,
переходных и новых учебных планов с учетом контингента обучаемых. Некоторым
студентам разрешено создавать индивидуальный учебный план, который также может
ежегодно корректироваться.
В рабочих учебных
планах, составляемых в конце предшествующего учебного года на следующий учебный
год, приведены для каждого направления, специальности и специализации актуальные
на текущий момент дисциплины и номера студенческих групп (подгрупп), которые
должны изучать эти дисциплины. В зачетных книжках студентов и ряде документов
деканатов фиксируется набор изучаемых каждым студентом дисциплин, а также те
оценки, которые они получили за курсовые проекты (работы), на зачетах и экзаменах.
Следовательно, единственными
актуальными документами, в которых прописаны реально изучаемые студентами дисциплины,
являются рабочие и индивидуальные планы и весь процесс обучения должен быть
привязан к ним.
На рис. 2.1 приведен
рабочий план для студентов 3-го курса специальности 220100.
В плане в столбце "Кафедра"
дана аббревиатура кафедры, которой поручено проведение занятий по дисциплине,
с названием указанным в столбце "Дисциплина". В следующих столбцах
приведены числа часов запланированных на лекции (Лек), лабораторные (Лаб) и
практические (Прак) занятия, а также на самостоятельную работу студента (СРС)
над материалом дисциплины (подготовка к лекциям, лабораторным и практическим
занятиям, выполнение домашних заданий, курсовых проектов и работ, ...). Далее
(Конт) указан вид контроля (экзамен, зачет, КП или КР) и индекс (Инд) дисциплины,
т.е. принадлежность ее к одному из следующих циклов дисциплин: "Гуманитарные
и социально-экономические дисциплины"
(ГСЭ), "Естественно-научные дисциплины"
(ЕН), "Дисциплины направления" (ДН), "Общепрофессиональные дисциплины"
(ОПД), "Специальные дисциплины" (СД), "Дисциплины специализации"
(ДС) и "Дополнительные виды обучения и факультативы" (ДФ). Если по
дисциплине запланировано N видов контроля, то в рабочем плане для нее отводится
N строк.
При этом одна выпускающая
кафедра может вести подготовку по нескольким специализациям (одной или нескольких
специальностей), бакалаврским и магистерским направлениям, а также разным формам
обучения (дневной, вечерней или заочной). Аналогично, по одной специализации
(одному направлению) могут вести подготовку несколько выпускающих кафедр.
Как правило, одна
студенческая группа "принадлежит" одной выпускающей кафедре и подготавливается
по одной специализации или одному направлению. Однако встречаются случаи, когда
в группе обучаются студенты (бакалавры или магистры) разных специализаций (они
вместе обучаются по общим для специализаций дисциплинам и разделяются на подгруппы
по остальным).
Схематично связь
между группами (подгруппами), кафедрами и специализациями (направлениями) на
1998/99 учебный год может быть показана следующим образом (рис. 2.2).
2.2.2.
Сведения об обучаемых
О каждом из обучаемых (студентов,
бакалавров, магистров, аспирантов и т.п.) требуется хранить в базе данных достаточно
много сведений. Для студентов, бакалавров и магистров часть из них определено
в учебной карточке студента (рис. 2.3).
Кроме того, сведения о студенте существуют
и в таких документах:
ЛИЧНЫЙ ЛИСТОК ПО УЧЕТУ КАДРОВ,
ЗАЧЕТНО - ЭКЗАМЕНАЦИОННЫЕ ВЕДОМОСТИ,
СВЕДЕНИЯ О ТЕКУЩЕЙ УСПЕВАЕМОСТИ (АТТЕСТАЦИИ),
ПРИКАЗ О ЗАЧИСЛЕНИИ НА СТИПЕНДИЮ,
РАСПИСАНИЕ ЗАНЯТИЙ,
...
Ограничимся в данном примере только следующими
ниже данными о студентах (бакалаврах и магистрах):
1. Номер зачетной книжки (НЗК).
2. Фамилия.
3. Имя.
4. Отчество.
5. Дата рождения.
6. Пол.
7. Признак: 1 - для студентов
из дальнего зарубежья (иностранцев) и 0 - для остальных.
8. Группы, в которых обучался
студент.
9. Номера рабочих (индивидуальных)
планов и деканаты - соавторы этих планов.
11. Изменения состояния обучаемого (академический
отпуск, повторное обучение и т.п.).
12. Номера приказов об изменении каких-либо
предшествующих данных.
При этом ряд данных должен
быть снабжен датой, указанной в приказе о их изменении (например, о переводе
студента на следующий курс или об окончании академического отпуска).
Наконец, будем еще хранить
в создаваемой базе сведения об оценках, получаемых по курсовым зачетам и экзаменам,
пока не затрагивая данные о текущей аттестации.
Для контрактных
студентов (т.е. студентов, полностью возмещающих затраты на обучение) необходимо
также знать: подразделение, заключившее договор на платное обучение, и номер
договора; начало и конец действия договора; ежегодную величину оплаты (в МРОТ
или $)
по договору; различные исключения по оплате; сведения о внесении в кассу ИТМО
средств за платное обучение и другие услуги.
2.3. Инфологическая
модель базы данных "Учебный процесс"
2.3.1.
О моделях данных
Как правило все
данные связаны между собой. (Со студентом связано множество дисциплин и их преподавателей,
один номер зачетной книжки и одна стипендия ...). Наличие множества связей требует
создания определенной структуры описания данных, которые называются моделями
данных.
Исторически первой появилась иерархическая
модель, например
== Факультет ==> группы ==>
студенты ==> дисциплины ==> преподаватели ==
Однако оказалось,
что поиск данных от листьев к корню выполняется значительно медленнее чем от
корня к листьям. (быстрый запрос - преподаватели обслуживающие факультет, медленный
запрос - количество неуспевающих студентов на каждом факультете).
Создали сетевую
модель (связанные кольца двухуровневых деревьев), но она оказалась настолько
сложной что проектированием данных могли заниматься только высококвалифицированные
специалисты.
В 70 г. была предложена
реляционная (relation - отношение, связь) или иначе табличная модель данных.
В дальнейшем будем ориентироваться только на эту модель данных, обладающую следующими
основными свойствами.
1. Данные воспринимаются
пользователями как таблицы (и никак иначе).
2. Каждая таблица
состоит из однотипных строк и имеет уникальное имя.
3. Строки имеют
фиксированное число полей (столбцов) и значений (множественные поля и повторяющиеся
группы недопустимы). Иначе говоря, в каждой позиции таблицы на пересечении строки
и столбца всегда имеется в точности одно значение или ничего.
4. Строки таблицы
обязательно отличаются друг от друга хотя бы единственным значением, что позволяет
однозначно идентифицировать любую строку такой таблицы.
5. Столбцам таблицы
однозначно присваиваются имена, и в каждом из них размещаются однородные значения
данных (даты, фамилии, целые числа или денежные суммы).
6. Полное информационное
содержание базы данных представляется в виде явных значений данных и такой метод
представления является единственным. В частности, не существует каких-либо специальных
"связей" или указателей, соединяющих одну таблицу с другой.
7. При выполнении
операций с таблицей ее строки и столбцы можно обрабатывать в любом порядке безотносительно
к их информационному содержанию. Этому способствует наличие имен таблиц и их
столбцов, а также возможность выделения любой их строки или любого набора строк
с указанными признаками.
2.3.2.
Первая попытка проектирования
В выбранной модели
все хранимые данные должны быть размещены в одной или нескольких таблицах. Можно
ли для этой цели использовать единственную таблицу, так называемое, универсальное
отношение ?
Принципиально можно,
но в этой таблице данные почти всех столбцов будут многократно повторятся (возникнет
избыточность). Так для каждого студента одной и той же группы
будут повторяться названия специализации, выпускающей кафедры и изучаемых дисциплин;
каждая из дисциплин рабочего плана будет предваряться всеми атрибутами студента
(Фамилия, Имя, Отчество, ...) и т.п.
Вследствие избыточности
могут возникать проблемы при изменении данных.
Аномалии обновления.
Если, например, студентка поменяла фамилию, то при изменении в таблице множества
экземпляров ее фамилии можно забыть? обновить некоторые строки со старой фамилией
и тем самым потерять? какие-либо данные об успеваемости этой студентки.
Аномалии включения.
В таблицу нельзя внести данные о том, по каким рабочим учебным планам будут
обучаться студенты в следующем учебном году, так как у вуза нет данных о будущих
студентах первого курса, да и неясно, кто из студентов старших курсов перейдет
на следующий курс, без изменения специализации.
Аномалии удаления.
Обратные проблемы возникают при необходимости удаления из таблиц сведений о
каком-либо студенте или о дисциплинах рабочего плана.
Многие проблемы
исчезнут, если выделить в отдельные таблицы сведения о кафедрах, специализациях,
рабочих планах, студентах и т.д. Такое разбиение таблицы на несколько таблиц,
обладающих лучшими свойствами при включении, изменении и удалении данных, называется
нормализацией. Окончательная цель нормализации сводится к получению
такого проекта базы данных, в котором каждый факт появляется лишь в
одном месте, т.е. исключена избыточность информации. Это делается
не столько с целью экономии памяти, сколько для исключения возможной противоречивости
хранимых данных (например, если в таблице одновременно существует и старая,
и новая фамилия студентки).
2.3.3.
Вторая попытка проектирования
Анализ определенных
выше объектов и атрибутов, а также показанных выше аномалий, позволяет выделить
сущности проектируемой базы данных, которые можно разделить на независимые (стержневые)
и зависимые (ассоциации, характеристики и обозначения) [4].
В процессе определения
атрибутов сущности (столбцов таблицы) необходимо обязательно определить их возможные
и первичные ключи:
Ключ
или возможный ключ - минимальный набор атрибутов, по значениям
которых можно однозначно найти требуемый экземпляр сущности (строку таблицы).
Минимальность означает, что исключение из набора любого атрибута не позволяет
идентифицировать сущность по оставшимся. Первичный ключ - любой
из возможных ключей (лучше несоставной и целочисленный). Например, если считать,
уникальными для каждого студента атрибуты номер зачетной книжки (НЗК) и сочетание
"Фамилия, Имя, Отчество,
Рождение, Пол", то для выбора конкретного студента можно использовать любой
из этих возможных ключей, но лучше целочисленный - НЗК.
Для зависимых сущностей
необходимо рассмотреть еще одно понятие - внешний ключ. Это атрибут
или набор атрибутов зависимой (дочерней) таблицы, с помощью которого (которых)
производится ссылка на первичный ключ другой (родительской) таблицы, т.е. как
бы производится связывание таблиц. При этом родительской может быть как независимая,
так и зависимая таблица - цепочка связей между таблицами ничем не ограничивается
и может иметь чрезвычайно сложную структуру. Ввод в СУБД правильного описания
первичных и внешних ключей включает механизмы ограничений базы данных, не позволяющие
удалить строку с тем значением первичного ключа, на который ссылается какой-либо
внешний ключ, или ввести строку с таким значением внешнего ключа, которое отсутствует
среди значений первичного ключа.
Кроме описания атрибутов
каждой таблицы (включая ключи) необходимо указать также те ограничения на значения
и форматы данных в отдельных столбцах или их сочетаниях, которые позволят задействовать
в СУБД дополнительные процедуры обеспечения целостности (правильности
и непротиворечивости) хранимых данных.
2.3.3.1. Независимые сущности
(таблицы)
1. Студенты (Номер, НЗК,
Фамилия, Имя, Отчество, Рождение, Пол, Иностранец, Пользов, Измен).
Таблица отводится
для хранения неизменных (кроме редкого изменения фамилии) данных об обучаемых
(студентах, бакалаврах, магистрах, аспирантах и докторантах). Остальные данные,
которые должны изменяться по мере перехода обучаемого с курса на курс или по
другим причинам будут размещены в зависимой таблице Ученик.
Здесь НЗК
и Рождение - номер зачетной книжки и дата рождения обучаемого, а Иностранец
- содержат 1 для обучаемых из "дальнего зарубежья" и 0 - в остальных
случаях. Тип данных атрибута Рождение - дата, а атрибутов Иностранец и НЗК -
целые числа, длиной одна и шесть цифр, соответственно.
Так как у аспирантов
и докторантов нет зачетных книжек и, следовательно, НЗК, а сочетание фамилии,
имени, отчества, даты рождения и пола достаточно громоздко и теоретически не
является уникальным, то для уникальной идентификации строки таблицы Студенты
в нее целесообразно добавить целочисленный атрибут Номер, объявив его
первичным ключом. Учитывая, что в ИТМО ежегодно поступает не более 2000 обучающихся,
длину номера может ограничить пятью цифрами и для удобства размещения в документах
сделать постоянной (от 10000 до 99999). Каждый новый номер целесообразно получать
с помощью процедуры, наращивающей последний существующий номер на единицу.
Фамилия, Имя
и Отчество - текстовые атрибуты, длина которых может быть ограничена
значениями 20, 15 и 20 символов, соответственно.
Пользов и
Измен - идентификатор пользователя, который ввел эту строку или исправил
ее содержание, и время ее изменения (дата и время с точностью до секунд); это
делается для повышения ответственности пользователей, вводящих и (или) изменяющих
данные. Формат этих значений: целое число длиной до четырех цифр и дата, соответственно.
Ввод Пользов и Измен должен производится автоматически и они не могут быть скорректированы
пользователями.
При вводе новых
или изменении атрибутов существующих строк необходимо обеспечить уникальность
НЗК и информировать о появлении дубликата существующего сочетания "НЗК,
Фамилия, Имя, Отчество, Рождение, Пол, Иностранец". Кроме того, необходимо,
чтобы значения столбца Пол принадлежали набору "м" или "ж",
а дата рождения была в пределах от (Текущая дата - 40 лет) до (Текущая дата
- 15 лет), первые буквы фамилии, имени и отчества были большими, а остальные
- малыми.
2. Уч_года (Уч_год, Начало,
Конец).
Таблица отводится
для хранения названий учебных годов (Уч_год в формате 'ГГГГ/ГГГГ')
и дат их начала (Начало) и окончания (Конец).
Первичным ключом
этой таблицы является Уч_год.
При вводе новых
или изменении атрибутов существующих строк необходимо отслеживать, чтобы:
-
дата начала была не больше,
чем дата окончания учебного года;
-
сочетание "Уч_год,
Начало, Конец" было уникальным.
3. Уч_циклы (Индекс, Имя_цикла).
Таблица отводится
для хранения индексов (например, ЕН) и названий (Естественно-научный) учебных
циклов. Это текстовые атрибуты, длиной до трех и сорока пяти символов.
В таблице два возможных
ключа, из которых в качестве первичного ключа целесообразно выбрать короткий
Индекс.
Эта таблица достаточно
проста и должна содержать мало данных (в пределах одного экрана), правильность
ввода и изменения отслеживается визуально.
4. Факультеты (Факульт, Факультет).
Таблица отводится
для хранения аббревиатур и названий факультетов. Здесь Факультет - название
факультета (например, Оптический факультет или Факультет компьютерных технологий
и управления), а Факульт - аббревиатура названия факультета (например,
ИФ или КТиУ). Это текстовые атрибуты длиной до пятидесяти и пяти символов, соответственно.
В таблице два возможных
ключа, из которых в качестве первичного ключа целесообразно выбрать короткий
Факульт.
Таблица достаточно
проста и должна содержать мало данных (в пределах одного экрана), правильность
ввода и изменения отслеживается визуально.
5. Специал (Специал, Статус,
Имя_спец, Пользов, Измен).
Таблица отводится
для хранения номеров (Специал) и полных названий (Имя_спец) специальностей,
направлений и специализаций, а также статуса (Статус) обучаемого (студент,
бакалавр, магистр, аспирант или докторант). Например, "220100, студент,
Вычислительные машины, комплексы, системы и сети", "552800, бакалавр,
Информатика и вычислительная техника" или "552800, магистр, Информатика
и вычислительная техника". Это текстовые атрибуты длиной до шести, ста
двадцати и девяти символов, соответственно.
Пользов и
Измен - см. описание этих полей в таблице 1. Студенты.
Как показано выше,
аналогичным бакалаврским и магистерским направлениям присваиваются одинаковые
номера и названия, поэтому в качестве первичного ключа придется использовать
один из двух возможных составных ключей: Специал, Статус или Имя_спец, Статус.
Используем короткий - Специал, Статус.
При вводе новых
или изменении атрибутов существующих строк необходимо обеспечить уникальность
сочетания "Специал, Статус, Имя_спец".
6. Имя_дисц (Кор_дисц, Имя_дисц,
Пользов, Измен).
Таблица отводится
для хранения сокращенных и полных названий дисциплин. Здесь Кор_дисц
- короткое, а Имя_дисц - полное название дисциплины. В таблице два возможных
ключа, из которых в качестве первичного ключа целесообразно выбрать короткий
- Кор_дисц. Это текстовые атрибуты длиной до шестнадцати и ста двадцати символов,
соответственно.
Пользов и
Измен - см. описание этих полей в таблице 1. Студенты.
При вводе новых
или изменении атрибутов существующих строк необходимо обеспечить уникальность
сочетания "Кор_дисц, Имя_дисц".
7. Вид_отд (Вид_отд, Имя_вида_отд).
Таблица отводится
для хранения кода (Вид_отд) и названия (Имя_вида_отд) вида отдела,
например, "1, Административные", "3, Учебные" и т.п. Из
двух ее возможных ключей (Вид_отд и Имя_вида_отд) выбирается короткий - Вид_отд.
Вид_отд целочисленный атрибут длиной до двух цифр, а Имя_вида_отд - текстовый
атрибут длиной до сорока символов.
Эта таблица достаточно
проста и должна содержать мало данных (в пределах одного экрана), правильность
ввода и изменения которых отслеживается визуально.
8. Тип_дог (Тип_дог, Имя_тип_дог).
Таблица отводится
для хранения кода (Тип_дог) и названия (Имя_тип_дог) типа договора
на платное обучение, например, "1, Контрактное обучение студента ",
"2, Дополнительные образовательные услуги " и т.п. Из двух ее возможных
ключей (Тип_дог и Имя_тип_дог) выбирается короткий - Тип_дог. Тип_дог целочисленный
атрибут длиной до двух цифр, а Имя_тип_дог - текстовый атрибут длиной до тридцати
символов.
Эта таблица достаточно
проста и должна содержать мало данных (в пределах одного экрана), правильность
ввода и изменения которых отслеживается визуально.
2.3.3.2.
Зависимые сущности (таблицы)
9. Фамилия (Номер, Фамилия,
Конец, Пользов, Измен).
Таблица отводится
для хранения старой (до изменения) фамилии (Фамилия) и даты (Конец),
до которой она существовала. Здесь Номер - внешний ключ, связанный с
первичным ключом табл. Студенты. Форматы этих атрибутов описывались ранее в
таблицах Студенты и Уч_года.
Пользов и
Измен - см. описание этих полей в таблице 1. Студенты.
При вводе новых
или изменении атрибутов существующих строк необходимо, чтобы дата окончания
действия старой фамилии была в пределах от (Текущая дата - 10 лет) до (Текущая
дата).
10. Отделы (Отдел, Кор_имя_отд,
Факульт, Вид_отд, Имя_отд, Пользов, Измен).
Таблица отводится
для хранения необходимого минимума сведений о кафедрах и других подразделениях
вуза. Здесь: Отдел, Кор_имя_отд и Имя_отд - номер, аббревиатура
и название отдела (например, "102, ВТ, Кафедра вычислительной техники"
или "209, ВМ, Кафедра высшей математики"); при этом из трех возможных
ключей (Отдел, Кор_имя_отд и Имя_отд) в качестве первичного выбирается простейший
- Отдел;
Факульт и
Вид_отд - внешние ключи, связанные с первичными ключами табл. Факультет
и Вид_отд, соответственно; из них выбираются название факультета и вид соответствующего
подразделения.
Отдел и Вид_отд
- целочисленные атрибуты длиной три и две цифры, соответственно. Кор_имя_отд,
Факульт и Имя_отд - текстовые атрибуты длиной восемь, пять и семьдесят два символа,
соответственно.
Пользов и
Измен - см. описание этих полей в таблице 1. Студенты.
При вводе новых
или изменении атрибутов существующих строк необходимо обеспечить уникальность
значений столбца Кор_имя_отд.
11. Вып_каф (Кафедра, Факульт,
Специал, Статус, Курс, Вариант, Начало, Конец, Форма, Нач_дипл, Ном_план, Пользов,
Измен).
Таблица отводится
для хранения сведений о связи выпускающих кафедр и закрепленных за ними специализаций.
Ее содержимое соответствует новым "шапкам" рабочих планов (рис. 2.4),
в которую добавлены атрибуты Кафедра (держатели этих рабочих планов) и Ном_план
(уникальный номер конкретного рабочего плана). Здесь:
Кафедра -
номер выпускающей кафедры (внешний ключ, связанный с первичным ключом Отдел
табл. Отделы);
Факульт -
аббревиатура названия факультета, обеспечивающего обучение по рабочему плану
(внешний ключ, связанный с первичным ключом табл. Факультет).
Специал и
Статус - номер специализации и статус обучаемых (составной внешний ключ,
связанный с первичным ключом табл. Специал);
Курс и Вариант
- номер курса (1, 2, ..., 6) и вариант рабочего плана: основной (Осн) или индивидуальный
(Ин1, Ин2, ...);
Начало и
Конец - даты начала и окончания обучения по данному рабочему плану (эти
даты могут не совпадать с датами начала и конца учебного года);
Форма -форма
обучения ("дневная", "вечерняя" или "заочная")
для обучаемых по данной специализации на данной кафедре;
Нач_дипл -
дата начала дипломного проектирования (для дипломников);
Ном_план
- уникальный номер строки в данной таблице (первичный ключ), который создается
путем наращивания максимального из существующих (до ввода этой строки) номеров
строк на единицу.
Кафедра, Курс и
Ном_план - целочисленные атрибуты длиной три, одна и четыре цифры, соответственно;
Факульт, Специал, Статус, Вариант и Форма - текстовые атрибуты длиной пять,
шесть, девять, три и восемь символов, соответственно; Начало, Конец и Нач_дипл
- даты.
Пользов и
Измен - см. описание этих полей в таблице 1. Студенты.
При вводе новых или изменении
атрибутов существующих строк надо отслеживать, чтобы:
-
значения столбца Курс лежали в пределах
от 1 до 6;
-
даты начала и окончания
обучения по плану должны лежать в пределах дат начала и окончания соответствующего
учебного года и дата начала была не больше даты окончания;
-
значения столбца Форма принадлежали
набору: "дневная", "вечерняя" или "заочная";
-
запрещается изменять и удалять строки
предшествующих учебных годов.
-
была обеспечена уникальность
сочетания "Кафедра, Специал, Статус, Вариант, Начало, Конец, Курс,
Форма".
12. Груп_спец (Ном_план, Группа,
Пользов, Измен).
Эта таблица служит
для хранения тех номеров групп (Группа), в которых обучаются студенты,
привязанные через внешний ключ Ном_план к соответствующей выпускающей
кафедре, специализации, форме обучения, курсу и учебному году. Группа - целочисленный
атрибут длиной три символа.
Пользов и
Измен - см. описание этих полей в таблице 1. Студенты.
Первичным ключом этой таблицы
является сочетание "Ном_план, Группа".
13. Ученик
(Номер, Группа, Ном_план, Признак, Начало, Конец, Приказ, Строка, Пользов, Измен).
Эта таблица отводится
для хранения изменяемых данных о студентах, бакалаврах и магистрах. Здесь:
Номер - внешний
ключ, связанный с первичным ключом табл. Студенты;
Группа и
Ном_план - номер студенческой группы, в которую по приказу зачислен студент
с номером, указанным в столбце Номер, и номер рабочего плана (заменяющий номер
подгруппы), который определяет специализацию и (или) выпускающую кафедру студента;
это сочетание является внешним ключом, связанным с первичным ключом табл. Груп_спец);
Признак -
содержит "обучен", "академ", "повтор", "отчисл",
"армия" в зависимости от того, обучается ли студент в указанном ниже
периоде или прекратил обучение (временно или постоянно);
Начало и
Конец - даты начала и окончания действия атрибутов данной строки или период
действия (если конец действия не определен приказом, то устанавливается "запредельная"
дата 22.02.2222);
Приказ -
номер приказа о изменении какого-либо из предыдущих атрибутов, который повлек
появление в таблице этой строки (строка со старым значениями атрибутов сохраняется
а таблице);
Строка -
уникальный номер строки в данной таблице (первичный ключ), который создается
путем наращивания максимального из существующих (до ввода этой строки) номеров
строк на единицу.
Признак и Приказ
- текстовые атрибуты длиной шесть и двадцать символов; Строка - целочисленный
атрибут длиной шесть цифр; форматы остальных атрибутов описаны выше.
Пользов и
Измен - см. описание этих полей в таблице 1. Студенты.
При вводе новых
или изменении атрибутов существующих строк надо отслеживать, чтобы в столбце
Признак содержались только перечисленные выше значения, а дата конца периода
действия строки была не меньше, чем дата ее начала.
Студент должен быть
зачислен в вузе в единственную группу и либо обучаться в ней, либо находится
в длительном отпуске. Любые изменения группы или подгруппы, в которой он обучается,
или уход в отпуск должны сопровождаться вводом новой строки с новыми данными
и указанием начала и конца периода действия этой строки. Следовательно, в данной
таблице фиксируется все изменения по приписке студента к определенной специализации,
кафедре, группе и др. во время его пребывания в вузе (до его окончания или до
исключения из вуза). При этом нельзя допускать временных "дыр", т.е.
дата окончания предшествующего периода должна вплотную примыкать к дате начала
следующего (при восстановлении после отчисления должна сохраняться строка со
значением "отчисл" в столбце Признак). Естественно, что удалять из
таблицы можно только те строки, которые являются "последними" (с максимальной
датой конца) среди строк, принадлежащих конкретному студенту.
14. Контр_студ (Номер, Отдел,
Договор, Тип_дог, Начало, Конец, Величина, Ед_изм, Приказ, Пользов, Измен).
Эта таблица служит
для хранения минимальных сведений о договорах на платное обучение контрактных
студентов.
Номер - внешний
ключ, связанный с первичным ключом табл. Студенты;
Отдел - номер
подразделения, заключившего договор на платное обучение; внешний ключ, связанный
с первичным ключом Отдел табл. Отделы;
Договор и Тип_дог - номер
договора на платное обучение и код типа договора;
Начало и
Конец - даты начала и окончания действия договора (если конец действия не
определен приказом, то устанавливается "запредельная" дата 22.02.2222);
Величина и Ед_изм
- величина и единица измерения (МРОТ, у.е. или $)
оплаты по договору;
Приказ -
номер приказа о изменении какого-либо из предыдущих атрибутов, который повлек
появление в таблице этой строки (строка со старым значениями атрибутов сохраняется
а таблице).
Договор и Величина
- целочисленные атрибуты длиной шесть цифр; Ед_изм - текстовый атрибут длиной
четыре символа; форматы остальных атрибутов описаны выше.
Пользов и
Измен - см. описание этих полей в таблице 1. Студенты.
Так как каждое подразделение,
заключающее договор на платное обучение студента, вводит свою нумерацию договоров,
которая может совпадать с аналогичной нумерацией других подразделений, то в
качестве первичного ключа придется использовать составной ключ: Отдел, Договор.
При вводе новых
или изменении атрибутов существующих строк необходимо отслеживать, чтобы:
-
дата начала была не больше,
чем дата окончания договора;
-
сочетание "Номер,
Отдел, Договор" было уникальным.
15. Контр_искл (Номер, Отдел,
Договор, Начало, Конец, Величина, Ед_изм, Приказ, Строка, Пользов, Измен).
Эта таблица служит
для хранения различных исключений от договора на платное обучение контрактных
студентов (оплата не за весь учебный год, временное отсутствие оплаты и т.п.).
Номер - внешний
ключ, связанный с первичным ключом табл. Студенты;
Отдел - номер
подразделения, заключившего договор на платное обучение; внешний ключ, связанный
с первичным ключом Отдел табл. Отделы;
Договор - номер договора на платное
обучение;
Начало и
Конец - даты начала и окончания действия исключения;
Величина
и Ед_изм - величина и единица измерения (МРОТ, у.е. или $)
оплаты по договору (оплата может быть 0, например, при академическом отпуске);
Приказ -
номер приказа о изменении какого-либо из предыдущих атрибутов, который повлек
появление в таблице этой строки (строка со старым значениями атрибутов сохраняется
а таблице);
Строка -
уникальный номер строки в данной таблице (первичный ключ), который создается
путем наращивания максимального из существующих (до ввода этой строки) номеров
строк на единицу.
Форматы атрибутов
встречаются в предшествующих таблицах и уже описаны выше.
Пользов и
Измен - см. описание этих полей в таблице 1. Студенты.
Сочетание Отдел,
Договор является составным внешним ключом, связанным с первичным составным ключом
табл. Контр_студ;
При вводе новых
или изменении атрибутов существующих строк необходимо отслеживать, чтобы:
-
дата начала была не больше,
чем дата окончания исключения;
-
сочетание "Отдел,
Договор,Начало" было уникальным.
-
Контр_сум (Отдел, Договор, Уч_год,
Внесено, Дата, Строка, Пользов, Измен).
Эта таблица служит
для хранения сведений об оплате по договорам на платное обучение контрактных
студентов.
Отдел - номер
подразделения, заключившего договор на платное обучение; внешний ключ, связанный
с первичным ключом Отдел табл. Отделы;
Договор - номер договора на платное
обучение;
Уч_год -
учебный год, за который произведена оплата исключения (внешний ключ, связанный
с первичным ключом табл. Уч_года);
Внесено -
сумма оплаты в рублях;
Дата - дата оплаты;
Строка -
уникальный номер строки в данной таблице (первичный ключ), который создается
путем наращивания максимального из существующих (до ввода этой строки) номеров
строк на единицу.
Внесено - денежный
атрибут (рубли и копейки) длиной восемь цифр; форматы остальных атрибутов описаны
выше.
Пользов и
Измен - см. описание этих полей в таблице 1. Студенты.
Сочетание Отдел,
Договор является составным внешним ключом, связанным с первичным составным ключом
табл. Контр_студ;
17. Дисцип (Дисципл, Семестр,
Испол, Индекс, Кор_дисц, Лек, Лаб, Прак, СРС, Контр, Пользов, Измен).
Эта таблица отводится
для хранения сведений о содержании дисциплин рабочих планов. Ее содержимое соответствует
строкам рабочих планов (см. выше), в которую добавлен атрибут Дисципл - уникальный
номер строки рабочего плана. Здесь:
Дисципл -
уникальный номер строки в данной таблице (первичный ключ), который создается
путем наращивания максимального из существующих (до ввода этой строки) номеров
строк на единицу;
Семестр -
номер семестра, в котором изучается дисциплина;
Испол - номер
кафедры, которой поручено обучение по дисциплине (внешний ключ, связанный с
первичным ключом табл. Отделы);
Индекс -
индекс дисциплины (ГСЭ, ЕН, ...);
Кор_дисц
- внешний ключ, связанный с первичным ключом табл. Имя_дисципл;
Лек, Лаб, Прак,
СРС и Контр - числа часов, отводимых рабочим планом на лекции, лабораторные
и практические занятия, самостоятельную работу, а также вид контроля и индекс
дисциплины.
Дисципл, Семестр,
Испол, Лек, Лаб, Прак и СРС - целочисленные атрибуты длиной четыре, два и по
три цифры, соответственно; Контр - текстовый атрибут длиной три символа; форматы
остальных атрибутов описаны выше.
Пользов и
Измен - см. описание этих полей в таблице 1. Студенты.
При вводе новых
или изменении атрибутов существующих строк надо отслеживать, чтобы:
-
значения Лек,
Лаб, Прак и СРС лежали в пределах от 0 до 400;
-
значения Контр
принадлежали набору "Экз", "Зач", "ЗсО", "КП",
"КР" и "Нет" (экзамен, зачет, зачет с оценкой, курсовой
проект, курсовая работа и отсутствие официального контроля);
-
значения индекса
принадлежали набору "ГСЭ", "ЕН", "ОПД", "ДН",
"СД", "ДС" и "ДФ" (см. п. 2.2.1);
-
значения семестра
лежали в пределах от 1 до 12;
-
была обеспечена
уникальность сочетания " Семестр, Исполнит, Индекс, Кор_дисц, Лек,
Лаб, Прак, СРС, Контр".
18. План (Ном_план,
Дисципл, План, Пользов, Измен).
Эта таблица служит
для хранения содержимого рабочих планов для групп, привязанных через внешний
ключ Ном_план к соответствующей выпускающей кафедре, специализации, форме
обучения, курсу и учебному году. Здесь:
Дисципл -
внешний ключ, связанный с первичным ключом табл. Дисципл;
План - уникальный
номер строки в данной таблице (первичный ключ), который создается путем наращивания
максимального из существующих (до ввода этой строки) номеров строк на единицу.
План - целочисленный
атрибут длиной пять цифр; форматы остальных атрибутов описаны выше.
Пользов и
Измен - см. описание этих полей в таблице 1. Студенты.
При вводе новых
или изменении атрибутов существующих строк необходимо обеспечить уникальность
сочетания "Ном_план, Дисципл".
19. Ведомость
(Номер, План, Оценка, Дата, Пользов, Измен).
Эта таблица служит
для хранения оценок обучаемого по всем, изученным им дисциплинам. Здесь:
Номер и
План - первичный ключ таблицы, составленный из внешних ключей Номер и План,
связанных с первичными ключами табл. Студенты и План, соответственно; служит
для привязки оценки к соответствующей дисциплине и форме контроля;
Оценка -
одна из следующих оценок: "зачет", "незач", "отл",
"хор", "удовл", "неуд" и "неявка";
Дата - дата
получения или изменения оценки.
Оценка - текстовый
атрибут длиной шесть символов; форматы остальных атрибутов описаны выше.
Пользов и
Измен - см. описание этих полей в таблице 1. Студенты.
При вводе новых
или изменении атрибутов существующих строк надо отслеживать, чтобы:
-
значения столбца Оценка
должны принадлежать указанному выше набору оценок;
-
была обеспечена уникальность
сочетания "Номер, План",
-
удаляемая строка или строка,
в которой производится любое изменение, должна быть сохранена в табл. Вед_изм.
20. Вед_изм (Номер, План, Оценка,
Дата, Пользов, Измен, Польз_изм, Измен_изм).
Эта таблица служит
для хранения удаленных и измененных строк табл. Ведомость. Здесь все столбцы
кроме Польз_изм и Измен_изм соответствуют столбцам табл. Ведомость,
а в Польз_изм и Измен_изм заносятся: идентификатор пользователя, который удалил
или изменил эту строку, а также время ее удаления или изменения.
Все
атрибуты таблиц, кроме НЗК, Имя, Отчество, Рождение, Пользов, Измен, Приказ,
Нач_дипл, Оценка и Дата (в таблице Ведомость), не могут содержать пустых
(NULL)
значений !
2.3.3.3. Инфологическая
модель на языке "Таблицы-связи"
Эта модель приведена
на рис. 2.5. В ней названия таблиц размещены над прямоугольниками, внутри которых
приведены имена столбцов. Стрелками показываются связи между первичными (начало
стрелок) и внешними (концы стрелок "<" или ">") ключами.
При этом составные ключи отмечаются, расположенной рядом с ними, жирной линией.
Первичные ключи напечатаны жирным шрифтом с подчеркиванием а внешние ключи напечатаны
курсивом. Наконец, пунктирной стрелкой показана связь между таблицами Ведомость
и Вед_изм. Последняя не имеет внешних ключей, так как ее строки являются копиями
удаляемых или изменяемых строк таблицы Ведомость, дополненных данными о авторе
и времени таких удалений (изменений).
Связи (отношения) между сущностями
характеризуются степенью связи (например, один ко многим? - 1:М
или многие ко многим? - М:М) и классом принадлежности (символы
0,? перед 1 или М указывают на необязательность связи).
Например, студентка может не изменять
фамилии за все время обучения или изменять ее несколько раз, т.е. между таблицами
Студенты и Фамилия устанавливается необязательная связь один ко многим? или
сокращенно 1:0,М. Связь же один ко многим? между таблицами Уч_циклы и Дисцип
является обязательной (1:М).
Следует отметить, что здесь многие?
трактуются во всех случаях как один и более?. Однако в некоторых работах и CASE-системах
многие? - это более одного?, а один или многие? - это один и более?.
2.3.3.4. Нормализация
2.3.3.4.1. О функциональных
и многозначных зависимостях
Выше (п. 2.3.3)
отмечалось, что нормализация - это разбиение таблицы на несколько, обладающих
лучшими свойствами при обновлении, включении и удалении данных [4].
Теория нормализации основывается на наличии той или иной зависимости между полями
таблицы. Определены два вида таких зависимостей: функциональные и многозначные.
Функциональная
зависимость. Поле В таблицы функционально зависит от поля А
той же таблицы в том и только в том случае, когда в любой заданный момент времени
для каждого из различных значений поля А обязательно существует только
одно из различных значений поля В. Отметим, что здесь допускается, что
поля А и В могут быть составными.
Например, в таблице
Факультеты (рис. 2.5) поле Факультет функционально зависит от ключа Факульт,
а в таблице Специал поле Имя_спец функционально зависит от составного ключа
(Специал, Статус). Однако последняя зависимость не является функционально полной,
так как Имя_спец функционально зависит и от части ключа - поля Специал.
Полная функциональная
зависимость. Поле В находится в полной функциональной зависимости
от составного поля А, если оно функционально зависит от А и не
зависит функционально от любого подмножества поля А.
Многозначная
зависимость. Поле А многозначно определяет поле В той же таблицы,
если для каждого значения поля А существует хорошо определенное множество
соответствующих значений В.
2.3.3.4.2. Нормальные формы
Сегодня существуют
следующие нормальные формы: 1НФ, 2НФ, 3НФ, НФБК, 4НФ и 5НФ, образующие общую
структуру, в которой (n+1)-я
нормальная форма автоматически находится в n-й
нормальной форме, но обладает лучшими свойствами, чем n-я.
В п. 2.3.1 отмечалось,
что каждое поле таблицы реляционной базе данных может содержать только одно
значение (которое может быть и пустым). Любая таблица, удовлетворяющая этому
условию, автоматически считается таблицей в первой нормальной форме (1НФ), строго
определяемой так.
Таблица находится
в первой нормальной форме (1НФ) тогда и только тогда, когда ни
одна из ее строк не содержит в любом своем поле более одного значения и ни одно
из ее ключевых полей не пусто.
Все таблицы рис.
2.5 удовлетворяет этим требованиям, т.е. находится в 1НФ. Правда, нам очень
хотелось иметь в таблице Вып_каф поле группы, где хранился бы перечень групп,
занимающихся по соответствующему плану. Это позволило бы быстро получить список
групп, принадлежащих какой-либо кафедре или факультету, и облегчило вывод содержимого
шапки? рабочих учебных планов (рис. 2.4). Но как при таком подходе связать таблицы
Вып_каф и Ученик, как добавлять или удалять группы и т.п.?
Таблица находится
во второй нормальной форме (2НФ), если она удовлетворяет определению 1НФ и все
ее поля, не входящие в первичный ключ, связаны полной функциональной зависимостью
с первичным ключом.
Ко второй нормальной
форме приведены почти все таблицы рис. 2.5 кроме таблицы Специал, в которой
Имя_спец зависит только от поля Специал, являющегося частью первичного ключа
(Специал, Статус). Для приведения таблицы Специал к 2НФ исключим из нее поле
Статус, объявим первичным ключом поле Специал, т.е. преобразуем часть инфологической
модели так, как это показано на рис. 2.6. При этом ограничения целостности для
столбца Статус предшествующей версии таблицы Специал необходимо перенести в
описание аналогичного столбца таблицы Вып_каф.
При создании инфологической
модели (пп. 2.3.3.1 и 2.3.3.2) почти во все таблицы были введены столбцы Пользов
и Измен, позволяющие узнать: кто и когда вводил или изменял ту или иную строку
этих таблиц. Кроме того, в ряд таблиц, где существовали только составные возможные
ключи, были введены столбцы с цифровыми первичными ключами. Все это формально
затрудняет процедуру установления функциональных связей между этими ключами
и остальными полями. Действительно, как в таблице Дисципл установить зависимость
между номером дисциплины (Дисципл) и ее сокращенным именем или числом лекционных
часов и решить, приведена ли эта таблица к 2НФ?
Однако, если мысленно удалить из этой таблицы столбцы Дисципл, Пользов и Измен,
то ее единственным возможным (и, следовательно, первичным) ключом будет сочетание
всех оставшихся столбцов. Ясно, что такая урезанная? таблица Дисцип находится
во 2НФ, так как не имеет полей, не входящих в первичный ключ.
Для упрощения нормализации
подобных таблиц целесообразно использовать следующую рекомендацию.
Рекомендация.
При проведении нормализации таблиц, в которые по разным причинам включены отсутствующие
в предметной области поля (например, цифровые или другие заменители составных
первичных ключей), следует хотя бы мысленно удалить эти поля и выбрать новые
(временные) первичные ключи; после окончания нормализации удаленные поля необходимо
восстановить.
Так, мысленно удаляя
из таблиц рис. 2.5 столбцы Пользов, Измен, Строка и ряд других, мы убеждаемся,
что эти урезанные? таблицы находятся не только в нормальной форме 2НФ, но и
в, описанных ниже, формах 3НФ и НФБК.
Таблица находится
в третьей нормальной форме (3НФ), если она удовлетворяет определению 2НФ и не
одно из ее неключевых полей не зависит функционально от любого другого неключевого
поля.
Более строгое определение,
получившее название НФБК, имеет вид.
Таблица находится
в нормальной форме Бойса-Кодда (НФБК), если и только если любая функциональная
зависимость между её полями сводится к полной функциональной зависимости от
возможного ключа.
В следующих нормальных
формах (4НФ и 5НФ) учитываются не только функциональные, но и многозначные зависимости
между полями таблицы. Для их описания познакомимся с понятием полной декомпозиции
таблицы.
Полной декомпозицией
таблицы называют такую совокупность произвольного числа ее проекций, соединение
которых полностью совпадает с содержимым таблицы.
Теперь можно дать
определения высших нормальных форм. И сначала будет дано определение для последней
из предложенных - 5НФ.
Таблица находится
в пятой нормальной форме (5НФ) тогда и только тогда, когда в каждой ее полной
декомпозиции все проекции содержат возможный ключ. Таблица, не имеющая ни одной
полной декомпозиции, также находится в 5НФ.
Четвертая нормальная
форма (4НФ) является частным случаем 5НФ, когда полная декомпозиция должна быть
соединением ровно двух проекций. Весьма не просто подобрать реальную таблицу,
которая находилась бы в 4НФ, но не была бы в 5НФ.
2.3.3.4.3. Процедура нормализации
Как уже говорилось,
нормализация - это разбиение таблицы на несколько, обладающих лучшими свойствами
при обновлении, включении и удалении данных. Теперь можно дать и другое определение:
нормализация - это процесс последовательной замены таблицы ее полными декомпозициями
до тех пор, пока все они не будут находиться в 5НФ. На практике же достаточно
привести таблицы к НФБК и с большой гарантией считать, что они находятся в 5НФ.
Разумеется, этот факт нуждается в проверке, однако пока не существует эффективного
алгоритма такой проверки. Поэтому остановимся лишь на процедуре приведения таблиц
к НФБК.
Шаг 1. Все
данные, которые должны содержаться в проектируемой базе данных, надо поместить
в одну или несколько таблиц, находящихся в первой нормальной форме.
Например, пусть
требуется создать базу данных для хранения рабочих учебных планов, часть одного
из которых приведена на рис. 2.7 и для этого создается единственная таблица:
Раб_план (Факультет,
Имя_кафедры, Специал, Имя_спец, Статус, Курс, Форма, Вариант, Уч_год, Группы,
Семестр, Исполнитель, Имя_дисц, Лек, Лаб, Прак, СРС, Конт, Инд),
внешний вид которой приведен
на рис. 2.8.
Эта таблица не является
отношением, так как содержит множественные поля со сведениями о содержимом дисциплин
(Семестр, Исполн, Имя_дисц, ...). Дополняя подстроки множественных полей недостающими
данными так, как это показано на рис. 2.9, можно создать универсальное отношение,
т.е. привести ее к первой нормальной форме.
Даже в усеченном
виде такая таблица очень громоздка несмотря на то, что почти все ее столбцы
усечены. Полный же текст, например, её третьей строки выглядит так:
Оптико-электронные
приборы и системы?, Оптико-электронных приборов и систем?, 190701?, Оптико-электронные
информационно-измерительные и следящие приборы и системы?, Студент?, 5?, Дневная?,
Основной?, 1999/2000?, 530?, 9?, Электротехники и прецизионных электромеханических
систем?, Электронно-электромеханическая база оптических приборов?, 36?, 36?,
0?, 90?, Экзамен?, СД?
и в такой таблице должно сохраняться
более сорока тысяч таких строк.
Кроме того, наличие
в ней огромного числа дубликатов несомненно приведет к аномалиям обновления,
включения и удаления (см. п. 2.3.2).
Поэтому целесообразно
уже на этом этапе разбить отношение Раб_план хотя бы на два:
Шапка_плана
(Факультет, Имя_кафедры, Специал, Имя_спец, Статус, Курс, Форма, Вариант, Уч_год,
Группы, Ном_план)
и
Содерж_плана
(Ном_план, Семестр, Исполнитель, Имя_дисц, Лек, Лаб, Прак, СРС, Конт, Инд),
связанных между собой значениями в столбцах
Ном_план.
Шаг 2. Определение возможных
ключей таблиц и выбор (назначение) первичных.
В рассматриваемом
примере первая таблица имеет два возможных ключа:
Факультет, Имя_кафедры,
Специал, Статус, Курс, Форма, Вариант, Уч_год
и
Ном_план.
Естественно, что
в качестве первичного ключа лучше использовать, специально введенное цифровое
автонумеруемое поле Ном_план.
Во второй таблице
единственный возможный (он же первичный) ключ:
Ном_план, Семестр,
Исполнитель, Имя_дисц, Лек, Лаб, Прак, СРС, Конт, Инд.
составленный из всех её полей
Шаг 3.
Выявление полей, функционально зависящих от части составного возможного ключа,
и формирование новых таблиц.
В таблице Содерж_плана
таких полей нет. В таблице же Шапка_плана
есть поле Имя_спец, зависящее только от поля Специал, являющегося частью возможного
ключа.
Полученная функциональная
зависимость определяет состав выделенной из Шапка_плана новой таблицы Специал:
Специал (Специал,
Имя_спец)
и вид скорректированной таблицы:
Шапка_плана
(Факультет, Имя_кафедры, Специал, Статус, Курс, Форма, Вариант, Уч_год, Группы,
Ном_план)
Шаг 4. Повторение
шагов 2 и 3 до тех пор, пока все таблицы не будут находиться в НФБК.
В нашем примере
все таблицы уже находятся в НФБК.
Шаг 5. Создание
справочных таблиц.
Если в таблице базы
данных существует большое текстовое поле с часто повторяющимися данными, то
целесообразно создать на его основе новую таблицу, состоящую из неповторяющихся
значений этого поля и связанных с ними ключей. В качестве ключа можно использовать
цифровое или короткое текстовое поле, значения которого будет заменять значения
большого текстового поля в исходной таблице.
В нашем примере
такой таблицей стала (правда по другим причинам) таблица Специал. Кроме нее
могут быть созданы таблицы:
Факультеты (Факульт,
Факультет),
Имя_дисц (Кор_дисц,
Имя_дисц),
Отделы (Отдел,
Имя_отд),
где аббревиатура имени факультета
(Факульт), сокращенное название дисциплины (Кор_дисц) и номер отдела (Отдел)
могут заменить в исходных таблицах поля Факультет, Имя_дисц, Имя_кафедры и Исполнитель.
2.4. Даталогическая
модель базы данных "Учебный процесс"
2.4.1.
О базе данных Oracle, ее пользователях и схемах
Под словосочетанием
"база данных Oracle" подразумевают физическую и логическую структуру,
используемую для хранения информации. В одной базе данных Oracle можно хранить
множество разных наборов информации - множество схем. Схема - это набор объектов
базы данных (таблиц, представлений, моментальных копий или снимков, индексов,
последовательностей, синонимов, процедур, функций, пакетов и триггеров). Понятие
схемы - это небольшая поправка к СУБД Oracle, введенная с версии 7. Ранее все
объекты принадлежали создавшему их пользователю (CREATору?).
По умолчанию схема аналогична подключенному в настоящее время пользователю.
Администратор базы
данных (АБД) Oracle создает новую пустую схему, задавая ее имя и пароль. Нередко
таким именем является имя пользователя. Так АБД сервера Oracle кафедры ВТ создал
такие схемы для многих студентов ФКТиУ, дав каждой из них имя, составленное
из символа s?
и номера студента. Например, студентам
Ветчинкину Сергею Борисовичу (Номер = 15174) и Кандиболоцкому Петру Андреевичу
(Номер = 15863) предоставлены для работы в Oracle схемы с именами s15174
и s15863.
Пароли для получения доступа к этим схемам отличаются от имени первым символом
(p?
вместо s?).
Кроме того, студентам даны права на создание объектов в своих схемах и права
на чтение таблиц схемы c именем ucheb?,
в которой храниться иллюстрационная база данных, используемая в лабораторном
практикуме.
2.4.2.
Как подключиться к Oracle
После регистрации
в операционной системе Solaris (версии UNIX Sun Microsystems), под управлением
которой работают рабочие станции и сервер Oracle кафедры ВТ, студент получает
возможность вызова интерактивного, программного интерфейса с Oracle - SQL*Plus.
Для этого вводится команда:
sqlplus.
После ввода имени пользователя
(схемы) и пароля будет выведено приглашение для ввода командной строки Oracle:
SQL>_
Эта строка может
содержать любую команду языков SQL, SQL*Plus или PL/SQL, о которых будет рассказано
ниже.
2.4.3.
Условные обозначения, используемые в синтаксисе команд
[ ] - в
эти скобки заключаются необязательные синтаксические единицы
{ } - конструкция,
заключенная в эти скобки, должна рассматриваться как одна синтаксическая
единица
| - используется
для разделения альтернативных синтаксических единиц
... - указывает
на то, что непосредственно предшествующая синтаксическая единица может повторяться
один или несколько раз
text -
'[символ | '']...'
integer
- цифра[цифра]...
number
- [+|-]{цифра[цифра]...[.][цифра]... |.цифра[цифра]...}[{e|E}[+|-]цифра[цифра]...]
expr -
любое выражение
Expr_list
- (expr [, expr] ...)
2.4.4.
О синтаксисе команд для создания и изменения описания таблиц
2.4.4.1. Создание описания
таблицы (CREATE TABLE)
Для создания описания
таблиц в языке SQL существует оператор CREATE TABLE, сокращенный синтаксис которого
имеет вид:
CREATE TABLE [schema.]table
( { column [datatype] [DEFAULT expr] [column_constraint]
... }
[, { column [datatype] [DEFAULT expr]
[column_constraint] ... } ]
[ {, table_constraint } ... ] )
[AS subquery] ;
где
schema -
необязательный параметр, идентифицирующий схему, в которую надо поместить создаваемую
таблицу (по умолчанию - схема пользователя);
table - имя
создаваемой таблицы: до 30 символов (буквы, цифры и символы "$", "#"
или "_"), начинающееся с буквы или "_"; возможно также использование
в имени любых символов (кроме двойных кавычек) или составлять имя из нескольких
слов, если такое имя будет заключено в кавычки (например, "Новая таблица");
column -
уникальное для данной таблицы имя столбца, формируемое по тем же правилам, что
и имя таблицы;
datatype
- тип данных значений столбца (NUMBER, DATE, VARCHAR2, CHAR, LONG, RAW, LONG
RAW), который можно не указывать, если столбец является внешним ключом или частью
такого ключа (тип данных будет определен по соответствующему столбцу в первичном
ключе);
DEFAULT expr
- необязательный параметр, используемый для задания с помощью выражения expr
значение столбца по умолчанию, если оператор INSERT "забудет" при
вставке строки занести значение в данный столбец; выражение (expr) не может
содержать ссылок на другие столбцы, псевдостолбцов (CURRVAL, NEXTVAL, LEVEL
или ROWNUM), дат и не полностью определенных констант; с синтаксисом написания
выражений (expr) можно познакомиться ниже (п. 2.4.5.2);
column_constraint
- необязательный параметр, с помощью которого задается одно или несколько (указано
с помощью "...") ограничений целостности на значения столбца (синтаксис
column_constraint приведен ниже);
table_constraint
- необязательный параметр, с помощью которого задаются, разделенные запятыми,
ограничения целостности таблицы (синтаксис table_constraint приведен ниже);
AS subquery
- необязательный параметр, с помощью которого можно вставить в создаваемую таблицу
строки данных в соответствии с подзапросом (subquery); может также использоваться
для копирования как описания (без ограничений целостности), так и содержимого
таблицы;
точка с запятой (;) - завершающий
элемент любого оператор языка SQL.
Пример. Создать описание таблицы
Вып_каф (см. п. 2.3.3.2):
CREATE TABLE Вып_каф
(Кафедра NUMBER(3),
Факульт CHAR(5),
Специал VARCHAR(6),
Статус CHAR(9),
Курс NUMBER(1),
Вариант CHAR(3),
Начало DATE,
Конец DATE,
Форма VARCHAR(8),
Нач_дипл DATE,
Ном_план NUMBER(4),
Пользов NUMBER(4),
Измен DATE);
2.4.4.2. Создание ограничений
целостности
Как уже отмечалось
выше, ограничения целостности задаются в операторе CREATE TABLE с помощью параметров
column_constraint и table_constraint. Они описывают правила, применимые
к таблицам во время и (или) после их создания, которые оговаривают допустимые
значения столбцов и (или) допустимые сочетания этих значений.
Синтаксис параметра column_constraint
имеет вид:
[CONSTRAINT constraint]
{ [NOT] NULL | {UNIQUE | PRIMARY KEY}
| REFERENCES [schema.]table [(column)]
[ON DELETE CASCADE]
| CHECK (condition) }
где
CONSTRAINT constraint
- необязательный параметр, используемый для задания имени (на месте constraint)
ограничения (имя формируется по тем же правилам, что и имена таблиц); если имя
не задано, то Oracle присваивает ограничению свое имя, вывод которого на экран
при ошибке (например, отсутствии уникальности вводимого значения) резко усложняет
процесс идентификации места ошибки;
PRIMARY KEY
- первичный ключ (не может быть назначен для столбцов с типом данных LONG, RAW,
LONG RAW); в столбце, объявленном первичным ключом, значения должны быть уникальными
и не пустыми (NOT NULL); по значениям такого столбца автоматически строится
индекс;
UNIQUE -
отличается от PRIMARY KEY тем, что в связанном с ним столбце допускаются пустые
(NULL) значения; фигурные скобки, в которые заключены UNIQUE и PRIMARY KEY,
оговаривают, что столбец может иметь лишь одно из этих ограничений;
[NOT] NULL
- указывает, что в этот столбец надо поместить пустое (NULL) или некоторое непустое
(NOT NULL) значение;
REFERENCES
- неявное объявление внешнего ключа, т.е. ссылка на первичный или уникальный
ключ, ранее объявленный в таблице [schema.]table для столбца column (когда имя
столбца опущено, автоматически выбирается первичный ключ); если опция ON DELETE
CASCADE опущена, то строки с ключевыми значениями в таблице с первичным или
уникальным ключом не могут быть удалены, пока не будут удалены все ссылающиеся
на них строки из данной таблицы (таблицы с внешним ключом);
REFERENCES допускает ввод пустых
значений (для их исключения надо одновременно вводить ограничение NOT NULL);
CHECK - используется
для контроля данных по условию, текст которого размещается в скобках; с синтаксисом
написания условий (condition) можно познакомиться ниже (п. 2.4.5.3); CHECK не
может содержать: запросов на обращение к другим таблицам или другим строкам
данной таблицы; обращений к системным переменным и константам (например, SYSDATE
- текущая дата). CHECK допускает ввод пустых (NULL) значений.
В тех случаях, когда
ограничение относится не к одному столбцу, а к их комбинации, используются ограничения
на уровне всей таблицы. Их синтаксис имеет вид:
[CONSTRAINT constraint]
{ {UNIQUE | PRIMARY KEY} (column [,column]
...)
| FOREIGN KEY (column [,column] ...)
REFERENCES [schema.]table [(column [,column]
...)]
[ON DELETE CASCADE]
| CHECK (condition) }
где
CONSTRAINT constraint,
UNIQUE и PRIMARY KEY имеют тот же смысл, что и в ограничениях для одного
столбца, однако здесь для UNIQUE или PRIMARY KEY должен задаваться в скобках
перечень столбцов, входящих в комбинацию;
FOREIGN KEY
- внешний ключ, состоящий из одного или нескольких столбцов (column [,column]
...), ссылающийся (REFERENCES) на первичный или уникальный ключ, ранее объявленный
в таблице [schema.]table для столбцов (column [,column] ...); отсутствие последнего
перечня и (или) опции ON DELETE CASCADE приводят к тем же результатам, что и
для описанных выше столбцовых ограничениях; для удовлетворения этого ограничения
значения всех столбцов внешнего ключа должны совпадать со значениями соответствующих
столбцов первичного (уникального) ключа или иметь пустое (NULL)
значение хотя бы в одном из столбцов;
CHECK - используется
для контроля данных по условию, текст которого размещается в скобках.
Пример. Создать описание таблицы
Вып_каф с ограничениями, описанными в п. 2.3.3.2:
CREATE TABLE Вып_каф
(Кафедра NUMBER(3) NOT NULL CONSTRAINT
"Такой кафедры нет !"
REFERENCES Отделы(Отдел),
Факульт CHAR(5) NOT NULL CONSTRAINT "Код
факультета в Вып_каф?"
REFERENCES Факультеты (Факульт),
Специал VARCHAR(6) NOT NULL,
Статус CHAR(9) NOT NULL,
Курс NUMBER(1) NOT NULL CONSTRAINT "Курс
должен быть 1-6 !"
CHECK (Курс IN (1,2,3,4,5,6)),
Вариант CHAR(3) NOT NULL CONSTRAINT "Должен
быть Осн,Ин1,... !"
CHECK (Вариант IN ('Осн','Ин1','Ин2','Ин3','Ин4',
'Ин5','Ин6','Ин7','Ин8','Ин9')),
Начало DATE NOT NULL,
Конец DATE NOT NULL,
Форма VARCHAR(8) NOT NULL CONSTRAINT "Вечерняя,
дневная, заочная !"
CHECK (Форма IN ('вечерняя', 'дневная',
'заочная')),
Нач_дипл DATE,
Ном_план NUMBER(4) CONSTRAINT "Номер
плана введен неверно!" PRIMARY KEY,
Пользов NUMBER(4),
Измен DATE,
CONSTRAINT "Не между Начало и Конец
!"
CHECK (Нач_дипл BETWEEN Начало AND Конец),
CONSTRAINT "Начало Вып_каф > конца
!" CHECK (Начало <= Конец),
CONSTRAINT "Этих Специал-Статус нет!"
FOREIGN KEY (Специал, Статус)
REFERENCES Специал(Специал, Статус),
CONSTRAINT "Уникальность строк Вып_каф
?"
UNIQUE (Кафедра, Специал, Статус, Вариант,
Начало, Конец, Курс, Форма));
Следует отметить,
что в данном примере используются имена ограничений, написанные на русском языке
(по правилам создания имен они заключены в кавычки и ограничены 30 символами).
Это сделано для облегчения идентификации ошибок, сообщения о которых (вместе
с таким именем) выводятся на экран при некорректном вводе или изменении данных
таблицы Вып_каф.
2.4.4.3. Оператор COMMENT
и способы ввода пояснений в текст оператора
Для сохранения в
базе данных комментария к таблице или любому ее столбцу используется оператор
COMMENT, синтаксис которого имеет вид:
COMMENT ON { TABLE [schema.]{table | view
| snapshot}
| COLUMN [schema.]{table | view | snapshot}.column
} IS 'text'
где
view и snapshot - имена
представления или моментальной копии (снимка);
text - набор любых символов
(кроме апострофов), заключенный в апострофы (')
Для включения пояснений
в любое предложение SQL и командные блоки PL/SQL можно либо расположить текст
пояснения между парами символов /* и */, либо предварить его двумя
дефисами (--).
С помощью первого
способа можно включать многострочные пояснения. Для включения многострочных
пояснений вторым способом приходится помещать "--" перед каждой их
строкой. Следует заметить, что комментарии, отмеченные "--" могут
располагаться либо в отдельных строках, либо в конце строк текста комментируемого
предложения.
Пример. Создать комментарии к
таблице Вып_каф и ее столбцам:
COMMENT ON TABLE Вып_каф
IS 'Специализации выпускающих кафедр и
их связь с группами (Груп_спец)';
COMMENT ON COLUMN Вып_каф.Кафедра IS 'Код
выпускающей кафедры';
COMMENT ON COLUMN Вып_каф.Факульт
IS 'Аббревиатура факультета, обеспечивающего
обучение по данному плану';
COMMENT ON COLUMN Вып_каф.Специал IS 'Номер
специальности (специализации)';
COMMENT ON COLUMN Вып_каф.Статус
IS 'Статус обучаемого (студент, бакалавр,
магистр, аспирант, докторант)';
COMMENT ON COLUMN Вып_каф.Курс IS 'Номер
курса';
COMMENT ON COLUMN Вып_каф.Начало IS 'Начало
действия атрибутов данной строки';
COMMENT ON COLUMN Вып_каф.Конец IS 'Конец
действия атрибутов данной строки';
COMMENT ON COLUMN Вып_каф.Форма
IS 'Форма обучения (дневная, вечерняя,
заочная)';
COMMENT ON COLUMN Вып_каф.Нач_дипл
IS 'Дата начала дипломного проектирования
(для дипломников)';
COMMENT ON COLUMN Вып_каф.Ном_план
IS 'Номер учебного рабочего плана (автонумеруемый)';
COMMENT ON COLUMN Вып_каф.Пользов
IS 'Код пользователя, создавшего (изменившего)
данную строку';
COMMENT ON COLUMN Вып_каф.Измен
IS 'Дата и время создания (изменения)
строки';
2.4.4.4. Изменение описания
(ALTER TABLE)
Для изменения описания
пустой или даже заполненной таблицы используется оператор языка SQL ALTER TABLE.
Он позволяет добавлять столбцы и изменить тип данных или ограничений целостности,
а также включать и выключать ограничения. Синтаксис оператора имеет вид:
ALTER TABLE [schema.]table
[ADD { { column [datatype] [DEFAULT expr]
[column_constraint] ...
| table_constraint }
| ( { column [datatype] [DEFAULT expr]
[column_constraint] ...
| table_constraint }
[, { column [datatype] [DEFAULT expr]
[column_constraint] ...
| table_constraint } ... ) } ]
[MODIFY { column [datatype] [DEFAULT expr]
[column_constraint]
| (column [datatype] [DEFAULT expr] [column_constraint]
[, column [datatype] [DEFAULT expr] [column_constraint]
] ...) } ]
[DROP { PRIMARY KEY | UNIQUE (column [,column]
...) |CONSTRAINI constraint }
[CASCADE] ] ...
[ENABLE enable_clause | DISABLE disable_clause
] ... ;
Здесь основные параметры
имеют тот же смысл, что и для оператора CREATE TABLE.
Добавляемые (указанные
вслед за ADD) новые столбцы помещаются лишь в конец таблицы и не содержат данных.
Если добавляемый столбец имеет ограничение NOT NULL, то его можно вставить только
в пустую таблицу.
С помощью синтаксических
конструкций table_constraint в описание существующих столбцов можно добавлять
любые новые ограничения кроме NOT NULL, которое добавляется опцией MODIFY.
При изменении описания
столбца (опция MODIFY) все параметры, которые не включены в это описание, сохраняют
старые значения. MODIFY используется для изменения типа данных и (или) размера
значений столбца, значения по умолчанию и ограничения NOT NULL (другие ограничения
можно только добавлять).
Допускаются изменения
типа данных CHAR на VARCHAR2 и наоборот, но только если столбец не содержит
данных или не уменьшается их размерность. Можно также увеличивать размер и (или)
точность столбцов с числовыми значениями.
Изменение значения
по умолчанию будет актуально только для вновь вставляемых строк (ранее существующие
пустые значения столбца не будут изменены).
Следует отметить,
что изменения, выполненные с помощью ALTER TABLE, могут привести к потере актуальности
связанных с таблицей представлений, процедур и функций. Поэтому после ALTER
TABLE следует "освежить" указанные объекты, например, пересоздав их,
используя опцию OR REPLACE в соответствующих операторах CREATE ... .
Фраза DROP
позволяет удалить указанные в ней
ограничения и, если задана опция CASCADE,
то одновременно удалить все связанные с ними внешние ключи.
С помощью DISABLE
disable_clause и ENABLE enable_clause можно отключать и вновь включать одиночные
ограничения целостности или все триггеры, связанные с таблицей, указанной в
ALTER TABLE. Их синтаксис имеет вид:
DISABLE { { UNIQUE (column [, column]
...) | PRIMARY KEY
| CONSTRAINT constraint } [CASCADE]
| ALL TRIGGERS }
ENABLE { { UNIQUE (column [, column] ...)
| PRIMARY KEY
| CONSTRAINT constraint }
| ALL TRIGGERS }
Для примера приведем
описание таблицы Вып_каф с помощью пары операторов: CREATE TABLE (для ввода
описания столбцов и ограничений типа NOT NULL) и ALTER TABLE (для добавления
к ним описаний других ограничений целостности):
CREATE TABLE Вып_каф
(Кафедра NUMBER(3) NOT NULL,
Факульт CHAR(5) NOT NULL,
Специал VARCHAR(6) NOT NULL,
Статус CHAR(9) NOT NULL,
Курс NUMBER(1) NOT NULL,
Вариант CHAR(3) NOT NULL,
Начало DATE NOT NULL,
Конец DATE NOT NULL,
Форма VARCHAR(8) NOT NULL,
Нач_дипл DATE,
Ном_план NUMBER(4),
Пользов NUMBER(4),
Измен DATE );
ALTER TABLE Вып_каф
ADD ( CONSTRAINT "Такой кафедры нет
! " FOREIGN KEY (Кафедра)
REFERENCES Отделы(Отдел),
CONSTRAINT "Код факультета в Вып_каф?
" FOREIGN KEY (Факульт)
REFERENCES Факультеты (Факульт),
CONSTRAINT "Этих Специал-Статус нет!
" FOREIGN KEY (Специал,Статус)
REFERENCES Специал(Специал, Статус),
CONSTRAINT "Курс должен быть 1-6
!" CHECK (Курс IN (1,2,3,4,5,6)),
CONSTRAINT "Должен быть Осн,Ин1,Ин2,...
!" CHECK (Вариант IN
('Осн','Ин1','Ин2','Ин3','Ин4','Ин5','Ин6','Ин7','Ин8','Ин9')),
CONSTRAINT "Начало Вып_каф > конца
! " CHECK (Начало <= Конец),
CONSTRAINT "Вечерняя, дневная,заочная
!"
CHECK (Форма IN ('вечерняя', 'дневная',
'заочная')),
CONSTRAINT "Не между началом и концом
!"
CHECK (Нач_дипл BETWEEN Начало AND Конец),
CONSTRAINT "Номер плана введен неверно
!" PRIMARY KEY (Ном_план),
CONSTRAINT "Уникальность строк Вып_каф
? "
UNIQUE (Кафедра,Специал,Статус,Вариант,Начало,Конец,Курс,Форма)
);
Добавление ограничений
можно осуществить и несколькими операторами ALTER TABLE (например, по одному
на каждое ограничение):
ALTER TABLE Вып_каф ADD CONSTRAINT "Такой
кафедры нет ! "
FOREIGN KEY (Кафедра) REFERENCES Отделы(Отдел);
ALTER TABLE Вып_каф ADD CONSTRAINT "Код
факультета в Вып_каф? "
FOREIGN KEY (Факульт) REFERENCES Факультеты
(Факульт);
...
2.4.4.5. Удаление таблицы
(DROP TABLE)
Для удаления описания
таблицы и содержащихся в ней данных используется оператор DROP TABLE, синтаксис
которого имеет вид:
DROP TABLE [schema.]table [CASCADE CONSTRAINTS]
;
где
[schema.]table
- полное имя удаляемой таблицы; если таблица расположена в схеме пользователя,
то имя схемы можно опустить;
CASCADE CONSTRAINTS
- используется для удаления всех внешних ключей, которые ссылаются на удаляемую
таблицу (иначе таблица не сможет быть удалена без предварительного удаления
внешних ключей какими-либо другими средствами).
2.4.5.
Синтаксис выражений и условий в командах SQL и PL/SQL
2.4.5.1. Иерархия операторов
Оператор Описание
----------- ------------------------------------------------
() Подавляет обычные правила старшинства
операций.
** NOT Возведение в степень и логическое
отрицание.
+ - Знак, предшествующий числовому
выражению.
* / Умножение и деление.
+ - Сложение и вычитание.
|| Сочленение текстовых выражений
и (или) констант.
:= Присвоение значения переменной
пользователя.
=, <>, <,
>, <=, >=, Операторы сравнения,
IS NULL, используемые при
LIKE, IN, построении условий
BETWEEN
AND Логическое "И"
OR Логическое "ИЛИ"
2.4.5.2. Синтаксис выражений
(expr)
1-я форма: { [[schema.]{table |
view | snapshot }.]
{column | pseudo-column | ROWLABEL}
| 'text' | number | sequence.{CURRVAL
| NEXTVAL} | NULL }
2-я форма: function_name [( [DISTINCT
| ALL] expr [, expr] ... )]
3-я форма: { (expr) | +expr | -expr
| PRIOR expr | expr * expr | expr / expr
| expr + expr | expr - expr | expr ||
expr }
Decode_expr: DECODE( expr, search,
result [, search, result] ... [, default] )
Если значение expr = search, то возвращает
значение result, иначе
значение default или NULL (при отсутствии
default).
2.4.5.3. Синтаксис условий
(condition)
1-я форма: { expr {= | <>
| > | < | >= | <=}
{expr | (subquery)} | expr_list {= | <>}
(subquery) }
2-я форма: { expr {= | <>
| > | < | >= | <=}
{ANY | SOME | ALL} {expr_list | (subquery)}
| expr_list {= | <>}
{ANY | SOME | ALL} ( { expr_list [, expr_list]
... | subquery} ) }
3-я форма: { expr [NOT] IN {expr_list
| (subquery)}
| expr_list [NOT] IN ( { expr_list [,
expr_list]... | subquery} ) }
4-я форма: expr [NOT] BETWEEN expr
AND expr
5-я форма: expr IS [NOT] NULL
6-я форма: EXISTS (subquery)
7-я форма: char1 [NOT] LIKE char2
[ESCAPE 'c']
8-я форма: { ( условие ) | NOT условие
| условие AND условие | условие OR условие
}
2.4.6.
О системных таблицах (словаре данных) Oracle
Почти вся информация
о структуре базы данных и ее объектах размещена в системных таблицах Oracle,
принадлежащих пользователь с именем SYS. Oracle обращается к этим таблицам при
любых запросах для получения необходимой для их выполнения информации.
Любой пользователь
имеет возможность получить из этих таблиц (точнее созданных по этим таблицам
представлений) некоторую информацию о принадлежащих ему объектах. Для этого
он должен знать имя и структуру того представления, в котором хранятся нужные
ему сведения. Имена и краткое описание представлений приведены в документе "Краткий
справочник по Oracle 7", а их структуру можно получить с помощью команды
SQL*Plus - DESCribe (допускается сокращение имени команды до четырех символов).
Она позволяет получить краткое описание любой пользовательской таблицы, например
SQL> desc Вып_каф
Имя Отсут.? Ввод
----------- --------- ---------------
КАФЕДРА NOT NULL NUMBER(3)
ФАКУЛЬТ NOT NULL CHAR(5)
СПЕЦИАЛ NOT NULL VARCHAR2(6)
СТАТУС NOT NULL CHAR(9)
КУРС NOT NULL NUMBER(1)
ВАРИАНТ NOT NULL CHAR(3)
НАЧАЛО NOT NULL DATE
КОНЕЦ NOT NULL DATE
ФОРМА NOT NULL VARCHAR2(8)
НАЧ_ДИПЛ DATE
НОМ_ПЛАН NOT NULL NUMBER(4)
ПОЛЬЗОВ NUMBER(4)
ИЗМЕН DATE
Информация из системного
представления выводится, как и из любой таблицы, с помощью оператора SELECT
(см. [5]). Например, по запросу
SELECT CONSTRAINT_TYPE,STATUS,CONSTRAINT_NAME,SEARCH_CONDITION
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'ВЫП_КАФ';
будет выведена информация, часть из которой
приведена ниже
C STATUS CONSTRAINT_NAME SEARCH_CONDITION
- -------- ------------------------------
-------------------------------------
C ENABLED SYS_C008549 КАФЕДРА IS NOT NULL
C ENABLED SYS_C008550 ФАКУЛЬТ IS NOT NULL
C ENABLED SYS_C008551 СПЕЦИАЛ IS NOT NULL
C ENABLED SYS_C008552 СТАТУС IS NOT NULL
C ENABLED SYS_C008553 КУРС IS NOT NULL
C ENABLED SYS_C008554 ВАРИАНТ IS NOT NULL
C ENABLED SYS_C008555 НАЧАЛО IS NOT NULL
C ENABLED SYS_C008556 КОНЕЦ IS NOT NULL
C ENABLED SYS_C008557 ФОРМА IS NOT NULL
C ENABLED Должен быть 1-6 ! Курс IN (1,2,3,4,5,6)
C ENABLED Должен быть Осн,Ин1,Ин2,... !
Вариант IN ('Осн','Ин1','Ин2','Ин3',
'Ин4','Ин5','Ин6','Ин7','Ин8','
C ENABLED Начало Вып_каф > конца ! Начало
<= Конец
C ENABLED Вечерняя, дневная,заочная ! Форма
IN ('вечерняя', 'дневная',
'заочная')
C ENABLED Не между началом и концом ! Нач_дипл
BETWEEN Начало AND Конец
P ENABLED Номер плана введен неверно !
U ENABLED Уникальность строк Вып_каф ?
R ENABLED Такой кафедры нет !
R ENABLED Код факультета в Вып_каф?
R ENABLED Этих Специал-Статус нет!
21 строк выбрано.
Здесь CONSTRAINT_TYPE
(C) тип ограничения (P - первичный ключ, U - уникальность и C - проверка). Так
как мы поленились? дать имена ограничениям NOT NULL, то Oracle присвоил им собственные
имена, начинающиеся с сочетания SYS_C
(SYS_C008549,
SYS_C008550,
...).
2.4.7.
О других средствах создания ограничений целостности
С помощью команд
CREATE TABLE и ALTER TABLE
нельзя реализовать любые ограничения целостности. Например, мы не сможем обеспечить
с их помощью такие ограничения таблицы Студенты, как: дата рождения должна быть
в пределах от (Текущая дата - 40 лет) до (Текущая дата - 15 лет)? и первые буквы
фамилии, имени и отчества должны быть большими, а остальные - малыми?. Действительно,
в CHECK нельзя включать обращения к текущей дате (SYSDATE) и сравнительно сложным
процедурам для проверки правильности ввода, например, таких фамилий как Смирнов-Сокольский,
Жан-Жак Руссо, Гай Юлий Цезарь и т.п.
Указанные выше и
многие другие ограничения целостности можно вводить с помощью триггеров. Триггер
- это сочетание хранимой в базе данных программы и события, которое заставляет
ее выполняться. Такими событиями могут быть: ввод новой строки таблицы, изменение
значений одного или нескольких ее столбцов и (или) удаление строки таблицы.
При любом из этих событий автоматически запускаются один или несколько заранее
созданных триггеров, которые производят проверку запрограммированных в них условий
и если они не выполняются, отменяют ввод, изменение или удаление, посылая об
этом заранее подготовленное сообщение пользователю.
Только с помощью
триггеров мы сможем обеспечить автоматический? ввод в таблицы таких атрибутов,
как Пользов и Измен, автоматическое? наращивание значений Номер и Строка, а
также заполнение таблицы Вед_изм по данным удаляемых и изменяемых строк таблицы
Ведомость.
Как уже отмечалось
выше, для написания текста триггера надо овладеть языком PL/SQL и рядом команд
языка SQL. Рассмотрим эти команды.
3. Команды
манипулирования данными
3.1. Выборка
данных
3.1.1.
Команда SELECT
Для выборки данных
из таблиц, представлений и моментальных копий (снимков) используется команда
SELECT, основные конструкции которой подробно описаны в [5] и изучаются в лабораторном
практикуме. Здесь мы приведем и кратко обсудим полный синтаксис команды SELECT
СУБД Oracle:
SELECT [DISTINCT | !!under!!ALL] { *
| { [schema.]{table | view | snapshot}.*
| expr [ [AS] c_alias ] }
[, { [schema.]{table | view | snapshot}.*
| expr [ [AS] c_alias ] } ] ... }
FROM { (subquery)
| [schema.]{table | view | snapshot}[@dblink]
} [t_alias]
[, { (subquery)
| [schema.]{table | view | snapshot}[@dblink]
} [t_alias] ] ...
[WHERE condition ]
[ [START WITH condition] CONNECT BY condition]
[GROUP BY expr [, expr] ...] [HAVING condition]
[{UNION | UNION ALL | INTERSECT | MINUS}
SELECT command ]
[ORDER BY {expr | c_alias | position}
[!!under!!ASC | DESC]
[, {expr | c_alias | position} [!!under!!ASC
| DESC]] ...]
[FOR UPDATE [OF [ [schema.]{table | view}.]column
[, [ [schema.]{table | view}.]column]
...]
[NOWAIT] ] ;
где
DISTINCT - используется
для исключения дубликатов выводимых строк;
ALL - используется
для вывода всех строк (устанавливается по умолчанию, на что указывает сочетание
!!under!!);
* - используется
для выбора строк, в которых присутствуют все столбцы из всех таблиц, представлений
и снимков, указанных в фразе FROM (об объектах вида представление - view и снимок
- snapshot будет рассказано ниже);
table.*, view.*
или snapshot.* - используются для выбора всех столбцов из указанной таблицы,
представления или снимка; для выбора данных из таблицы (представления или снимка),
расположенных в другой схеме, используется спецификатор schema.;
expr - используется
для описания выражения, составленного из столбцов (входящих в объекты, указанные
в фразе FROM) и (или) любых констант; чаще всего это просто имя одного из столбцов;
если имя какого-либо столбца одного из объектов в фразе FROM совпадает с именем
столбца в другом объекте, то их имена должны предваряться спецификатором вида
[schema.]table (view или snapshot);
c_alias или
AS c_alias - используется для присвоения столбцу или выражению альтернативного
имени (псевдонима); это имя будет выводиться в заголовке столбца (выражения)
и может использоваться в фразе ORDER BY;
schema -
необязательный параметр, идентифицирующий схему, в которой находится таблица,
представление или снимок (по умолчанию - схема пользователя);
table, view,
snapshot - имя таблицы, представления или снимка, из которых выбираются
данные;
dblink -
имя канала связи с удаленной базой данных, в которой размещена таблица (представление
или снимок);
subquery
- текст подзапроса, являющегося в данном контексте динамическим представлением,
из которого выбираются данные аналогично тому, как они выбираются из явно указанных
объектов (таблиц, представлений или снимков); синтаксис подзапроса приведен
ниже;
t_alias -
используется для присвоения другого имени (псевдонима) таблице, представлению
или снимку; после такого присвоения в тексте запроса должен использоваться только
псевдоним;
WHERE - содержит
условия, оговаривающие признаки тех строк, которые должны выводится (или не
должны выводиться) по данному запросу; если WHERE фраза отсутствует, то выводятся
все строки таблицы (представления или снимка) или декартово произведение таблиц
(представлений и снимков), указанных в фразе FROM;
[START WITH condition]
CONNECT BY condition - используются для построения запроса, позволяющего
выводить результат в виде древовидной (иерархической) структуры; подробнее о
этом расширении стандарта языка SQL будет рассказано ниже;
GROUP BY
- используется для группировки выбранных строк по указанному перечню и получения
для каждой группы единственной строки с итоговой информацией: минимумом (MAX),
максимумом (MIN), количеством (COUNT), суммой (SUM) и (или) средним значением
(AVG);
HAVING -
содержит условия, оговаривающие признаки тех итоговых строк (строк, полученных
с помощью GROUP BY), которые должны выводится (или не должны выводиться) по
данному запросу; если HAVING фраза отсутствует, то выводятся все итоговые строки;
UNION, UNION
ALL, INTERSECT, MINUS - используются для комбинирования нескольких команд
SELECT в одном запросе; по UNION выводятся все различимые строки объединяемых
SELECT; по UNION ALL выводятся все строки объединяемых SELECT; по INTERSECT
выводятся только те строки, которые находятся во всех пересекаемых SELECT; по
MINUS выводятся строки выбранные первым SELECT и отсутствующие во втором;
ORDER BY
- используется для упорядочения выводимых запросом строк; список упорядочения
может содержать перечень столбцов, их псевдонимов (c_alias) или номеров позиций
(position) в списке выбора SELECT; для каждого элемента списка может быть указан
порядок упорядочения: по возрастанию - ASC (устанавливается по умолчанию) или
по убыванию - DESC;
FOR UPDATE
- используется для блокировки выбираемых строк на момент выполнения запроса;
NOWAIT - отменяет выполнение
запроса, если SELECT пытается блокировать строку, которая ранее заблокирована
другим пользователем; при отсутствии этой фразы Oracle ждет, пока строка не
будет доступна и затем возвращает результаты SELECT.
Следует также отметить,
что в SELECT, UPDATE, DELETE, subquery и в любой их части можно вставлять специальные
комментарии - подсказки (hints) оптимизатору Oracle. Оптимизатор, преобразующий
любой запрос в набор низкоуровневых процедур и составляющий план его выполнения,
использует пользовательские подсказки для корректировки плана.
Пример 1. Получить аббревиатуры
и названия факультетов.
Это можно сделать с помощью запроса:
SELECT Факульт, Факультет
FROM Факультеты;
или, учитывая что Факульт
и Факультет это все столбцы таблицы Факультеты, используя спецификатор "*":
SELECT * FROM Факультеты;
Пример 2. Получить список неповторяющихся
имен студентов.
SELECT DISTINCT Имя
FROM Студенты;
3.1.2.
Фраза WHERE
Для выбора определенных
строк таблицы (представления или снимка) используют фразу WHERE, синтаксис которой
имеет вид:
WHERE [NOT] condition [{AND | OR} [NOT]
condition ] ...
где
condition - условие, синтаксис
которого строится по правилам п. 2.4.5.3;
AND, OR, AND
NOT и OR NOT - логические операторы, используемые для соединения нескольких
условий.
Для получения желаемого
результата WHERE фразы с множеством условий их надо вводить в правильном порядке,
который можно организовать с помощью скобок. Надо также учитывать приоритет
AND над OR.
Пример 1. Получить
список тех студентов групп 250 - 255, которые по разным причинам прекратили
или прервали обучение в 1998/1999 учебном году (данные на 4.7.1999). Список
должен содержать Номер, Фамилию, Имя, Отчество, Группу, Признак, Начало и быть
упорядочен по номеру группы и фамилии:
SELECT s.Номер, Фамилия, Имя, Отчество,
Группа, Признак, Начало
FROM Студенты s, Ученик u
WHERE s.Номер = u.Номер
AND Группа BETWEEN 250 AND 255
AND Признак <> 'обучен'
AND '4.7.1999' BETWEEN Начало AND Конец
ORDER BY Группа, Фамилия ;
Пример 2. Вместо
оператора BETWEEN (между) для указания групп можно использовать оператор IN
(принадлежит), а для задания даты операторы сравнения:
SELECT s.Номер, Фамилия, Имя, Отчество,
Группа, Признак, Начало
FROM Студенты s, Ученик u
WHERE s.Номер = u.Номер
AND Группа IN (250,251,252,253,254,255)
AND Признак <> 'обучен'
AND Начало <= '4.7.1999' AND Конец
>= '4.7.1999'
ORDER BY Группа, Фамилия ;
3.1.3.
Представление
Представление (view)
- это логическая (виртуальная) таблица, определяемая перечнем тех столбцов таблиц
и признаками тех их строк, которые хотелось бы в ней увидеть. Представление
является как бы "окном" в одну или несколько базовых или логических
таблиц. Оно создается с помощью команды:
CREATE [OR REPLACE] [ FORCE | !!under!!NOFORCE
] VIEW [schema.]view
[(alias [,alias]...)] AS subquery
[ WITH READ ONLY | WITH CHECK OPTION [CONSTRAINT
constraint] ] ;
где
OR REPLACE
- используется для перезаписи существующего представления (иначе перед созданием
новой версии представления надо уничтожить его старую версию с помощью команды
DROP VIEV);
FORCE - служит
для создания текста представления по пока несуществующим или недоступным для
данного пользователя таблицам; перед работой с таким представлением необходимо
создать указанные в subquery таблицы и (или) получить права на их использование;
NOFORCE -
служит для создания представления из существующих таблиц, на использование которых
имеются необходимые привилегии доступа (устанавливается по умолчанию);
schema -
необязательный параметр, идентифицирующий схему, в которой должно быть создано
представление (по умолчанию - схема пользователя);
view - имя
представления (строится по тем же правилам, что и для таблицы); выбираются данные;
(alias [,alias]...)
- имена столбцов представления, в которые будут записаны значения из выражений
(столбцов) подзапроса (subquery); если этот перечень отсутствует, то столбцам
представления будут присвоены имена столбцов (выражений или псевдонимов) из
подзапроса;
AS subquery
- определение подзапроса, генерирующего данные в представление при указании
имени этого представления в тексте какого-либо запроса или родительского подзапроса;
WITH READ ONLY
- запрещает модификацию представления и, следовательно, тех таблиц, на базе
которых создано данное представление;
WITH CHECK OPTION
- запрещает модифицировать строки, которые затем не смогут быть выбраны посредством
данного представления;
constraint -
имя ограничения вида CHECK.
Таблица (таблицы),
на базе которой создается представление, называется основной таблицей.
Представления используются для:
-
обеспечения дополнительного
уровня защиты основной таблицы путем ограничения доступа к определенному
набору ее строк и (или) столбцов;
-
скрытия сложности данных,
например, за счет размещения в одном представлении данных из нескольких
таблиц и других представлений;
-
создания различного видения
одних и тех же данных, возможно в одно и то же время, различными категориями
пользователей;
-
обеспечения независимости
прикладных программ от изменения логической структуры базы данных (например,
при необходимости расщепления какой-либо таблицы на несколько новых таблиц
можно создать по ним представление, дав ему имя расщепленной таблицы).
К недостаткам представлений
можно отнести:
-
серьезные ограничения
по модификации данных через представления;
-
отсутствие возможности
построения индексов по столбцам представления, что может снизить производительность
приложений, использующих представления вместо базовых таблиц.
Пример 1. Создать
представление со списком обучающихся в данный момент контрактных студентов групп
350 - 355, содержащий номер группы, номер студента, номер его зачетной книжки,
фамилию, имя и отчество:
CREATE OR REPLACE Контр_групп_350_355 AS
SELECT Группа, s.Номер, НЗК, Фамилия,
Имя, Отчество
FROM Студенты s, Ученик u, Контр_студ
k
WHERE s. Номер = u. Номер AND s. Номер
= k. Номер AND Признак = 'обучен'
AND SYSDATE BETWEEN u.Начало AND u.Конец
AND Группа BETWEEN 350 AND 355;
Так как подзапрос,
используемый для описания представления, не может содержат фразы ORDER BY (см.
п. 3.1.4), то упорядочение выводимых данных придется осуществлять в запросе
на получение данных из представления.
Пример 2. Используя
представление Контр_групп_350_355 получить список контрактных студентов групп
350 и 353, упорядоченный по номеру группы и фамилии:
SELECT * FROM Контр_групп_350_355
WHERE Группа IN (350, 353)
ORDER BY Группа, Фамилия ;
3.1.4.
Подзапрос
Во многих командах
SQL используются подзапросы (subquery). Подзапрос это несколько урезанная команда
SELECT (без фраз ORDER BY, FOR UPDATE и NOWAIT), синтаксис которой имеет вид:
SELECT [DISTINCT | !!under!!ALL] { *
| { [schema.]{table | view | snapshot}.*
| expr [ [AS] c_alias ] }
[, { [schema.]{table | view | snapshot}.*
| expr [ [AS] c_alias ] } ] ... }
FROM { (subquery) | [schema.]{table |
view | snapshot}[@dblink] } [t_alias]
[, { (subquery) | [schema.]{table | view
| snapshot}[@dblink] } [t_alias] ]
... }
[ WHERE condition ]
[ [START WITH condition] CONNECT BY condition
]
[ GROUP BY expr [, expr] ...] [HAVING
condition ]
[ WITH READ ONLY | WITH CHECK OPTION ]
[ {UNION | UNION ALL | INTERSECT | MINUS}
subquery ] ] ;
Выше уже были описаны все фразы,
составляющие это описание. Поэтому рассмотрим здесь лишь для чего могут использоваться
подзапросы:
-
для определения набор
строк, которые должны вставляться в таблицу по командам INSERT или CREATE
TABLE (фраза AS subquery);
-
для описания строк, включаемое
в представление или снимок по командам CREATE VIEW или CREATE SNAPSHOT;
-
для определения значений,
которые будут заменять существующие по команде UPDATE;
-
для отбора нужных строк
в фразах WHERE, HAVING и WITH START команд SELECT, UPDATE и DELETE;
-
для использования в качестве
динамического представления, заменяющего имя таблицы или представления в
фразе FROM команды SELECT или подзапроса (subquery), а также в командах
INSERT, UDPATE и DELETE.
В одной команде
SELECT, INSERT, UDPATE или DELETE может содержаться множество подзапросов. Каждый
подзапрос может содержать подзапрос (множество подзапросов) и уровень вложенности
подзапросов не ограничен.
Пример 1. Определить
наиболее часто встречающееся студенческое имя и вывести это имя, а также количество
студентов с таким именем.
Существует множество
вариантов решения этой задачи. Рассмотрим один из них, использующий вложенные
подзапросы:
SELECT Имя, COUNT(Имя) Кол_во FROM Студенты
GROUP BY Имя
HAVING COUNT(Имя) = (SELECT MAX(Кол_во)
FROM
(SELECT Имя, COUNT(Имя) Кол_во FROM Студенты
GROUP BY Имя) );
В первой строке
формируется таблица, содержащая все неповторяющиеся имена и количество этих
имен (псевдоним Кол_во). Во второй строке производится отбор той строки этой
таблицы, Кол_во которой равно максимальному Кол_во из той же таблицы, сформированной
внутри подзапроса (третья строка).
3.2. Модификация
данных
3.2.1.
Команда INSERT
Эта команда используется
для вставки новых строк в таблицу прямо или через
представление, построенное на основе
этой таблицы.
Синтаксис команды
имеет вид:
INSERT INTO { { [schema.]{table | view}[@dblink]
} | (subquery_1) }
[ (column [, column] ...) ] { VALUES(expr
[, expr] ...) | subquery_2 } ;
где
schema -
необязательный параметр, идентифицирующий схему, в которой находится
таблица (представление), в которую
вставляются строки (по умолчанию - схема
пользователя);
table, view
- имя таблицы, в которую вводятся новые строки, или имя
представления, через которое они вводятся;
dblink -
имя канала связи с удаленной базой данных, в которой размещена
таблица (представление);
subquery_1
- текст подзапроса, определяющего имена столбцов и таблицы,
в которую вводятся новые строки;
column [, column]
... - список имен столбцов таблицы (представления), в
которые должны быть вставлены данные
из списка выражений (expr [, expr] ...)
фразы VALUES или подзапроса (subquery_2);
если в списке отсутствуют какие-либо столбцы,
то в них после выполнения команды будут содержаться NULL-значения или
значения по умолчанию, определенные
при создании таблицы в CREATE TABLE; если
же список отсутствует, то во фразе
VALUES или подзапросе необходимо определить
значения для всех столбцов таблицы
(представления) и перечислить из в том
порядке, в котором они перечислены
при создании таблицы (представления);
VALUES -
определяет строку значений, которые будут вставлены в таблицу или
представление;
subquery_2
- подзапрос, генерирующий строку или набор строк вставляемых в
таблицу или представление; в подзапросе
могут использоваться данные из любых таблиц
(представлений и снимков) включая ту, в которую вводятся эти данные.
Пример 1.
Студентка Орлова (номер 12345) стала
с 7.10.1998 Воробьевой. Для сохранения
старой фамилии в таблице Фамилии можно
выполнить команду:
INSERT INTO Фамилии
VALUES(12345, 'Орлова', '7.10.1998') ;
или
INSERT INTO Фамилии
SELECT Номер, Фамилия, '7.10.1998'
FROM Студенты
WHERE Номер = 12345 ;
если в таблице Студенты еще храниться старая
фамилия.
Пример 2.
Перед окончанием осеннего семестра
1999/2000 учебного года (например, 1.12.1999) надо поместить в
таблицу Ведомость данные, содержащие
номера всех фактически обучаемых в данный момент студентов
(Признак = 'обучен') и коды дисциплин
(План), по которым они должны отчитаться
во время зимней зачетной и экзаменационной
сессий:
INSERT INTO Ведомость (Номер, План)
SELECT x.Номер, y.План
FROM Ученик x, План y
WHERE x.Ном_план = y.Ном_план
AND Признак = 'обучен'
AND '1.12.1999' BETWEEN Начало AND Конец
AND y.Ном_план IN (SELECT Ном_план
FROM Вып_каф
WHERE Начало = '1.9.1999')
AND Дисципл IN (SELECT Дисципл
FROM Дисцип
WHERE Семестр IN (1,3,5,7,9,11)) ;
или, используя описание модифицируемой
таблицы с помощью подзапроса, так:
INSERT INTO (SELECT Номер, План FROM Ведомость)
SELECT x.Номер, y.План
FROM Ученик x, План y
...
2.2.
Команда DELETE
Эта команда используется
для удаления строк из таблицы прямо или через
представление, построенное на основе
этой таблицы.
Синтаксис команды
имеет вид:
DELETE [FROM] { { [schema.]{table | view}[@dblink]
} | (subquery) } [alias]
[WHERE condition] ;
где
schema - необязательный
параметр, идентифицирующий схему, в которой находится
таблица (представление), из которой
удаляются строки (по умолчанию - схема
пользователя);
table, view - имя
таблицы, из которой удаляются строки, или имя
представления, через которое они удаляются;
dblink - имя канала
связи с удаленной базой данных, в которой размещена
таблица (представление);
subquery - текст
подзапроса, определяющего имя таблицы, из которой
удаляются строки; подзапрос не должен
содержать запроса по таблице, указанной
в его фразе FROM;
alias - псевдоним
таблицы, представления или подзапроса, который может быть
использован в условиях фразы WHERE;
WHERE - используется
для того, чтобы из таблицы удалялись только те строки,
которые удовлетворяют условию или
набору условий (condition); если эта фраза
отсутствует, то удаляются все строки
таблицы (отметим, что удаление всех строк
целесообразнее выполнять с помощью
рассмотренной ниже команды TRANCATE).
Пример 1. Удалить
все данные из таблицы Вед_изм:
DELETE FROM Вед_изм ;
или
DELETE Вед_изм ;
Пример 2. Удалить из таблицы Вед_изм
строки введенные до 1.1.1998:
DELETE Вед_изм
WHERE Дата < '1.1.1998' ;
3.2.3.
Команда TRANCATE
Эта команда используется
для быстрого удаления всех строк таблицы. Дело в
том, что удаление строк с помощью
команды DELETE сопровождается их сохранением
в системных сегментах отката на случай
восстановления, а это может потребовать
значительного времени.
Несколько урезанный
синтаксис команды имеет вид:
TRUNCATE TABLE [schema.]table [ { !!under!!DROP
| REUSE } STORAGE ] ;
где
schema -
необязательный параметр, идентифицирующий схему, в которой находится таблица,
из которой удаляются все строки (по умолчанию - схема пользователя);
table - имя
таблицы, из которой удаляются все строки;
DROP STORAGE
(устанавливается по умолчанию) - используется
для освобождения памяти,
выделенной для хранения строк таблицы и индексов;
REUSE STORAGE
- используется для сохранения памяти для повторной загрузки
того же объема данных на ранее отведенное
для таблицы место;
Пример 1. Удалить
все данные из таблицы Вед_изм:
TRUNCATE TABLE Вед_изм ;
Пример 2.
Удалить все данные из таблицы Вед_изм
и сохранить в памяти место для повторной
ее загрузки:
TRUNCATE TABLE Вед_изм REUSE
STORAGE ;
3.2.4.
Команда UPDATE
Эта команда используется
для изменения значений в столбцах таблицы прямо или
через представление, построенное на
основе этой таблицы.
Синтаксис команды имеет вид:
UPDATE { { [schema.]{table | view}[@dblink]
} | (subquery_1) } [alias]
SET { (column [, column] ...) = (subquery_2)
| column = { expr | (subquery_3) } }
[, { (column [, column] ...) = (subquery_2)
| column = { expr | (subquery_3) } } ]
...
[WHERE condition] ;
где
schema -
необязательный параметр, идентифицирующий схему, в которой находится таблица
(представление), в которой изменяются значения столбцов (по умолчанию - схема
пользователя);
table, view
- имя таблицы, в которой изменяются значения, или имя представления, через которое
они изменяются;
dblink -
имя канала связи с удаленной базой данных, в которой размещена таблица (представление);
subquery_1
- текст подзапроса, определяющего имена таблицы и изменяемых столбцов;
alias - псевдоним
таблицы, представления или подзапроса, который может быть использован в других
местах команды;
фраза SET
определяет набор модифицируемых столбцов и их новых значений;
column [, column]
... - список имен столбцов таблицы (представления), значения которых должно
быть заменено на значения, выводимые подзапросом subquery_2;
subquery_2
- подзапрос, определяющий новые значения для списка изменяемых столбцов; подзапрос
должен выдать точно одну строку для каждой модифицируемой строки таблицы и в
этой строке должно выводиться ровно столько значений, сколько перечислено столбцов
в списке столбцов;
column -
имя столбца, значение которого должно быть заменено на значение выражения (expr)
или значение, полученное из подзапроса (subquery_3);
subquery_3
- подзапрос, определяющий новое значение изменяемого столбца; подзапрос должен
вывести точно одну строку для каждой модифицируемой строки таблицы и в этой
строке должно выводится только одно значение;
Указанные выше опции
(список и имя столбца) могут быть многократно повторены
в команде; если все же в полном перечне
будут отсутствуют какие-либо столбцы таблицы,
то их значения останутся неизменными;
expr - выражение,
определяющее новое значение изменяемого столбца;
WHERE - используется
для того, чтобы столбцы изменялись только в тех строках,
которые удовлетворяют условию или
набору условий (condition); если эта фраза
отсутствует, то изменяются столбцы
во всех строках таблицы.
Пример 1. Студент
номером 10042 пересдал 10.2.1999 экзамен по дисциплине Системное программное
обеспечение? за девятый семестр и получил оценку Отлично?. Необходимо обновить
соответствующую запись в таблице Ведомость.
UPDATE vedomost SET ocenka
= 'отл', data = '10.2.1999'
WHERE nomer = 10042 AND
plan IN (SELECT plan FROM plan WHERE discipl IN
(SELECT discipl FROM discip
WHERE semestr = 9 AND kontr = 'Экз' AND kor_disc =
(SELECT kor_disc FROM imia_disc
WHERE imia_disc =
'Системное программное обеспечение')));
Пример 2. Для иллюстрации
возможности применения подзапроса, определяющего новые значения для списка изменяемых
столбцов, обновим аналогичную оценку студента с номером 10043, используя только
что измененную оценку студента с номером 10042.
UPDATE vedomost SET (ocenka,
data) =
(SELECT ocenka, data FROM
vedomost WHERE nomer = 10042 AND plan IN
(SELECT plan FROM plan WHERE
discipl IN
(SELECT discipl FROM discip
WHERE semestr = 9 AND kontr = 'Экз'
AND kor_disc =
(SELECT kor_disc FROM imia_disc
WHERE imia_disc =
'Системное программное обеспечение'))))
WHERE nomer = 10043 AND
plan IN (SELECT plan FROM plan WHERE discipl IN
(SELECT discipl FROM discip
WHERE semestr = 9 AND kontr = 'Экз' AND kor_disc =
(SELECT kor_disc FROM imia_disc
WHERE imia_disc =
'Системное программное обеспечение')));
Естественно,
что мы не предлагаем использовать для обновления
этот навороченный? иллюстрационный
запрос.
4. Другие средства
создания ограничений целостности
4.1. О триггерах
и языке PL/SQL
В п. 2.4.7 уже упоминалось
о таком средстве создания ограничения целостности, как триггер. Триггер - это
сочетание хранимой в базе данных процедуры и события, которое заставляет ее
выполняться. Такими событиями могут быть: ввод новой строки таблицы, изменение
значений одного или нескольких ее столбцов и (или) удаление строки таблицы.
При любом из этих событий автоматически запускаются один или несколько заранее
созданных триггеров, которые производят проверку запрограммированных в них условий
и если они не выполняются, отменяют ввод, изменение или удаление, посылая об
этом заранее подготовленное сообщение пользователю.
Там же отмечалось,
что для написания текста триггера надо не только овладеть рядом команд языка
SQL., но и языком PL/SQL. PL/SQL
(PL -
programmatic language)
создан фирмой Oracle
для расширения возможностей языка SQL
при написании хранимых процедур и сценариев, вызываемых из SQL*Plus.
Вот некоторые конструкции последнего:
-
Блоки IF
... THEN;
-
Циклы WHILE
... DO и FOR
... NEXT;
-
Переменные, используемые
внутри программы;
-
Курсоры (возможность просматривать
результаты запроса по одной строке за один раз, сохраняя результаты в переменных,
которые можно обрабатывать);
-
Обработка исключительных
ситуаций, которая позволяет предпринимать действия, исходя из проблем, обнаруженных
Oracle во
время выполнения программы.
PL/SQL - это блочно-структурированный
язык. Это означает, что основные единицы программ PL/SQL (анонимные блоки, процедуры
и функции) являются логическими блоками, которые могут содержать любое число
вложенных в них блоков. Структура блока представлена на рис. 4.1.
Обязательной является
лишь исполняемая часть блока (BEGIN),
а вложенные блоки можно вкладывать только в исполняемую часть и часть обработки
исключительных ситуаций.
Ниже мы кратко (на
примерах) рассмотрим основные конструкции PL/SQL,
которые могут использоваться при создании триггеров и их отладки. Первые примеры
будут связаны с созданием разных типов программ для проверки правильности ввода
(изменения) фамилии, имени или отчества и, если необходимо, их корректировки.
Фамилия (имя) должна состоять только из русских букв и символов тире и пробел.
Первая буква фамилии (имени или отчества) должна быть заглавной, а остальные
строчными. В двойных (тройных) фамилиях и именах каждая часть должна начинаться
с заглавной буквы (например, Смирнов-Сокольский, Жан-Жак Руссо, Гай Юлий Цезарь).
4.2. Анонимные
блоки (сценарии SQL)
Анонимный блок PL/SQL
- это неименованная программа, обычно используемая для отладки текстов программ
других видов. Довольно часто анонимные блоки называют сценариями
SQL.
DECLARE -- декларативная
часть анонимного блока
-- описание локальных переменных
tekst VARCHAR2(20) := 'Жан
- жак руссо'; -- Проверяемый текст (фамилия,
-- имя или отчество)
vychod VARCHAR2(20); --
Результат (проверенный и может быть исправленный
-- входной текст или "0
"при неисправимой ошибке)
kol INTEGER; -- Количество
символов в проверяемом тексте
BEGIN -- исполняемая часть
(тело) анонимного блока
vychod := LOWER(RTRIM(LTRIM(tekst)));
-- первый вариант результата,
-- полученный после удаление
из входного текста начальных (LTRIM) и
-- конечных (RTRIM) пробелов,
а также преобразования всех букв текста
-- в строчные (LOWER)
kol := LENGTH(vychod); --
определение числа символов, оставшихся в тексте
-- после удаления начальных
и конечных пробелов
IF kol > 0 THEN -- если
текст содержит символы, то его дальнейшее
-- преобразование и проверка
на наличие запрещенных символов
vychod := REPLACE(vychod,'
',' '); -- замена двух пробелов на один
vychod := REPLACE(vychod,'-
','-'); -- замена тире и пробела на тире
vychod := REPLACE(vychod,'
-','-'); -- замена пробела и тире на тире
kol := LENGTH(vychod); --
определение числа символов, оставшихся в тексте
FOR i IN 1..kol LOOP --
перебор всех символов текста
IF INSTR('- абвгдеёжзийклмнопрстуфхцчшщъыьэюя',SUBSTR(vychod,i,1))
= 0
THEN
-- с помощью функции INSTR
определяется позиция первого включения
-- i-го символа текста (вырезанного
с помощью функции SUBSTR) в
-- набор '- абвгдеёжзийклмнопрстуфхцчшщъыьэюя'
и если эта позиция
-- равна 0 (i-го символа
нет в наборе), то производится:
vychod := '0'; -- установка
нулевого результата и
EXIT; -- выход из цикла
END IF;
END LOOP;
ELSE
vychod := '0'; -- установка
нулевого результата при отсутствии
-- в тексте символов, отличных
от пробелов
END IF;
IF vychod <> '0' THEN
-- если текст содержит символы, то:
vychod := INITCAP(vychod);
-- преобразование первых букв слов текста в
-- заглавные
END IF;
DBMS_OUTPUT.PUT_LINE(vychod);
-- использование утилиты DBMS_OUTPUT пакета
-- DBMS_STANDARD для вывода
на терминал
-- результата преобразования
END;
/ -- наклонная черта, указывающая
на окончание текста блока PL/SQL
Этот
анонимный блок можно ввести с помощью текстового редактора в файл (например,
с именем an_fio.sql) или вводить строчка за строчкой прямо в SQL*Plus.
В последнем случае ввод в первой строке
одного из зарезервированных слов DECLARE или BEGIN переводит SQL*Plus
в режим построчного ввода строк анонимного блока так, как это показано ниже.
SQL> DECLARE -- декларативная
часть анонимного блока
2 -- описание локальных
переменных
3 tekst VARCHAR2(20) :=
'Жан - жак руссо'; -- Проверяемый текст (фамилия,
4 -- имя или отчество)
5 vychod VARCHAR2(20); --
Результат (проверенный и может быть исправленный
6 -- входной текст или "0
"при неисправимой ошибке)
7 kol INTEGER; -- Количество
символов в проверяемом тексте
8 BEGIN -- исполняемая часть
(тело) анонимного блока
9 vychod := LOWER(RTRIM(LTRIM(tekst)));
-- первый вариант результата,
. . .
44 END;
45 /
После
ввода наклонной черты блок выполняется и на экран выдается результат его работы:
Жан-Жак Руссо
Процедура PL/SQL успешно завершена.
SQL> _
Если
текст анонимного блока был сохранен в файле an_fio.sql, то такой сценарий SQL
можно выполнить с помощью команды @an_fio.sql
или @an_fio
(расширение .sql, указывающее, что данный файл является файлом запроса, можно
опустить):
SQL> @an_fio
Жан-Жак Руссо
Процедура PL/SQL успешно завершена.
SQL> _
4.3. Процедуры
Недостатком
выполнения файлов сценариев SQL
в распределенной вычислительной среде является то, что
необходимо хранить актуальную версию
этих сценариев на большом числе компьютеров. Программное обеспечение намного
проще хранить в базе данных (в одном месте), чтобы к нему мог обратиться каждый.
Для этого используются хранимые процедуры (функции и пакеты). Команда для создания
(CREATE) или замены (CREATE OR REPLACE) независимой (не входящей в состав пакета)
процедуры имеет вид:
CREATE [OR REPLACE] PROCEDURE
[schema.]name
[ (argument [!!under!!IN
| OUT | IN OUT] datatype
[, argument [!!under!!IN
| OUT | IN OUT] datatype ] ...) ] IS
pl/sql_subprogram_body
END [name];
где
schema -
необязательный параметр, идентифицирующий схему, в которой должна быть создана
процедура;
name
- имя процедуры, формируемое по правилам,
описанным, например, в п. 2.4.4.1;
argument
- имя параметра, формируемое по тем же правилам, что и имя процедуры;
IN | OUT | IN
OUT - используются для обозначения параметров, значения которых:
-
(IN) передаются
в процедуру и не могут быть в ней изменены (устанавливается по умолчанию);
-
(OUT) должны
быть возвращены из процедуры (такие значения нельзя присваивать другим переменным
или переприсваивать);
-
(IN OUT) могут
передаваться в процедуру, возвращаться из нее и использоваться как обычные
переменные внутри процедуры.
datatype
- тип данных значений параметра (типы данных PL/SQL
см. в п. 4.5.1.1);
pl/sql_subprogram_body
- тело процедуры.
Для удаления описания
процедуры и ее перекомпиляции используются команды:
DROP PROCEDURE [schema.]name
;
и
ALTER PROCEDURE
[schema.]name COMPILE ;
Создадим
хранимую процедуру, выполняющую те же действия, что и анонимный блок п. 4.2.
Текст её тела отличается от текста тела анонимного блока лишь отсутствием обращения
к утилите DBMS_OUTPUT.PUT_LINE,
используемой для вывода результата:
CREATE OR REPLACE PROCEDURE
p_fio -- имя процедуры
(tekst VARCHAR2, -- входной
параметр (опущена умалчиваемая мода IN)
vychod IN OUT VARCHAR2)
-- параметр с модой IN OUT позволяет передавать в
-- процедуру начальное значение
и возвращать обновленное значение
-- подпрограмме, вызывающей
данную процедуру
IS
-- описание локальных переменных,
используемых в теле процедуры
kol INTEGER;
BEGIN
. . .
-- тело процедуры, отличающиеся
от текста тела анонимного блока лишь
-- отсутствием в его последней
строке команды DBMS_OUTPUT.PUT_LINE(vychod)
. . .
END p_fio;
/
Также
как и текст анонимного блока, текст команды по созданию процедуры можно ввести
с помощью текстового редактора в файл (например, с именем p_fio.sql)
и выполнить в SQL*Plus
команду @p_fio.sql
или вводить строчка за строчкой прямо в SQL*Plus.
После
создания процедуры p_fio
ее можно вызвать из другой процедуры или анонимного блока:
DECLARE
vchod VARCHAR2(20) := 'Жан
- жак руссо';
rezult VARCHAR2(20);
BEGIN
p_fio (vchod, rezult);
dbms_output.put_line(rezult);
END;
/
Обращение к процедуре
осуществляется с использованием позиционной (как это показано выше) или именной
нотации:
p_fio (vychod
=> rezult, tekst => vchod);
При
обращении к процедуре в качестве фактических параметров можно использовать любые
выражения и, естественно, их составляющие, например, константы:
p_fio ('Жан - жак руссо',
rezult);
или
p_fio ('Жан'||' - жак'||'
руссо', rezult);
4.4. Функции
Функция
PL/SQL отличается
от процедуры тем, что возвращаемое значение расположено в её имени, так же,
как и в стандартных функциях (например, SIN,
ABS, SUBSTR).
Для описания типа, расположенного
в имени значения, в синтаксис функции включена фраза RETURN
datatype?:
CREATE [OR REPLACE] FUNCTION
[schema.]name
[ (argument [!!under!!IN
| OUT | IN OUT ] datatype
[, argument [!!under!!IN
| OUT | IN OUT ] datatype ] ...) ] RETURN datatype IS
pl/sql_subprogram_body
END [name];
Для удаления описания
функции и ее перекомпиляции используются команды:
DROP FUNCTION [schema.]name ;
и
ALTER FUNCTION
[schema.]name COMPILE ;
Создадим хранимую
функцию, выполняющую те же действия, что и процедура п. 4.3. Текст её тела отличается
от текста тела процедуры наличием описания возвращаемого значения (RETURN
VARCHAR2) и предложением RETURN
vychod, завершающим выполнение функции
и присваивающим значение её имени:
CREATE OR REPLACE FUNCTION
fio -- имя функции
(tekst VARCHAR2) -- параметр
(текст фамилии, имени или отчества)
-- и тип данных этого текста
RETURN VARCHAR2 -- тип возвращаемого
значения
IS
-- описание локальных переменных,
используемых в теле функции
vychod VARCHAR2(20);
kol INTEGER;
BEGIN -- начало тела функции
. . .
-- тело функции, отличающиеся
от текста тела процедуры лишь наличием
-- его последней строке команды
RETURN vychod
. . .
RETURN vychod;
END fio;
/
Текст
команды по созданию функции вводится в Oracle
также, как и текст процедуры. Вызов
функции PL/SQL можно
осуществить в тех же местах, где и вызов стандартной функции. Например, в списке
фразы SELECT команды:
SELECT fio('Жан
- жак руссо') Фамилия FROM DUAL;
Результат её выполнения
имеет вид:
ФАМИЛИЯ
--------------------
Жан-Жак Руссо
Здесь
использовалась специальная однострочная таблица DUAL, которая создается Oracle
для каждой схемы и обычно используется
для вывода значения каких-либо выражений.
4.5.
Немного о синтаксисе команд PL/SQL
4.5.1. Типы данных, переменные,
константы и выражения
4.5.1.1. Типы данных, доступные
в PL/SQL
Кроме типов данных
Oracle (см. п. 2.4.4.1), PL/SQL поддерживает несколько дополнительных типов
данных:
Тип данных Описание
-------------- -----------------------------------------------------------------
BINARY_INTEGER Этот тип данных
и его подтипы NATURAL и POSITIVE применяются для
создания переменных и констант,
которые хранят число со знаком.
Двоичные целые числа могут
принимать значения в диапазоне от -2
в 31 степени до 2 в 31 степени
минус 1.
BOOLEAN Принимается для создания переменных
и констант, в которых
хранятся логические значения TRUE и FALSE.
CHAR Есть подтипы CHARACTER и STRING. Максимальный
размер 32767.
NUMBER Есть подтипы DEC, DECIMAL, DOUBLE
PRECISION, INT, INTEGER, REAL,
NUMERIC, и SMALLINT.
RECORD Используется для создания пользовательских
типов записей.
TABLE Служит для создания табличных типов
данных PL/SQL.
VARCHAR2 Есть подтип VARCHAR. Максимальный
размер 32767.
col%TYPE Используется для определения типа
данных столбца или переменной
по типу данных другого столбца или переменной,
к имени которого
или которой (col) приписан суффикс %TYPE.
tab%ROWTYPE Используется для определения
типа данных записи по типу данных
столбцов таблицы, к имени которой (tab)
приписан суффикс
%ROWTYPE.
4.5.1.2. Таблицы PL/SQL
Таблица PL/SQL -
это одномерный массив с неограниченным числом строк. Для
объявления этого массива (таблицы
PL/SQL или TABLE) необходимо сначала определить
его тип данных:
TYPE type_name IS TABLE OF {
column_type | variable%TYPE |
table.column%TYPE } [NOT NULL]
INDEX BY BINARY_INTEGER;
где "type_name"
- спецификатор типа, используемый в последующих объявлениях таблиц PL/SQL, и
"column_type" - любой из скалярных типов данных: CHAR, DATE или NUMBER.
С помощью атрибута %TYPE можно установить "type_name" соответствующим
типу данных какой-либо переменной (variable) или столбца (table.column).
Имя (например, name_plsql_table),
которое описывается табличным типом данных, называется таблицей PL/SQL. Это
описание, размещаемое в разделе DECLARE, имеет вид:
name_plsql_table type_name;
Ссылки на строки
таблицы PL/SQL осуществляются аналогично ссылкам на элементы одномерного массива:
name_plsql_table(index) ,
где index принадлежит типу
BINARY_INTEGER. Например, для ссылки на третью строку в таблице PL/SQL "ename_tab"
следует написать: ename_tab(3).
Для присвоения значения
конкретной строке таблицы PL/SQL используется синтаксис:
name_plsql_table(index) := expr;
Для ввода в таблицу
PL/SQL значений из какого-либо столбца базовой таблицы или представления, а
также для выборки значений из таблицы PL/SQL, необходимо использовать цикл.
(Примеры таких операций приведены ниже.)
4.5.1.3. Записи PL/SQL
Record PL/SQL -
это совокупность полей, каждое из которых должно иметь уникальное имя (в пределах
записи). Эти поля могут принадлежать различным типам данных.
Если создаваемая
запись (stud)
соответствует описанию столбцов какой-либо базовой таблицы (например, studenty),
то ее объявление можно осуществить в разделе DECLARE с помощью атрибута %ROWTYPE:
stud studenty%ROWTYPE;
В противном случае
для объявления записи необходимо сначала определить ее тип данных. Для описания
типа данных RECORD используется синтаксис:
TYPE type_name IS RECORD
( field_name1 {field_type |
variable%TYPE | table.column%TYPE
| table%ROWTYPE} [NOT NULL],
field_name2 {field_type | variable%TYPE
| table.column%TYPE
| table%ROWTYPE} [NOT NULL],
...) ;
где "type_name"
- спецификатор типа, используемый в последующих объявлениях записей PL/SQL,
и "field_type" - любой тип данных. С помощью атрибута %TYPE можно
установить "type_name" соответствующим типу данных какой-либо переменной
(variable) или столбца (table.column). Атрибут %ROWTYPE позволяет определить
поле как запись, соответствующую описанию столбцов какой-либо базовой таблицы.
При объявлении типа
записи можно присвоить ее полям некоторые значения. Если же для поля вводится
ограничение NOT NULL (для предотвращения назначения пустых значений), то этому
полю надо обязательно присвоить значение. Например:
TYPE StudRecTyp IS RECORD (nomer
NUMBER(5) NOT NULL := 10001,
familiia CHAR(20), priznak
CHAR(6), grupa NUMBER(3) := 350);
Объявление создаваемой
записи (например, name_plsql_record) производится в разделе DECLARE и имеет
вид:
name_plsql_record type_name;
Ссылки на отдельные поля записи
осуществляются так:
name_plsql_record.field_name;
Для присвоения значения конкретному
полю записи используется синтаксис:
name_plsql_record.field_name
:= expr;
Примеры использования
записей в программах PL/SQL приведены ниже.
4.5.1.4. Переменные, константы
и выражения
В программах PL/SQL
могут использоваться переменные и константы, описываемые в разделе DECLARE с
помощью конструкции вида:
variable_name [CONSTANT] type_name
[NOT NULL] [ { := | DEFAULT } expr ]
Например
rojdenie DATE;
kol_grup SMALLINT := 0;
priznak VARCHAR2(6) NOT NULL := 'академ';
pi CONSTANT REAL := 3.14159;
area REAL := pi * radius**2;
valid_id BOOLEAN;
valid_id VARCHAR2(5); -- недопустимое
вторичное описание valid_id
i, j, k SMALLINT; -- нельзя описывать
список; надо:
-- i SMALLINT; j SMALLINT; k SMALLINT;
credit REAL(7,2);
debit credit%TYPE; -- тип данных аналогичный
типу данных "credit"
4.5.2.
Команды управления ходом выполнения программы
4.5.2.1.
Команды условного перехода (IF ...)
Существует три модификации оператора
условного перехода:
IF-THEN | IF-THEN-ELSIF
----------- | --------------------
IF условие THEN | IF условие1 THEN
последовательность команд; | 1-я последовательность
команд;
END IF; | ELSIF условие2 THEN
| 2-я последовательность команд;
IF-THEN-ELSE | ...
------------------- | ELSIF условиеN THEN
IF условие THEN | N-я последовательность
команд;
1-я последовательность команд; | [ ELSE
ELSE | N+1-я последовательность команд;
]
2-я последовательность команд; | END IF;
END IF |
Синтаксис условий
приведен в п. 2.4.5. Во всех модификациях если "условие" или "условие1"
истинно, то выполняется "последовательность команд" или "1-я
последовательность команд" и управление передается на первый оператор после
END IF. Если же оно ложно, то:
-
в модификации IF-THEN
управление передается на первый оператор после END IF;
-
в модификации IF-THEN-ELSE
выполняется 2-я последовательность команд и управление передается на первый
оператор после END IF;
-
в модификации IF-THEN-ELSIF
проверяется условие 2; если оно истинно, то выполняется 2-я последовательность
команд и управление передается на первый оператор после END IF; если условия
1 и 2 ложны, а условие 3 истинно, то выполняется 3-я последовательность
команд и управление передается на первый оператор после END IF; наконец,
если условия 1, 2, ..., N ложны, то выполняется N+1-я последовательность
команд и управление передается на первый оператор после END IF.
Все это справедливо,
если внутри последовательности команд нет операторов, осуществляющих переход
за пределы этой последовательности.
4.5.2.2.
Метки и оператор безусловного перехода (GOTO)
В любом месте программы
может быть поставлена метка, имеющая синтаксис:
<<имя_метки>>
Оператор GOTO позволяет
осуществить безусловный переход к метке, имя которой должно быть уникальным
внутри программы или блока PL/SQL. Например, управление передается вниз к помеченному
оператору:
BEGIN
...
GOTO insert_row;
...
<<insert_row>>
INSERT INTO plan VALUES
...
END;
В следующем примере
управление передается вверх к помеченной последовательности операторов:
BEGIN
...
<<update_row>>
BEGIN
UPDATE plan SET ...
...
END;
...
GOTO update_row;
...
END;
Следует отметить,
что использование GOTO (особенно в тех случаях, когда метка предшествует оператору
GOTO) может привести к сложным, нераспознаваемым кодам ошибок, которые трудно
обрабатывать. Поэтому реже используйте GOTO, тем более что этот оператор нельзя
использовать для выполнения перехода:
-
в IF-блок, LOOP-блок или
в другой блок, не включающий текущий;
-
из одного предложения
IF-оператора к другому;
-
из внешнего блока в SUB-блок;
-
из обработчика особых
ситуаций в текущий блок.
4.5.2.3.
Операторы цикла (LOOP, WHILE...LOOP и FOR...LOOP)
Циклы служат для
повторяемого выполнения последовательности команд. В PL/SQL используются три
модификации операторов цикла: LOOP, WHILE...LOOP и FOR...LOOP.
Цикл LOOP имеет
следующий синтаксис:
LOOP
последовательность команд;
END LOOP;
и приводит к бесконечному
повторению последовательности команд, если внутри нее нет команд EXIT (выход
из цикла), RAISE (вызов обработчика исключительных ситуаций) или GOTO (безусловный
переход). Например,
LOOP
последовательность команд;
IF условие THEN EXIT; END
IF;
END LOOP;
приведет к выходу из цикла
после выполнения последовательности команд, как только условие станет истинным.
Цикл WHILE предназначен
для повторения последовательности команд, пока условие остается истинным:
WHILE условие LOOP
последовательность команд;
END LOOP;
Наиболее распространен
цикл FOR, имеющий следующий синтаксис:
FOR индекс IN [REVERSE]
нижняя_граница..верхняя_граница LOOP
последовательность команд;
END LOOP;
Здесь индекс (счетчик
циклов) изменяется от нижней до верхней границы с шагом 1, а при использовании
"REVERSE" - от верхней до нижней границы с шагом -1. Например,
FOR i IN 1..3 LOOP -- для
i = 1, 2, 3
последовательность команд;
-- цикл выполняется 3 раза
END LOOP;
FOR i IN REVERSE 1..3 LOOP
-- для i = 3, 2, 1
последовательность команд;
-- цикл выполняется 3 раза
END LOOP;
Если нижняя граница
равна верхней, последовательность выполняется один раз. Если нижняя граница
больше верхней, последовательность не выполняется, и управление переходит к
следующему за циклом оператору.
Пределы диапазона
цикла могут быть литералами, переменными или выражениями, но должны быть целыми
числами. Например, допустимы следующие диапазоны:
j IN -5..5
k IN REVERSE first..last
step IN 0..TRUNC(high/low) * 2
code IN ASCII('A')..ASCII('J')
Объявлять индекс
не нужно - он объявлен неявно как локальная переменная типа integer.
PL/SQL позволяет
определять диапазон цикла динамически во время выполнения. Например:
SELECT COUNT(kafedra) INTO
kaf_count FROM vyp_kaf;
FOR i IN 1..kaf_count LOOP
...
END LOOP;
Значение "kaf_count"
- неизвестно
при компиляции; предложение SELECT определяет это значение во время выполнения.
Индекс может использоваться
в выражениях внутри цикла, но не может изменяться.
Индекс определен
только внутри цикла и на него нельзя ссылаться снаружи цикла. После выполнения
цикла индекс неопределен.
Подобно PL/SQL блокам,
циклы могут быть помечены. Метка устанавливается перед оператором LOOP и её
имя может быть указано после соответствующего END
LOOP:
<<label_name>>
LOOP
последовательность команд;
END LOOP [label_name];
Помеченные циклы
используются для улучшения чтения программы (разборчивости).
С любой формой утверждения
EXIT можно завершать не только текущий цикл, но и любой внешний цикл. Для этого
маркируйте внешний цикл, который надо завершить, и используйте метку в утверждении
EXIT, следующим образом:
<<outer>>
LOOP
...
LOOP
...
EXIT outer WHEN ... -- завершаются
оба цикла
END LOOP;
...
END LOOP outer;
Если требуется преждевременно
выйти из вложенного цикла FOR, маркируйте цикл и используйте метку в утверждении
EXIT. Например:
<<outer>>
FOR i IN 1..5 LOOP
...
FOR j IN 1..10 LOOP
FETCH s1 INTO ShRec;
EXIT outer WHEN s1%NOTFOUND;
-- завершаются оба цикла
...
END LOOP;
END LOOP outer;
-- управление передается
сюда
4.5.2.4.
Операторы EXIT, EXIT-WHEN и NULL
EXIT используется
для завершения цикла, когда дальнейшая обработка нежелательна или невозможна.
Внутри цикла можно помещать один или большее количество операторов EXIT. Имеются
две формы EXIT: EXIT и EXIT-WHEN.
По оператору EXIT
цикл завершается немедленно и управление переходит к следующему за END LOOP
оператору.
По оператору EXIT-WHEN
цикл завершиться только в том случае, когда становится истинным условие в предложении
WHEN.
Оператор EXIT-WHEN
позволяет завершать цикл преждевременно. Например, следующий цикл обычно выполняется
десять раз, но как только не находится значение s1, цикл завершается независимо
от того сколько раз цикл выполнился.
FOR j IN 1..10 LOOP
FETCH s1 INTO ShRec;
EXIT WHEN s1%NOTFOUND; --
выход при отсутствии возвращаемой строки
...
END LOOP;
NULL - пустой оператор;
он передает управление к следующему за ним оператору. Однако, к нему может передаваться
управление и его наличие часто улучшает читаемость программы. Он также полезен
для создания фиктивных подпрограмм для резервирования областей определения функций
и процедур при отладке программ.
4.5.3.
Обработка ошибок
4.5.3.1.
Введение
Нельзя создать приложение,
которое будет безошибочно работать в любых ситуациях: возможны аппаратные сбои,
невыявленные ошибки приложения и ошибки из-за некорректных действий пользователей
приложения (клиентов). Если при этом программная ошибка произошла в блоке PL/SQL,
вложенном в другой блок, а тот, в свою очередь, вложен в третий блок и т.д.,
то она может дойти до клиентского приложения. Чтобы устранить возможную отмену
большого объема ранее выполненных операций и трафик из-за возвращаемых клиенту
ошибок, чтобы посылать клиенту точные сообщения о причине ошибки и способе ее
устранения (если она все же дошла до клиента), разработчики приложения должны
предусматривать возможные программные ошибки и создавать процедуры, адекватно
реагирующие на них.
В PL/SQL предусмотрен
механизмы перехвата и обработки ошибок, возникающих при выполнении программы.
Эти механизмы называются исключительными ситуациями. Когда программа обнаруживает
заданное условие ошибки, то вызывается соответствующая исключительная ситуация.
Обработки исключительных ситуаций в программе производится в разделе EXCEPTION
(см. п. 4.1).
При обнаружении
исключительной ситуации, обработка основного тела программы останавливается
и управление передается соответствующему обработчику исключительной ситуации,
который определяет дальнейшие действия.
В PL/SQL используются
следующие типы исключительных ситуаций:
4.5.3.2.
Встроенные исключительные ситуации
Oracle включает
четырнадцать встроенных исключительных ситуаций, соответствующих типовым ошибкам,
приведенным в следующей таблице:
Ошибка
Исключительная ситуация ORACLE
Описание
----------------------- --------
----------------------------------------------
CURSOR_ALREADY_OPEN ORA-06511
Попытка открытия уже открытого курсора
DUP_VAL_ON_INDEX ORA-00001
Попытка вставить дубликат значения для
уникального индекса
INVALID_CURSOR ORA-01001
Попытка выполнения запрещенной операции с кур-
сором (например, закрытие
неоткрытого курсора)
INVALID_NUMBER ORA-01722
Отказ преобразования строки символов в число
LOGIN_DENIED ORA-01017 Неправильное
имя пользователь/пароль
NO_DATA_FOUND ORA-01403 Предложение
SELECT...INTO возвращает ноль строк
NOT_LOGGED_ON ORA-01012 Нет
подключения к Oracle7
PROGRAM_ERROR ORA-06501 Внутренняя
ошибка PL/SQL
STORAGE_ERROR ORA-06500 Пакет
PL/SQL вышел из пределов памяти или если
память разрушена
TIMEOUT_ON_RESOURCE ORA-00051
Истекло время ожидания ресурса Oracle7
TOO_MANY_ROWS ORA-01422 Предложение
SELECT...INTO возвращает более
одной строки
TRANSACTION_BACKED_OUT ORA-00061
Удаленный сервер отменил транзакцию
VALUE_ERROR ORA-06502 Арифметическая
ошибка, ошибка преобразования,
усечения или ограничения
ZERO_DIVIDE ORA-01476 Попытка
деления на ноль
Если в раздел EXCEPTION
программы (блока) включена фраза
WHEN имя_исключения THEN
текст_обработчика_исключения;
с именем какого-либо встроенного
исключения и возникла соответствующая ошибка, то вместо прекращения исполнения
программы и выдачи типового сообщения об ошибке, будет исполняться созданный
пользователем текст обработчика исключения. Такой обработчик может, например,
выяснить ситуацию, при которой произошло деление на ноль, и выдать правдоподобный
результат операции деления или прервать исполнение программы и дать сообщение
об изменении каких-либо данных. В последнем случае это может быть не типовое
сообщение "Вы пытаетесь делить на ноль", а любое подготовленное пользователем
сообщение.
Для выдачи сообщения
об ошибке, обеспечения возврата в среду, из которой вызывалась текущая программа
(блок) и отмены всех действий, выполненных в текущей транзакции, целесообразно
использовать процедуру
RAISE_APPLICATION_ERROR(errnum,errtext);
где errnum - отрицательное
целое число в диапазоне от 20000 до 20999 и errtext - символьная строка длиной
до 2048 символов.
В приведенном ниже
триггере "uchins"
использованы два типа встроенных исключительных ситуаций: NO_DATA_FOUND и TOO_MANY_ROWS.
4.5.3.3.
Исключительные ситуации, определяемые пользователем
Кроме встроенных
могут быть использованы собственные исключительные ситуации, имена которых необходимо
описать в разделе DECLARE блока PL/SQL (например, err_nachalo
EXCEPTION). В разделе EXCEPTION блока должен быть описан соответствующий обработчик
исключительной ситуации, например
WHEN err_nachalo THEN RAISE_APPLICATION_ERROR(-20013,
'Дата начала должна быть
больше '||to_char(nach));
В теле основной
программы определяемые пользователем ошибки обычно проверяются с помощью операторов
условия (IF...THEN). Для передачи управления обработчику пользовательской исключительной
ситуации в случае обнаружения ошибки используется оператор
RAISE имя_пользовательского_исключения
Например
IF :new.nachalo <>
kon + 1 THEN
RAISE err_nachalo;
END IF;
4.5.3.4.
Обработчик OTHERS
Если исключительная
ситуация явно не обрабатывается в блоке и для ее перехвата не используется обработчик
OTHERS, то PL/SQL отменяет выполняемые блоком транзакции и возвращает необработанную
исключительную ситуацию обратно в вызывающую среду.
Обработчик особых
ситуаций OTHERS описывается последним в блоке для перехвата всех исключительных
ситуаций, которые не были описаны в этой программе блоке. Он может иметь вид:
WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20011,'Какая-то
другая ошибка');
4.5.4.
Курсоры
4.5.4.1. Связь объектов
PL/SQL с таблицами базы данных
Чтобы программа
PL/SQL могла работать с информацией, содержащейся в базах данных, необходимо
организовать обмен между значениями столбцов таблиц баз данных и переменными
PL/SQL.
Известно, что для
выбора информации из таблиц используется SQL предложение SELECT. При его выполнении
Oracle создает специальную рабочую область, содержащую информацию о самом SELECT,
данные, которые требуются для его выполнения (например, результаты подзапросов),
и, наконец, окончательный результат выполнения SELECT. PL/SQL имеет несколько
механизмов доступа к этой рабочей области. Одним из них является курсор, с помощью
которого можно присвоить имя этой рабочей области и манипулировать содержащейся
в ней информацией, последовательно выбирая строки результата и пересылая значения
столбцов текущей строки в переменные PL/SQL. Существуют и другие механизмы,
не требующее создания явного курсора.
4.5.4.2. Явный курсор
Курсор - это средство
языка SQL, позволяющее с помощью команд OPEN, FETCH и CLOSE получить построчный
доступ к результату запроса к базе данных. (Будем также называть курсором и
сам набор строк, полученный в результате выполнения запроса.)
Для использования
курсора его надо сначала объявить, т.е. дать ему имя и указать (с помощью предложения
SELECT), какие столбцы и строки базовых таблиц должны быть помещены в набор
строк, названный этим именем. Команда OPEN инициализирует получение указанного
набора и установку перед первой его строкой указателя текущей строки. Команда
FETCH служит для установки указателя текущей строки на следующую запись (первый
раз на строку с номером 1) и выборки из текущей строки курсора значений указанных
столбцов с пересылкой их в переменные PL/SQL. (Выполнением FETCH в цикле можно
последовательно выбрать информацию из всех строк курсора.) Наконец, команда
CLOSE позволяет закрыть (удалить из памяти) набор строк (при этом описание курсора
сохраняется и его можно снова открыть командой OPEN).
Существует модификация
("Курсор в цикле FOR"), позволяющая организовать последовательный
выбор строк объявленного курсора без явного использования команд OPEN, FETCH
и CLOSE.
4.5.4.2.1. Объявление
курсора
Перед работой с
курсором его следует объявить в разделе DECLARE или другом допустимом разделе,
используя синтаксис:
CURSOR cursor_name
[ (parameter [, parameter, ... ] ) ] IS SELECT ... ,
где
cursor_name
- имя курсора, формируемое по правилам, описанным, например, в п. 2.4.4.1;
SELECT ...
- предложение SELECT, определяющее строки курсора;
parametr
- имеет следующий синтаксис:
variable_name
[IN] type_name [ { := | DEFAULT } value ] ,
а type_name - любой
тип (подтип) данных PL/SQL без указания ограничений (например, длины символьных
значений).
Формальные параметры
курсора используются только для передачи значений в WHERE фразу предложения
SELECT с целью отбора нужных строк запроса. Передача таких значений производится
во время открытия курсора командой OPEN. Если значения формальных параметров
отсутствуют в команде OPEN и не заданы по умолчанию (:= value или DEFAULT value),
то выдается ошибка. При наличии тех и других используются параметры из команды
OPEN.
В следующем примере
(где для заданного начала учебного года, номера курса и набора специализаций
надо получить строки, содержащие номер специальности, форму обучения и номер
плана) использованы оба способа задания значений по умолчанию параметрам курсора:
CURSOR sp (spec VARCHAR2
:= '2201', kur INTEGER DEFAULT 1,
nach DATE := '1.9.1998')
IS
SELECT special||' '||RPAD(forma,8)||'
'||imia_otd sfo,nom_plan
FROM vyp_kaf v, otdely o
WHERE v.kafedra = o.otdel
AND special LIKE spec||'%' AND kurs = kur
AND nachalo = nach AND variant
= 'Осн';
4.5.4.2.2. Открытие
курсора (OPEN)
Команда OPEN имеет
следующий синтаксис
OPEN cursor_name
[ (value [,value]...) ];
где список значений (value,
value, ...) используется для передачи параметров курсора и должен по числу и
типу данных совпадать с описанием этих параметров.
Команда выполняет
объявленный в курсоре SELECT ... , используя (если есть параметры) передаваемые
из OPEN значения или значения, указанные при объявлении курсора, создавая набор
строк и устанавливая указатель текущей строки перед первой из них. Так, по команде
OPEN sp; будет создан набор:
sfo nom_plan
Указатель --> -----------------------------------------------------------
------
220100 вечерняя Кафедра
вычислительной техники 15
220100 дневная Кафедра вычислительной
техники 16
220100 дневная Кафедра информатики
и прикладной математики 83
где использовались значения
параметров, заданные при описании.
По команде OPEN
sp ('2201',4,'1.9.1998'); будет
создан другой набор:
sfo nom_plan
Указатель --> -----------------------------------------------------------
------
220100 вечерняя Кафедра
вычислительной техники 22
220111 дневная Кафедра вычислительной
техники 30
220112 дневная Кафедра вычислительной
техники 39
220109 дневная Кафедра информатики
и прикладной математики 92
4.5.4.2.3. Выборка
строк из курсора (FETCH)
Команда FETCH, используемая
для продвижения на один шаг указателя текущей строки курсора и пересылки ее
значений в переменные или запись, имеет следующий синтаксис:
FETCH cursor_name
INTO {variable_name1[,variable_name2]...} | record_name ;
Для каждого значения
столбца, возвращенного запросом, в списке INTO должна иметься переменная или
поле записи соответствующего типа данных. Такие переменные или записи должны
быть заранее описаны в декларативной части блока PL/SQL. Например
DECLARE
spec VARCHAR2(6); kur INTEGER;
nach DATE;
CURSOR sp (spec VARCHAR2
:= '2201', kur INTEGER DEFAULT 1,
nach DATE := '1.9.1998')
IS
SELECT special||' '||RPAD(forma,8)||'
'||imia_otd sfo,nom_plan
FROM vyp_kaf v, otdely o
WHERE v.kafedra = o.otdel
AND special LIKE spec||'%' AND kurs = kur
AND nachalo = nach AND variant
= 'Осн';
sp_sfo VARCHAR2(88); --
переменная для хранения значения sfo
sp_np INTEGER; -- переменная
для хранения значения nom_plan
BEGIN
OPEN sp ('2201',4,'1.9.1998');
LOOP
FETCH sp INTO sp_sfo, sp_np;
EXIT WHEN sp%NOTFOUND; --
выход при отсутствии возвращаемой строки
-- (см. п. 4.5.4.2.6)
DBMS_OUTPUT.PUT_LINE(sp_sfo||'
'||TO_CHAR(sp_np));
END LOOP;
CLOSE sp; -- команда закрытия
курсора (см. п. 4.5.4.2.4)
END;
/
В цикле можно использовать
значения переменных sp_sfo
и sp_np,
которые после открытия курсора равны 220100
вечерняя Кафедра вычислительной техники?
и 22?,
после первого прохождения цикла - 220100
дневная Кафедра вычислительной техники?
и 30? и
т.д.
4.5.4.2.4. Закрытие
курсора (CLOSE)
Команда CLOSE используется
для освобождения всех ресурсов, которые поддерживались открытым курсором (при
этом описание курсора сохраняется и его можно снова открыть командой OPEN).
Синтаксис команды CLOSE имеет вид:
CLOSE cursor_name;
4.5.4.2.5. Использование
курсора в цикле FOR
В большинстве ситуаций,
которые требуют явного курсора, текст программы может быть упрощен при использовании
"курсора в цикле FOR", заменяющего команды OPEN, FETCH и CLOSE. Курсор
в цикле FOR:
-
неявно объявляет индекс
цикла записью, поля которой соответствуют столбцам (псевдонимам) предложения
SELECT ... из описания курсора;
-
передает параметры курсора
(если они есть) и открывает курсор;
-
выбирает в цикле строки
из полученного набора в индекс цикла (поля записи);
-
закрывает курсор после
обработки всех строк набора или досрочному выходу из него с помощью команд
EXIT или GOTO.
Синтаксис курсора
в цикле FOR имеет вид:
FOR var_rec_name
IN cursor_name [ (value [,value]...) ] LOOP
ТЕЛО ЦИКЛА
END LOOP;
где
var_rec_name
- индекс цикла, в котором при первом прохождении цикла хранится первая строка
набора, при втором прохождении цикла вторая строка и т.д.;
(value [,value]...)
- список значений, используемый для
передачи параметров курсора (он заменяет в данном случае список из команды OPEN);
ТЕЛО ЦИКЛА
- содержит нужные строки повторяющейся части программы, в которых используются
переменные с именами var_rec_name.column_name, а column_name имя столбца из
перечня столбцов предложения SELECT в описании курсора.
Вот как сокращается
текст предыдущего анонимного блока при использовании цикла FOR:
DECLARE
spec VARCHAR2(6); kur INTEGER;
nach DATE;
CURSOR sp (spec VARCHAR2
:= '2201', kur INTEGER DEFAULT 1,
nach DATE := '1.9.1998')
IS
SELECT special||' '||RPAD(forma,8)||'
'||imia_otd sfo,nom_plan
FROM vyp_kaf v, otdely o
WHERE v.kafedra = o.otdel
AND special LIKE spec||'%' AND kurs = kur
AND nachalo = nach AND variant
= 'Осн';
BEGIN
FOR sp_rec IN sp ('2201',4,'1.9.1998')
LOOP
DBMS_OUTPUT.PUT_LINE(sp_rec.sfo||'
'||TO_CHAR(sp_rec.nom_plan));
END LOOP;
END;
/
4.5.4.2.6. Атрибуты
явного курсора
Для анализа состояния
курсора используются специальные переменные, имена которых составляются из имени
курсора и суффиксов %FOUND, %NOTFOUND, %ROWCOUNT и %ISOPEN, называемых атрибутами
курсора. Если курсор назван "cursor_name", то эти переменные имеют
имена:
cursor_name%NOTFOUND, cursor_nane%FOUND,
cursor_nane%ROWCOUNT и cursor_nane%ISOPEN.
Значения таких переменных
анализируются при выполнении программы с помощью различных операторов управления
(IF...THEN, EXIT WHEN и т.п.), которые изменяют (при необходимости) ход выполнения
программы. Следует отметить, что ссылка на эти переменные до открытия курсора
приводит к появлению сообщения INVALID_CURSOR.
Переменная с атрибутом
%ISOPEN позволяет определить, открыт ли курсор. Если он открыт то эта переменная
возвращает TRUE, иначе - FALSE. Например:
IF NOT sp%ISOPEN THEN --
курсор не открыт ?
OPEN sp; -- открыть курсор
!
IF END;
FETCH ...
Переменные с %NOTFOUND
и %FOUND атрибутами показывают состояние текущей позиции курсора (перед первой
выборкой строки курсора обе переменных имеют значение NULL). Переменная с %NOTFOUND
принимает значение FALSE тогда, когда выборка возвратила строку (при этом переменная
с %FOUND принимает значение TRUE). Если же в результате выборки строка не возвращается,
то переменные с %NOTFOUND и %FOUND принимают значения TRUE и FALSE, соответственно.
Пример использования %NOTFOUND был рассмотрен в п. 12.3.1.3.
Переменная с атрибутом
%ROWCOUNT содержит количество строк, выбранных из курсора на текущий момент
(при открытии курсора эта переменная содержит ноль). В следующем примере переменная
s1%ROWCOUNT ограничивает выборку из курсора s1 десятью строками:
LOOP
FETCH sp INTO sp_sfo, sp_np;
IF sp%ROWCOUNT > 10 THEN
...
END IF;
...
END LOOP;
4.5.4.2.7. Изменение
или удаление текущей строки курсора
Существует два предложения,
позволяющие изменить или удалить ту строку
таблицы базы данных, на которую позиционирована
текущая строка курсора:
UPDATE [schema.]{table |
view}[@dblink] [alias]
SET { (column [, column]
...) = (subquery)
| column = { expr | (subquery)
} }
[, { (column [, column]
...) = (subquery)
| column = { expr | (subquery)
} } ] ...
WHERE CURRENT OF cursor_name;
DELETE [FROM] [schema.]{table
| view}[@dblink] [alias]
WHERE CURRENT OF cursor_name;
Для этого необходимо,
чтобы при объявлении курсора предложение SELECT ...
содержало фразу:
FOR UPDATE OF [[schema.]{table
| view}.]column
[, [[schema.]{table | view}.]column
] ... ;
в которой следует привести
список обновляемых столбцов.
4.5.4.3. Неявный
курсор (SQL курсор)
Для всех команд
языка SQL, не связанных с объявлением курсора (явным курсором?), PL/SQL открывает
курсор (неявный курсор?), на который можно ссылаться по курсорному имени SQL%.
При работе с таким курсором нельзя использовать команды OPEN, FETCH и CLOSE,
но можно использовать атрибуты курсора, чтобы получить информацию о текущем
его состоянии.
4.5.4.3.1. SELECT
... INTO
В тех случаях, когда
программе необходимо иметь значения столбцов из одной строки таблицы, можно
воспользоваться предложением SELECT ... INTO, формат которого имеет вид:
SELECT [DISTINCT | !!under!!ALL]
{ [schema.]{table | view
| snapshot}.expr [c_alias] }
[, { [schema.]{table | view
| snapshot}.expr [c_alias] } ] ... }
INTO { variable_name [,
variable_name ] ... } | record_name
FROM table_list [WHERE condition]
[GROUP BY expr [, expr]
...] [HAVING condition]
[ {UNION | UNION ALL | INTERSECT
| MINUS} SELECT command]
[ORDER BY {expr | c_alias
| position}
[!!under!!ASC | DESC] [,
{expr | c_alias | position}
[!!under!!ASC | DESC]] ]...
[FOR UPDATE [OF [[schema.]{table
| view}.]column
[, [[schema.]{table | view}.]column]
...]
[NOWAIT] ]
Практически это
обычный SELECT, выполняющий присвоение выбираемых значений столбцов переменным,
перечисленным во фразе INTO. Однако такое присвоение происходит только в том
случае, если "WHERE condition" обеспечивает возвращение по запросу
лишь одной строки и переменные заранее описаны в декларативной части блока PL/SQL.
4.5.4.3.2. UPDATE,
DELETE и INSERT
Эти предложения
отличаются от аналогичных предложений интерактивного SQL лишь тем, что в их
выражениях (expr) могут использоваться переменные PL/SQL.
4.5.4.3.3. Атрибуты
неявного курсора (SQL курсора)
Для анализа результата
выполнения предложений SELECT...INTO, INSERT, UPDATE и DELETE используются три
переменные: SQL%NOTFOUND, SQL%FOUND и SQL%ROWCOUNT (Oracle закрывает SQL курсор
сразу после выполнения SQL предложения, что делает бессмысленным использование
переменной SQL%ISOPEN, так как ее значение всегда равно FALSE).
Перед выполнением
предложений SELECT...INTO, INSERT, UPDATE и DELETE переменные SQL%NOTFOUND и
SQL%FOUND имеют значение NULL. Переменная SQL%NOTFOUND принимает значение TRUE,
если INSERT, UPDATE и DELETE не произвели изменений таблиц базы данных или SELECT...INTO
не возвратил строк (при этом переменная SQL%FOUND принимает значение FALSE).
В противном случае переменная SQL%NOTFOUND принимает значение FALSE, а переменная
SQL%FOUND - TRUE
Вот один из примеров
использования SQL%NOTFOUND для добавления новой строки в таблицу temp при сбое
модификации:
UPDATE vedomost SET ocenka
= 'отл', data = '27.1.1999'
WHERE nomer = 10123 AND
plan = 1567;
IF SQL%NOTFOUND THEN -- изменение
не выполнено
INSERT INTO ved_izm SELECT
...;
END IF;
4.6.
Триггера
4.6.1. Назначение триггера
и синтаксис
Триггер базы данных
- это хранимый блок PL/SQL, который ассоциирован с указанной в нем таблицей
и автоматически запускается, когда производится модификация этой таблицы. Он
может быть создан для запуска при выполнении только одного из предложений INSERT,
UPDATE или DELETE,
а также при выполнении любых двух или всех трех предложений.
Команда для создания
(CREATE) или замены (CREATE OR REPLACE) триггера имеет вид:
CREATE [OR REPLACE] TRIGGER
[schema.]trigger
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE
[OF column [, column] ...]}
[OR {DELETE | INSERT | UPDATE
[OF column [, column] ...]}] ...
ON [schema.]table
[ [REFERENCING { OLD [AS]
old [NEW [AS] new]
| NEW [AS] new [OLD [AS]
old] } ]
[ FOR EACH ROW ]
[WHEN (condition)] ]
pl/sql_block
где
[schema.]trigger
- имя триггера, где необязательный параметр
schema, идентифицируюет схему,
в которой должен быть создан этот триггер;
ON [schema.]table
- используется
для указания имени таблицы, для которой создается триггер (необязательный
параметр schema,
идентифицируюет схему, в которой находится эта таблица);
INSERT, DELETE
или UPDATE - SQL-предложение,
при выполнении которого должен запуститься
триггер; с помощью связки OR можно
запускать один и тот же триггер при выполнении любых двух или всех трех предложений,
связанных с вводом в таблицу новых строк, их удалением и (или) изменением;
[OF column [, column]
...]- может использоваться вместе
с предложением UPDATE для указания перечня столбцов, изменение любого из которых
должно запустить триггер (по умолчанию триггер будет запускаться при
изменении любого столбца таблицы);
BEFORE -
триггер должен запускаться перед выполнением соответствующего SQL-предложения,
т.е. из тела триггера можно увидеть? таблицу, в которую еще не введена новая
строка или не произведено удаление (изменение) существующей;
AFTER -
триггер должен запускаться после
выполнения соответствующего SQL-предложения,
т.е. из тела триггера можно увидеть? таблицу, в которую уже введена новая строка
или уже произведено удаление (изменение) существующей (если при выполнении тела
триггера будет обнаружено, что строка не должна быть введена, удалена или изменена,
то таблица будет возвращена в состояние, которое существовало до выполнения
триггера);
[ FOR EACH ROW
] - этот параметр
устанавливается для триггера строчного типа (который должен запускаться
в процессе ввода (удаления, изменения) каждой строки таблицы; при отсутствии
FOR EACH ROW создается триггер операторного
типа, запускаемый только один раз, независимо от того, сколько строк
должно быть введено (удалено, изменено)
при выполнении заданного SQL-предложения;
[ [REFERENCING { OLD
[AS] old [NEW [AS] new] | NEW [AS] new [OLD [AS] old] } ] -
используется для изменения заданных по умолчанию префиксов NEW и
OLD, которые указываются перед именами столбцов, новые или старые значения которых
должны использоваться в тексте тела строчного триггера; естественно, что при
выполнении предложения INSERT во вводимой строке существуют только новые значения
(например, NEW.Фамилия при вводе данных в таблицу Фамилии или Студенты), при
выполнении предложения DELETE - только старые значения, а при выполнении UPDATE
- и те и другие; REFERENCING используется
тогда, когда имя столбца должно уточняться именем таблицы, которую назвали NEW
или OLD и возникает неоднозначность
в описании столбца;
[WHEN (condition)] -
используется для отбора тех вводимых, удаляемых или изменяемых строк, которые
должны вызвать запуск строчного триггера (по умолчанию строчный триггер запускается
при вводе, удалении или изменении каждой строки соответствующей таблицы);
pl/sql_block
- тело триггера, содержащее все компоненты, которые допускаются при создании
хранимых процедур.
Для удаления описания
триггера и его перекомпиляции используются команды:
ALTER TRIGGER
[schema.]trigger
и
DROP TRIGGER [schema.]trigger
Создадим триггер
для формирования номера нового студента в момент ввода данных о нем в таблицу
Студенты, а также для исправления некорректных значений его фамилии и имени
с помощью рассмотренной выше функции fio (п.
4.4).
CREATE OR REPLACE TRIGGER studins
BEFORE INSERT ON Студенты
FOR EACH ROW
DECLARE
err_fam EXCEPTION; err_im EXCEPTION;
BEGIN
:new.Фамилия := fio(:new.Фамилия); --
фамилия замещается результатом
-- работы функции fio
:new.Имя := fio(:new.Имя); -- имя замещается
результатом работы
-- функции fio
IF :new.Фамилия = '0' THEN RAISE err_fam;
END IF; -- выход по ошибке
-- при неправильном написании фамилии
IF :new.Имя = '0' THEN RAISE err_im; END
IF; -- выход по ошибке
-- при неправильном написании имени
-- Формирование нового номера обучаемого
SELECT MAX(Номер)+1 INTO :new.Номер FROM
Студенты;
EXCEPTION -- начало обработчика исключений
основной программы
WHEN err_fam THEN RAISE_APPLICATION_ERROR(-20040,
'Фамилия должна состоять только из букв
русского алфавита, '||
'пробела, дефиса и начинаться с заглавной
буквы !');
WHEN err_im THEN RAISE_APPLICATION_ERROR(-20041,
'Имя должно состоять только из букв русского
алфавита, '||
'пробела, дефиса и начинаться с заглавной
буквы !');
WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20999,
'Какая-то другая ошибка');
END studins;
/
Номер студента
лучше получать используя Последовательность, т.е. объект Oracle, предназначенный
для генерации уникальных целых чисел.
Для создания последовательностей
используется команда CREATE SEQUENCE, урезанный синтаксис которой имеет вид:
CREATE SEQUENCE
[schema.]sequence
[INCREMENT BY
integer]
[START WITH integer];
где
schema -
схема, в которой создается последовательность (если она опущена, то последовательность
создается в схеме текущего пользователя);
sequence
- имя создаваемой последовательности;
INCREMENT BY
- задает интервал между значениями последовательности; это значение может быть
любым (отличным от нуля) положительным или отрицательным целым числом (для возрастающей
или убывающей последовательности); по умолчанию принимается интервал равный
1;
START WITH
- определяет первый генерируемый номер последовательности (по умолчанию он равен
1).
Для выбора следующего
уникального значения последовательности используется псевдостолбец sequence.NEXTVAL,
а для текущего - sequence.CURRVAL .
Если создать последовательность
с именем Ном_студ:
CREATE SEQUENCE
Ном_студ
INCREMENT BY 1
START WITH 10000;
то команда
SELECT MAX(Номер)+1
INTO :new.Номер FROM Студенты;
в триггере studins может
быть изменена на команду
SELECT Ном_студ.NEXTVAL
INTO :new.Номер FROM Студенты;
Создадим еще два
триггера, срабатывающие перед удалением и перед изменением строки той же таблицы
Студенты:
-- Строчный триггер срабатывающий перед
удалением строки из таблицы Студенты
CREATE OR REPLACE TRIGGER studdel
BEFORE DELETE ON Студенты
FOR EACH ROW
DECLARE
test INTEGER; err_studdel EXCEPTION;
BEGIN
-- Проверка существования удаляемого номера
студента
SELECT COUNT(Номер) INTO test FROM Студенты
WHERE Номер = :old.Номер;
IF test = 0 THEN RAISE err_studdel; END
IF;
EXCEPTION
WHEN err_studdel THEN RAISE_APPLICATION_ERROR(-20022,
'Нет студента с указанным номером !');
WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20999,
'Какая-то другая ошибка');
END studdel;
/
--=================================================================
-- Строчный триггер срабатывающий перед
изменением строки в таблице Студенты
CREATE OR REPLACE TRIGGER studupd
BEFORE UPDATE ON Студенты
FOR EACH ROW
DECLARE
err_nom EXCEPTION; err_fam EXCEPTION;
err_im EXCEPTION;
BEGIN
IF :new.Номер <> :old.Номер THEN
RAISE err_nom; END IF;
:new.Фамилия := fio(:new.Фамилия);
:new.Имя := fio(:new.Имя);
IF :new.Фамилия = '0' THEN RAISE err_fam;
END IF;
IF :new.Имя = '0' THEN RAISE err_im; END
IF;
EXCEPTION -- начало обработчика исключений
основной программы
WHEN err_nom THEN RAISE_APPLICATION_ERROR(-20044,
'Номер нельзя изменять !');
WHEN err_fam THEN RAISE_APPLICATION_ERROR(-20040,
'Фамилия должна состоять только из букв
русского алфавита, '||
'пробела, дефиса и начинаться с заглавной
буквы !');
WHEN err_im THEN RAISE_APPLICATION_ERROR(-20041,
'Имя должно состоять только из букв русского
алфавита, '||
'пробела, дефиса и начинаться с заглавной
буквы !');
WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20999,
'Какая-то другая ошибка');
END studupd;
/
4.7.
Пакеты
CREATE [OR REPLACE] PACKAGE
[schema.]package
{IS | AS} pl/sql_package_spec
CREATE [OR REPLACE] PACKAGE
BODY [schema.]package
{IS | AS} pl/sql_package_body
ALTER PACKAGE [schema.]package
COMPILE [!!under!!PACKAGE | BODY] ;
DROP PACKAGE [BODY] [schema.]package
;
|
