Погодаев, Дозоров — Разр. структур


Чтобы посмотреть этот PDF файл с форматированием и разметкой, скачайте его и откройте на своем компьютере.


А.К. Погодаев

В.В. Дозоров








Разработка информационных структур


Учебное пособие











Липецк

2002


-
1
-

ЛИПЕЦКИЙ ЭКОЛОГО
-
ГУМАНИТАРНЫЙ ИНСТИТ
УТ







А.К. Погодаев

В.В. Дозоров





Программное обеспечение

автоматизированных систем.

Разработка
информационной структуры


Учебное пособие









Липецк

2002

-
2
-



ББК 22.18

УДК 681.3:62
-
52


П50



Погодаев А.К., Дозоров В.В.

Программное обеспечение автоматизированных
систем. Разработка информационной структуры: Учебное пособие


Липецк:
ЛЭГИ,
2002.


52 с.


В пособии изложены общие сведения о реляционных СУБД и их практич
е
ском применении на примере СУБД
Oracle

8 с описанием элементов программир
о
вания в еѐ среде. Приведены задания для выполнения лабораторных работ по дисц
и
плине ©Организация баз
данныхª. Изложение сопровождается поясняющими пр
и
мерами.

Утверждено и рекомендовано РИС ЛЭГИ и предназначено для студентов
специальностей ©Информационные системы в экономикеª, ©Автоматизированные
системы обработки информации и управленияª, ©Прикладная мате
матикаª.


Библиогр.: 7 назв.




Рецензент:
В.В. Ведищев

кандидат технических наук,

доцент кафедры автоматизированных систем управления ЛГТУ















© Липецкий эколого
-
гуманитарный институт, 2002


-
3
-


ОГЛАВЛЕНИЕ



Введение
…………………………………………………………………

4


1. Инфологическое проектирование баз данных
………………..
5

1.1.

У
РОВНИ АБСТРАКЦИИ И

ЭТАПЫ ПРОЕКТИРОВАНИ
Я
АИС

.............

5

1.2.

И
НФОРМАЦИОННО
-
ЛОГИЧЕСКАЯ МОДЕЛЬ

................................
........

5

1.3.

О
БЪЕДИНЕНИЕ МОДЕЛЕЙ

ЛОКАЛЬНЫХ ПРЕДСТАВЛ
ЕНИЙ

.............

10

1.4.

П
РЕОБРАЗОВАНИЕ ИНФОЛ
ОГИЧЕСКОЙ МОДЕЛИ В

РЕЛЯЦИОННЫЕ


ОТНОШЕНИЯ

................................
................................
.....................

15

1.5.

З
АДАНИЕ ДЛЯ

ПРАКТИЧЕСКОЙ РАБОТЫ

................................
.........

17

1.6.

П
РИМЕР ВЫПОЛНЕНИЯ РА
БОТЫ

................................
.......................

18


К
ОНТРОЛЬНЫЕ ВОПРОСЫ

................................
................................

22


2.
Ф
изическая реализация модели
…………………..……………
23

2.1.

Х
АРАКТЕРИСТИКА
СУБД

O
RACLE

................................
.................

23

2.2.

SQL*P
LUS

................................
................................
........................

23

2.3.

О
БЩ
ИЕ СВЕДЕНИЯ О
SQL

................................
..............................

25

2.3.1. Элементы языка SQL

................................
..............................

27

2.3.2. Основные
операторы SQL

................................
.....................

29

2.3.3. Работа с
таблицами
................................
................................
..

31

2.4.

З
АДАНИЕ ДЛЯ ПРАКТИЧ
ЕСКОЙ РАБОТЫ

................................
.........

34

2.5.

П
РИМЕР ВЫПОЛНЕНИЯ Р
АБОТЫ

................................
......................

35


К
ОНТРОЛЬНЫЕ

ВОПРОСЫ

................................
...............................

38


3. Использование программных единиц PL/SQL
…………...….
39

3.
1.

О
СНОВЫ
PL/SQL

................................
................................
.............

39

3.2.

И
СПОЛЬЗОВАНИЕ ПРОГРА
ММНЫХ ЕДИНИЦ
PL/SQL

.....................

46

3.3.

З
АДАНИЕ ДЛЯ ПРАКТИЧ
ЕСКОЙ РАБОТЫ

................................
.........

49

3.4.

П
РИМЕР ВЫПОЛНЕНИЯ Р
АБОТЫ

................................
......................

50


К
ОНТРОЛЬНЫЕ ВОПРОСЫ

................................
................................

51


Библиографический список
……………………………………..….
51



-
4
-

ВВЕДЕНИЕ



В главе 1 приведены уровни абстракции, используемые для описания
баз данных, что позволит изучить принципы построения информационно
-
логической инфологической модели п
ри проектировании автоматизирова
н
ных информационных систем (АИС), освоить правила перевода инфолог
и
ческой модели в реляционные отношения. Изложены принципы построения
информационно
-
логических моделей баз данных. Приведены правила пер
е
вода инфологической мо
дели в реляционные отношения. Представлены
принципы объединения локальных представлений баз данных.

Глава 2 дает общее представление о системе управления реляцио
н
ными базами данных
Oracle
. В главе изложены основы языка
SQL
, а также
общие принципы работы в интерактивной среде
SQL
*
Plus
. Приведены пр
и
меры команд для работы с объектами базы данных.

В главе 3 содержит информацию о создании программных единиц на
языке
PL
/
SQL
. Представлены варианты использования программных ед
и
ниц

в структуре базы данных.


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


-
5
-

1. ИНФОЛОГИЧЕСКОЕ
ПРОЕКТИРОВАНИЕ

БАЗ ДАННЫХ


1.1.

Уровни а
бстракции и этапы проектирования АИС


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

В подходе, разработанном группой
ANSI
/
SPARC
, рассматривается
трехуро
вневое представление данных:











Внешние представления соответствуют представлениям о предме
т
ной области (ПО) и отображают
информационные потребности локальных
пользователей. Внешние представления существуют вне связи со средств
а
ми реализации автоматизированных информационных систем (АИС) и не
затрагивают вопроса о представлении данных в ЭВМ.

Концептуальное представление данны
х является совокупностью всех
требований к данным, полученной из пользовательских внешних предста
в
лений.

Внутренний уровень абстракций соответствует физической организ
а
ции БД и размещению ее на внешних запоминающих устройствах.

На основе анализа информацио
нных потребностей пользователей а
д
министратор БД создает мысленную модель ПО, формализация которой
приводит к двум видам моделей:

1)

информационно
-
логической (инфологической);

2)

даталогической.

Инфологическая модель ориентирована на пользователя, даталогич
е
ская



на реализацию в вычислительной среде.


1.2.

Информационно
-
логическая модель


Информационно
-
логическое (инфологическое) проектирование АИС
заключается в установлении соответствия между состоянием предметной
Внешнее

представление 1

Внешнее

представление
n

Концептуальное
представление

Внутреннее


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

-
6
-

области и ее описанием данными.

Средства,
предназначенные для инфологического моделирования,
должны удовлетворять следующим требованиям:

1.

Язык спецификаций должен быть понятен заказчику и не содержать п
а
раметры реализаций информационных систем;

2.

Инфологическая схема должна содержать все сведения о П
О, необх
о
димые для последующих этапов проектирования (включая количес
т
венные параметры, требования процессов обработки данных и т.д.);

3.

Инфологическая модель ПО должна легко преобразовываться в модели
данных распространенных СУБД.

На этапе инфологического п
роектирования информационная модель
предметной области не связана со средствами реализации АИС. Она опр
е
деляет особенности предметной области и информационные потребности
разрабатываемой системы. Для этого используется диаграмма ©сущность
-
связьª. Диаграмма

имеет следующие составные части:
атрибут,

сущность,
связь
.

Атрибут



логический неделимый элемент структуры информации.

Сущность



тип характеризуемого, реально существующего объекта,
описываемого атрибутами (это может быть личность, место, вещь и т.д.)
.
Тогда экземпляр сущности


реализация типа в конкретных значениях атр
и
бута. Например, СЛУЖАЩИЙ

может быть сущность; ФИО,

Отдел,

Год_рождения


атрибуты; ©Васьков Л.П.ª, ©научно


производственныйª,
©1965ª


экземпляр сущности.

Связь



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


Классификация бинарных связей по степени связи


1
). Отображение 1:1 (связь ©один
-
к
-
одномуª).





1

1



С помощью отображения 1:1 определяют такой тип связи между т
и
пами сущностей А и В, когда каждому экземпляру сущности А соответств
у
ет один экземпляр сущности В и наоборот.

Идентификация экземпляров сущностей уникальна в обоих напра
в
лениях.



ДИРЕКТОР

ПРЕДПРИЯТИЕ

-
7
-

2). Отображение 1:М (связь ©один
-
ко
-
многимª).







1

М



С помощью отображения 1:М определяется тип связи между тип
ами
сущностей А и В, когда одному экземпляру сущности А может соответств
о
вать 0, 1 или несколько экземпляров сущности типа В, а каждому экземпл
я
ру типа В соответствует только один экземпляр сущности типа А.

Идентификация экземпляров уникальна только в нап
равлении от
М
-
связанной сущности к 1
-
связанной сущности.


3). Отображение М:1 (связь ©многие
-
к
-
одномуª).






М


1



отображение является обратным отображению 1:М.


4). Отображение М:N (связь ©многие
-
ко
-
многимª).






М


N




С помощью отображения М:N определяется тип связи между сущн
о
стями А и В, когда каждому экземпляру сущности А может соответствовать
0, 1 или несколько экземпляров сущности В и наоборот.

Идентификация экземпляров сущностей неун
икальна в обоих н
а
правлениях.

В некоторых случаях целесообразно рассматривать однонаправле
н
ные связи:








1








М




Класс принадлежности



характеристика, которая определяет обяз
а
тельность связей между

экземплярами сущностей типов A и B.



ФАКУЛЬТЕТ

КАФЕДРА

СТУДЕНТ

ГРУППА

ПАЦИЕНТ

ЗАБОЛЕВАНИЕ

СЛУЖАЩИЙ

ОТДЕЛ

ГОРОД

РАЙОН

-
8
-

Классификация связей по классу принадлежности:


1)
обязательный класс принадлежности обоих типов сущностей

2) класс принадлежности сущности типа А обя
зательный, сущности
типа В необязательный

3) класс принадлежности обоих типов сущностей необязательный

При построении диаграммы ©сущность
-
связьª используются сл
е
дующие операции.

Формулирование сущностей

При формулировании сущностей указываются типы объектов пре
д
метной области, о которых в АИС будет накапливаться информация.

Выбор
идентифицирующего атрибута

