close

Вход

Забыли?

вход по аккаунту

?

МУ по ОБД

код для вставкиСкачать
3 ЗАВДАННЯ ДО ЛАБОРАТОРНИХ РОБІТ
3.1 ЛАБОРАТОРНА РОБОТА № 1
Тема роботи:
Операции с базой данных
Мета роботи:
Изучить операции, проводимые с базами данных в целом. Получить
навыки использования программы "IB Expert" для создания, удаления, регистрации,
подключения, извлечения метаданных, резервного копирования и восстановления базы
данных СУБД Firebird. Изучить SQL-операторы для создания, подключения и удаления
базы данных. 1
Теоретические сведения На сегодняшний день известно более двух десятков серверных СУБД, из которых
наиболее популярными являются Oracle, Microsoft SQL Server, Informix, DB2, Sybase,
InterBase, MySQL. Говоря об InterBase, мы будем подразумевать семейство трех серверов – InterBase,
Firebird и Yaffil, так как приемы работы с этими СУБД практически идентичны. СУБД InterBase – это SQL-сервер реляционных баз данных, предназначенный для
использования в приложениях с архитектурой клиент-сервер. Этот сервер обладает
всеми характеристиками, обязательными для SQL-сервера баз данных, предъявляет
минимальные требования к аппаратным средствам, обеспечивает легкость в
администрировании, надежность и производительность, необходимую для создания
современных информационных систем любого масштаба. В InterBase база данных представляет собой один или несколько файлов, в которых
хранятся данные пользователя и метаданные. Расширение этого файла может быть
любым, но при выполнении лабораторных работ следует использовать расширение
"*.
g
db". Файл с базой данных представляет собой набор страниц одинакового размера.
Размер страницы задается при создании базы данных и может быть изменен только при
ее восстановлении из резервной копии. Чтение и запись данных в базе данных
осуществляется постранично. Сервер СУБД не имеет интерфейса пользователя и для выполнения операций с
базой данных ему необходимо посылать команды с помощью какой-либо прикладной
программы. Для выполнения операций с базой данных при проведении лабораторных
работ предлагается использовать программу "IB Expert" (рис. 1), представляющую собой
наиболее распространенное и удобное средство администрирования баз данных
InterBase/Firebird/Yaffil. Большинство действий с базой данной InterBase в программе IBExpert может быть
осуществлено двумя способами: либо выполнением операторов языка SQL в окнах
"Script Execute" (подключение к базе данных не обязательно) и "SQL Editor" (требуется
подключение к базе данных), либо с использованием меню и диалоговых окон. В
последнем случае операторы SQL, которые требуются для выполнения данного
действия, будут сгенерированы и выполнены программой IBExpert автоматически. Для открытия окон "Script Execute" и "SQL Editor" используются одноименные
команды меню "Tools". 2 Ход работы 2.1 Создание базы данных Для создания базы данных можно использовать один из двух способов: 1. Выполнить команду "Database
Create Database..." в программе IBExpert, ввести
параметры создаваемой базы данных в диалоговом окне "Create Database" (рисунок 1.1) и
нажать кнопку [OK]. Рисунок 1.1 – Диалоговое окно создания базы данных
2. Выполнить в программе IBExpert
команду "
Tools
Script
Executive
", затем
ввести команды, создающие базу данных в окне "
Script
Execute
" (рисунок 1.2) и нажать
кнопку [
Run
Script
] (
F
9). Рисунок 1.2 – Окно выполнения сценария создания базы данных
В окне "Create Database" требуется ввести следующие параметры: 1. В поле "Server" необходимо выбрать тип подключения к серверу. Подключение
может быть локальным или удаленным. 2. Ввести имя сервера в поле "Server Name". Представляет собой название
компьютера в сети, на котором установлен сервер СУБД. Если сервер установлен на том
же компьютере, где сейчас работает пользователь, то в качестве имени компьютера
используется localhost. 3. В поле "Protocol" выбрать сетевой протокол TCP/IP, который будет
использоваться для подключения к серверу. (пункты 2 и 3 выполняются в случае
удаленного подключения).
2
4. В поле "Database" вводится путь и имя файла базы данных на том компьютере,
где она будет находиться. В примере база данных создается на локальном компьютере в
папке d:\
work
. Файл базы данных – Sales
.
g
db. 5. В полях "Username" и "Password" ввести имя и пароль пользователя,
зарегистрированного на сервере (по умолчанию – «
SYSDBA
» и «
masterkey
»
соответственно). 6. В поле "Page Size" выбрать размер страницы базы данных. Файл базы данных
разбивается на страницы фиксированного размера и все обращения к диску InterBase
выполняет постранично. Этот размер влияет на эффективность работы сервера InterBase
с базой данных. 7. В поле "Charset" выбрать кодировку по умолчанию для текстовых полей. Для
использования символов латинского и русского алфавита это кодировка Win1251. 8. Выбрать в поле "SQL Dialect" используемый диалект базы данных. В диалекте 3
в отличие от диалекта 1 используется расширенный набор типов данных, различается
регистр идентификаторов, записанных в двойных кавычках, а также не поддерживается
неявное приведение типов данных. 9. Чтобы сразу зарегистрировать созданную базу данных в программе IBExpert,
установить флажок "Register Database After Creating". После нажатия на кнопку [OK] программа "IB Expert" сгенерирует необходимый
SQL-код для создания базы данных с теми свойствами, которые указаны в этом
диалоговом окне и передаст его серверу СУБД для выполнения. Пример этих операторов
приведен на рисунке 1.2. Если параметры введены правильно, база данных будет
создана. Содержащиеся в сценарии операторы отделяются друг от друга символом ";".
Сценарий может содержать поясняющие комментарии двух видов: многострочный
комментарий (начинается символами "
/*
" и заканчивается символами "
*/
") и
однострочный комментарий, который начинается символами "
--
" и продолжается до
конца строки. Если при создании базы данных выбрать опцию "Register Database After Creating",
то после создания базы данных откроется диалог регистрации базы данных (рисунок 1.3),
в котором все необходимые поля уже будут заполнены. Для регистрации базы данных
достаточно просто нажать кнопку [Register]. 3
Рисунок 1.3 – Окно регистрации базы данных в программе IBExpert
При создании базы данных возможны следующие типичные ошибки: 1. На целевом компьютере не запущен или не установлен сервер СУБД – т.е.
выполнять команду создания базы данных просто некому. 2. На целевом компьютере нет каталога, в котором предполагается создать базу
данных. 3. Файл, в котором должна будет находиться база данных на сервере, уже
существует. 2.2 Регистрация базы данных Перед выполнением с помощью программы IBExpert любых операций с
существующей базой данных она должна быть зарегистрирована. Для этого либо
используется команда меню "Database
Register Database", либо в окне создания базы
данных выбирается опция "Register Database After Creating". В результате о
ткрывается диалоговое окно "Database Registration" (рисунок 1.3), в
котором надо заполнить практически такие же поля, что и при создании базы данных,
затем нажать кнопку [Register]. Для проверки правильности параметров, введенных в оке регистрации базы
данных, следует нажать кнопку [Test Connect]. Это приведет к открытию диалогового
окна "Communication Diagnostics", в котором в поле "Test Results" будут содержаться
4
результаты подключения. Там же будут находиться сведения о версии используемого
сервера. После регистрации вся введенная о базе данных информация запоминается
программой IBExper
t и в окно "Database Explorer" в дерево на вкладке "Databases"
добавляется узел с зарегистрированной базой данных.
2.3 Подключение к базе данных Чтобы подключиться к зарегистрированной базе данных, надо выбрать нужную
базу данных в списке (рисунок 1.4) и выполнить команду "Database
Connect to
Database", либо сделать двойной щелчок мышкой на выбранной базе данных. Если все параметры подключения были введены правильно, то произойдет
подключение к базе данных, название подключенной базы данных в окне "Database
Explorer" будет выделено жирным шрифтом, а также появятся вложенные узлы c
объектами, содержащимися в подключенной базе данных.
Если окна "Database Explorer" нет на экране, выполните команду "View
DB
Explorer" или нажмите кнопку F
11.
После подключения к базе данных можно просматривать имеющиеся объекты,
создавать новые, вносить и просматривать данные, а также проводить операции с
имеющимися объектами. 2.4 Удаление базы данных Для удаления базы данных можно использовать один из трех способов: 1. Выполнить в программе "IB Expert" команду меню "Database
Drop Database", а
затем подтвердить свое желание в диалоговом окне. 2. Выполнить оператор DROP DATABASE. 3. Удалить файл с базой данных. 2.5 Извлечение метаданных Метаданными называется описание собственной структуры базы данных. Это
"данные о данных". В Firebird метаданные хранятся в 32-ух системных таблицах,
названия которых начинаются с символов "RDB$". При извлечении метаданные
представляют собой последовательность операторов, выполнение которых приводит к
созданию базы данных требуемой структуры. При извлечении метаданных имеется возможность извлечь также данные из таблиц
пользователя. Это также будут операторы языка SQL, выполнение которых восстановит
содержащиеся в таблицах данные пользователя. В этом случае результат извлечения
метаданных может служить резервной копией базы данных. Для извлечения метаданных используется команда главного меню "Tools
Extract
Metadata", которая
открывает
окно
"Extract Metadata". На рисунке 1.5 приведен пример
этого окна для базы данных "
Univer
_2.
g
db". 5
Рисунок 1.5 – Окно извлечения метаданных
В окне "Extract Metadata" можно либо выбрать объекты, информация о которых
будет извлечена, либо установить флажок "Extract all", чтобы извлечь все метаданные. Чтобы начать процесс извлечения, необходимо выбрать извлекаемую информацию,
затем с помощью выпадающего списка "Extract to" выбрать место, в которое будут
извлечены метаданные, а затем нажать кнопку [Start Extract] (F9). Если в качестве места извлечения выбрать "Script Executive", то после извлечения
откроется окно "Script Executive", в котором будут находиться извлеченные метаданные. 2.6 Резервное копирование и восстановление Резервное копирование (backup) базы данных и восстановление из резервной копии
(restore) – два важнейших и наиболее частых процесса, осуществляемых
администраторами баз данных. Резервное копирование базы данных – единственный
надежный способ предохранить данные от потери в результате поломки диска, сбоев
электропитания, действий злоумышленников и ошибок в программах. В процессе
резервного копирования создается независимый от платформы "снимок" базы данных, с
помощью которого можно перенести данные на другую операционную систему или даже
другую платформу. Полный цикл: резервное копирование и восстановление из резервной
копии приводит к корректировке статистической информации, является средством от
излишнего "разбухания" базы данных и необходимой операцией обслуживания базы
данных. Кроме того, миграция от одной версии сервера к другой также происходит при
помощи процесса backup/restore. Для создания резервной копии базы данных с помощью программы "IB Expert"
необходимо подключиться к базе данных, выполнить команду меню "Services
Backup
Database", в открывшемся диалоговом окне "Database Backup" задать несколько
параметров и нажать кнопку [Start Backup]. В результате будет создан файл с резервной
6
копией. Стандартным расширением таких файлов для Interbase
является "*.
g
bk". Файл с
резервной копией базы данных обычно на порядок меньше оригинала. Для восстановления базы данных из резервной копии используется команда
"Services
Restore Database". В результате откроется диалоговое окно "Database Restore",
в котором надо выбрать файл, из которого будет восстанавливаться база данных и
нажать кнопку [Start Restore]. 3 Задание Лабораторную работу следует выполнять в следующем порядке: 1. Создать на рабочую папку для хранения файлов, получаемых при выполнении
лабораторной работы. 2. На основании индивидуального задания выбрать имя файла создаваемой базы
данных. Для имени лучше всего выбрать одно или несколько английских слов,
соответствующих наименованию предметной области. 3. Открыть приложение "IB Expert". 4. Создать базу данных для своей предметной области с помощью диалога. 5. Зарегистрировать базу данных в программе "IB Expert" и подключиться к
созданной базе данных. 6. Извлечь метаданные для автоматической генерации команды создания базы
данных. 7. Удалить базу данных, выполнив команду "Database
Drop Database". 8. Создать базу данных вторым способом, выполнив в окне "Script Executive"
операторы, полученные при извлечении метаданных перед предыдущим удалением. 9. Создать резервную копию базы данных. 10. Удалить базу данных. 11. Восстановить базу данных из резервной копии. 12. Оформить отчет о выполнении лабораторной работы №1. 4 Отчет о выполнении работы Отчет о выполнении лабораторной работы №1 необходимо оформить на листах
формата A4 (титульный лист оформить в соответствии с приложением). Отчет должен
содержать описание и результаты работы, представляемые в следующей
последовательности: 1. Краткое описание предметной области в соответствии с вариантом. 2. Распечатка сценария создания базы данных (комментарии должны содержать
сведения об авторе, дате создания и выполняемых действиях). 3. Данные о версии использованного в лабораторной работе сервера СУБД. 4. Перечисление всех использованных при выполнении лабораторной работы
команд главного меню приложения "IB Expert" с краткими пояснениями выполняемых
действий. 5. Перечень файлов, полученных при выполнении лабораторной работы с
указанием их имен, места расположения, даты изменения и размеров (сценарий, база
данных, резервная копия базы данных, файл с отчетом). 5 Контрольные вопросы 1. Дать определение термина "База данных". 7
2. Что означают сокращения СУБД и DBMS? 3. Какие операции проводятся с базой данных в целом? 4. Что представляет собой база данных СУБД InterBase/Firebird? Какой по-лучился
размер файла с базой данных? От чего он зависит? Что содержит-ся в файле с базой
данных? 5. Какой рекомендуется выбирать размер страниц базы данных? 6. Что надо сделать, чтобы в базе данных можно было хранить символы рус-ского
алфавита? 7. Назвать отличия между первым и третьи диалектами базы данных. 8. Какие существуют способы создания и удаления базы данных? 9. Как указывается путь до файла с базой данных, расположенной на уда-ленном
компьютере? 10. Какие стандартные расширения имеют файлы баз данных и сценариев? 11. Как с помощью программы "IB Expert" подключиться к имеющейся базе
данных, расположенной на локальном компьютере? 12. Какое имя и начальный пароль имеет администратор сервера Inter-
Base/Firebird? 13. Какие виды комментариев могут содержать файлы сценариев для выпол-нения
операций с базой данных? 14. Сколько системных таблиц содержит созданная база данных? С каких символов
они начинаются? Привести названия любых 3-х системных таб-лиц. 15. Каковы правила оформления текста сценария? 3.
2 ЛАБОРАТОРНА РОБОТА № 2
Тема роботи:
Домены Цель работы Изучить типы данных InterBase. Получить навыки использования
про-граммы "IB Expert" для выполнения операций с доменами. Изучить SQL-операторы
для работы с доменами. 1 Теоретические сведения 1.1 Типы данных InterBase Понятие типов данных в реляционной модели данных соответствует понятию типа
данных в языках программирования. В современных реляционных БД допускается
хранение символьных, числовых данных, специальных числовых данных таких как
"деньги", а также дат и времени. В таблице 2.1 приведены основные типы данных, используемые в InterBase для
полей таблиц, при описании доменов, для переменных внутри хранимых процедур и
триггеров и для параметров в процедурах и функциях пользователя. Домен – это описание множества допустимых значений поля таблицы. Описание
домена показывает тип данных (например, число или строка), дли-ну данных и другие
ограничения (например, требование, чтобы первый сим-вол был буквой или чтобы
значение не превышало заданного числа). В InterBase домены используются только в
операторах создания и изменения структуры таблиц. 8
Таблица 2.1 – Типы данных InterBase
Название Размер Диапазон/точность
Описание SQL диалект 1
INTEGER 32 бита от –2 147 483 648 до 2
147 483 647 Длинное целое. SMALLINT 16 бит от –32 768 до 32 767 Короткое целое. FLOAT 32 бита от 3,4х10P–38P до
3,4х10P38P Число с одинарной точностью
(точность 7 значащих цифр). DOUBLE
PRECISION 64 бита от 1,7х10P-308P до
1,7х10P308P Число с двойной точностью
(точность 15 значащих цифр) DATE 64 бита от 1 января 100 года н.э.
до 29 февраля 32768
года Дата. Включает информацию о
времени. CHAR(
n
) n сим-
волов n = 1÷32767 Строка символов фиксиро-ванной
длины. VARCHAR(
n
) n сим-
волов n = 1÷32767 Строка символов переменной
длины. BLOB пере-
менный нет Двоичные большие объекты,
такие как графика, текст, циф-
ровой звук. Содержание опре-
деляется подтипом. SQL диалект 3
BIGINT 64 бита от -2P63P до 2P63P-1 Большое целое. DATE 32 бита от 1 января 100 года н.э.
до 29 февраля 32768
года Только дата без информации о
времени. TIME 32 бита от 00:00 до 23:59:9999 Данные о времени с точно-стью
до десятитысячной доли секунды.
TIMESTAMP 64 бита Комбинация типов DATE и
TIME.
Для создания доменов используется команда CREATE DOMAIN
, для изменения –
ALTER DOMAIN
, а для удаления – DROP DOMAIN
. Ниже приведен упрощенный синтаксис оператора создания домена. CREATE DOMAIN Имя_Домена [
AS
] <тип данных> [
DEFAULT {
Literal | NULL |
USER}
] [
NOT NULL
] [
CHECK (
Проверяемое_Условие
)
]; При описании синтаксиса операторов квадратные скобки обозначают
необязательный элемент, фигурные скобки объединяют группу элементов оператора в
один, | означает выбор одной из нескольких альтернатив. Элемент, расположенный в
угловых скобках должен поясняться отдельно. В приведенном примере необязательный элемент [
DEFAULT {
Literal | NULL | USER}
] 9
позволяет задать значение столбца по умолчанию с использованием одной из трех
возможностей. Первая возможность, обозначенная как Literal, позволяет задавать
значения по умолчанию в виде текстовых констант, чисел и дат. Вторая возможность –
указать в определении столбца DEFAULT NULL
, что приведет к тому, что во вновь
создаваемых записях значение этого столбца будет NULL
. Третий способ – указать при
определении столбца DEFAULT USER
. Если в этом случае в таблицу будет вставлена
строка без указания значения этого поля, то в него будет занесено имя текущего
пользователя. Если необходимо, чтобы поле имело какое-то непустое значение, в описание
домена вносят NOT NULL
. Последний необязательный элемент в описании домена позволяет заставить сервер
проверять заносимые в базу данных значения на выполнение заданного условия. Вместо элемента <тип данных>
должно быть использовано название одного из
типов данных InterBase (таблица 2.1). Фрагмент
сценария
создания
доменов
: /*************************************************
** Домены
***************************************************/ CREATE DOMAIN ID INTEGER NOT NULL
; -- идентификатор
CREATE DOMAIN Name VARCHAR(
30
) NOT NULL
; -- имя
CREATE DOMAIN CurrentDate DATE DEFAULT '
TODAY
' NOT NULL
; CREATE DOMAIN Percent INTEGER -- процент
CHECK (VALUE >= 0 AND
VALUE <= 100)
; -- текст
без
ограничения
длины
: CREATE DOMAIN MEMO BLOB SUB_TYPE TEXT SEGMENT SIZE 128
; Синтаксис оператора удаления домена: DROP DOMAIN Имя_Домена; Оператор изменения домена ALTER DOMAIN
позволяет изменить тип данных,
имя домена, а также добавить или удалить условие контроля данных. Чтобы изменить
домен можно также внести изменения в системные таблицы.
2 Ход работы Подключившись к базе данных можно выполнять операции по созданию,
удалению и изменению доменов. В окне "Database Explorer" появляются вложенные
узлы, которые можно использовать для выполнения операций с доменами. Чтобы увидеть сведения о домене, необходимо либо сделать на узле с названием
этого домена двойной щелчок мышью, либо воспользоваться ко-мандой "Edit Domain…
(Ctrl+O)" контекстного меню. В результате откроется окно со сведениями о доменах
(рис. 9). Для изменения имени домена достаточно отредактировать его в столбце "Name".
Для изменения типа данных следует воспользоваться выпадающим списком в столбце
"Field Type". Длину текстового поля можно изменить, если отредактировать значение в
столбце "Size". 10
Рисунок 2.1 – Окно программы "IB Expert" с информацией о доменах
Чтобы сделанные изменения были выполнены, необходимо нажать кнопку
[Compile] (Ctrl+F9). В результате этого откроется диалоговое окно "Compiling
domains…" (рисунок 2.2), в котором надо подтвердить (кнопка [Com-mit]) или отменить
(кнопка [Rollback]) выполнение операции. Рисунок 2.2 – Диалоговое окно
"Compiling Domains..."
3 Задание Лабораторную работу следует выполнять в следующем порядке: 1. Создать рабочую папку для хранения файлов, получаемых при выполнении
лабораторной работы №2. 11
2. Скопировать в эту папку файл сценария, созданный при выполнении
лабораторной работы №1. 3. Открыть окно "Script Executive" в приложении "IB Expert". 4. Используя кнопку [Load from File] на панели инструментов, открыть диалоговое
окно "Open SQL File" и с его помощью открыть файл сценария, скопированный ранее в
папку "ЛР2". 5. Изменить в сценарии комментарии, в которых должно быть отмечено, что это
сценарий для лабораторной работы №2 и исправить путь до файла с базой данных, чтобы
база данных создавалась в папке для второй лабораторной работы. 6. Добавить в сценарий команды создания доменов с использованием всех типов
полей, приведенных в таблице 2.1. Необходимо обязательно следовать правилам
оформления текста сценария. 7. Выполнить сценарий, создав в базе данных не менее 11 доменов. 8. Сохранить файл сценария. 9. Зарегистрировать созданную базу данных в программе "IB Expert" и
подключиться к ней. 10. Создать в диалоговом режиме столько доменов, сколько имеется в
выпадающем списке колонки "Field Type" типов данных. 11. Создать в папке резервную копию базы данных. 12. Оформить отчет о выполнении лабораторной работы №2. 4 Отчет о выполнении работы Отчет должен содержать описание и результаты работы, представляемые в
следующей последовательности: 1. Распечатка сценария создания базы данных с комментариями, которые должны
содержать сведения об авторе, дате создания, всех выполняемых действиях и пояснения
к доменам. 2. Перечень файлов, полученных при выполнении лабораторной работы с
указанием их имен, места расположения, даты изменения и размеров (сценарий, база
данных, резервная копия базы данных, файл с отчетом). 5 Контрольные вопросы 1. Дать определение термина "Домен". 2. Где в InterBase используются домены? 3. Какова максимальная длина имени домена? 4. Какие из имеющихся типов данных не приведены в табл. 1. 5. Перечислить числовые типы данных InterBase. 6. Перечислить целые типы данных в порядке возрастания их размера. 7. Перечислить строковые типы данных. 8. Сколько различных типов данных можно использовать при создании до-менов в
диалоговом режиме? 9. Чем отличается тип данных DATE в диалектах 1 и 3. 10. Какие дополнительные новые типы добавились в диалекте 3? 11. В чем отличие типа данных VARCHAR от CHAR
? 12. Чему равно наибольшее целое число, которое можно сохранить в типе
BIGINT
? 12
13. Что означает аббревиатура BLOB
? 14. Какие существуют способы изменения домена? 15. В какой системной таблице хранятся метаданные о доменах? 3.3 ЛАБОРАТОРНА РОБОТА № 3
Тема роботи:
Таблицы
.
Мета роботи:
Изучить способы создания, изменения и удаления таблиц. Получить
навыки использования приложения "IB Expert" для создания, удаления и изменения
структуры таблиц. Изучить SQL-операторы для работы с таблицами и индексами.
1
Теоретические сведения 1.1 Таблицы (Tables) InterBase – реляционная СУБД, поэтому все данные в InterBase хранятся в виде
двумерных таблиц со строками и столбцами. Строки называются кортежами или
записями, а столбцы – доменами или полями. В этой лабораторной работе рассматриваются не правилами проектирования
правильного набора таблиц для хранения данных о некоторой предметной области, а
только способы реального создания необходимых таблиц. Основные ограничения, которым должны удовлетворять таблицы: 1. Каждый столбец в таблице имеет уникальное имя. 2. Все данные в столбце должны быть одного типа. 3. Порядок строк и столбцов в таблице не имеет значения. 4. В таблице не может быть двух одинаковых строк. Часть языка SQL, которая управляет метаданными, называется Data Definition
Language (DDL). К DDL относятся операторы для определения любых содержащихся в
базе данных объектов, в том числе и таблиц. Операторы, определяющие структуру
таблиц в InterBase, соответствуют стандарту SQL, и поэтому без изменений будут
работать и во многих других СУБД. Для создания таблиц используется оператор "
CREATE TABLE
", который приводит
к созданию пустой таблицы без строк. При создании таблиц задается имя таблицы,
описание набора столбцов с их именами, типами и размерами, а также ограничения на
хранящуюся в таблице информацию. Максимальная длина названий таблиц, полей и
ограничений – 31 символ. Имена таблиц в пределах базы данных должны быть
уникальны. Каждый столбец в таблице должен иметь имя, уникальное в пределах таблицы, а
также либо тип данных, либо выражение для вычисления значения столбца, либо ссылку
на домен. Упрощенный синтаксис оператора создания таблицы: CREATE TABLE Имя_Таблицы(
<описание колонки> [, <описание колонки> | <ограничение> ...] ); 13
В этом операторе элемент <ограничение> означает ограничение, которое задается
на уровне таблицы без одновременного описания колонки. <описание колонки> = Колонка { <тип данных> | COMPUTED [BY] (<вычисляемое выражение>) | Имя_Домена }
[DEFAULT {Literal | NULL | USER}] [NOT NULL] [<ограничение поля>]; Здесь элемент <вычисляемое выражение>
означает выражение, которое будет
вычисляться каждый раз при необходимости вернуть значение этого вычисляемого поля.
Элемент <ограничение поля>
означает ограничение, которое задается при
одновременном определении поля (функционально полностью аналогично варианту
описания ограничения отдельной строкой при создании таблицы). Остальные элементы
были описаны в предыдущей лабораторной работе. Пример создания таблицы "Человек" (домены ID и Name описаны в предыдущей
лабораторной работе): CREATE TABLE Person( Pr_ID ID, -- номер человека Pr_LastName Name, -- фамилия Pr_FirstName Name, -- имя
Pr_Patronymic CHAR(30), -- отчество
Pr_FIO COMPUTED BY ( -- фамилия
и
инициалы
Pr_LastName || ' ' || SUBSTRING(Pr_FirstName FROM 1 FOR 1) || '.' || SUBSTRING(Pr_Patronymic FROM 1 FOR 1) || '.') ); Первые три поля заданы с использованием доменов, тип поля Pr_Patronymic
задан
непосредственно при создании таблицы, а поле Pr_FIO
является вычисляемым. В этом операторе || означает операцию конкатенации (соединения) строк,
SUBSTRING()
– функция, возвращающая подстроку (в приведенном примере – первую
букву полей "
Pr_FirstName
" и "
Pr_Patronymic
"). Если при описании поля не использовать домен, то Firebird все равно создает
домен специально для этого поля. Имя таких доменов состоит из символов "
RDB$
" и
порядкового номера. С такими системными доменами можно выполнять такие же
операции, как и с обычными доменами. Для удаления таблицы используется оператор DROP TABLE
. DROP TABLE Person; -- Пример удаления таблицы Оператор ALTER TABLE
предназначен для изменения структуры таблицы вместо
ее удаления и повторного создания. С его помощью можно изменять названия полей,
добавлять новые и удалять имеющиеся поля, а также добавлять и удалять ограничения. Примеры операторов ALTER TABLE
: -- добавление поля "День рождения": ALTER TABLE Person ADD Birthday DATE; -- удаление поля "Отчество": ALTER TABLE Person DROP Pr_Patronymic; -- переименование
поля
"
День
рождения
": ALTER TABLE Person ALTER COLUMN Birthday TO Pr_Birthday; 14
Если требуется изменить тип поля, например, увеличить число символов,
хранимых в поле, то необходимо изменять домен этого поля оператором ALTER
DOMAIN или выполнить изменения в системных таблицах. 1.2 Индексы InterBase (как и другие реляционные СУБД) хранит записи в таблицах в
неупорядоченном виде. Записи, добавляемые в таблицу одна за другой, не обязательно
окажутся "рядом". Данные, извлекаемые из таблицы, также не имеют какого-либо
порядка, кроме того, который явно указан в запросе на выборку информации. Индекс – это упорядоченный указатель на записи таблицы. Индекс состоит из пар
значений "значение поля" – "физическое расположение записи", поэтому по значению
поля (или полей), входящего в индекс, при помощи индекса можно быстро найти место в
таблице, где располагается запись, содержащая это значение. Важно только знать, что создание индексов может привести к значительному
ускорению процессов поиска и сортировки. Не следует создавать индекс на поля с ограниченным набором значений –
например, на поле, хранящие пол человека, которое содержит только два значения – "м"
и "ж". Использование индексов имеется два отрицательных последствия: 1. Для индексов дополнительно тратится дисковое пространство. 2. Наличие индексов замедляет модификацию данных в таблице. Индексы создаются при помощи команды CREATE INDEX
. Синтаксис оператора создания индекса: CREATE [UNIQUE] [ASC[ENDING] | DESC[ENGING]] INDEX Имя_Индекса ON Таблица(Поле [, Поле ...]); Индекс может быть либо возрастающим (
ASC[ENDING]
), либо убывающим
(
DESC[ENGING]
). Если при создании индекса указать UNIQUE
, то можно будет
заносить в таблицу только уникальные значения индексированных полей. Пример создания индекса: -- индекс по фамилии человека: CREATE INDEX Index_Person_Name ON Person(Pr_LastName); По мере изменения данных в таблице производительность индекса уменьшается.
Периодически требуется пересоздавать индекс, чтобы восстановить его
производительность. Чтобы пересоздать индекс, можно использовать следующие способы: 1. Перестроить индекс с помощью команды ALTER INDEX
. Для этого надо
последовательно выполнить две команды: ALTER INDEX Имя_Индекса INACTIVE; ALTER INDEX Имя
_
Индекса
ACTIVE; 2. Удалить индекс (командой DROP INDEX
), а затем повторно создать его. 3. Выполнить резервное копирование и восстановление базы данных. Индекс характеризуется числом в пределах от 0 до 1, которое называется
статистикой. Это число зависит от числа различных значений поля в таблице.
Оптимизатор запросов InterBase использует это число для определения эффективности
применения индекса в запросе. Статистика определяется при создании и перестройке
индекса, а также по команде пересчета статистики. Когда число записей в таблице
15
изменяется, пересчет статистики может улучшить производительность. Пересчет
статистики не перестраивает индекс, а только определяет статистику. Команда пересчета статистики индекса: SET STATISTICS INDEX Имя
_
Индекса
; 25 2
Ход работы Для создания таблицы в диалоговом режиме, нажмите в окне "Database Explorer"
правую клавишу мыши на узле "Tables" или на одной из имеющих-ся таблиц и в
открывшемся меню выберите команду "New Table… (Ctrl+N)". В результате откроется
окно создания таблицы (рисунок 3.1). Рисунок 3.1 – Окно формирования таблицы в диалоговом режиме
В верхней части этого окна вводится имя создаваемой таблицы (в приведенном
случае – "TESTDIALOG"). Сетка в средней части окна содержит сведения о полях таблицы. Чтобы добавить
поле в таблицу, следует нажать клавишу [Insert] или [Стрелка вниз]. В колонке "Field
Name" вводится имя создаваемого поля, в колонке "Field Type" выбирается тип данных,
либо в колонке "Domain"– созданный ранее домен. Для текстовых полей (
CHAR
и
VARCHAR
) в колонке "Size" вводится размер поля. Чтобы задать полю ограничение
"
NOT NULL
" достаточно уста-новить флажок в колонке "Not Null". В нижней части окна на вкладке "Field description" можно вводить для каждого
поля поясняющие комментарии. После введения данные о всех полях таблицы следует нажать кнопку [Compile]
(Ctrl+F9). В результате этого откроется диалоговое окно "Creating Table
Имя_Таблицы…" (рисунок 3.2), в котором надо подтвердить (кнопка [Commit]) или
отменить (кнопка [Rollback]) выполнение операции. 16
Рисунок 3.2 – Диалоговое окно создания таблицы
Если закрыть это диалоговое окно кнопкой [Commit], то в окне, показанном на рис.
12 появятся дополнительные вкладки и оно примет следующий вид (рисунок 3.3.) Рисунок 3.3 – Окно просмотра и редактирования таблицы
Теперь появится возможность добавить к таблице индексы. Для этого перейдите на
вкладку "Indices". Сетка на этой вкладке позволяет просматривать, редактировать, создавать и
удалять индексы. Для создания индекса выполните следующие действия: 1. Нажмите в этой сетке клавишу [Insert]. В результате будет вставлена новая
строка. 2. Задайте в колонке "Index" имя индекса. 3. Нажмите кнопку в колонке "On field". В результате откроется окно с двумя
списками (рисунок 3.4). В левом списке "Available fields" будут находиться поля,
которые можно добавить к индексу, в правом списке "In-cluded fields" будут находиться
поля, которые уже включены в список. Для формирования перечня полей, которые будут
входить в индекс, переместите нужные поля из левого списка в правый. 17
Рисунок 3.4 – Окно просмотра и редактирования индексов
4. Если создается уникальный индекс, установите флажок в колонке "Unique". 5. Чтобы активизировать индекс установите флажок в колонке "Active". 6. В колонке "Sorting" выберите способ сортировки. 7. Чтобы создать индекс нажмите кнопку [Compile] (Ctrl+F9). 8. В результате откроется диалоговое окно (рисунок 3.5), в котором надо
подтвердить (кнопка [Commit]) или отменить (кнопка [Rollback]) выполнение операции. После создания индекса его можно в любой момент изменить, если изменить
параметры индекса и снова нажать кнопку [Compile]. Рисунок 3.5 – Диалог создания индекса
3 Задание Лабораторную работу следует выполнять в следующем порядке: 1. Создать рабочую папку для хранения файлов, получаемых при выполнении
лабораторной работы №3. 2. Скопировать в эту папку файл сценария, созданный при выполнении
лабораторной работы №2. 3. Открыть окно "Script Executive" в приложении "IB Expert" и прочитать в него
файл сценария, скопированный ранее. 4. Добавить в сценарий команды создания таблиц в соответствии с
индивидуальным заданием. Должно получиться не менее 4-х таблиц. При описании
18
полей необходимо использовать и созданные ранее домены и непосредственно типы
данных. В таблицах должно быть не менее трех вычисляемых полей. 5. Добавить в сценарий команды создания нескольких индексов. Необходимо
создать не менее чем по одному индексу для каждой из созданных таблиц. 6. Выполнить сценарий и сохранить его. 7. Зарегистрировать созданную базу данных в программе "IB Expert" и
подключиться к ней. 8. Создать в своей базе данных таблицу в диалоговом режиме. Она должна
называться "TestDialog" и содержать десять полей разных типов с произвольными
именами. Скопировать в отчет сценарий создания таблицы "TestDialog", который
сгенерирует "IB Expert". 9. Создать в диалоговом режиме в таблице "TestDialog" три различных индекса и
сценарии их создания скопировать в отчет. 10. Создать резервную копию базы данных. 11. Оформить отчет о выполнении лабораторной работы №3. 4 Отчет о выполнении работы Отчет должен содержать описание и результаты работы, представляемые в
следующей последовательности: 1. Краткое описание таблиц, предназначенных для моделирования предметной
области в соответствии с вариантом. 2. Распечатка сценария создания базы данных с комментариями, которые должны
содержать сведения об авторе, дате создания, всех выполняемых действиях и пояснения
к таблицам и индексам. 3. Перечень файлов, полученных при выполнении лабораторной работы с
указанием их имен, места расположения, даты изменения и размеров (сценарий, база
данных, резервная копия базы данных, файл с отчетом). 5 Контрольные вопросы 1. Что такое таблица в InterBase? Какими свойствами должны обладать таблицы? 2. Как по-другому называются строки и столбцы таблиц? 3. Как задаются вычислимые поля? 4. Как изменить название поля в таблице? 5. Что такое индекс? 6. Для чего используются индексы? 7. Какие отрицательные последствия имеет использование индексов? 8. В каком случае применение индексов не дает положительного результата? 9. Каковы четыре способа улучшения производительности индексов? 10. Как сделать индекс по убыванию? 11. Сколько системных доменов имеется в созданной вами базе данных? 19
3.4 ЛАБОРАТОРНАЯ РАБОТА № 4
Тема работы: Ограничения
Цель работы: Изучить используемые в Firebird типы ограничений. Получить
навыки использования программы "IB Expert" для создания, изменения и удаления
ограничений. Изучить SQL-операторы для работы с ограничениями.
1 Теоретические сведения Ограничения базы данных – это правила, которые проверяются при сохранении
информации в базе данных. Главным преимуществом использования ограничений является возможность
реализации проверки данных, а значит части бизнес-логики приложения, на уровне базы,
т.е. централизовать ее. Это существенно упрощает разработку приложений баз данных. В InterBase для обеспечения целостности базы данных используется четыре вида
ограничений (
CONSTRAINTS
): 1. Ограничение первичного ключа (
Primary key
). 2. Ограничения уникальности (
Unique
). 3. Ограничения внешнего ключа (
Foreign keys
). 4. Контрольные ограничения (
Checks
). Все четыре вида ограничений могут создаваться двумя способами: 1. На основе одного поля таблицы (одновременно при создании этого поля). 2. На основе нескольких полей (без одновременного создания поля). Ограничения задаются либо при создании таблицы в операторе CREATE TABLE
,
либо добавляются позже в операторе ALTER TABLE
. Удалить ограничение можно в операторе ALTER TABLE
. Упрощенный синтаксис задания ограничений при создании поля приведен ниже. <ограничение поля> = [CONSTRAINT Название_Ограничения] <описание ограничения поля> [<ограничение поля> ...] <описание ограничения поля> = { PRIMARY KEY | UNIQUE | REFERENCES Другая
_
Таблица
[(
Поле
[, Поле
...])] | CHECK (Проверяемое_Условие) } Упрощенный синтаксис задания ограничений на основе нескольких полей таблицы
или без одновременного создания поля имеет следующий вид: <ограничение> = [CONSTRAINT Имя_Ограничения] <описание ограничения> [<ограничение> ...] <Описание ограничения> = { {PRIMARY KEY | UNIQUE} (
Поле
[, Поле
...]) | FOREIGN KEY (Поле [, Поле ...]) REFERENCES Другая_Таблица[(Поле [, Поле ...])] | CHECK (Проверяемое_Условие) } Как видно из синтаксиса задания ограничений, каждое ограничение может иметь
имя, записываемое после ключевого слова CONSTRAINT
. Все ограничения в базе данных
20
должны иметь уникальные имена. Если при задании ограничения не задать его имени, то
оно будет назначено сервером СУБД автоматически. При выполнении лабораторных
работ необходимо каждому ограничению обязательно давать имя. 1.2 Ограничение первичного ключа (
Primary key
) Это ограничение обозначает, какие поля в таблице единственным образом
идентифицируют каждую строку. Добавление ограничения первичного ключа заставляет
сервер базы данных гарантировать, что значения, вставленные в поля первичного ключа,
будут уникальны внутри таблицы. У таблицы может быть только одно такое
ограничение. Первичные ключи выполняют следующие функции: 1. Они служат средством различения отдельных записей. 2. Используются в ссылочной целостности. 3. При создании первичного ключа автоматически создается индекс, поэтому они
ускоряют поиск и сортировку. Обычно первичные ключи рекомендуется создавать для всех таблиц. Все поля
первичного ключа должны быть объявлены NOT NULL
. Пример задания ограничения первичного ключа: ALTER TABLE Customer ADD CONSTRAINT PK_Customer PRIMARY KEY (C
ust_No)2 1.2 Ограничения уникальности (
Unique
) Используются для гарантии уникальности одного или нескольких полей записи.
Единственное отличие от ограничения первичного ключа – возможность задания для
таблицы нескольких таких ограничений. Пример задания ограничения уникальности: ALTER TABLE Employee ADD CONSTRAINT Unique_Phone UNIQUE (Phone); 1.3 Ограничения внешнего ключа (
Foreign keys
) Эти ограничения используются для определения связей между таблицами. Они
гарантируют соответствие значений полей одной таблицы значениям ключевых полей в
другой таблице. Такие ограничения могут предотвратить стирание данных из главной
таблицы, на которые имеются ссылки в подчиненной таблице. При определении внешнего ключа автоматически генерируется индекс. Пример задания ограничения внешнего ключа: ALTER TABLE Book ADD CONSTRAINT FK_Book FOREIGN KEY (B_PubNo) REFERENCES Publisher (B_NO); В отличие от полей первичных ключей, в полях, которые являются внешним
ключом, могут допускаться пустые значения. Эта возможность необходима для
разрешения взаимных ссылок и возможности хранить в таблице иерархические
структуры, когда корневые узлы содержат NULL. 1
.4 Контрольные ограничения (
Checks
) Это условия, налагаемые на возможные значения полей. Контрольные ограничения
гарантируют истинность заданного правила для каждой записи в таблице. 21
При использовании контрольных ограничений следует помнить, что сложные и
очень большие условия проверки могут значительно замедлить операции вставки и
обновления записей. Пример контрольного ограничения, которое гарантирует, что значение поля "Num"
будет иметь значение от 0 до 4: ALTER TABLE Property ADD CONSTRAINT Check_Invalid_Number CHECK (Num >= 0 AND Num <= 4); Контрольные ограничения реализованы при помощи системных триггеров.
2 Ход работы Действия по созданию ограничений с помощью выполнения команд сценария, как
и операции создания таблиц в диалоговом режиме, были изучены при выполнении
предыдущих лабораторных работ. Для создания ограничений с помощью программы "IB Expert" в диалоговом
режиме необходимо выполнить следующие действия: 1. Подключиться к базе данных. 2. Открыть окно с таблицей, в которую будут добавляться ограничения (рис. 4.1). Рисунок 4.1 – Окно просмотра и редактирования ограничений
3. Перейти на вкладку "Constraints". На этой вкладке имеется четыре
дополнительных вкладки для просмотра и редактирования ограничений всех четырех
типов (рис. 4.1). 4. Чтобы создать новое ограничение надо перейти на нужную вкладку и нажать
клавишу [Insert]. В результате в сетку с перечнем имеющихся ограничений будет
добавлена строка с новым ограничением. 5. После задания желаемых значений в новой строке необходимо нажать кнопку
[Compile] (Ctrl+F9), которая приведет к автоматическому созданию оператора
создания ограничения и его выполнению. На рис. 4.2 показан результат создания ограничения уникальности для таблицы
"Человек". 22
Рисунок 4.2 – Окно с примером создания ограничения уникальности
3 Задание Лабораторную работу следует выполнять в следующем порядке: 1. Создать рабочую папку для хранения файлов, получаемых при выполнении
лабораторной работы №4. 2. Скопировать в эту папку файл сценария, созданный при выполнении
лабораторной работы №3. 3. Открыть окно "Script Executive" в приложении "IB Expert" и прочитать в него
файл сценария, скопированный ранее. 4. Изменить соответствующим образом в сценарии текст комментариев и сделать,
чтобы база данных теперь создавалась новой папке. 5. Добавить в сценарий в операторы создания таблиц создание ограничений всех
четырех видов. Каждая таблица должна иметь ограничение первичного ключа. С
помощью ограничений внешнего ключа должны быть заданы все имеющиеся связи
между таблицами. 6. Выполнить сценарий и сохранить его. 7. Зарегистрировать созданную базу данных в программе "IB Expert" и
подключиться к ней. 8. Создать в своей базе данных таблицу в диалоговом режиме. Она должна
называться "TestConstraints" и содержать десять полей разных типов с произвольными
именами. 9. Добавить в диалоговом режиме к этой таблице по одному ограничению каждого
типа. Скопировать в отчет сценарий создания этой таблицы, который сгенерирует "IB
Expert". 10. Создать в папке резервную копию базы данных. 11. Оформить отчет о выполнении лабораторной работы №4. 4 Отчет о выполнении работы 23
Отчет должен содержать описание и результаты работы, представляемые в
следующей последовательности: 1. Словесное описание добавляемых к базе данных ограничений. 2. Распечатка сценария создания базы данных с комментариями. 3. Перечень файлов, полученных при выполнении лабораторной работы с
указанием их имен, места расположения, даты изменения и размеров (сценарий, база
данных, резервная копия базы данных, файл с отчетом). 5 Контрольные вопросы 1. Дать определение термина "Ограничение". 2. Какие существуют варианты операторов создания ограничений? 3. Какие особенности имеют поля, входящие в первичный ключ и ограничение
уникальности? 4. Какие функции имеет ограничение первичного ключа? 5. Что такое внешний ключ? 6. Зачем в полях внешнего ключа могут разрешаться пустые значения? 7. Что такое контрольное ограничение? 8. При помощи чего реализованы контрольные ограничения? 9. Какие имена получают ограничения первичного ключа, если их не задать в
явном виде? Какие имена при этом будут иметь автоматически создаваемые индексы? 10. Какие имена получают ограничения уникальности, если их не задать в явном
виде? Какие имена при этом будут иметь автоматически создаваемые индексы? 11. Какие имена получают ограничения внешнего ключа, если их не задать в явном
виде? Какие имена имеют индексы, автоматически создаваемые для ограничений
внешнего ключа? 12. Какие имена назначаются системным триггерам для реализации кон-трольных
ограничений? 3.5 ЛАБОРАТОРНАЯ РАБОТА №5.
Тема работы:
Триггеры, генераторы, исключения, Цель работы:
Изучить используемые в Firebird триггеры, генераторы и
исключения. Получить навыки работы с триггерами, генераторами и исключениями с по-
мощью команд SQL и с помощью программы "IB Expert". 1 Теоретические сведения 1.1 Генераторы (Generators) Генераторы используются для генерации уникальных номеров, используемых в
ключевых полях. То есть они реализуют те же функциональные возможности в таблицах
InterBase, что поля с автоматическим приращением в других СУБД. Можно указать,
чтобы числа генерировались, начиная с заданного начального значения. Пример создания генератора: CREATE GENERATOR MyGenerator; SET GENERATOR MyGenerator TO 1000; 24
Генераторы непосредственно не привязываются к какому-либо полю. Они просто
позволяют генерировать уникальные числа. Для этого используется функция Gen_ID(),
встроенная в InterBase, которая генерирует целочисленные значения. Она берет
генератор в качестве первого параметра и значение шага в качестве второго. Обычно
приращение равно 1. Обращаться к генератору можно только через функцию Gen_ID(). 1.2 Бизнес-правила В реальной жизни вопрос целостности базы данных связан с правилами,
установленными у пользователя информационной системы. Например, в компании могут
быть установлены такие правила: • клиентам не разрешается размещать заказы на сумму, превышающую их лимит
кредита; • сведения о выполненных заказах хранятся в течение шести месяцев, а затем
удаляются; • нельзя выдавать читателю новых книг, пока он не вернет взятых ранее; • каждый раз, когда продается какой-нибудь товар, для служащего, оформившего
продажу, и для отдела, в котором этот служащий работает, на стоимость проданного
товара увеличивается значение определенного поля, используемого для вычисления
премии; в случае возврата всего или части проданного товара, значение этого поля
соответствующим образом должно уменьшиться; • каждый раз, когда приходит новая поставка, количество поставленного товара на
складе увеличивается на количество товара в поставке. Такие правила называются бизнес-правилами. В первом стандарте SQL считалось,
что эти правила выходят за рамки ответственности СУБД и за их реализацию отвечает
прикладная программа, осуществляющая доступ к базе данных. Впервые в 1986 году в
СУБД Sybase было введено понятие триггер, что позволило включить реализацию
бизнес-правил в базу данных. 1.3 Триггеры (Triggers) С любым событием, вызывающим изменение содержимого таблицы, можно
связать сопутствующее действие (триггер), которое СУБД должна выполнять при
каждом возникновении события. Триггер – это группа операторов языка SQL, которые
автоматически выполняется при вставке, модификации или удалении записи. В СУБД InterBase можно создавать триггеры, работающие при следующих шести
условиях: • до вставки записи (
BEFORE INSERT
); • после вставки записи (
AFTER INSERT
); • до удаления записи (
BEFORE DELETE
); • после удаления записи (
AFTER DELETE
); • до модификации записи (
BEFORE UPDATE
); • после модификации записи (
AFTER UPDATE
). В Firebird 1.5 триггер может срабатывать при возникновении одного из нескольких
событий. Триггеры могут вызывать выполнение хранимых процедур, выполнять
различные проверки и генерировать исключения. Обычно триггеры используются для
25
задания сложных правил контроля целостности базы данных, которые невозможно
реализовать с помощью ограничений. Недостатком триггеров является их влияние на производительность операций с
базой данных. Синтаксис оператора создания триггера: CREATE TRIGGER Имя FOR Таблица [ACTIVE | INACTIVE] {BEFORE | AFTER} <операция> [OR <операция> [OR <операция>]] [POSITION Номер] AS [<список переменных>] <блок>; <операция> = {INSERT | UPDATE | DELETE} <список переменных> = DECLARE [VARIABLE] Переменная <тип данных>; [DECLARE [VARIABLE] Переменная <тип данных>; ...] <блок> = BEGIN <составной оператор> [<составной оператор>...] END <составной оператор> = <блок> | Оператор; Параметры, входящие в этот оператор, пояснены в таблице 5.1 Таблица 5.1 – Описание параметров оператора создания триггера Параметр Описание Имя Уникальное название триггера. ACTIVE | I
NACTIVE Указывает будет ли работать триггер BEFORE | AFTER Обязательный параметр, показывающий когда будет
срабатывать триггер INSERT | UPDATE |
DELETE Одной из событий, на которые будет срабатывать триггер POSITION Номер Определяет порядок срабатывания триггера, когда имеется
несколько триггеров, реагирующих на одно и то же событие.
Номер – целое число между 0 и 32767 DECLARE [VARIABLE]
Переменная <тип
данных>
Описание локальной переменной, которую можно будет
использовать только в этом триггере Оператор Любой одиночный оператор языка хранимых процедур и
триггеров InterBase Внутри тела триггера доступны две контекстные переменные NEW
и OLD
, которые
позволяют получить доступ к новым и старым значениям полей записи, при изменении
которой был вызван триггер. Пример генератора и триггера: CREATE GENERATOR EMP_NO_GEN; CREATE TRIGGER SET_EMP_NO FOR EMPLOYEE ACTIVE BEFORE INSERT POSITION 0 26
AS BEGIN IF (NEW.EMP_NO IS NULL) THEN NEW.EMP_NO = GEN_ID(EMP_NO_GEN, 1); END; 2. Ход работы Операции по созданию генераторов, исключений и триггеров с помощью
выполнения сценария были изучены в ходе выполнения предыдущих лабораторных
работ. Для создания триггеров с помощью программы "IB Expert" в диалоговом режиме
необходимо выполнить следующие действия: 1. Подключиться к базе данных. 2. Открыть окно с таблицей, для которой будет создаваться триггер. 3. Перейти на вкладку "Triggers" (рисунок 4.1). Рисунок 4.1 – Вкладка "Triggers" окно просмотра таблицы
4. Нажать в этом окне правую кнопку мыши на одном из событий, для которого
будет создаваться триггер. 5. Откроется контекстное меню, в котором надо выбрать команду "New Trigger". 6. В результате откроется окно создания триггера (рисунок 4.2 или 4.3), в котором
достаточно лишь ввести тело триггера, а затем нажать кнопку [Compile Trigger]
(Ctrl+F9). Рисунок 4.2 – Окно создания триггера в обычном режиме
27
Рисунок 4.3 – Окно создания триггера в "ленивом" режиме
Переключение между двумя режимами просмотра и редактирования триггера,
производится кнопкой [Lazy mode on/off]. Создание с помощью программы "IB Expert" в диалоговом режиме генераторов и
исключений происходит следующим образом: 1. Открывается
окно
"Generators" либо
"Exceptions". Для этого можно, на-пример,
выбрать команду главного меню "Database
New Generator" или "Database
New
Exception". 2. В окне "Generators" или "Exceptions" вводятся для генератора – имя и начальное
значение, а для исключения – имя и текст сообщения. Можно ввести данные сразу о
нескольких генераторах и исключениях. 3. После ввода данных необходимо нажать кнопку [Compile] (F9), которая
приведет к автоматической генерации необходимых операторов и их выполнению. 3
Задание Лабораторную работу следует выполнять в следующем порядке: 1. Создать аналогично предыдущим лабораторным работам рабочую папку. 2. Скопировать в эту папку файл сценария, созданный при выполнении
лабораторной работы №4. 3. Открыть в приложении "IB Expert" этот сценарий. 4. Исправить текст комментариев и сделать, чтобы база данных теперь создавалась
в новой папке. 5. Добавить в сценарий операторы создания генераторов, исключений и триггеров.
Для каждого первичного ключа должен быть создан соответствующий генератор и
триггер. 6. Создать триггеры, реализующие ссылочную целостность, заменив одно
ограничение внешнего ключа. Команды создания этого внешнего ключа из сценария не
удалять, а добавить команду удаления этого ограничения перед созданием триггеров.
Также добавить оператор создания индекса по полю бывшего внешнего ключа. Триггеры
должны обеспечивать каскадное обновление и удаление зависимых данных, а также
запрет ссылки на отсутствующую запись. 7. Создать не менее пяти триггеров, реализующих бизнес-правила в соответствии с
выданным индивидуальным заданием. Для каждого триггера должны присутствовать
комментарии, поясняющие выполняемые операции. 8. Выполнить сценарий и сохранить его. 28
9. Зарегистрировать созданную базу данных в программе "IB Expert" и
подключиться к ней. 10. Создать в своей базе данных таблицу в диалоговом режиме. Она должна
называться "TestTriggers" и содержать десять полей разных типов с произвольными
именами. При создании поля первичного ключа сразу указать необходимость создания
для него генератора и триггера. Добавить в диалоговом режиме к этой таблице три
триггера выполняющих произвольные действия. Скопировать в отчет сценарий создания
этой таблицы и триггеров, который сгенерирует "IB Expert". 11. Создать в папке резервную копию базы данных. 12. Оформить Создать и сохранить в папке "ЛР5" файл с отчетом о выполнении
лабораторной работы, который должен называться "Отчет.doc". 4
Отчет о выполнении работы Отчет должен содержать описание и результаты работы, представляемые в
следующей последовательности: 1. Описание бизнес-правил, реализованных с помощью триггеров. 2. Распечатка сценария создания базы данных с комментариями, которые должны
содержать сведения об авторе, дате создания, всех выполняемых действиях и пояснения
к генераторам, исключениям и триггерам. 3. Перечень файлов, полученных при выполнении лабораторной работы с
указанием их имен, места расположения, даты изменения и размеров (сценарий, база
данных, резервная копия базы данных, файл с отчетом). 5 Контрольные вопросы 1. Что такое бизнес-правила? 2. Какие существуют способы реализации бизнес-правил? 3. Какие преимущества и недостатки имеет реализация бизнес-правил на сервере и
в клиенте? 4. Что называется триггером? При каких событиях работают триггеры в InterBase?
Для чего используются переменные NEW и OLD? 5. Как в триггере запретить выполнение операции? 6. Какие преимущества и недостатки имеют триггеры? 7. Что такое генератор? Как получить значение генератора? 8. Для чего используется функция GEN_ID? 9. Что такое исключение? 10. Как происходит процесс обработки исключений? 11. Какие ошибки могут быть кроме исключений, созданных пользователем? 29
3.6
ЛАБОРАТОРНАЯ РАБОТА №6
Тема работы:
Внесение изменений в базу данных Цель работы: Изучить используемые в реляционных СУБД операторы изменения
данных. Получить навыки работы с SQL-операторами INSERT, DELETE и UPDATE.
Заполнить разрабатываемую базу данных тестовыми данными для последующего
использования. 1 Теоретические сведения В SQL имеется три оператора, относящиеся к группе операторов DML (Data
Manipulation Language), которые предназначены для выполнения запросов на
добавление, удаление и обновление данных. 1.1 Добавление новых данных Новые данные добавляются оператором INSERT
. Наименьшей единицей
информации, которую можно добавить в реляционную базу данных, является одна
строка таблицы. Немного упрощенный синтаксис оператора INSERT имеет вид: INSERT INTO Имя_Таблицы [(Колонка [, Колонка ...])] {VALUES(<величина> [, <величина> ...]) | <оператор SELECT>}; <величина> = {:Переменная | <константа> | <выражение> | <функция> | udf([<величина> [, <величина> ...]]) | NULL | USER} <константа> = Число | 'Строка' <функция> = CAST(<величина> AS <тип данных>) | UPPER(<величина>) | GEN_ID(Имя_Генератора, <величина>) <выражение> = SQL выражение, возвращающее единичное значение В этом описании можно выделить два варианта оператора: 1. Вставка одной строки. Для этого после ключевого слова VALUES в круглых
скобках указывают вставляемые величины. 2. Вставка в таблицу нескольких строк, выбранных с помощью оператора SELECT
. В этой лабораторной работе рассматривается только первый вариант оператора
INSERT
. Пример, когда в качестве вставляемых величин применены константы: INSERT INTO Person(Pr_ID, Pr_LastName, Pr_FirstName) VALUES
(150, 'Иванов', 'Петр'); Пример использования для вставки значений переменных: DECLARE Person_Number INTEGER; -- объявление
DECLARE LastName VARCHAR(30); -- локальных
DECLARE FirstName VARCHAR(30); -- переменных
BEGIN Person_Number = 150; LastName = '
Иванов
'; 30
FirstName = '
Петр
'; INSERT INTO Person(Pr_ID, Pr_LastName, Pr_FirstName) VALUES(:Person_Number, :LastName, :FirstName); END
; 1.2 Удаление существующих данных Для удаления строк из таблицы используется оператор DELETE
. Вот его
упрощенный синтаксис: DELETE FROM Имя_Таблицы [WHERE <условие поиска>]; <условие поиска> = как в операторе SELECT Если не использовать предложение WHERE
, то будут удалены все строки в
таблице. -- Удаление всех служащих: DELETE
FROM
Employee
; -- Удаление всех людей с номерами 150 и больше: DELETE FROM Person WHERE Pr_ID >= 150; Отбирать строки для удаления не обязательно только на основании содержимого
этих строк. Можно составить условие для удаляемых строк, опираясь на данные из
других таблиц. Для составления таких условий необходимо сначала изучить оператор
SELECT
. 1.3 Обновление существующих данных Оператор UPDATE обновляет значения одного или нескольких столбцов в
выбранных строках одной та
блицы. Строки для обновления указываются в предложении
WHERE
. Если пропустить предложение WHERE
, то изменятся все строки таблицы. UPDATE Имя_Таблицы SET Колонка = <величина> [, Колонка = <величина> ...] [WHERE <условие поиска>] <величина> = { Колонка | :Переменная | <константа> | <выражение> | <функция> | udf([<величина> [, <величина> ...]]) | NULL | USER} <выражение> = SQL выражение, возвращающее единичное значение <условие поиска> = как в операторе SELECT Примеры: -- Увеличить зарплату всем служащим на 10%: UPDATE Employee SET Salary = 1.1*Salary; /* Увеличить зарплату всем служащим, которые имеют зарплату меньше 10000 на
15%: */ UPDATE Employee SET Salary = 1.15*Salary; WHERE
Salary
<= 10000; 31
Отбирать строки для изменения, как и для удаления, можно с использование
подчиненного запроса SELECT, который позволит учитывать в условии поиска
изменяемых строк данные из других таблиц. Например, можно выполнить такой запрос: увеличить зарплату на 10% всем
служащим, работающим в отделе продаж, которые обслужили за последний месяц
клиентов больше чем в полтора раза, чем в среднем по их отделу. 2. Ход работы Для заполнения созданной базы данных тестовой информацией в диалоговом
режиме программы "IB Expert" необходимо выполнить следующие действия: 1. Подключиться к базе данных. 2. Открыть окно с таблицей, в которую будет заноситься информация. 3. Перейти на вкладку "Data" (рисунок 6.1). На этой вкладке располагается сетка со
строками и столбцами. Рисунок 6.1 – Окно ввода данных в таблицу
При вводе полей есть возможность использования различных диалогов, таких как
календарь, калькулятор или окно ввода поля внешнего ключа. 4. Ввести данные, используя для добавления строки клавишу [Insert] или кнопку
, а для удаления строки – клавиши [Ctrl+Delete] или кнопку . 5. После ввода данных для сохранения их в базе данных необходимо
зафиксировать изменения кнопкой [Commit Transaction] (Ctrl+Alt+C). Чтобы
отменить сделанные изменения, можно воспользоваться кнопкой [Rollback
Transaction] (Ctrl+Alt+R). 3. Задание Лабораторную работу следует выполнять в следующем порядке: 1. Создать аналогично предыдущим лабораторным работам рабочую папку. 32
2. Скопировать в эту папку файл сценария, созданный при выполнении
лабораторной работы №5. 3. Открыть в приложении "IB Expert" этот сценарий. 4. Исправить текст комментариев и сделать, чтобы база данных теперь создавалась
в новой папке. 5. Добавить в конец сценария для каждой имеющейся таблице по три оператора,
выполняющих операции INSERT
, UPDATE и DELETE
. 6. Выполнить сценарий и сохранить его в папке. 7. Зарегистрировать созданную базу данных в программе "IB Expert" и
подключиться к ней. 8. Заполните созданные таблицы согласованными данными в диалоговом режиме
программы "IB Expert". В каждой таблице должно быть не менее 30 строк (кроме
справочников с ограниченным количеством данных). 9. Создать в папке резервную копию базы данных. 10. Оформить отчет о выполнении лабораторной работы №6. 4. Отчет о выполнении работы Отчет должен содержать описание и результаты работы, представляемые в
следующей последовательности: 1. Распечатка сценария создания базы данных с комментариями, которые должны
содержать сведения об авторе, дате создания, всех выполняемых действиях и пояснения
к введенным вручную операторам изменения данных. 2. Сведения о количестве введенных в каждую таблицу тестовых данных. 3. Перечень файлов, полученных при выполнении лабораторной работы с
указанием их имен, места расположения, даты изменения и размеров (сценарий, база
данных, резервная копия базы данных, файл с отчетом). 5. Контрольные вопросы 1. Что такое DML? 2. Какие бывают разновидности оператора вставки новых данных? 3. Как удалить из таблицы сразу все строки? 4. Как удалить из таблицы только некоторые строки? 5. Как изменить все строки таблицы? 6. Как изменить только некоторые строки? 7. Написать синтаксис оператора вставки данных. 8. Написать синтаксис оператора удаления данных. 9. Написать синтаксис оператора обновления данных. 10. Привести примеры операторов INSERT, DELETE, UPDATE. 11. Как добавить данные в несколько таблиц? 12. Как удалить данные из нескольких таблиц? 13. Как изменить данные в нескольких таблицах? 14. Как запретить уменьшать значение какого-либо поля, но разрешить его
увеличивать? 15. Как разрешить вносить изменения в базу данных только в определенные дни
недели? 16. Как разрешить вносить изменения только определенным пользователям? 33
3.
7 ЛАБОРАТОРНАЯ РАБОТА №7
Тема работы:
Выборка данных
Цель работы: Изучить используемый в реляционных СУБД оператор извлечения
данных из таблиц. Получить навыки работы с оператором SELECT в программе "IB
Expert".
1 Теоретические сведения В SQL имеется единственный оператор, который предназначен для выборки
данных из базы данных. Как и операторы INSERT, DELETE и UPDATE он относится к
подмножеству DML. Ниже приведен почти полный синтаксис оператора SELECT. SELECT [
DISTINCT | ALL
] {* | <величина> [, <величина> ...]
} [
INTO :Переменная [, :Переменная ...]] FROM
<
tableref
> [, <
tableref
> ...] [
WHERE
<условие поиска>] [
GROUP
BY
Колонка [, Колонка ...]] [
HAVING
<условие поиска>] [
UNION [
ALL
] <select_expr>] [
ORDER BY <
список
сортировки
>]; <величина> = {
Колонка | :Переменная | <константа> | <выражение> | <функция> | udf
(
[<величина> [, <величина> ...]]
) | NULL | USER} [
AS
Псевдоним] <константа> = Число | 'Строка' <выражение> = SQL
выражение, возвращающее единичное значение <
функция
> = COUNT (* | [
ALL
] <
величина
> | DISTINCT <
величина
>
) | SUM
(
[
ALL
] <величина> | DISTINCT
<величина>
) | AVG (
[
ALL
] <величина> | DISTINCT <величина>
) | MAX
(
[
ALL
] <величина> | DISTINCT
<величина>
) | MIN
(
[
ALL
] <величина> | DISTINCT
<величина>
) | CAST
(
<величина> AS
<тип данных>
) | UPPER
(
<величина>
) | GEN
_
ID
(
Имя_Генератора, <величина>
) <tableref> = {
<joined_table> | table | view | procedure[
(
<
величина
> [, <
величина
> ...]
)
]
} [
Псевдоним
] <joined_table> = <tableref> <join_type> JOIN <tableref> ON <
условие
поиска
> | (<joined_table>) 34
<join_type> = [INNER] | {LEFT | RIGHT | FULL } [OUTER]
<условие поиска> = <величина> <оператор сравнения> {<величина> | (<select_one>)} | <величина> [NOT] BETWEEN <величина> AND <величина> | <величина> [NOT] LIKE <величина> | <величина> [NOT] IN (<величина> [, <величина> ...] | <select_list>) | <величина> IS
[
NOT
] NULL
|<величина> {>= | <=} <величина> | <величина> [
NOT
] {= | < | >} <величина> | {
ALL
| SOME
| ANY
} (<
select
_
list
>) | EXISTS (<select_expr>) | SINGULAR (<select_expr>) | <
величина
> [NOT] CONTAINING <
величина
> | <
величина
> [NOT] STARTING [WITH] <
величина
> | (<
условие
поиска
>) | NOT
<условие поиска> | <условие поиска> OR
<условие поиска> | <условие поиска> AND
<условие поиска> <оператор сравнения> = {= | < | > | <= | >= | !< | !> | <> | !=} <select_one> = оператор SELECT, выбирающий одну колонку и возвра щающий
ровно одно значение <select_list> = оператор SELECT, выбирающий одну колонку, возвра щающий ноль
или много значений <select_expr> = оператор SELECT, выбирающий несколько величин и
возвращающий ноль или много значений <список сортировки> = {Колонка | Номер} [ASC | DESC] [, <список сортировки> ...] Некоторые параметры, входящие в этот оператор, описаны в табл. 1
Таблица 1 - Описание параметров оператора SELECT
Параметр Описание DISTINCT | ALL DISTINCT – предотвращает дублирование данных, которые будут
извлечены. ALL (по умолчанию) – приведет к извлечению всех
данных {* | <величина> [,
<величина> ...]} Звездочка (*) означает, что надо извлекать все колонки из
указанных таблиц. <величина> [, <величина> ...] – извлекает список
указанных колонок, переменных или выражений INTO :Переменная
[, :Переменная ...] Используется только в триггерах и хранимых процедурах для
операторов SELECT, возвращающих не более одной строки.
35
Указывается список переменных, в которые извлекаются величины FROM <tableref> [,
<tableref> ...] Указывает список таблиц, просмотров и хранимых процедур, из
которых извлекаются данные. Список может включать соединения
и соединения могут быть вложенными table Имя существующей в базе данных таблицы view Имя существующего базе данных просмотра procedure Имя существующей хранимой процедуры, предназначенной для
использования в операторе SELECT Псевдоним Короткое альтернативное имя для таблицы, просмотра или
колонки. После описания в <tableref>, псевдоним может
использоваться для ссылок на таблицу или просмотр join_type Задает тип соединения, которое может быть внутренним или
внешним Как видно из синтаксиса оператора SELECT, обязательными являются только
предложение SELECT с перечнем выдаваемых колонок и предложение FROM. Пример простейшего оператора SELECT: Выдать перечень всех служащих: SELECT * FROM Employee; Ниже приведено несколько упрощенных вариантов синтаксиса оператора SELECT,
помогающих научиться составлять простые запросы. Упрощенный синтаксис внутреннего соединения (стандарт SQL-92): SELECT Колонка [, Колонка ...] | * FROM <tableref_left> [INNER] JOIN <tableref_right> [
ON <
условие
поиска
>] [
WHERE <
условие
поиска
>]; Упрощенный
синтаксис
внешнего
соединения
: SELECT Колонка
[, Колонка
...] | * FROM <tableref_left> {LEFT | RIGHT | FULL} [OUTER] JOIN <
tableref
_
right
> [
ON
<условие поиска>] [
WHERE
<условие поиска>]; Упрощенный синтаксис использования подзапроса: SELECT [DISTINCT] Колонка [, Колонка ...] FROM <tableref> [, <tableref> ...] WHERE {expression {[NOT] IN | <
оператор
сравнения
>} | [NOT] EXISTS } (SELECT [DISTINCT] Колонка
[, Колонка
...] FROM <tableref> [, <tableref> ...] WHERE <
условие
поиска
> ); 36
2. Ход работы Для выполнения запросов в программе "IB Expert" необходимо выполнить
следующие действия: 1. Подключиться к базе данных и выполнить команду "Tools
SQL Editor" (F12). В
результате откроется окно "SQL Editor" (рисунок 7.1). Рисунок 7.1 – Окно выполнения запросов
2. Ввести в поле на вкладке "Edit" текст запроса. 3. Нажать на панели инструментов кнопку [Execute] (F9). 4. Если запрос правильный, то в результате произойдет его выполнение и результат
будет отображен на вкладке "Results" (рисунок 7.2). Рисунок 7.2 – Окно с результатом выполнения запроса
5. Количество извлеченных в результате выполнения запроса строк отображается
над сеткой с данными справа. На рис. 24 там содержится строка "7 records fetched". В
данном примере извлечено столько строк, сколько требуется, чтобы заполнить сетку (в
ней помещается только 7 строк)TP*PT. 6. Чтобы узнать, сколько всего строк соответствуют выполненному оператору,
надо перейти в конец отображаемого набора данных. Чтобы выполнить другой запрос, надо вернуться на вкладку "Edit", заменить
содержимое редактора на новый запрос и повторить те же действия. К тексту ранее
выполнявшихся правильных запросов можно вернуться, если перейти на вкладку
37
"History", либо находясь на вкладке "Edit" нажимать кнопки [Previous Query] и [Next
Query]. 3. Задание Лабораторную работу следует выполнять в следующем порядке: 1. Изучить синтаксис оператора SELECT и примеры запросов. 2. Зарегистрировать базу данных и подключиться к ней в программе "IB Expert". 3. Выполнить в окне "SQL Editor" несколько запросов к базе данных и сохранить
их в файле в рабочей папке. Каждый запрос должен иметь комментарии с описанием, а
файл в целом должен иметь комментарии со сведениями об авторе и дате создания. 4. Создать аналогично предыдущим лабораторным работам рабочую папку. 5. Скопировать в эту папку файл сценария, созданный при выполнении
лабораторной работы 6. Открыть в приложении "IB Expert" этот сценарий, исправить комментарии и
сделать, чтобы база данных теперь создавалась в новой папке. 7. Выполнить сценарий и сохранить его в папке. 8. Зарегистрировать созданную базу данных в программе "IB Expert" и
подключиться к ней. 9. Выполнить в окне "SQL Editor" десять запросов к своей базе данных и сохранить
их, добавив комментарии, в рабочей папке в файле "select_10.sql". 10. Создать в папке резервную копию базы данных. 11. Оформить отчет о выполнении лабораторной работы. 4. Отчет о выполнении работы Отчет должен содержать описание и результаты работы, представляемые в
следующей последовательности: 1. Распечатка сценария создания базы данных с комментариями, которые должны
содержать сведения об авторе, дате создания, всех выполняемых действиях и пояснения
к введенным вручную операторам изменения данных. 2. Сведения о количестве введенных в каждую таблицу тестовых данных. 3. Перечень файлов, полученных при выполнении лабораторной работы с
указанием их имен, места расположения, даты изменения и размеров (сценарий, база
данных, резервная копия базы данных, файл с отчетом). 5. Контрольные вопросы 1. Какие обязательные разделы входят в оператор SELECT? 2. Перечислить в правильном порядке все возможные разделы оператора SELECT. 3. Как надо задавать числовые и текстовые константы? 4. Как надо проводить сравнение с пустым значением? 5. Какие есть способы задания дат в операторе SELECT? 6. Какие бывают виды соединений таблиц в запросах? 7. Какие есть два способа задать внутреннее соединение? 8. Какие бывают виды внешних соединений? 9. В чем отличие внутренних и внешних соединений? 10. Для чего используются псевдонимы? 38
11. Где и для чего применяется ключевое слово BETWEEN? Можно ли его
заменить другими операторами? 12. Где и для чего применяется ключевое слово LIKE? 13. Чем отличается оператор CONTAINING от оператора LIKE? 14. Как устранить дублирование строк? 15. Как изменить порядок выводимых строк? 3.8 ЛАБОРАТОРНАЯ РАБОТА №8
Тема работы:
Представления
Цель работы:
Изучить используемые в реляционных СУБД операторы создания и
удаления представлений. Получить навыки работы с представлениями с помощью
команд SQL и с помощью программы "IB Expert". 1 Теоретические сведения 1.1 Представления (Views) Представление представляет собой запрос на выборку, которому присвоили имя и
сохранили в базе данных. Другими словами это виртуальная таблица, которая реально не
содержит данных, а всяких раз, когда требуется его содержимое, происходит выполнение
запроса. Когда СУБД встречает в инструкции SQL ссылку на представление, она находит
его определение, сохраненное в базе данных, преобразует пользовательский запрос,
ссылающийся на представление, в эквивалентный запрос к исходным таблицам
представления и выполняет этот запрос. Таким образом, СУБД создает иллюзию
существования представления в виде отдельной таблицы. Причины использования представлений 1. Они изолируют реализацию хранения данных от пользователя. Если клиенты
будут работать с данными через представление, то у разработчика базы данных
появляется возможность менять запрос, лежащий в основе представления, а клиент
ничего не будет замечать. 2. С их помощью можно ограничить доступ к данным, разрешая определенным
пользователям иметь права на чтение/изменение данных в представлении, но даже не
иметь понятия о таблицах, лежащих в основе этого представления. 3. Они упрощают доступ к базе данных, показывая пользователям структуру
хранимых данных в наиболее подходящем для них виде. Представления могут основываться на данных из нескольких таблиц и других
представлений. Представления можно использовать как обычные таб-лицы. Оператор
SELECT, образующий представление, может делать то же, что и обычный оператор
SELECT, кроме включения предложения ORDER BY и использования в качестве
источника данных хранимых процедур. Синтаксис оператора создания представления: 39
CREATE VIEW Имя_Представления [(Колонка [, Колонка …])] AS <select> [WITH CHECK OPTION]; <select> – оператор SELECT, который выбирает данные включаемые в
представление WITH CHECK OPTION – если указать этот необязательный параметр, то при
вставке или изменении строки представления будет проверяться усло-вие "попадания"
этой строки в представление. Если новая или измененная запись не удовлетворяет
условиям запроса, на котором основано пред-ставление, то операция будет отменена и
возникнет ошибка. Пример создания представления: CREATE VIEW ViewStudent(St_ID, LastName, FirstName) AS SELECT Pr_ID, Pr_LastName, Pr_FirstName FROM Person, Student
WHERE Pr_ID = St_ID; Чтобы изменить какое-либо представление, его надо удалить и создать заново. При
удалении представления необходимо также удалить все зависимые от его объекты –
триггеры, хранимые процедуры и другие представления. Программа "IB Expert"
пересоздает зависимые объекты автоматически. Пример удаления представления: DROP VIEW ViewStudent; 1.
2 Модифицируемые представления Для некоторых представлений вставку, удаление или обновление строки можно
преобразовать в эквивалентные операции по отношению к исходным таблицам этого
представления. Могут существовать представления, для которых добавление, удаление
или обновление строк не имеет смысла. Такие представления можно только читать. Содержимое представления может изменяться командами модификации, которые
будут автоматически перенаправлены к базовой таблице. Чтобы представление было
модифицируемым, оно должно быть создано на основе одной таблицы (или другого
модифицируемого представления), а запрос, на котором основано представление, не
должен содержать подзапросов, агрегатных функций, UDF, хранимых процедур,
предложений DISTINCT, GROUP BY и HAVING. Если выполняются все эти условия, то
представление автоматически становится модифицируемым, т.е. для него можно
выполнять запросы DELETE, INSERT и UPDATE, которые будут изменять данные в
таблице-источнике. Чтобы сделать модифицируемым представление, которое нарушает любое из
вышеперечисленных условий, применяется механизм триггеров. Триггеры для
модификации представления должны срабатывать при событиях BEFORE DELETE,
BEFORE UPDATE и BEFORE INSERT. В теле триггеров необходимо описать, что
должно происходить с данными при удалении, изменении и вставке. 2
Ход работы Для создания представлений в диалоговом режиме программы "IB Expert"
необходимо выполнить следующие действия: 40
1. Подключиться к базе данных и выполнить команду главного меню
"Database
New View". В результате откроется окно "View", в котором будет
содержаться заготовка оператора создания представления (рисунок 8.1). Рисунок 8.1 – Окно ввода оператора создания представления
2. Изменить в поле на вкладке "SQL" текст оператора создания представления для
создания нужного представления, а затем нажать кнопку [Compile] (Ctrl+F9). В
результате откроется диалоговое окно (рисунок
8.2
), в котором надо подтвердить (кнопка
[Commit]) или отменить (кнопка [Rollback]) выполнение операции Рисунок 8.2 – Диалог создания представления
3. Представление, команда создания которого приведена на рисунке 8.2 не является
модифицируемым. Чтобы сделать его модифицируемым, создадим для него триггер
позволяющий изменять поле "Name" (изменять поле "NameCount" не имеет смысла). 4. Чтобы создать триггер у имеющегося представления, необходимо в окне "View"
перейти на вкладку "Triggers", нажать правую кнопку мыши на типе события, при
котором должен срабатывать триггер, и в контекстном меню выбрать команду "New
Trigger" (рисунок 8.3). В результате откроется окно "Trigger", в котором будет
находиться заготовка оператора создания триггера. Используя эту заготовку, следует
ввести правильный оператор создания триггера и нажать кнопку [Compile Trigger]
(Ctrl+F9). 41
Рисунок 8.3 – Окно создания триггера для представления
Пример триггера, которые для представления "TestView" позволяет
модифицировать поле "Name" приведен на рисунке 8.4. Рисунок 8.4 – Триггер, позволяющий модифицировать представление
3 Задание Лабораторную работу следует выполнять в следующем порядке: 1. Создать аналогично предыдущим лабораторным работам рабочую папку. 2. Скопировать в эту папку файл сценария, созданный при выполнении
предыдущей лабораторной работы. 3. Открыть в приложении "IB Expert" этот сценарий. 4. Добавить в конец сценария операторы создания не менее двух представлений. 5. Выполнить сценарий и сохранить его в папке. 6. Зарегистрировать созданную базу данных в программе "IB Expert" и
подключиться к ней. 7. Создать в своей базе данных еще одно представление в диалоговом режиме,
которое не должно быть модифицируемым. Оно должно называться "TestView" и
основываться, например, на нескольких таблицах или других представлениях. Добавить
в диалоговом режиме к этому просмотру триггеры для разрешения выполнения
обновления. Перенести в сценарий операторы создания этого представления и триггеров,
которые автоматически сгенерирует "IB Expert". 8. Создать в папке "ЛР8" резервную копию базы данных. 9. Оформить отчет о выполнении лабораторной работы. 42
4 Отчет о выполнении работы Отчет должен содержать описание и результаты работы, представляемые в
следующей последовательности: 1. Словесное описание и сценарий создания представлений для своей базы данных.
2. Краткое описание запросов с использованием всех созданных представлений.
Распечатка этих запросов с указанием количества возвращенных ими строк. 3. Перечень файлов, полученных при выполнении лабораторной работы с
указанием их имен, места расположения, даты изменения и размеров (сценарий, база
данных, резервная копия базы данных, файл с запросами и файл с отчетом).
5 Контрольные вопросы 1. Что такое представление? 2. Какие запросы допускается использовать для создания представлений? 3. Как изменить представление? 4. Для чего можно использовать представления? 5. Какие представления являются модифицируемыми? 6. Как сделать любое представление модифицируемым? 7. Зачем используется параметр WITH CHECK OPTION? 8. Какие имеются неудобства работы с представлениями? 9. Для чего предназначено представление, созданное на рисунке 8.2? 10. Почему представление, показанное на рисунке 8.2, не является
модифицируемым? 11. Можно ли сделать так, чтобы у представления, показанного на рисунке 8.2,
можно было удалять строки? Что для этого надо сделать? 3.
9
ЛАБОРАТОРНАЯ РАБОТА №9
Тема работы:
Хранимые процедуры
Цель работы: Изучить виды используемых в Firebird хранимых процедур.
Получить навыки работы с хранимыми процедурами с помощью команд SQL и с
помощью программы "IB Expert". 1
Теоретические сведения 1.1
Хранимые процедуры (Procedures) Хранимая процедура – это откомпилированная во внутреннее представление
сервера СУБД подпрограмма, хранящаяся в базе данных. Хранимые процедуры пишутся
на специальном языке хранимых процедур и триггеров, в котором имеются операторы
присваивания, ветвлений и циклов, и в которых можно использовать операторы SQL,
такие как INSERT, DELETE, UPDATE и SELECT. Хранимые процедуры позволяют переносить часть прикладных функций,
связанных с обработкой данных, в саму данных. Например, хранимая процедура может
управлять приемом заказа или переводом денег с одного банковского счета на другой.
Для автоматического выполнения хранимых процедур при возникновении в базе данных
определенных условий используются триггеры. 43
Хранимые процедуры создаются оператором CREATE PROCEDURE, в котором
указываются следующие элементы: 1. имя хранимой процедуры; 2. входные и выходные параметры и их типы; 3. имена и типы данных локальных переменных, используемых процедурой; 4. последовательность инструкций, которые выполняются при вызове процедуры. С помощью входных параметров внутрь хранимой процедуры можно передать
значения, которые будут использоваться в ходе выполнения процедуры. С помощью
выходных параметров хранимые процедуры возвращают значения, вычисленные в ходе
выполнения процедуры. Многие СУБД поддерживают хранимые функции, которые отличаются от
хранимых процедур тем, что возвращают значение. InterBase не поддерживает хранимых
функций. В InterBase для возврата значений из хранимых процедур применяются
выходные параметры. Некоторые СУБД поддерживают параметры, которые одновременно являются и
входными, и выходными. InterBase не поддерживает такой возможности. Для параметров и локальных переменных хранимых процедур используются те же
типы данных, которые поддерживаются СУБД для столбцов таблиц. Синтаксис оператора создания хранимой процедуры в InterBase: CREATE PROCEDURE Имя_Процедуры [(Параметр <тип данных> [, Параметр <тип данных> ...])] [RETURNS (Параметр <тип данных> [, Параметр <тип данных> ...])] AS [<список переменных>] <блок>; <список переменных> = DECLARE
[
VARIABLE
] Переменная <тип данных>; [
DECLARE
[
VARIABLE
] Переменная <тип данных>; ...] <блок> = BEGIN <составной оператор> [<составной оператор>...] END <составной оператор> = <блок> | Оператор; <тип данных> = один из типов данных InterBase Оператор – любой одиночный оператор языка хранимых процедур и триггеров
InterBase. Внутри хранимых процедур может быть предусмотрена обработка исключений.
Хранимые процедуры могут быть вложенными и рекурсивными, т.е. вызывающими сами
себя. Основные преимущества хранимых процедур: 44
1. Производительность – перенос на сервер часто используемых действий приводит
к существенному повышению производительности. 2. Многократное использование кода – части приложения, перенесенные на сервер,
могут использоваться любыми другими приложениями, имеющими доступ к серверу. 1.
2 Виды хранимых процедур в InterBase В InterBase существует два типа хранимых процедур: 1. Процедуры выбора данных – могут использоваться вместо таблиц в операторе
SELECT. 2. Выполняемые процедуры – производят какие-либо действия с базой данных и не
обязаны возвращать данные. Вызываются с помощью команды EXECUTE PROCEDURE.
Отличие в работе этих двух видов хранимых процедур заключается в том, что
процедуры выборки данных обычно возвращают множество наборов выходных
параметров, сгруппированных построчно, а выполняемые процедуры могут либо вообще
не иметь параметров, либо возвращать только один набор выходных параметров, т.е.
одну строку параметров. Оба типа процедур имеют одинаковый синтаксис создания и формально ничем не
отличаются. Любая выполнимая процедура, если она имеет выходные параметры, может
быть вызвана в запросе SELECT, любая процедура выбора данных – с помощью
EXECUTE PROCEDURE. Разница заключается в цели разработки процедур каждого
вида. Процедура выбора данных специально создается для вызова из оператора SELECT,
а выполняемая процедура – для вызова оператором EXECUTE PROCEDURE. Пример создания процедуры выбора данных: -- возвращает перечень служащих, работающих в отделе, -- который передается в качестве входного параметра: CREATE
PROCEDURE
Test
_
Procedure
(
DeptNo
CHAR
(3)) RETURNS
( Number
INT
, -- порядковый номер EmpNo
SMALLINT
, -- идентификатор служащего FirstName
VARCHAR
(15), -- имя LastName
VARCHAR
(20) -- фамилия ) AS
BEGIN
Number = 0; FOR SELECT EMP_NO, FIRST_NAME, LAST_NAME FROM Employee WHERE DEPT_NO = :DeptNo INTO :EmpNo, :FirstName, :LastName DO BEGIN Number = Number + 1; SUSPEND; END
END
; 45
Каждая процедура выбора данных должна обязательно содержать оператор
SUSPEND, который передает набор выходных параметров в то место, откуда вызвали
процедуру. Пример вызова процедуры выбора данных: SELECT * FROM Test_Procedure('000'); Пример создания выполняемой процедуры: CREATE PROCEDURE MakeGenerator (GenName VARCHAR(31), GenValue INTEGER ) AS DECLARE VARIABLE Sql VARCHAR(256); BEGIN Sql = 'CREATE GENERATOR ' || GenName || ';'; EXECUTE STATEMENT Sql; Sql = 'SET GENERATOR ' || GenName || ' TO ' || CAST(GenValue AS VARCHAR(10)) || ';'; EXECUTE STATEMENT Sql; END; Синтаксис оператора вызова процедуры: EXECUTE PROCEDURE ИмяПроцедуры [Параметр [, Параметр ...]] [RETURNING_VALUES Параметр [, Параметр ...]]; Пример вызова выполняемой процедуры: EXECUTE PROCEDURE MakeGenerator 'Test_Gen', 1; 2
Ход работы Операции по созданию хранимых процедур с помощью выполнения сценария были
изучены в ходе выполнения предыдущих лабораторных работ. Для создания хранимой
процедуры с помощью программы "IB Expert" в диалоговом режиме необходимо
выполнить следующие действия: 1. Подключиться к базе данных и выполнить команду главного меню
"Database
New Procedure". В результате откроется окно "Procedure" для создания
процедуры (рисунок 9.1). 46
Рисунок 9.1 – Окно создания хранимой процедуры в режиме диалога
2. В этом окне в правом верхнем углу следует ввести имя процедуры (заменив имя
"NEW_PROCEDURE"). 3. При нажатой кнопке [Input Parameters] добавить входные параметры. Для этого
находясь в сетке, расположенной в средней части окна, нажать клавишу [Insert] или
[Стрелка вниз]. После этого ввести в новой строке имя входного параметра (на рисунке
9.1 имя входного параметра – "PARAM1") и с помощью показанного на рисунке 9.1
выпадающего списка выбрать его тип. 4. При нажатой кнопке [Output Parameters] таким же образом добавить выходные
параметры. 5. При необходимости точно так же добавить локальные переменные (нажать для
этого кнопку [Variables]). 6. В поле ввода, расположенном в нижней части окна, ввести тело процедуры. 7. Нажать
кнопку
[Compile Procedure] (Ctrl+F9). После тог, как хранимая процедура будет создана, ее можно легко изменить,
используя для этого, как и для триггеров, либо обычный режим, когда можно изменить
весь оператор создания процедуры, либо "ленивый" – когда вручную меняется только
тело процедуры, а входные и выходные параметры и локальные переменные создаются и
изменяются в режиме диалога. Переключение между двумя режимами просмотра и редактирования хранимой
процедуры, показанными на рисунке
9.2
. и 9.3 производится кнопкой [Lazy mode on/off]. 47
Рисунок 9.2 – Окно редактирования хранимой процедуры в режиме диалога
Рисунок 9.3 – Окно редактирования хранимой процедуры в обычном режиме
3 Задание Лабораторную работу следует выполнять в следующем порядке: 1. Создать аналогично предыдущим лабораторным работам рабочую папку. 2. Скопировать в эту папку файл сценария, созданный при выполнении
предыдущей лабораторной работы. 3. Открыть в приложении "IB Expert" этот сценарий. 4. Добавить в сценарий операторы создания хранимых процедур и других
объектов, которые могут потребоваться. Создать не менее трех хранимых процедур. Для
каждой хранимой процедуры должны присутствовать комментарии, поясняющие
выполняемые операции. 5. Выполнить сценарий и сохранить его в папке. 6. Зарегистрировать созданную базу данных в программе "IB Expert" и
подключиться к ней. 48
7. Создать в своей базе данных по одной хранимой процедуре каждого вида в
диалоговом режиме. Они должны называться "TestProcedure1" и "TestProcedure2" и иметь
входные и выходные параметры. Скопировать в отчет сценарий создания этих процедур,
который сгенерирует "IB Expert". 8. Выполнить в окне "SQL Editor" по одному запросу с каждой созданной
хранимой процедурой. Поместить выполненные запросы в отчет, добавив комментарии и
сведения о результате их выполнения. 9. Создать в папке "ЛР9" резервную копию базы данных. 10. Оформить отчет о выполнении лабораторной работы. 4. Отчет о выполнении работы Отчет должен содержать описание и результаты работы, представляемые в
следующей последовательности: 1. Описание бизнес-правил, реализованных с помощью хранимых процедур. 2. Распечатка сценария создания базы данных с комментариями к созданным
хранимым процедурам. 3. Результаты выполнения запросов 4. Перечень файлов, полученных при выполнении лабораторной работы с
указанием их имен, места расположения, даты изменения и размеров (сценарий, база
данных, резервная копия базы данных, файл с отчетом). 5. Контрольные вопросы 1. Что такое бизнес-правила? 2. Что такое хранимая процедура? 3. Какие в InterBase существуют виды хранимых процедур? 4. Как происходит вызов процедур каждого вида? 5. В чем главное отличие в работе процедур каждого вида? 6. Какие преимущества имеет использование хранимых процедур? 7. Как записываются параметры и локальные переменные в операторах SQL внутри
хранимых процедур и триггеров? 8. Какие операторы SQL допускается использовать в хранимых процедурах и
триггерах? 9. Какие операторы можно использовать в хранимых процедурах? 10. Для чего используется оператор SUSPEND? 11. Какие хранимые процедуры называются рекурсивными? 12. Привести синтаксис оператора IF … THEN … ELSE. 13. Привести синтаксис оператора FOR SELECT … DO. 14. Привести синтаксис оператора WHILE … DO. 15. Чем отличается обычный и "ленивый" режим создания и редактирования
хранимых процедур? 16. Как происходит процесс обработки исключений в хранимых процедурах? 49
3.
10 ЛАБОРАТОРНАЯ РАБОТА №10
Тема работы:
Функции пользователя
Цель работы: Изучить способы создания функций пользователя для InterBase и
опе-ратор описания их в базе данных. Получить навыки создания функций поль-зователя
с помощью Delphi 7.
1 Теоретические сведения 1.1 Функции пользователя (UDFs) Функциями пользователя (user defined functions) называются функции для
выполнения операций, которые непосредственно не поддерживаются InterBase. Для
сервера InterBase на платформе Windows функции пользователя располагаются внутри
DLL-библиотек, созданных на языках C, C++, Pascal или других языках высокого уровня.
К функциям пользователя можно обра-щаться из операторов SELECT, хранимых
процедур и триггеров. Процесс создание функции пользователя состоит из трех шагов: 1. Написание функции на одном из языков, таких как C или Delphi. 2. Создание динамической библиотеки, содержащей функцию и размещение ее в
папке "С:\Program Files\Firebird 1.5\UDF\" на сервере. 3. Объявление функции пользователя в базе данных. 10.4.2. Создание динамической библиотеки с UDF Ниже приведен пример библиотеки и модуля с тремя UDF: // файл LibUDF.dpr с текстом библиотеки для Delphi 7: library LibUDF; uses
ExtFunct in 'ExtFunct.pas'; exports IsDate index 1 name 'ISDATE', IBDateToStr index 2 name
'IBDATETOSTR', RoundFloat index 3 name 'ROUNDFLOAT'; end. 72 // файл ExtFunct.pas с текстом модуля для Delphi 7: unit ExtFunct; interface uses
SysUtils; type PIBDateTime = ^TIBDateTime; TIBDateTime = record Date: Integer; Time:
Integer; end; function IsDate(var InputDate: TIBDateTime): PIBDateTime; cdecl; function
IBDateToStr(var InputDate: TIBDateTime): PChar; cdecl; function RoundFloat(var Value:
Double; var Digits: Integer): Double; cdecl; procedure isc_decode_date(IBDateTime:
PIBDateTime; P: Pointer); stdcall; implementation procedure isc_decode_date; external
'gds32.dll' name 'isc_decode_date'; var OutDate: TIBDateTime; function IsDate(var InputDate:
TIBDateTime): PIBDateTime; cdecl; begin // первые 4 байта в InputDate - дата, // последние
4 байта в InputDate - время; // достаточно обнулить данные о времени чтобы // получить
только дату. with OutDate do begin Date := InputDate.Date; Time := 0; end; Result :=
@OutDate; end; 73 type PCTimeStructure = ^TCTimeStructure; TCTimeStructure = record tm_sec :
integer; // Seconds tm_min : integer; // Minutes tm_hour : integer; // Hour (0--23) tm_mday :
integer; // Day of month (1--31) tm_mon : integer; // Month (0--11) tm_year : integer; // Year =
"calendar year"-1900 tm_wday : integer; // Weekday (0--6) Sunday = 0) tm_yday : integer; //
Day of year (0--365) tm_isdst : integer; end; var DateStr: string[11] =
#0#0#0#0#0#0#0#0#0#0#0; function IBDateToStr(var InputDate: TIBDateTime): PChar;
50
cdecl; var B: TCTimeStructure; D, M, Y, I: Integer; begin // преобразует дату из формата
InterBase. isc_decode_date(@InputDate, @B); D := B.tm_mday; M := B.tm_mon + 1; Y :=
B.tm_year + 1900; DateStr := Format('%2d-%2d-%4d', [D, M, Y]); repeat I := Pos(' ', DateStr);
if I > 0 then DateStr[I] := '0'; until I = 0; Result := @DateStr[1]; end; function RoundFloat(var
Value: Double; var Digits: Integer): Double; cdecl; var F: Double; begin F := Frac(Value); case
Digits of 74 1: F := Round(F*10.0)/10.0; 2: F := Round(F*100.0)/100.0; 3: F :=
Round(F*1000.0)/1000.0; else F := 0; end; Result := Int(Value) + F; end; end. Еще один пример создания библиотеки с помощью Delphi с одной функцией
пользователя: T// Файл 'TestUDF.dpr'T для Delphi 7T: library TestUDF; // функция определения
длины текста в строковых полях // типов CHAR(n) и VARCHAR(n): function
LengthCharField(C: PChar): Integer; cdecl; begin // ищем нулевой символ, который
является завершителем // строки: Result := 0; while (C[Result] <> #0) do Inc(Result); // Если
бы эта функция использовалась бы только для // полей типа VARCHAR, то больше
ничего делать не // надо было бы. // Если поле имеет тип CHAR, то оно всегда //
дополняется до максимальной длины пробелами, // поэтому их надо учесть: Dec(Result);
while (Result >= 0) and (C[Result] = ' ') do Dec(Result); Inc(Result); end; exports
LengthCharField name 'LENGTH_CHAR_FIELD'; end. Чтобы получить файл с DLL-библиотекой, содержащей UDF, можно, например,
ввести исходный текст библиотеки с помощью любого редактора в 75 файл 'TestUDF.dpr' и откомпилировать его с помощью утилиты командной строки
'dcc32.exe', введя в командной строке следующую команду: C:\Рабочая папка>dcc32 TestUDF.dpr В результате этой команды будет создан файл 'TestUDF.DLL', который перед
использованием необходимо поместить в специальную папку 'UDF', расположенную в
папке с установленным сервером Firebird 1.5. 10.4.3. Объявление функций пользователя в базе данных Синтаксис оператора объявления функции пользователя следующий: DECLARE EXTERNAL FUNCTION name [datatype | CSTRING(int) [, datatype |
CSTRING(int) ...]] RETURNS {datatype [BY VALUE] | CSTRING(int)} [FREE_IT]
ENTRY_POINT 'entryname' MODULE_NAME 'modulename'; Параметры, входящие в этот оператор, пояснены в табл. 5. Таблица 5 Описание параметров оператора описания UDF Ниже приведены примеры объявления в базе данных созданных выше функций
пользователя. Параметр Описание 51
name Имя функции внутри базы данных. Оно не обязательно
должно совпадать с названием функции в DLL datatype Определяет тип параметров. Все параметры всегда
передаются по ссылке. значение функ-ции (результат) может
возвращаться по зна-чению CSTRING Используется для строковых параметров. В скобках
необходимо указать максимальную длину строки. Если строка
является результа-том функции, она всегда передается по ссыл-ке,
т.е. возвращается адрес строки (указатель) 'entryname' Строка с названиемTP‡‡PT функции в DLL, кото-рую мы
объявляем как пользовательскую функцию 'modulename'
Строка с названием файла DLL, в котором находится функция
‡‡ Параметры entryname и modulename чувствительны к регистру 76 DECLARE EXTERNAL FUNCTION ISDATE DATE RETURNS DATE
ENTRY_POINT 'ISDATE' MODULE_NAME 'LIBUDF'; DECLARE EXTERNAL
FUNCTION IBDATETOSTR DATE RETURNS CSTRING(11) ENTRY_POINT
'IBDATETOSTR' MODULE_NAME 'LIBUDF'; DECLARE EXTERNAL FUNCTION
ROUNDFLOAT DOUBLE PRECISION, INTEGER RETURNS DOUBLE PRECISION BY
VALUE ENTRY_POINT 'ROUNDFLOAT' MODULE_NAME 'LIBUDF'; -- функция
определение длины строкового поля с -- отбрасыванием конечных пробелов: DECLARE
EXTERNAL FUNCTION STRING_LENGTH CSTRING(100) -- в функцию передается
указатель на -- строку, заканчивающуюся нулевым -- символом (#0) и длина здесь
должна -- быть не меньше, чем длина поля CHAR -- или VARCHAR RETURNS
INTEGER BY VALUE –- возврат по значению ENTRY_POINT 'LENGTH_CHAR_FIELD'
MODULE_NAME 'EducatorUDF'; 10.4.4. Использование UDF в базе данных Объявленную в базе данных функцию пользователя можно использо-вать при
создании доменов и таблиц в вычислимых полях, значениях по умолчанию и
контрольных ограничениях. Их можно использовать в операто-рах изменения данных,
триггерах, хранимых процедурах и просто в запросах. Приведем пример запроса с созданными и объявленными выше функ-циями
пользователя: -- это запрос для базы данных Employee.fdb: SELECT SALARY, HIRE_DATE,
ROUNDFLOAT(SALARY, 2), IBDateToStr(HIRE_DATE), IsDate(HIRE_DATE),
CAST(SALARY AS INTEGER) FROM EMPLOYEE; 77 10.5. Задание Лабораторную работу №10 следует выполнять в следующем порядке: 52
1. Создать аналогично предыдущим лабораторным работам рабочую папку и
назвать ее "ЛР10". 2. Разработать для своей базы данных не менее трех функций пользователя. 3. Создать любым текстовым редактором файл, содержащий текст на языке Pascal с
этими функциями. Назвать этот файл таким же именем, как базу данных. Дать ему
расширение '.dpr'. 4. Откомпилировать его либо с помощью Delphi 7.0, либо с помощью ком-пилятора
командной строки 'dcc32.exe'. 5. Поместить исходный файл проекта и откомпилированную библиотеку в рабочую
папку "ЛР10" на сервере для копирования в папку 'UDF'. 6. Скопировать в эту папку файл сценария, созданный при выполнении пре-
дыдущей лабораторной работы. 7. Открыть в приложении "IB Expert" этот сценарий. 8. Исправить текст комментариев и сделать, чтобы база данных теперь соз-давалась
в папке "ЛР10". 9. Добавить в сценарий операторы описания разработанных функций поль-
зователя. Для каждой функции пользователя должны присутствовать комментарии,
поясняющие ее назначение. 10. Выполнить сценарий и сохранить его в папке "ЛР10". 11. Зарегистрировать созданную базу данных в программе "IB Expert" и под-
ключиться к ней. 12. Выполнить в окне "SQL Editor" по одному запросу с каждой созданной
функцией пользователя. Описать результаты этих запросов в отчете. 13. Создать в папке "ЛР10" резервную копию базы данных. 14. Создать и сохранить в папке "ЛР10" файл с отчетом о выполнении лабо-
раторной работы, который должен называться "Отчет.doc". 10.6. Ход работы Для создания файла проекта Delphi с исходным текстом библиотеки можно
использовать либо среду программирования Delphi 7.0, либо любой текстовый редактор.
Процесс получения откомпилированной библиотеки (файла с расширением '*.DLL')
описан в разделе "Создание динамической библиотеки с UDF". После помещения команд объявления функций пользователя в сцена-рий, его надо
выполнить с помощью программы "IB Expert" аналогично пре-дыдущим лабораторным
работам.78 10.7. Отчет о выполнении работы Отчет о выполнении лабораторной работы №10 необходимо оформить на листах
формата A4. Отчет должен содержать описание и результаты рабо-ты, представляемые в
следующей последовательности: 1. Описание разработанных для своей базы данных функций пользователя. 2. Распечатка исходного текста проекта с функциями пользователя. Обяза-тельно
должны присутствовать комментарии ко всему проекту со сведе-ниями о назначении
файла и его авторе, а также комментарии к создан-ным функциям пользователя. 3. Текст операторов объявления UDF в базе данных. 4. Операторы, использующие созданные и объявленные в базе данных функции
пользователя. 53
5. Примеры запросов с использованием разработанных функций и описание
результата их выполнения. 6. Перечень файлов, полученных при выполнении лабораторной работы с
указанием их имен, места расположения, даты изменения и размеров (сценарий, база
данных, резервная копия базы данных, исходный текст проекта с функциями
пользователя, откомпилированная библиотека с функциями пользователя, файл с
отчетом). 10.8. Контрольные вопросы 1. Что такое функция пользователя? 2. Как расшифровывается UDF? 3. Как расшифровывается DLL? 4. Где должна располагаться библиотека, чтобы ее могла использовать СУБД
Firebird 1.5? 5. Что означают в исходных текстах на Pascal: cdecl, stdcall, exports, external, Result,
PChar? 6. Какие существуют способы передачи параметров в функцию? 7. Какой способ передачи параметров используется для UDF в InterBase? 54
Автор
includ
Документ
Категория
Без категории
Просмотров
783
Размер файла
5 758 Кб
Теги
обд
1/--страниц
Пожаловаться на содержимое документа