Для каждой сущности необходимо указать идентификатор, служащий
для однозначного распознавания экземпляров сущности. В качестве иде
н
тификатора служит один атрибут или совокупность из нескольких атриб
у
А


В


А


В


А


В



1:1




1:М




М:N



А


В


А


В


А


В



1:1


. 1:М




М:N


А


В


А


В


А


В




1:1




1:М




М:N



-
9
-

тов. Набор значений этих атриб
утов уникален, то есть значения идентиф
и
катора находятся во взаимнооднозначном соответствии с экземплярами
сущности. В АИС идентифицирующие атрибуты называют
первичными
ключами
.
Первичный ключ может быть простым (состоящим из одного
атрибута) и составным (
состоящим из двух и более атрибутов). Если для
описания сущности выбрана совокупность атрибутов, не содержащая кл
ю
ча, то в состав атрибутов вводится специальный атрибут, который выступ
а
ет в качестве первичного ключа.

Назначение сущностям описательных атриб
утов

Выделенным сущностям, в дополнение к первичным ключам, назн
а
чаются описательные атрибуты, среди которых можно выделить
втори
ч
ные ключи
.

Вторичный ключ



это атрибут или совокупность атрибутов,
которые идентифицируют экземпляры сущности, обладающих опр
еделе
н
ным общим свойством. Определение числа атрибутов, входящих во втори
ч
ный ключ, зависит от логики запроса и специфично для конкретной АИС.

Определение связей

Выявляются зависимости между сущностями. Определяют какие св
я
зи необходимы, а какие избыточны.

Для выделенных связей определяются
их характеристики (степень связи, класс принадлежности).

В графической диаграмме ©сущность
-
связьª обозначают: типы су
щ
ностей


прямоугольниками; атрибуты


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


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



пунктирной линией. Идентифицирующие атр
и
буты подчеркиваются.

Инфологическая модель включает также диаграммы ©атрибут
-
атрибутª. Диаграмма ©атрибут
-
атрибутª представляет собой графическое
изображение связей между атрибутами, которые относятся к одной сущн
о
сти. Целесообразно стремиться к тому, чтобы зависимость ©атрибут
-
атрибутª описывалась диаграммой, где существует только зависимость
описательных атрибутов от идентифицирующего, а других связей нет. Ан
а
лиз диаграммы ©атрибут
-
атрибутª позволяет судить о том
, насколько уд
а
чен был выбор основных конструкций данной модели, с точки зрения пр
о
стоты представления смысловых связей между этими конструкциями. М
о
дель оформляется в виде графических диаграмм ©сущность
-
связьª, ©атр
и
бут
-
атрибутª и составлениям спецификаци
й по атрибутам, сущностям
и связям.



-
10
-

1.3.

Объединение моделей локальных представлений


Объединение моделей локальных представлений позволяет [1]:

1)

объединить в композиционно единое представление фрагментал
ь
ные представления о различных свойствах одно
го и того же
объекта;

2)

устранить несущественные различия в представлениях подобных
объектов;

3)

образовать классы и подклассы подобных объектов;

4)

образовать производные типы объектов, соответствующих объ
е
динению пересечению, разности и т.д. исходных
объектов.

Вводимые конструкции должны обеспечивать непротиворечивое
представление данных.

При объединении модели необходимо установить порядок очередн
о
сти объединения.

Например, имеется
n
-
моделей. Можно попытаться объединить все
модели за один шаг. Это воз
можно, если
n

невелико.

При большом значении
n

растут временные затраты, возникает вер
о
ятность ошибок и упущений. Поэтому целесообразно объединять модели в
несколько этапов. Обычно используются бинарное объединение:














При бинарном объединении результат объединения
N
1

объектов
о
д
ного представления с
N
2

объектами другого представления дает (
N
1

+
N
2


X
)

объектов, где
X



количество совпадающих объектов в объединяемых пре
д
ставлениях. Если перед объединением выполнить соответствующую гру
п
пировку ЛП, то можно увеличить значение
X

и тем самым уменьшить кол
и
чество операций при объединении.

При объединении лок
альных представлений используют три конце
п
ции: идентичность, агрегация, обобщение [1].

Модель

ЛП 1

Модель

ЛП 1
,2

Результирующая
модель


Модель

ЛП 2

Модель

ЛП (
n
-
1),
n

Модель

ЛП (
n
-
1)

Модель

ЛП
n

-
11
-

Два или более элементов модели идентичны, если имеют одинаковые
семантическое значение.

Агрегация позволяет рассматривать связь между элементами модели
как новый
элемент.

Агрегация увеличивается в трех формах:

1). В одном локальном представлении агрегатный объект определен
как единое целое, а в другом рассматриваются его составные части.

Например, в одном представлении определен только объект А (нек
о
торое изделие),

в другом представлении объекты В
1

, В
2

, В
3

(некоторые д
е
тали), которые являются составными частями объекта А.

Если рассматривать объединение ЛП как для различных объектов, то
потеряется информация о том, что объекты В
1

, В
2

, В
3

есть составные части
А. П
оэтому необходимо произвести объединение моделей с использованием
агрегации:


А


В
1

В
2

В
3



А













В
1

В
2

В
3


2). Агрегатный объект как единое целое не определен ни в одном из
представлений, но в обоих представлениях рассматрива
ются его составные
части.


Например, в одном представлении рассматриваются объекты В
1
, В
2
,
В
3
, в другом представлении В
4
, В
5.

Все объекты являются составными ча
с
тями неопределенного объекта А, о существовании которого знает проект
и
ровщик. Тогда необходимо
ввести в рассмотрение объект А.


В
1

В
2

В
3


В
4

В
5




А














В
1

В
2

В
3

В
4

В
5



3). Один и тот же агрегатный объект рассматривается в обоих н
а
правлениях, но составляющие различны.

Например, в обоих
представлениях рассматривается агрегат А с ра
з
личными составляющими. Тогда при объединении можно получить агрегат
вида:



А



А





А



В
1

В
2

В
3


В
3

В
4

В


В
1

В
2

В
3

В
4

В
5


-
12
-

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

Рассмотрим пример: пусть имеются модели ЛП одной ПО.

ЛП
-
1





















ЛП
-
2

ОТДЕЛ

ДОКУМЕНТ

СЛУЖАЩИЙ

Оформляют

Работают

Номер

Название

Название

Форма

Оклад

№ документа

Должность

ФИО

Таб
.

номер

ПРОИЗВОДСТВО

В составе П

Работает

Номер

Название

Разряд

УПРАВЛЕНИЕ

РАБОТНИК

Должность

ФИО

Таб номер

Должность

ФИО

Таб номер

Оклад

В составе Б

БРИГАДА

В составе Р

Номер

-
13
-

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

эти модели представления можно определить в модель следующего
вида:


Для того, чтобы провести обобщение, необходимо добавить недо
с
тающие атрибуты в обобщающие сущности. Поскольку сущности УПРА
В
ЛЕНИЕ, СЛУЖАЩИЙ, РАБОТНИК представляют собой различные кат
е
гории типов, но отражающие обобщенное поня
тие ©категория персоналаª,
то введем в модель сущность ПЕРСОНАЛ, и для того, чтобы представить
в модели информацию о категориях, введем новый атрибут ©Категорияª.


ПРОИЗВОДСТВО

Номер П

Название П

Работают

В составе О

В составе Б

В составе У

УПРАВЛЕНИЕ

БРИГАДА

Номер Б

Оклад У

Должн. У

ФИО У

Таб
N
У

РАБОТНИК

В составе Р

Разряд Р

Должн. Р

ФИО Р

Таб
N
Р

В составе О

Обрабат
-
ет

ОТДЕЛ

Номер О

Назван. П

ДОКУМЕНТ

Назв. Д.

Форма д.

№ докум.

СЛУЖАЩИЙ

Таб № С

ФИО С

Должн. С

Оклад. С

-
14
-

ПРОИЗВОДСТВО

Номер П

Название П

ОТДЕЛ

Номер О

Название О

ДОКУМЕНТ


документа

Форма Д

Название Д

ПЕРСОНАЛ

Табельн.ном
ер

Должность

Разряд

Категория

ФИО

Оклад


Построим диаграммы ©атрибут
-
атрибутª.



















Скорректируем диаграмму ©сущность
-
связьª:

№ документа

Форма докум
ен
та

Название

ПРОИЗВОДСТВО

В составе П

Работает

Номер

Название


Разряд

ОТДЕЛ

ПЕРСОНАЛ

Название

Номер

Должность

ФИО

Таб номер

Категория

В составе Б

БРИГАДА

В составе Б

Номер


В составе О

Оформляют

ДОКУМЕНТ

-
15
-



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


1.4. Преобразование инфологической модели в реляционные отношения


Концепция реляционной модели была разработана Е. Коддом. В о
с
нове этой модели лежит понятие ©отношениеª (от англ. relation). Элемент
а
ми отношения являются
кортежи.

Реляционные отношения удобно пре
д
ставлять в виде таблиц. При этом строки таблицы соответствуют кортежам,
ПРОИЗВОДСТВО

В составе П

Работает

Номер

Название

ОТДЕЛ

ПЕРСОНАЛ

Название

Номер


В составе

П

БРИГАДА

В составе Б

Номер


В
составе О

Оформляют

ДОКУМЕНТ

ФИО

Табельный



Получают

ТАРИФ

Оклад

Категория

Разряд

Должность



документа

Форма
докум
-
та

Название

-
16
-

а столбцы


атрибутам. Поскольку отношение есть

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

R





атрибуты



A

B

C

D

A

b

e

f

G

k

l

m

B

c

d

n










кортежи

Рис. 1. Состав и структура отношения R


Проблемы проектирования схемы базы данных (БД) в рамках рел
я
ционной модели заключаются в определении состава атрибутов в отнош
е
ниях. Задача группировки атрибутов в отношения допускает

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

1)

число выбранных атрибутов в отношениях для формирования пе
р
вичны
х ключей должно быть минимальным;

2)

выбранный состав отношений по возможности должен быть м
и
нимальным;

3)

процедуры обработки и обновления данных не должны вызывать о
с
ложнений.

Для решения этих задач Коддом был разработан математический а
п
парат, называемый
нор
мализацией отношений
.

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

Прав
ило № 1

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

Правило № 2

Если имеется связь степени 1:1 и класс
принадлежности одной су
щ
ности является обязательным, а другой


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

-
17
-

Правило № 3

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

Правило № 4

Если имеется связь

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


Правило № 5

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

Правило № 6


Если имеется связь степени М:N, то требуется три таблицы. Для к
а
ждой сущности необходимо
создать свою таблицу, первичным ключом к
о
торой будет ключ этой сущности. Кроме того, связующая таблица должна
содержать в себе ключи связываемых таблиц в качестве ее атрибутов.


1.5. Задание для практической работы


Порядок выполнения работы


1.

Описать предм
етную область в соответствии с заданием.

2.

Выделить атрибуты, сущности, связи и построить диаграмму ©сущность
-
связьª.

3.

Построить для каждой сущности диаграммы ©атрибут
-
атрибутª.

4.

При необходимости построить откорректированную диаграмму
©сущность
-
связь
ª.

5.

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

6.

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


Содержание отчета


Кроме титульного листа и цели работы отчет
должен содержать:

-

описание предметной области с указанием предполагаемых внешних
пользователей и запросов на обработку данных;

-
18
-

-

диаграмму ©сущность
-
связьª;

-

диаграмму ©атрибут
-
атрибутª;

-

при необходимости исправленную диаграмму ©сущность
-
связьª;

-

спецификацию
по атрибутам, сущностям и связям;

-

реляционные таблицы с примерными значениями атрибутов (указать
не менее пяти значений атрибутов).


1.6. Пример выполнения работы


Описание предметной области


Разработать модель для создания автоматизированной информацио
н
н
ой системы.
Предметной областью

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



расчет заработной платы,
внешними пользователями



сотрудники расчетного отдела. Работнику
начисляется заработная плата, состоящая из нескольких сумм
, каждая из
которых рассчитывается за каждую выполненную работу отдельно. Видов
работ может быть несколько. Необходимо учесть, что:

1)

работник выполняет разные виды работ;

2)

одну работу может выполнять несколько работников;

3)

работник имеет баланс начисления;

4)

вы
полненные работы составляют баланс.

Проанализировав особенности предметной области, можно выделить
типы сущностей: РАБОТНИК, ВИДЫ_РАБОТ. Назначим сущностям атр
и
буты: РАБОТНИК (
Табельный_N,

ФИО, Отдел
), ВИДЫ_РАБОТ (
Назв
а
ние_работы
,
Расценка, N_наряда, Дата
_выполнения
). Поскольку каждый
работник может выполнять несколько видов работ и каждый вид работы
может быть выполнен несколькими работниками, то запросы 1), 2) опред
е
ляют степень связи М : N. Каждый работник должен выполнять какую
-
то
работу, следовательно
, сущность РАБОТНИК имеет обязательный класс
принадлежности. Обязательный класс принадлежности будет и у сущности
ВИДЫ_РАБОТ, так как работники предприятия должны выполнять весь
спектр работ (имеется в виду нормальное предприятие). Запросы 3), 4)
можно не
учитывать, поскольку все суммы денег можно вычислить из ра
с
ценок на работы и закрытых нарядов:


,


(1)

-
19
-



,



(2)

где



сумма начисления j
-
тому работнику за выполнение i
-
той рабо
ты,



расценка на выполнение i
-
той работы,



количество единиц выпо
л
ненной i
-
той работы j
-
тым работником,



заработная плата j
-
го рабо
т
ника,



сумма всех начислени
й за выполненную i
-
тую работу,



б
а
ланс выполненных работ.


Выражения (1) определяют баланс начисления j
-
го работника, выр
а
жения (2)


баланс начислений за выполнение всех работ. При этом


.




Построение
диаграмм модели


1. Построим диаграмму ©сущность
-
связьª:






















РАБОТНИК

Выполнение


ВИДЫ_РАБОТ

Табельный_
N

ФИО

Отдел

Название_работы

Расценка

N
_наряда


Дата_выполнения

-
20
-

2. Построим диаграммы ©атрибут
-
атрибутª:



РАБОТНИК






ВИДЫ_РАБОТ










Анализируя диаграмму ©атрибут
-
атрибутª для типа сущности
ВИДЫ_РАБОТ, можно установить нежелательную связь между описател
ь
ными атрибутами ©
N_наряда
ª и ©
Дата_выполнения
ª, так как наряд закр
ы
вается после выполнения работы. Это влечет за собой невозможность хр
а
нения информации о работах, которые еще не выполнены (по

принципу
хранения бесконечного числа экземпляров). Таким образом, разбиваем ди
а
грамму ©атрибут
-
атрибутª для сущности ВИДЫ_РАБОТ на две диаграммы,
вводим новый тип сущности ВЫПОЛНЕННЫЕ_РАБОТЫ, устанавливаем
связи в соответствии с запросами, определяем их х
арактеристики и соста
в
ляем спецификации.

3. Скорректируем диаграмму ©атрибут
-
атрибутª:


ВИДЫ_РАБОТ
















Табельный_
N


ФИО


Отдел

Название_работы

Расценка

N
_наряда


Дата_выполнения

Название_работы


Расценка

N_
наряда


Дата_выполнения

-
21
-

4. Скорректируем диаграмму ©сущность
-
связьª:






















Составление спецификаций



Таблица 1

Спецификация связей


Тип

связи

Тип

сущности
А

Класс

прина
д
лежности

Тип

сущности
В

Класс

прина
д
лежности

Напра
в
ленность

Ст
е
пень
связи

Выпо
л
нение

РАБО
Т
НИК

обяз
а
тельный

ВИДЫ

РАБОТ

обяза
-
тельный

двунаправ
-
ленная

М:N

Уча
стие

РАБО
Т
НИК

необяз
а
тельный

ВЫПОЛ
-
НЕННЫЕ

РАБОТЫ

обяза
-
тельный

двунаправ
-
ленная

1:N

Состо
я
ние

ВЫПОЛ
-
НЕННЫЕ

РАБОТЫ

обяз
а
тельный

ВИДЫ

РАБОТ

необяза
-
тельный

двунаправ
-
ленная

М:1




M

N

N

1

РАБОТНИК

Участие

ВЫПОЛНЕ
Н
НЫЕ_РАБОТЫ

Выполнение

Состояние


ВИДЫ_РАБОТ

Табельный

N


ФИО

N
_наряда

Дата_выполнения

Название_работы

Название_работы

Расценка

N

1

Отдел

-
22
-

Таблица 2

Спецификация атрибутов

Тип
сущн
о
сти

Название


атрибута

Назначение

атрибута

Тип

атрибута

Длина

Формат

РАБОТ
-
НИК

Таб.


ФИО

Отдел

идентифици
-
рующий

описательный

описательный

цифровой, целый


символьный

символьно
-
цифровой

6 симв.


20 симв.

10 симв.


ВИДЫ

РАБОТ

Название
работы

Расценка

идентифици
-
рующий

описательный

символьный


цифровой, веществ.

15 симв.


7 симв.



2 симв.
после зпт.

ВЫПОЛ
-
НЕННЫЕ

РАБОТЫ

N наряда


Дата в
ы
полнения

идентифици
-
рующий

описательный

цифровой, целый


дата

7 симв.


8 симв.



Формирование реляционных отношений


Проанализировав характеристики

связей (степень связи и классы
принадлежности сущностей), используя правила № 4, 5, 6, получим сл
е
дующие реляционные отношения:

1)

РАБОТНИК (
Табельный_N, ФИО, Отдел
);

2)

ВИДЫ_РАБОТ (
Название_работы,

Расценка
);

3)

ВЫПОЛНЕННЫЕ_РАБОТЫ (
N_наряда
, Дата_выполнения,

Название
_
работы,
Табельный_N
)
;

4)

ВЫПОЛНЕНИЕ (
Табельный_N
,
Название_работы
)
.

В отношении 3) реализованы связи ©Состояниеª и ©Участиеª (по прав
и
лу № 4); в отношении 4) реализована связь ©Выполнениеª (по правилу № 6).






Контрольные вопросы



1.

Предметная область, пользователи информационных систем.

2.

Уровни абстракции и этапы проектирования автоматизированных информац
и
онных систем.

3.

Информационно
-
логическое проектирование; атрибут, сущность, связь.

4.

Классификация бинарных связей.

5.

Диаграммы ©сущность
-
связьª, ©атрибут
-
атрибутª.

6.

Проверка корректности инфологической схемы.

7.

Объединение моделей локальных представлений.

8.

Модели данных. Реляционное отношение, атрибуты, кортежи.

9.

Правила преобразования инфологической модели в реляционные отношения.




-
23
-

2. ФИЗИЧЕСКАЯ РЕАЛ
ИЗАЦИЯ МОДЕЛИ


2.1. Характеристика СУБД Oracle


Oracle



это система управления реляционными базами данных


СУРБД

(
Relational Database Menagement System


RDBMS
), разработанная
корпорацией Oracle из Бельмонта (шт. Калифорния, США). Р
еляционная
СУБД Oracle обеспечивает высочайшую производительность баз данных
для пользователей в различных областях науки и бизнеса. Oracle предоста
в
ляет пользователям надежную реляционную базу данных, а также широкий
набор средств для разработки приложени
й, поддержки решений и автомат
и
зации. Этими продуктами покрывается широкий спектр операционных си
с
тем и аппаратного обеспечения. Кроме того, СУБД постоянно совершенс
т
вуется для лучшего удовлетворения потребностей хранилищ данных. Се
р
вер СУРБД дополняется р
азнообразными хорошо интегрированными пр
о
граммными продуктами, которые специально созданы для организации ра
с
пределенной обработки данных и разработки приложений клиент/сервер.

Основная идея среды клиент/сервер состоит в распределении выпо
л
няемой задачи ме
жду несколькими процессорами в сети. Каждый процессор
предназначен для определенного набора подзадач, с которыми он справл
я
ется наилучшим образом, а конечный результат выражается в увеличении
производительности и эффективности системы в целом. Распределени
е в
ы
полнения задач между процессорами осуществляется с помощью протокола
сервисных запросов; один процессор, клиент, запрашивает обслуживание
у другого процессора, сервера. Чаще всего при построении систем кл
и
ент/сервер часть приложения, отвечающая
за пользовательский интерфейс,
отделяется от части, отвечающей за доступ к данным.

В настоящее время основными направлениями развития Oracle
являются:

-

использование параллельной обработки данных;

-

использование технологий Internet и World Wide We
b;

-

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


2.2. SQL*Plus


SQL*Plus



это интерактивный инструмент для среды СУБД Oracle.
SQL*Plus может обрабатывать операторы SQL в диалоге с пользовате
лями
(интерактивно); обращаться к средствам PL/SQL для
процедурной обрабо
т
ки; выводить на экран и печать результаты запроса; форматировать резул
ь
таты запроса в отчеты; описывать содержимое указанной таблицы; копир
о
вать данные между базами данных.



-
24
-

SQL*Plus


©исторически сложившийсяª интерфейс с Oracle RDBMS,

работающий в текстовом режиме. SQL*Plus позволяет легко создавать те
к
стовые отчеты различного типа и может использоваться для созда
ния дин
а
мических сценариев SQL*Plus или даже динамических программ на к
о
мандных языках операцион
ных систем. SQL*Plus может

применяться для
выполнения некоторых функций администрирования Oracle, а также раб
о
тать в интерактивном режиме, поддерживая сеанс терминала. SQL*Plus м
о
жет обрабатывать блоки ANSI SQL так же, как блоки PL/SQL. SQL*Plus
имеет ряд ограничений; некоторые огр
аничения определяются операцио
н
ной системой.

SQL*Plus входит в состав как серверной, так и клиентской поставки
Oracle во всех поддерживаемых операционных системах. Кроме того, он
входит в состав всех клиентских приложений, таких как Oracle Developer
2000,
PL/SQL Developer и других.

Запуск SQL*Plus производится указанием в командной строке
команды SQLPLUS следующего формата:

sqlplus [username[[/password][@databasealias]]] [@scriptfile.sql]

-

username


имя пользователя Oracle;

-

password


п
ароль пользователя;

-

databasealias


алиас (заданное имя) базы данных;

-

scriptfile.sql


файл, содержащий набор команд SQL*PLUS.

При запуске SQLPLUS без параметров будет запрошено имя польз
о
вателя и его пароль.

В ОС Windows SQL*Plus также можно запустить из
меню операц
и
онной системы при установленном программном обеспечении. В этом сл
у
чае имя пользователя, его пароль и алиас базы данных указываются в соо
т
ветствующих полях ввода.





Рис. 2. Окно авторизации SQL*Plus



-
25
-

Таблица 3

Основные команды SQL*PLUS


Команда

Описание

@

Выполняет указанный командный файл.

/

Выполняет текущую команду SQL или блок PL/SQL из
буфера SQL.

COLUMN

Задает атрибуты вывода для указанной колонки. Или п
о
казывает текущие установки вывода для одной или всех
колонок.

CONNECT

Подключает указанного пользователя к Oracle.

COPY

Копирует данные из запроса в таблицу локальной или
удаленной БД.

DEL

Удаляет текущую строку из буфера.

DESCRIBE

Показывает описания колонок для указанной таблицы,
представления (view) или синонима.

DI
SCONNECT

Производит отложенные изменения в БД и отключает т
е
кущего пользователя от Oracle, но не покидает
SQL*PLUS.

EDIT

Загружает текстовый редактор ОС с указанным файлом
или содержимым буфера SQL.

EXIT

Производит все отложенные изменения в БД, заверша
ет
SQL*PLUS, и возвращает управление ОС.

GET

Загружает файл ОС в буфер.

LIST

Выводит одну или несколько строк буфера.

PAUSE

Выводит пустую строку и строку с указанным текстом,
затем ожидает нажатие пользователем [Return]. Или, в
ы
водит две пустые строки
и ожидает [Return].

PROMPT

Посылает указанное сообщение или пустую строку на
экран.

RUN

Выводит и выполняет текущую команду SQL или блок
PL/SQL из буфера SQL.

SAVE

Сохраняет содержимое буфера в файле ОС.


Настраивает окружение SQL*PLUS для текущего
сеанса.

SHOW

Выводит значения системных переменных SQL*PLUS.

SQLPLUS

Запускает SQL*PLUS из ОС.

START

Выполняет указанный командный файл.


2.3. Общие сведения о SQL


SQL



это множество операторов, которые должен применять пол
ь
зователь или приложение
для доступа к данным базы данных Oracle. Пр
и
-
26
-

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

Структурированный язык запр
осов (Structured Query Language


SQL)
появился в системе System R фирмы IBM в середине семидесятых годов.
В 1979 году Oracle Corporatuon представила первую коммерческую версию
SQL. Язык SQL также был применен в системах DB2 и SQL/DS корпорации
IBM
. SQL давно стал промышленным стандартом, но его развитие продо
л
жается. Американский национальный институт стандартов ANSI (American
National Standards Institute) объявил SQL стандартным языком для систем
управления реляционными базами данных. Большинство
компаний, кот
о
рые создают системы управления реляционными базами данных, поддерж
и
вают SQL и придерживаются стандарта ANSI SQL89. Хотя многие разр
а
ботчики, в том числе и Oracle, объявили о соответствии своих версий SQL
спецификации ANSI SQL92.

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

SQL


это непроцедурный язык благодаря следующим свойствам:

-

обрабатывает одновременно не одну, а множество записей;

-

обеспечива
ет автоматическое управление доступом к данным.

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


таблица. Все SQL


оп
е
раторы имеют

на входе набор записей и на выходе также генерируют мн
о
жество записей. Это свойство SQL
-
операторов позволяет выход одного оп
е
ратора использовать на входе другого.

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


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

SQL обеспечивает операции для самых различных задач, включая:

-

запросы данных;

-

добавление, изменение и удаление строк из таблицы;

-

создание, модификацию
и удаление объектов базы данных;

-

управление доступом к базе данных и объектам базы;

-

гарантирование согласованности данных.

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

-
27
-

2.3.1. Элементы языка SQL


Объекты базы данных



это элементы, поддерживаемые Oracle
RDBMS. Большинство объектов занимает память в базе данных; некоторые
объекты (например, табличные пространства) используются для хранения

других объектов базы данных. Таблицы


это единственный объект, кот
о
рый хранит данные пользователя и к которому он может обращаться неп
о
средственно.

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

Именование объектов базы данных подчиняется следующим правилам:

1.

Имя объекта базы данных должно быть длиной от 1 до 30 символов.

2.

Имя не должно содержать кавычек.

3.

Имя не является регистрочувствит
ельным

4.

Имя должно начинаться с буквы.

5.

Имя может содержать только символы A
-
Z, 0
-
9, _,$ и #.

6.

Имя не должно дублировать зарезервированные слова.

7.

Имя не должно дублировать имя какого
-
либо иного объекта базы да
н
ных, владельцем которого является тот же поль
зователь.

8.

Некоторые объекты (индексы, последовательности, таблицы и обзоры)
базы данных могут в дальнейшем определяться вместе с именем пол
ь
зователя


владельца данного объекта. Например, кроме имени EMP
можно использовать имя USER.EMP.

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

Термины: literal, constant, value (литерал, константа, значение)
в Oracle имеют одинаковое значение; все представляют определенную часть
информации. Например:



'JACK', 'BLUE ISLAND', '101'


символьные л
и
тералы; '01
-
JAN
-
89'


литерал типа дата; 5001


цифровой литерал. Замет
ь
те, что символьные и датные литералы заключены в одиночные кавычки.
Кавычки позволяют Oracle отличать имена объектов базы данных от си
м
вол
ьных и датных литералов.

Переменные в системе Oracle служат местом для помещения литер
а
лов. Переменные Oracle работают аналогично переменным в языках пр
о
граммирования. В любой данный момент времени переменная может быть
либо пустой, либо хранить литерал. П
еременные в основном используются
во вложенном SQL.

Каждый литерал или переменная, с которыми работает система
Oracle, имеет определенный тип данных. Тип данных ассоциируется с фи
к
сированным набором свойств, присущих его значениям. Этот набор свойств
позво
ляет Oracle различать типы данных. Например, сложение может в
ы
полняться с данными типа NUMBER, но не с данными RAW.

-
28
-

Типы данных также ограничивают диапазон значений, которые м
о
жет принимать элемент данных. Например


тип данных DATE не может
принимать зна
чение 29 февраля, если год не високосный. Этот же тип да
н
ных не может принимать значение 2 или 'LITERA'.

Таблица 4

Основные типы данных Oracle


Тип данных

Описание

NUMBER (p,s)

Числовые данные переменной длины. Точность p
(общее количество цифр) может
задаваться от 1 до
38. Масштаб s (число цифр после десятичной точки)
может быть от

84 до 127.

NUMBER (p)

Число с фиксированной точкой с нулевым масшт
а
бом и точностью p.

NUMBER

Число с плавающей точкой с точностью 38.

DATE

Значение даты и времени фикси
рованной длины.

CHAR (размер)

Символьные данные фиксированной длины. Макс
и
мальный размер


255 байт.

VARCHAR2
(размер)

Символьные данные переменной длины. Макс
и
мальный размер


2000 символов.

LONG

Символьные данные переменной длины. Макс
и
мальный размер


до 2 Гбайт.

RAW

Двоичные данные переменной длины. Максимал
ь
ный размер


2000 символов.

LONGRAW

Двоичные данные переменной длины. Максимал
ь
ный размер


до 2 Гбайт.


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


и каждому значению, пр
и
надлежащему данному столбцу. Oracle назначает тип данных небазовым
значениям, исходя из контекста их использования. Например,
если вставить
'01
-
JAN
-
89'
в столбец с типом DATE, Oracle после проверки на правильность
будет трактовать символьную строку '01
-
JAN
-
89' как значение типа DATE.

В общем случае в выражении не должно быть смешения типов да
н
ных. Например, выражение не может
содержать произведения 5 на 10 и з
а
тем сложения с ним 'JAMES'. Однако Oracle будет выполнять преобразов
а
ние одного типа данных в другой, если это имеет смысл. Например, лит
е
ральная строка '10' имеет тип CHAR, Oracle преобразует ее в NUMBER, е
с
ли она исполь
зуется в числовом выражении.

Выражения (булевы, арифметические или строчные) могут использ
о
вать при сравнении данные различных типов. В этих случаях для выполн
е
ния оператора Oracle обязательно использует преобразование данных. Для
выполнения преобразовани
я Oracle может:

-
29
-

-

преобразовать константу в тип данных столбца;

-

преобразовать значение столбца к типу данных константы;

-

преобразовать тип данных столбца к типу данных другого столбца.

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

-

если применять функции явного преобразования, SQL
-
операторы ст
а
новятся проще для понимания;

-

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

-

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

-

если в определенном столбце строки данные отсутствуют, говорят что
его значение null. Столбец с
данными любого типа может содержать
значение null, если только он специально не описан как NOT NULL
в момент создания. Значение null обладает следующими свойствами:



присваивается, когда действительное значение неизвестно или
бессмысленно;



не эквивале
нтно значению нуль;



будет превращать в null любое выражение. Например, NULL,
умноженное на 10, дает NULL.

Для временного преобразования null в действительное значение испол
ь
зуется функция NVL. Например, NVL(COLUMN,0) возвращает 0, если знач
е
ние
в столбце C
OLUMN


null и действительное значение, если оно не null.

Напрямую со значениями null должны работать только операции
сравнения (IS NULL или IS NOT NULL). Если со значением null использ
у
ется любой другой оператор, результат всегда неизвестный. Так как N
ULL
представляет собой отсутствие данных, он не может быть равен или не р
а
вен любому другому значению, кроме null.


2.3.2. Основные операторы SQL


SQL
-
операторы

делятся на следующие три типа:

DDL



Data Defenition Language (Язык Определения Данных): эти
операторы действуют на структуру объектов базы данных и неявно испол
ь
зуют словарь данных. До и после выполнения каждого DDL
-
оператора
Oracle неявно выполняет операцию COMMIT (фиксацию всех изменени
й).

DML



Data Manipulation Language (Язык Манипуляции Данными):
выполняют различные действия над данными.

-
30
-

EMB



Embedded SQL (Встроенный SQL): операторы, используемые
для помещения DDL
-

и DML
-
операторов внутрь программы на процеду
р
ном языке.

Таблица 5

О
сновные операторы SQL


Описание

Тип

Действие

1

2

3

/* ... */


Помещает комментарий внутри или между
SQL
-
операторами.

ALTER INDEX

DDL

Переопределяет будущее распределение пам
я
ти индекса.

ALTER S
E-
QUENCE

DDL

Переопределяет генерацию последовательных
номеров.

ALTER TABLE

DDL

Добавляет, переопределяет столбцы к таблице.

ALTER USER

DDL

Меняет пароль пользователя, умалчиваемое
табличное пространство и умалчиваемое вр
е
менное табличное пространство.

COMMIT

DML

Делает изменения, проведенные с начала

тра
н
закции, постоянными.

CONNECT

EMB

Присоединяет (подключает) пользователя
к базе данных.

CREATE INDEX

DDL

Создает индекс для таблицы.

CREATE S
E-
QUENCE

DDL

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

CREATE SY
N-
ONYM

DDL

Создает синоним для таблицы или обзора.

CREATE TABLE

DDL

Создает таблицу и определяет ее столбцы и
распределение памяти.

CREATE VIEW

DDL

Определяет обзор на одной или нескольких
таблицах или других обзорах.

DELETE

DML

Удаляет все или выбранные
строки из таблицы.

DROP object

DDL

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

GRANT

DDL

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

INSERT

DML

Добавляет к таблице или обзору новые строки.

LOCK TABLE

DML

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


-
31
-

Окончание таблицы 5


1

2

3

PREPARE

EMB

Выполняет разбор SQL
-
оператора.

RENAME

DDL

Изменяет имена таблиц, обзоров или синон
и
мов.

REVOKE

DDL

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

ROLLBACK

DML

Откатывает все проведенные изменения к точке
сохранения или к началу транзакции.

SAVEPOINT

DML

Устанавливает контрольную точку, к которой
впоследствии можно будет выполнить откат.

SELECT

DML

Выполняет запрос; выбирает строки и
столбцы
из одной или нескольких таблиц.

UPDATE

DML

Изменяет данные в таблице.

VALIDATE
INDEX

DDL

Проверяет целостность индекса.


2.3.3. Работа с
таблицами


Таблицы


основные единицы хранения данных в реляционной базе
данных.


Создание таблиц


В общем
виде команда создания таблицы выглядит так:

CREATE

TABLE


имя
_
таблицы
�(

<имя_столбца1> тип_данных1,



<имя_столбцаN> тип_данныхN);

Необходимо выбрать имя, ширину и тип данных для каждого столбца
в таблице. Столбцы должны быть перечислены в скобках после
имени та
б
лицы. Информация о последовательных столбцах должна быть разделена
запятыми.
Имена столбцов могут содержать до 30 символов. Для конкре
т
ной таблицы каждое имя столбца является уникальным. Необходимо и
с
пользовать спецификацию NOT NULL, если существе
нны данные для опр
е
деленного столбца. Тогда, если будет предпринята попытка ввести запись
без заполнения значения для этого столбца, система Oracle возвратит соо
б
щение об ошибке.


Просмотр структуры таблицы


Для просмотра спецификации столбцов таблицы мож
но применить
команду SQL DESCRIBE в виде:

DESCRIBE <имя_таблицы>;

-
32
-

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


Модификация таблиц

После создания таблиц иногда возникает необходимость модифиц
и
ровать ее. Это можно осуществить с помощью команды
ALTER TABLE.

Добавление столбца обеспечивается командой:

ALTER

TABLE


имя
_
таблицы


ADD имя_столбца тип_данных;


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

В любой момент можно изменить существующую базовую таблицу,
увеличив размер столбца или заменив в нем число десятичных позиций:

ALTER

TABLE


имя
_
таблицы


MODIFY <имя_столбца>

(тип_данных);

Можно замени
ть для столбца вариант NOT NULL на NULL, добавив
для спецификации столбца предложение NULL. Можно заменить для
столбца NULL на NOT NULL аналогичным образом, но только если в
столбце нет пустых значений. В процессе работы может возникнуть нео
б
ходимость доба
вить столбец NOT NULL к таблице, которая уже создана, но
это нельзя сделать в один этап. С помощью предложения MODIFY эту оп
е
рацию можно осуществить в три этапа:

1.

Добавить столбец;

2.

Заполнить некоторым непустым значением каждое поле в столбце;

3.

Использовать A
LTER TABLE MODIFY для того, чтобы добавить
к столбцу спецификацию NOT NULL.

Для уменьшения ширины столбца или изменения ширины столбца
необходимо, чтобы все значения в столбце для всех строк были пустыми
(NULL).

Заполнение таблицы


После
создания таблицы необходимо внести в нее данные. Эту оп
е
рацию можно осуществить с помощью команды INSERT. В общем виде
команда выглядит так:

INSERT

INTO <имя_таблицы> [(<имя_столбца1>, …)]

VALUES („<значение1>‟, …);

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

значения расположены точно в таком же порядке, как и столбцы, и ни одно
значение

не пропущено, то можно опустить имена столбцов и написать так:

INSERT

INTO

имя
_
таблицы


VALUES (значение1, значение2, …);

Если отсутствуют значения для одного или нескольких столбцов, то
в качестве соответствующего значения можно указать NULL, если
этот
столбец не содержит спецификации NOT NULL.

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

Вообще говоря, вставляемые значения должны удовлетворять сл
е
дующим требованиям:

1.

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

2.

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

3.


Вставляемые значения должны разделяться запятыми.

4.

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

5.

Символьные значения должны быть заключены в
одинарные кавычки.


Просмотр содержимого таблицы


Для того чтобы просмотреть данные, содержащиеся в таблице, и
с
пользуют команду языка SQL SELECT. Формат этой команды выглядит так:

SELECT <имя_столбца1>, … FROM <имя_таблицы>;

Если имена столбцов не указаны

и команда имеет следующий формат:

SELECT
*
FROM


имя
_
таблицы
�;

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

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

SELECT

*
FROM

TAB
;

TAB


системная таблица Oracle, содержащая записи с информацией
о таблицах пользователя.


Модификация данных


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

команды UPDATE, которая служит для изменения знач
е
-
34
-

ний в существующих строках таблиц. Общая форма команды UPDATE им
е
ет вид:

UPDATE <имя_таблицы>

SET

<имя_столбца 1><новое значение >, …

<имя_столбца N><новое значение >

WHERE <условие>;

Предложение SET в
команде UPDATE содержит информацию о том,
какие столбцы нужно обновить и какие значения должны быть в них закл
ю
чены. Команда UPDATE оперирует над всеми строками, которые удовл
е
творяют условию (условиям) предложения WHERE. Предложение WHERE
является необяза
тельным, но если оно опущено, то все строки будут
обновлены.

Командой UPDATE можно перевести столбец в состояние NULL, е
с
ли этот столбец не содержит спецификации NOT NULL.


Удаление данных


Также в процессе работы с таблицами возникает
необходимость уд
а
ления данных из таблицы. Команда DELETE служит для удаления строк из
таблицы. В общем виде команда DELETE выглядит так:

DELETE FROM
имя
_
таблицы


WHERE


условие
�;

Невозможно удалить командой DELETE неполные строки, поэтому
нет необходимост
и выбирать имена столбцов. Предложение WHERE опр
е
деляет какие строки будут удалены. Если WHERE не выбрано, то будут
удалены все строки и останутся только спецификации столбцов и имя
таблицы.


Удаление таблицы


Иногда необходимо ликвидировать та
блицу, что производится с п
о
мощью команды DROP TABLE. В общем виде команда выглядит так:

DROP

TABLE


имя
_
таблицы
�;

Более подробная информация по синтаксису команд SQL и их и
с
пользованию при построении запросов различной степени сложности пре
д
ставлена в [3]

и [4].


2.4. Задание для практической работы


Порядок выполнения работы


1.

Ознакомиться с командами манипулирования данными в таблицах.

2.

Создать и заполнить таблицы данными для всех имеющихся реляцио
н
ных отношений.

-
35
-

3.

Сделав копию любой из таблиц, осуществить
для нее операцию доба
в
ления столбца, присвоив ему спецификацию NOT NULL в случае, если
такой спецификации нет ни у одного из столбцов.

4.

Выбрав один из столбцов копии, модифицировать его спецификацию,
т.е. увеличить или уменьшить ширину столбца. Выбрать ст
олбец с сп
е
цификацией NOT NULL, изменить ее на NULL .

5.

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

6.

Вывод части данных на экран по условию.

7.

Удалить часть данных по условию.

8.

Очистить

и удалить копию таблицы от всех данных.


Содержание отчета


Кроме титульного листа и цели работы отчет должен содержать:

-

SQL
-
запросы, осуществляющие создание таблиц в соответствии с
предметной областью;

-

SQL
-
запросы, осуществляющие занесение данных в табли
цы;

-

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

-

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


2.5. Пример выполнения работы


Для входа в SQL*Plus наберите "sqlplus", после чего введите имя и
пароль, выданные администратором. В интерактивном
режиме SQL*Plus
достаточно набрать нижеприведенные запросы, чтобы получить ответ на
них. Для пакетного режима необходимо создать файл средствами операц
и
онной системы, в который поместить данный запрос. Затем, зайдя
в SQL*Plus, с
озданный файл запускается на выполнение командой
'@ <имя_файла>'.












Рис. 3. Рабочее окно SQL*Plus в ОС Microsoft Windows

-
36
-

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

Таблица 6

Соответствие названий отношений и таблиц


Название реляц
и
онного отношения

Названия атрибутов

Название
таблицы

Названия полей

Работник

Табельный номер

Worker

Tabnumber

ФИО

Fio

Отдел

Department

Виды работ

Название работы

Workkind

Workname

Расценка

Price

Выполненные
работы

Номер наряда

Donework

Ordernumber

Дата

выполнения

Donedate

Название работы

Workname

Табельный номер

Tabnumber

Выполнение

Табельный номер

Execute

Tabnumber

Название

работы

Workname


Создадим таблицу,
содержащую сведения о работниках:

CREATE TABLE WORKER(

TABNUMBER NUMBER (6
) NOT NULL,

FIO

VARCHAR
(20),

DEPARTMENT

VARCHAR
(10));

Данная команда установит таблицу с тремя столбцами, первый из к
о
торых будет характеризовать табельный номер
работника в пространстве из
шести цифр. Во втором столбце будет содержаться имя работника с испол
ь
зованием до 20 символов. В третьем столбце будет представлен отдел,
в котором работает сотрудник в пространстве из 10 символов.

После осуществления

этой операции система выдаст сообщение:
"таблица создана”
.

Таблицы создаются пустыми (без данных), если не специфицирован
подзапрос. Аналогичным образом создадим таблицу, отражающую сведения
о
видах работ:

CREATE

TABLE

WORKKIND
(

WORKNAME CHAR(15) NO
T NULL
,

PRICE

NUMBER

(7, 2));


Таблица выполненных работ:

CREATE

TABLE

DONEWORK
(

ORDERNUMBER NUMBER(6
) NOT NULL,

DONEDATE DATE
NOT NULL,

WORKNAME CHAR(15),

TABNUMBER NUMBER(6)
);


-
37
-

Таблица

выполнения
:

CREATE TABLE EXECUTE(

TABNUMBER NUMBER (6
),

WORKNAME

VARCHAR

(20));


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

Например, увеличение размера столбца в таблице, содержащей св
е
дения о видах работ с 15 символов до 20, выглядит
так:

ALTER TABLE WORKKIN
D

MODIFY
WORKNAME CHAR(20)
;

Для замены спецификации столбца варианта NOT NULL на NULL в
рассмотренном примере может выглядеть так:

ALTER TABLE WORKKIN
D

MODIFY
WORKNAME CHAR(20) NU
LL
;

Заполнение таблицы

данными можно осуществить следующим образом:

INSERT INTO WORKER

VALUES (3456, „
ИВАНОВ

Р
.
С
.‟, „
ОТДЕЛ

СБЫТА
‟ );

После осуществления этой операции система выдаст сообщение:
“1 запись создана”

Эту же операцию можно осуществить и так:

INSERT

INTO WOR
KER (DEPARTMENT, TAB
NUMBER, FIO)

VALUES („ОТДЕЛ СБЫТА
‟,3456, „ИВАНОВ Р. С
.‟);

Если неизвестно значение одного или нескольких столбцов и они
имеют спецификацию NOT NULL, например, неизвестна дата закладки
фундамента, то можно записать так:

INSERT INTO DONEW
ORK (ORDERNUMBER, WO
RKNAME)
VALUES (36254, „
ЗАКЛАДКА

ФУНДАМЕНТА
‟ );

или

так
:

INSERT

INTO DONEWORK

VALUES

(36254,
NULL
, „
ЗАКЛАДКА

ФУНДАМЕНТА
‟ );

Для модификации информации в столбцах, можно использовать
команду UPDATE. Например, необходимо изменить
табельный номер
Иванова Р.С.:

UPDATE

WORKER

SET

TABNUMBER
=1020,

WHERE FIO‟ИВАНОВ Р.

С.‟;

-
38
-

Иногда необходимо по той или иной причине удалить данные из та
б
лицы. Тогда необходимо поступить так. Например, необходимо удалить
записи о закладке
фундамента:

DELETE FROM DONEWORK

WHERE WORKNAME„
ЗАКЛАДКА

ФУНДАМЕНТА
‟;

Если же надо удалить всю таблицу, например с информацией о видах
работ, можно поступить так:

DROP

TABLE

WORKKIND
;

Для получения сведения о заработной плате каждого работника:

SELECT WOR
KER.FIO, SUM (WORKKIND.PRICE)

FROM WORKER, WORKKIND, DONEWORK

WHERE WORKER.TABNUMBER=DONEWORK.TABNUMBER

AND DONEWORK.WORKNAME=WORKKIND.WORKNAME

GROUP BY WORKER.TABNUMBER;

Получить сведения о заработной плате всех работников, у которых
она меньше 500:

SEL
ECT WORKER.FIO, SUM (WORKKIND.PRICE)

FROM WORKER, WORKKIND, DONEWORK

WHERE WORKER.TABNUMBER=DONEWORK.TABNUMBER

AND DONEWORK.WORKNAME=WORKKIND.WORKNAME

GROUP BY WORKER.TABNUMBER

HAVING

SUM

(
WORKKIND
.
PRICE
)500;

Получить наименования всех работ, расценки
которых выше среднего:

SELECT WORKNAME

FROM WORKKIND

WHERE PRICE�(SELECT AVG(PRICE) FROM WORKKIND);


Контрольные вопросы

1.

Характеристика СУБД Oracle.

2.

Характеристика SQL*Plus.

3.

Типы данных в СУБД Oracle и их основные характеристики.

4.

Характеристика языка SQL.

5.

Основные команды манипуляции с таблицами в СУБД Oracle (синтаксис,
назначение).

6.

Фразы SELECT

предложения и их назначения.

7.

Функции вычислений в SELECT

предложениях.

8.

Сущность простого объединения.

9.

Сущность декартового объединения.

10.

Сущность внешнего
объединения.

11.

Сущность запроса и коррелированного запроса.

12.

Операции реляционной алгебры.

13.

Реляционное исчисление с переменными
-
кортежами.

14.

Формулы реляционного исчисления и операции над переменными
-
кортежами.

15.

Реляционное исчисление с переменными на доменах.

-
39
-

3
. ИСПОЛЬЗОВАНИЕ ПР
ОГРАММНЫХ ЕДИНИЦ
PL
/
SQL


3.1. Основы
PL
/
SQL



PL/SQL



это принадлежащее Oracle процедурное расширение SQL,
языка реляционных баз данных. PL/SQL полностью интегрирует совреме
н
ные средства разработки программного обеспечения, такие
как инкапсул
я
ция данных, скрытие информации, переопределение имен и обработка и
с
ключений, и тем самым доставляет современное искусство программир
о
вания серверу Oracle и разнообразным инструментам Oracle.

PL
/
SQL



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

-

поддержку
SQL
;

-

повышение продуктивности разработки;

-

улучшение производительности выполнения;

-

переносимость;

-

интеграцию с
ORACLE
.

PL
/
SQL

не только позволяет вставлять, удалять,
обновлять и извл
е
кать данные
Oracle

и управлять потоком предложений для обработки этих
данных. Более того, можно объявлять константы и переменные, определять
подпрограммы (процедуры и функции) и перехватывать ошибки времени
выполнения. Таким образом,
PL
/
SQL

комбинирует возможность манипул
и
рования данными
SQL

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

SQL

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

SQL



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

сам определяет н
аилучший способ
удовлетворения вашего запроса. Более того, необходимая связь между п
о
следовательными предложениями отсутствует, поскольку
Oracle

выполняет
предложения
SQL

по одному за раз.

PL
/
SQL

позволяет

использовать как все предложения манипулиров
а
ния данными языка
SQL
, команды управления курсорами и транзакциями,
так и все функции, операторы и псевдостолбцы
SQL
. Таким образом, имее
т
ся возможность гибко и безопасно манипулировать данными
Oracle
.

Без P
L/SQL система O
racle

должна обрабатывать предложения SQL
по одному за раз. Каждое предложение SQL приводит к очередному обр
а
щению к O
racle

и дополнительным накладным расходам. Эти накладные
расходы могут стать существенными, когда выдается много предложени
й
SQL в сетевой среде. Каждое выдаваемое предложение SQL должно быть
послано по сети, утяжеляя сетевой трафик.

-
40
-

При PL/SQL, однако, целый блок предложений может быть послан в
O
racle

за один раз. Это позволяет радикальн
о сократить общение между
приложением и O
racle
. Если приложение интенсивно использует базу да
н
ных, можно сгруппировать предложения SQL, используя управляющие
структуры, а затем послать этот сгруппированный блок в O
racle

для испо
л
нения. Например, чтобы выпо
лнить десять индивидуальных предложений
SQL, требуется десять вызовов, но для выполнения подпрограммы, соде
р
жащей десять предложений SQL, необходим лишь один вызов O
racle
.

Приложения, написанные на PL/SQL, переносимы на любое оборуд
о
вание и в среду любой
операционной системы, на которых выполняется
O
racle
. Иными словами, программы PL/SQL могут выполняться всюду, где
может выполняться Oracle; не требуется перенастраивать их на каждое н
о
вое окружение. Это значит, что можно разрабатывать библиотеки перен
о
симы
х программ, которые можно использовать в различных окружениях.

PL/SQL


это язык, структурированный блоками. Это значит, что о
с
новные единицы (процедуры, функции и анонимные блоки), составляющие
программу PL/SQL, являются логическими блоками, которые м
огут соде
р
жать любое число вложенных в них подблоков. Обычно каждый логический
блок соответствует некоторой проблеме или подпроблеме, которую он р
е
шает. Таким образом, PL/SQL поддерживает подход к решению задач по
принципу ©разделяй и властвуйª, известный
как пошаговое уточнение.

Блок

(или подблок) позволяет группировать логически связанные
объявления и предложения. Благодаря этому можно размещать объявления
близко к тем местам, где они используются. Объявления локальны в блоке
и перестают существовать,
когда блок завершается.

Как показывает рис. 4, блок PL/SQL имеет три части: декларативную
часть, исполняемую часть и часть обработки исключений (исключением
в PL/SQL называется условие, вызывающее предупреждение или ошибку).
Исполняемая часть обяза
тельна; две остальные части блока могут отсутс
т
вовать.












Рис. 4. Структура блока
PL
/
SQL

DECLARE




BEGIN




EXCEPTION



END
;

Объявления

Выполняемые предложения

Обработчики исключений

-
41
-

Порядок частей блока логичен. Блок начинается с
декларативной ча
с
ти, в которой объявляются объекты. С объявленными объектами
осущест
в
ляются манипуляции в исполнительной части. Исключения, возбуждаемые во
время исполнения, могут быть обработаны в части обработки исключений.

Каждый блок может содержать др
угие блоки; иными словами, блоки
могут быть вложены друг в друга. Вложенный блок называется подблоком;
он вложен в окружающий блок. Можно вкладывать блоки в исполнительной
части или части обработки исключений блока PL/SQL, но не в декларати
в
ной части. Кром
е того, можно определять локальные подпрограммы в де
к
ларативной части любого блока. Однако вызывать локальные подпрогра
м
мы можно только из того блока, в котором они определены.


Переменные и константы


PL/SQL позволяет объявить переменные и константы, а з
атем и
с
пользовать их в SQL и процедурных предложениях в любом месте, где д
о
пускается использование выражения. Однако ссылки вперед не допускаю
т
ся. Таким образом, необходимо объявить переменную или константу пре
ж
де, чем сможете ссылаться на нее в других пре
дложениях, в том числе
в других объявлениях.

Каждая константа и переменная имеет тип данных, который спец
и
фицирует ее формат хранения, ограничения и допустимый интервал знач
е
ний. PL/SQL предусматривает разнообразие предопределенных скалярных
и
составных типов данных. Скалярный тип не имеет внутренних компонент.
С
оставной тип имеет внутренние компоненты, которыми можно манипул
и
ровать индивидуально.

Скалярные

типы
:

BINARY_INTEGER, DEC, DECIMAL, DOUBLE PRECISION,
FLOAT, INT, INTEGER, NATURAL,
NUMBER, NUMERIC, POSITIVE,
REAL, SMALLINT, CHAR, CHARACTER, LONG, LONG RAW, RAW, R
O-
WID, STRING, VARCHAR, VARCHAR2, DATE, BOOLEAN.

Составные

типы
:

RECORD, TABLE.

Объявляемая

переменная

может

иметь

любой

тип

данных
,
присущий

SQL
,
такой

как

NUMBER
,
CHAR

и

DAT
E
,
или

присущий

PL
/
SQL
,
такой

как

BOOLEAN

или

BINARY
_
INTEGER
.
Например, переменная с именем
tab_number может хранить шестизначные числовые значения, а переменная
с именем work_done может принимать булевские значения TRUE или
FALSE:

TAB_NUMBER NUMBER(6);


WORK_DONE BOOLEAN;

Также можно объявлять записи и таблицы PL/SQL, используя с
о
ставные типы данных PL/SQL: RECORD и TABLE.

-
42
-

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

Первый способ использует оператор присваивания : (двоеточие, за
которым следует знак равенства). Слева от оператора присваивания кодир
у
ется имя переменной, а справа


выражение.

Второй способ присвоить значение переменной


это извлечь в нее
значение из базы данных посредством фразы
INTO

предложения
SELECT

или
. Например, общая сумма стоимостей работ передается пер
е
менной
total
:

SELECT

SUM
(
PRICE
)
INTO

TOTAL

FROM

WORKKIND


Пос
ле этого значение переменной
total

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

Объявление константы аналогично объявлению переменной, с той
разницей, что необходимо добавить ключевое слово CONSTANT и сразу
присвоить

константе значение. Впоследствии никакие присваивания ко
н
станте не допускаются. Например, создание константы
minimal
_
sum
:

MINIMAL
_
SUM

CONSTANT

FLOAT

:= 10000.00;


Атрибуты


Переменные и константы PL/SQL имеют атрибуты, т.е. свойс
тва, п
о
зволяющие ссылаться на тип данных и структуру объекта, не повторяя его
объявление. Аналогичные атрибуты имеются у таблиц и столбцов базы да
н
ных, что позволяет упростить объявления переменных и констант.

Атрибут %TYPE представляет тип данных перем
енной, константы
или столбца. Он особенно полезен при объявлении переменной, которая
ссылается на столбец из таблицы базы данных. Например, предположим,
что таблица
worker

содержит столбец с именем
fio
. Чтобы дать переменной
my_
fio

тот же тип данных, что у

столбца
fio
, не зная точного определения
этого столбца в базе данных, объявляется my_
fio

с использованием атриб
у
та %TYPE:

MY_FIO WORKER.FIO%TYPE;

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

в базе данных изменится (например, увеличится его
длина), тип данных переменной my_fio изменится соответственно во время
выполнения.

В PL/SQL для группирования данных ис
пользуются записи. Запись
состоит из нескольких полей, в которых могут храниться значения данных.
Атрибут %ROWTYPE обозначает тип записи, представляющей строку в
таблице. Такая запись (т.е. переменная, объявленная с атрибутом
%ROWTYPE) может хранить целую
строку данных, выбранную из таблицы
или извлеченную из курсора.

-
43
-

Столбцы в строке таблицы и соответствующие поля в записи имеют
одинаковые имена и типы данных. Для обращения к значениям полей зап
и
си используются квалифицированные ссылки, как показывает след
ующий
пример:

DECLARE


WORKER_REC WORKER%ROWTYPE;

MY_FIO := WORKER_REC.FIO;


Управляющие структуры


Управляющие структуры составляют наиболее важное расширение
языка
SQL

в
PL
/
SQL
. Благодаря им мо
жно не просто манипулировать да
н
ными
Oracle
, но и управлять потоком выполнения, используя предложения
условного, итеративного и последовательного управления выполнением, т
а
кими как
IF
-
THEN
-

ELSE
,
FOR
-
LOOP
,
WHILE
-
LOOP
,
EXIT
-
WHEN

и
G
O-
TO
. В совокупности, э
ти предложения могут обработать любую ситуацию.

Часто бывает необходимо предпринять альтернативные действия в
зависимости от обстоятельств. Предложение
IF
-
THEN
-
ELSE

позволяет
управлять тем, будет ли выполняться последовательность предложений.
Фраза IF пров
еряет условие; фраза THEN определяет, что делать, если это
условие истинно (TRUE); фраза ELSE определяет, что делать, если это у
с
ловие ложно (FALSE) или пусто (NULL).

IF SALA�RY= 4500 THEN



ELSE




END IF;

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

LOOP



...

END

LOOP
;

Для цикла FOR
-
LOOP задается интервал целых чисел, и предло
жения
внутри цикла выполняются один раз для каждого целого в этом интервале.

FOR I IN 1..100 LOOP



END

LOOP
;

Чтобы организовать цикл WHILE
-
LOOP, условие ассоциируется с
последовательностью предложений.
Перед каждым повторением цикла это
условие вычисляется. Если оно дает TRUE, то предложения цикла выпо
л
-
44
-

няются, и управление возвращается на начало цикла. Если условие дает
FALSE или NULL, то цикл завершается, и управление передается на сл
е
дующее за циклом п
редложение.

WHILE SALARY 4000 LOOP



END

LOOP
;

Предложение EXIT
-
WHEN позволяет прекратить работу цикла, когда
обнаруживаются условия, которые делают дальнейшее повт
орение цикла
нежелательным или невозможным. Когда встречается предложение EXIT,
вычисляется условие, которое специфицировано фразой WHEN. Если это
условие истинно, то цикл завершается, и управление передается на сл
е
дующее за циклом предложение.

LOOP



TOTAL := TOTAL + SALARY;


EXIT WHEN TOTAL � 25000;

END LOOP;

Предложение GOTO позволяет передать управление на метку
безу
с
ловно. Метка, которая представляет собой необъявляемый идентификатор,
заключенный в двойные угловые скобки, должна предшествовать выпо
л
няемому предложению или блоку PL/SQL. При своем выполнении предл
о
жение GOTO передает управление на помеченное предло
жение или блок.

IF SALARY � 90 THEN

GOTO LABEL1;

END

IF
;


LABEL
1��


Курсоры


Для выполнения предложений SQL и хранения их результатов O
racle

использует рабочие области, называемые личными областями SQL. Конс
т
рукт

PL/SQL, называемый курсором, позволяет обращаться к личной обла
с
ти SQL по имени и извлекать из нее информацию.

Есть два вида курсоров: неявные и явные. PL/SQL неявно объявляет
курсор для любого предложения манипулирования данными SQL, в том
числе для запр
оса, возвращающего только одну строку. Для запросов, во
з
вращающих более одной строки, вы можете явно объявить курсор, чтобы
обрабатывать возвращаемые строки по одной. Например
:

DECLARE



CURSOR CUR1 IS


SELECT FIO, TAB_NUMBER FROM WORKER WHERE D
E-
PARTMENT‟
ОТДЕЛ

СБЫТА
‟;


...

-
45
-

Множество с
трок, возвращаемых многострочным запросом, называе
т
ся активным множеством. Его размер равен числу строк, удовлетворяющих
условиям поиска. Явный курсор ©указываетª на текущую строку в активном
множестве. Это позволяет программе обрабатывать строки по одной
за раз.


Обработка многострочного запроса напоминает обработку файла.
Программа PL/SQL открывает курсор, обрабатывает строки, возвращенные
запросом, а затем закрывает курсор. Точно так же, как указатель файла о
т
мечает текущую позицию в открытом файле, курс
ор отмечает текущую п
о
зицию в активном множестве.

Для управления курсором используются предложения OPEN, FETCH
и CLOSE.

Предложение OPEN выполняет запрос, ассоциированный с курсором,
идентифицирует активное множество и позиц
ионирует курсор перед его
первой строкой. Предложение FETCH извлекает текущую строку и продв
и
гает курсор к следующей строке. После того, как обработана последняя
строка, предложение CLOSE закрывает курсор.

В большинстве случаев, требующих явного курсора, м
ожно испол
ь
зовать курсорные циклы FOR вместо предложений OPEN, FETCH и CLOSE,
чтобы упростить кодирование. Курсорный цикл FOR неявно объявляет и
н
декс своего цикла как запись %ROWTYPE, открывает курсор, итеративно
извлекает строки данных из активного множес
тва в поля записи, и закрыв
а
ет курсор после того, как все строки обработаны. В следующем примере
курсорный цикл FOR неявно объявляет запись emp_rec как принадлежащую
типу c1%ROWTYPE:

DECLARE


CURSOR CUR1 IS

SELECT FIO,TAB_NUMBER,SALARY FROM WORKER;

BEGIN


FOR WORK_REC IN CUR1 LOOP


TOTAL := TOTAL+WORK_REC.SALARY;


END

LOOP
;

END
;

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

Если объявляется курсор, извлекающий данные сотрудника, то
PL/SQL позволяет создать запись, содержащую такую же информацию.

Это делается с помощью атрибут
а %ROWTYPE. Предположим, зап
и
саны следующие объявления курсора и записи:

DECLARE

CURSOR
С
UR1 IS SELECT FIO, TAB_NUMBER, SA
L-
ARY FROM WORKER;


WORKER
_
REC

CUR
1%
ROWTYPE
;



...

-
46
-

При выполнении предложения


FETCH

CUR
1
INTOWORKER
_
REC
;

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



Обработка ошибок


В PL/SQL можно обрабатывать как внутренне определенные, так и
определенные условия ошибок, называемые исключениями. Когда возник
а
ет ошибка, исключение возбуждается. Это значит, что нормальное выпо
л
нение прекращается, и управление
передается на часть обработки исключ
е
ний вашего блока или подпрограммы PL/SQL. Для обработки исключений
создаются специальные программы


обработчики исключений.

Предопределенные исключения возбуждаются неявно исполнител
ь
ной системой. Например, при попыт
ке деления числа на 0 автоматически
возбуждается предопределенное исключение ZERO_DIVIDE. Пользовател
ь
ские исключения должны возбуждаться явно, через предложения RAISE.

Можно определить собственные исключения в декларативной части
любого блока или подпрог
раммы PL/SQL. В исполнительной части пров
е
ряются условия, требующие специального внимания.

Если обнаруживается, что это условие существует, выдается команда
RAISE, с указанием имени исключения.

DECLARE



SAL NUMBER(7,2);


SAL_EX EXCEPTION;

BEGIN


SELECT SALARY INTO SAL FROM WORKER


WHERE TAB_NUMBER=:ID;


IF SAL NULL THEN


RAISE SAL_EX;


ELSE


:PROFIT := SAL * 0.05;


END IF;




EXCEPTION


WHEN SAL_EX THEN



END;

Переменные id и
profit

объявляются и получают значени
я во вне
ш
нем окружении.


3.2. Использование программных единиц
PL
/
SQL


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

конструкты, называемые программными единицами. Помимо таких пр
о
граммных единиц, как блоки и подпрограммы, предусматриваетс
я спец
и
альный конструкт пакет, который позволяет группировать взаимосвязанные
объекты программы в единицы большего размера.

PL/SQL имеет два типа подпрограмм


процедуры и функции
; всем
им можно передавать параметры при вызове. Как показывает следующий
пр
имер, подпрограмма выглядит как миниатюрная программа. Она начин
а
ется с заголовка, за которым следуют необязательная декларативная часть,
исполнительная часть и необязательная часть обработки исключений:

PROCEDURE

PROFIT_CALC (ID NUMBER) IS


PROFIT REAL;


SAL_EX EXCEPTION;

BEGIN

SELECT
SAL*0.25 INTO PROFIT FROM WORKER
WHERE TAB_NUMBER= ID;


IF PROFIT IS NULL THEN


RAISESAL_EX;


END IF;

EXCEPTION



WHEN SAL_EX THEN


...

END

PROFIT
_
CALC
;

При вызове эта

процедура принимает номер сотрудника. Она испол
ь
зует этот номер для выбора из таблицы базы данных значения для этого
сотрудника. Затем процедура проверяет значение. Если это значение пусто,
возбуждается исключение.


PL/SQL позволяет объединять логически связанные типы, програм
м
ные объекты и подпрограммы в пакет. Каждый пакет легко понять, а инте
р
фейсы между пакетами просты, ясны и хорошо определены. Это облегчает
разработку приложений.

Пакет обычно состоит из
двух частей: спецификации и тела. Спец
и
фикация


это интерфейс пакета с вашими приложениями; она объявляет
типы, константы, переменные, исключения, курсоры и подпрограммы, до
с
тупные для использования. Тело пакета определяет курсоры и подпрогра
м
мы, и тем са
мым реализует спецификацию пакета. В следующем
примере объединяются в пакет две процедуры управления кадрами:

PACKAGE WORKER_ACTIONS IS

PROCEDURE HIRE_WORKER (ID NUMBER, FIO CHAR, ...);


PROCEDURE FIRE_WO
RKER (ID NUMBER);

END WORKER_ACTIONS;

PACKAGE BODY WORKER_ACTIONS IS


PROCEDURE HIRE_WORKER (ID NUMBER, FIO CHAR, ...) IS

-
48
-


BEGIN


INSERT INTO WORKER VALUES (ID, FIO,

...);


END HIRE_WORKER;


PROCEDURE FIRE_WORKER (ID NUMBER) IS


BEGIN


DELETE FROM WORKER WHERE TAB_NUMBER=ID;



END FIRE_WORKER;

END WORKER_ACTIONS;

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

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

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


Хранимые подпрограммы


Именованные б
локи (подпрограммы) PL/SQL можно компилировать
отдельно и передавать на постоянное хранение в базу данных O
racle
, где
они готовы к исполнению.

Подпрограмма, явно созданная (посредством create) с помощью и
н
струмента O
racle
, называется ХРАНИМОЙ подпрограммо
й. Однажды о
т
компилированная и сохраненная в словаре данных, она является объектом
базы данных, к которому можно обращаться из любого приложения, соед
и
ненного с этой базой данных.

Хранимые подпрограммы, определенные внутри пакета, называются
пакетированны
ми подпрограммами; подпрограммы, определенные вне п
а
кета, называются независимыми подпрограммами. (
П
одпрограммы, опред
е
ленные внутри других подпрограмм или внутри блока PL/SQL, называются
локальными подпрограммами. Такие подпрограммы недоступны для других
приложений, и существуют лишь для удобства окружающего блока.)

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

Можно вызывать хранимые подпрограммы из триггера б
азы данных,
другой хранимой подпрограммы, приложения

прекомпилятора

O
racle

или
интерактивно из SQL*Plus. Например, из SQL*Plus вызвать независимую
процедуру
hire
_
worker

следующим способом:

S
QL� EXECUTE HIRE_WORKER(132, '
ШВАРЦ

ИВАН

ПЕТРОВИЧ
');

-
49
-

Подпрограммы хранятся в синтаксически разобранной, откомпил
и
рованной форме. Поэтому при вызове они загружаются и передаются пр
о
цессору PL/SQL немедленно. Более того, хранимые подпрограммы испол
ь
зуют преимущества разделяемой памяти в O
racle
.

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

Триггеры базы данных


Триггер базы данных


это хранимая подпрограмма, ассоциирова
н
ная с таблицей. Можно заставить O
racle

автоматически возбуждать т
риггер
базы данных перед или после исполнения предложения insert,
update

или

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

CREATE TRIGGER OR REPLACE SALARY_AUDIT


AFTER UPDATE OF SALARY ON WORKER


FOR EACH ROW

BEGIN


INSERT INTO WORKER_AUDIT VALUES ...

END;


Можно использовать все предложения манипулирования данными

SQL и любые процедурные предложения в исполняемой части триггера б
а
зы данных.

Более подробная информация по синтаксису команд
PL
/SQL и их и
с
пользованию при реализации различных подпрограмм представл
ена в [7].


3.3. Задание для практической работы


Порядок выполнения работы

1.

Ознакомиться с возможностями и конструкциями языка
PL
/
SQL
.

2.

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

3.

Выпо
лнить вызов процедур и проконтролировать результаты их выпо
л
нения.

4.

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

5.

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


Содержание отчета

Кроме титульного листа и цели
работы отчет должен содержать:



PL
/SQL
-
программы, осуществляющие манипуляции над
данными созданных таблиц;



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

-
50
-

3.4. Пример выполнения работы


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

Для создания триггеров используем среду
SQL
*
Plus
, для чего запу
с
тим его и соединимся с базой данных как было показано в п. 2.5.

После запуска
SQL
*
Plus

запустим встроенный редактор, используя
команду „
ed
‟. В окне редактора набирается текст триггера. Затем, после з
а
крытия окна редактора и подтверждения внесения изменений выполним
текст триггера, выполнив команду „/‟.

Создадим триггер, выполняющий автомати
ческое присвоение знач
е
ний табельных номеров работников. Для этого предварительно создадим
автоинкрементную последовательность командой:

CREATE

SEQUENCE

AUTO
_
TAB
_
NUMBER


START WITH 1


INCREMENT BY 1


MINVALUE 1


MAXVALUE

200


CYCLE


ORDER
;

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

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

CREATE OR REPLACE TRIGGER AUTO_TAB

BEFORE INSERT

ON W
ORKER

FOR EACH ROW

DECLARE


TAB NUMBER(38);

BEGIN

SELECT AUTO_TAB_NUMBER.NEXTVAL INTO TAB FROM
DUAL;

:NEW.TAB_NUMBER:=TAB;

END
;

Здесь используются псевдостолбцы
new

и
nextval
. Псевдостолбец
new
, как и парный к нему
old

используются для указания нового (на выходе
триггера) и старого (на входе) значений столбцов таблиц соответственно.
-
51
-

Псевдостолбец
nextval

выбирает следующее значение из последовател
ь
ности.

Создадим журнал обращений на изменение к таблице работников н
е
зависимо от типа обращения. Для этого создадим таблицу
audit

c

полем
day

типа
date
, в которую будем записывать значения даты обращения

CREATE TABLE AUDIT (DAY DATE);

Текст

триггера
:

CREATE OR REPLACE TRIGGER WORKER_AUDIT

AFTER INSERT OR UPDATE OR DELETE

O
N WORKER

FOR EACH ROW

BEGIN

INSERT INTO AUDIT VALUES(SYSDATE);

END
;

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


Контрольные вопросы


1.

Характеристика
PL
/
SQL
.

2.

Сфера применения
PL
/
SQL
.

3.

Использование переменных и констант.

4.

Использование управляющих конструкций.

5.

Использование курсоров.

6.

Обработка ошибок.

7.

Программные единицы
PL
/
SQL
.

8.

Использование триггеров базы данных.




БИБЛИОГРАФИЧЕСКИЙ С
ПИ
СОК


1.

Четвериков В.Н., Самохвалов Э.Н., Ревунков Г.И. Базы данных.


М.: Высшая
школа, 1987.


248 с.

2.

Блюмин С.Л., Погодаев А.К., Тарасов Н.А. Математическое обеспечение инфо
р
мационных технологий. Часть1. Реляционная математика и базы данных: Уче
б
ное пособи
е.


Липецк: ЛЭГИ, 2001.


75 с.

3.

Oracle
8. Энциклопедия пользователя: Пер. с англ.


Киев: Издательство “Ди
а
Софт”, 1999.


864 с.

4.

Погодаев А.К., Батищев Р.В. Обработка данных на языке
SQL

в реляционных
системах:
У
чебное пособие.


Липецк: ЛГТУ, 2000.


63 с
.

5.

Грабер М. Понимание
SQL
:
П
ер. с англ.


1993.

6.

Краткий справочник по
SQL
*
Plus
.


1990.

7.

Справочник и руководство пользователя
PL
/
SQL
.


1992.


-
52
-



















Учебное издание


Погодаев Анатолий Кирьянович

кандидат технических наук, доцент,


Дозоров
Владимир Владимирович

кандидат технических наук

Программное обеспечение автоматизированных систем.

Разработка информационной структуры

Учебное пособие


Техническое редактирование
Правильникова Н.С.


Подписано в печать 23.09.2002 г. Бумага 50
-
60 г/м
2
.

Формат 60х84/16. Гарнитура ©Таймсª. Усл. печ. л. 3,25.

Тираж 250 экз. Заказ № 641. Цена свободная.

Издательство ЛЭГИ. 398 600, Липецк, ул. Интернациональная, 5а.

Ризография ЛЭГИ. 398 600, Липецк, ул. Интернациональная, 5а.


Приложенные файлы

  • pdf 4398793
    Размер файла: 674 kB Загрузок: 0

Добавить комментарий