Лекции SQL. Лекции по sql учебное пособие для студентов

• Название: Лекции по sql учебное пособие для студентов
• Анкор: Лекции SQL
• Дата: 16.10.2019
• Размер: 0.84 Mb.
• Имя файла: Lekcii_po_SQL_CHupinNA.pdf
• Тип: Лекции; #90508
• страница: 6 из 6

8.9. Генераторы
?
При работе с реляционными БД настоятельно рекомендуется иметь в каждой таблице первичный ключ – поле, значения которого уникальны (не повторяются) для различных записей. (Возможен также случай, когда первичный ключ – это набор из нескольких полей. Тогда уникальным должен быть набор значений этих полей.)
Перед разработчиком часто стоит проблема, как генерировать неповторяющиеся значения для первичного ключа. В некоторых СУБД имеются специальные типы, которые специально придуманы для полей первичного ключа. Они удобны тем, что значения для них автоматически вырабатываются системой управления БД при вставке новых записей в таблицу. Обычно это – автоматически нарастающие целые числа. Например, в MS Access такой тип называется типом «счетчик», в СУБД Paradox имеется тип «autoincremental». В СУБД InterBase такого специального типа данных нет, вместо этого предлагается особый механизм – использование генераторов.
!
Генератор в Interbase – это комплект из целочисленной переменной, хранящейся в базе данных, и механизма ее автоматического увеличения и использования значений этой переменной для заполнения полей в таблицах
БД.
Основное назначение генераторов – вырабатывать неповторяющиеся целочисленные значения, которые можно использовать в качестве значений для первичного ключа.
Для создания генератора в диалект SQL для СУБД Interbase добавлена SQL- команда CREATE GENERATOR <имя_генератора>. Кроме того, после создания генератора бывает необходимо установить некоторое начальное значение
(если этого не сделать, то начальным значением будет 0). Это делается командой SET GENERATOR <имя_генератора> TO <начальное_значение>.
Пример 8.1. Создание генератора c именем new_id.
CREATE GENERATOR new_id
Пример 8.2. Установка начального значения для генератора new_id.
SET GENERATOR new_id TO 5000
!
После создания генератора и установки начального его значения можно вызывать функцию GEN_ID(<имя_генератора>,<шаг_приращения>). Эту функцию можно вызывать в триггерах или хранимых процедурах (о них

— 55 — ниже), а также ее можно вызывать в командах добавления записей в таблицу (команда INSERT из DML).
Пример 8.3. Вариант команды INSERT, в котором одно из полей заполняется значением из генератора.
INSERT INTO spisok (id, fam, im, ot, date_r, kod_fakulteta) VALUES
(GEN_ID(new_id,1), 'Николаев', 'Николай', 'Николаевич', '1990-06-06',
'ФМФ');
√ При выполнении данной команды сервер СУБД увеличит текущее значение генератора на 1 и полученное значение поместит в поле соответствующее таблицы. В следующий раз при вызове INSERT в таком варианте значение генератора будет увеличено еще раз. Таким образом, поле первичного ключа будет заполняться гарантированно неповторяющимися (уникальными) значениями.
√ Удаление неиспользуемого генератора выполняется командой DROP
GENERATOR <имя_генератора>.
8.10. Хранимые процедуры
?
Часто возникает необходимость некоторые типовые процедуры обработки данных хранить в самой БД, а из пользовательских программ только вызывать их.
!
В Intebase имеется механизм хранимых процедур. Они хранятся в БД в откомпилированном виде, что, в сочетании с высокой мощностью компьютера-сервера СУБД, обеспечивает их высокую эффективность. При написании хранимых процедурах используются команды
SQL с добавлениями, реализующими основные алгоритмические конструкции
(циклы, ветвления, обработка ошибок). Подобно процедурам в популярных алгоритмических языках, процедуры имеют механизм передачи входных и выходных параметров по ссылке и по значению, локальные переменные. Из одной хранимой процедуры можно вызывать другую.
√ Приведем краткое описание элементов языка хранимых процедур.
Служебные слова BEGIN и END также как и в Паскале объединяют в единое целое блок операторов для использования там, где по синтаксису требуется одно действие.
Оператор присваивания выглядит так <Имя_переменной>=<Значение>.
Комментарии оформляются так: /* комментарий */ .

— 56 —
Вызов исключений по имени: EXCEPTION <имя_исключения>. Исключение – это ошибка, определяемая пользователем. Обработка ошибок выполняется оператором WHEN. Если такой обработчик не определен, в вызывающую программу посылается сообщение об ошибке.
Вызов другой хранимой процедуры из текущей
EXECUTE PROCEDURE <имя_процедуры>
[var [, var …]] -входные переменные
[RETURNING_VALUES var [, var …]] выходные переменные
Оператор цикла FOR <выбор> DO <действие > повторяет действие для каждой записи из выбора, записанного после FOR. Выбор – это обычный
SELECT за исключением того, что в конце к нему приписано INTO и далее следует имена переменных, в которые следует разместить все поля SELECT.
Оператор выбора IF (<условие>) THEN <действия> [ELSE <действия >].
Отметим, что для условия возможно третье значение UNKNOWN, при котором не будет выполняться ни одна из ветвей ветвления.
Посылка сообщения POST_EVENT <сообщение>. Сообщения должны быть зарегистрированы в БД.
Оператор цикла WHILE (<условие>) DO <действия>
Обработчик ошибок WHEN {<ошибка> [, <ошибка> …]|ANY} DO <действия>
Если ANY, то это обработчик для всех ошибок.
Процедуры в InterBase делятся на два принципиально различных класса : процедуры выбора и процедуры действия. Процедуры действия по общему назначению не отличаются от процедур(подпрограмм) в алгоритмических языках, таких как Паскаль, различия – только в синтаксисе языка.
Пример 8.4. Простой пример процедуры действия. Процедура возвращает сумму двух чисел.
CREATE PROCEDURE sum ( a INTEGER, b INTEGER )
RETURNS ( c INTEGER )
as
BEGIN
c=a+b
END
Процедуры выбора (Select Procedure) возвращают в качестве результата много записей и поэтому в прикладных программах могут быть использованы вместо запросов и таблиц.

— 57 —
Пример 8.5. Простой пример процедуры выбора.
CREATE PROCEDURE mmm ( fk VARCHAR(10) )
RETURNS ( afam VARCHAR(30), aim VARCHAR(30) )
as
BEGIN
SELECT fam, im
FROM spisok
WHERE kod_fakulteta=:fk
INTO :afam, :aim;
SUSPEND;
END
Такая процедура эквивалентна запросу
SELECT fam AS aim, im AS aim
FROM spisok
WHERE KOD_FAKULTETA=:fk
В процедурах выбора должен быть фрагмент INTO :имена переменных, которых определяет значения, возвращаемые процедурой в качестве результатов. Кроме того, должно присутствовать служебное слово SUSPEND, которое после отправки каждого сформированного набора данных (в нашем случае :afam, :aim) делает паузу, пока вызывающая программа не сообщит, что набор данных принят, после чего формирует следующий набор. Таким образом, наличие SUSPEND в сочетании с циклом обеспечивает циклическую отправку многих наборов данных (записей).
8.11. Триггеры
?
Часто возникает необходимость автоматически выполнять некоторые типовые процедуры обработки данных при наступлении некоторого события в состоянии БД.
!
В СУБД Intebase имеется механизм триггеров. По сути дела, триггеры – это хранимые процедуры, которые автоматически запускаются при наступлении одного из ниже перечисленных событий в таблице.
Событие
Когда возникает в таблице
BEFORE INSERT
Перед добавлением новой записи в таблицу
BEFORE DELETE
Перед удалением записи из таблицы
BEFORE UPDATE
Перед изменением содержимого записи

— 58 —
AFTER INSERT
После добавления новой записи в таблицу
AFTER DELETE
После удаления записи из таблицы
AFTER UPDATE
После изменения содержимого записи
√ В отличие от хранимой процедуры, триггер всегда «привязан» к некоторой таблице БД. Триггер не имеет входных или выходных параметров. Его заголовок отличается от заголовка хранимой процедуры.
Пример 8.6. Представим себе, что в БД имеется еще таблица balls, в которой хранятся сведения о каждой оценке студента на экзамене. Она связана с таблицей spisok вторичным ключом по полю id_studenta. Тогда при удалении записи о студенте из таблицы spisok следует удалить и его оценки.
Очевидно, это следует делать непосредственно перед удалением записи из spisok.
CREATE TRIGGER mt1 FOR spisok
BEFORE DELETE
AS
BEGIN
DELETE FROM balls WHERE id_student=OLD.id
END
√ Обратите внимание на использование служебного слова OLD. Оно записывается перед одним из полей таблицы, для которой определяется триггер, и указывает, что будет использоваться старое значение поля, то есть то значение, которое было до события, к которому присоединен триггер. Кроме слова OLD, используется еще слово NEW – новое значение поля. Легко понять, что в триггерах BEFORE DELETE и AFTER DELETE можно использовать только OLD, в триггерах BEFORE INSERT и AFTER INSERT – только
NEW, а вот в триггерах BEFORE UPDATE и AFTER UPDATE можно встретить и
OLD и NEW. Например, фразу «если поменялась фамилия студента …» можно
«перевести» как «IF (OLD.fam<>NEW.fam) THEN …».
√ Общий синтаксис триггера
CREATE TRIGGER <имя_триггера> FOR <имя_таблицы>
[ACTIVE | INACTIVE]
{BEFORE | AFTER} {DELETE | INSERT | UPDATE}
[POSITION <номер>]
AS
[<список_локальных_переменных>]
BEGIN
<команды>
END

— 59 —
√ Напоминаем, что фигурные скобки { } обозначают обязательный элемент, который должен быть выбран из вариантов, разделенных вертикальной чертой, а квадратные скобки [ ] обозначают необязательный элемент.
Необязательный параметр POSITION нужен, когда для одного и того же события в одной и той же таблице объявлены несколько триггеров. Тогда для каждого из этих триггеров номер, записанный после служебного слова
POSITION указывает, каким по счету будет выполняться данный триггер.
Каждая локальная переменная объявляется «персонально» DECLARE
VARIABLE <имя_переменной> <тип_переменной>; (точка с запятой обязательна).
В остальном синтаксис триггеров такой же, как синтаксис хранимых процедур.
Сокращения
ANSI - Американский национальный институт стандартов
DDL – Data Definition Language, язык определения данных
DML - Data Manipulation Language, язык манипулирования данными
ISO - Международная организация по стандартам
QBE - Query By Example, язык запросов по образцу.
SQL - Structured Query Language, структурированный язык запросов
SYSDBA – стандартное имя администратора для СУБД InterBase. Регистр символов неважен. masterkey – стандартный пароль администратора для СУБД InterBase. Регистр символов важен. Некоторые авторы утверждают, что важны только первые 8 символов, однако нельзя ручаться, что это верно во всех версиях
InterBase/FireBird.
БД – база данных
СУБД – система управления базами данных
ИС – информационная система
АИС – автоматизированная информационная система
Термины
Информационная система – система ввода, хранения и выдачи информации, физические устройства хранения данных, технические

— 60 — средства для их обработки, людские ресурсы для работы, регламент работы.
Автоматизированная информационная система – информационная система, в которой ввод, хранение и выдача информации обеспечивается с помощью средств электронной техники. Примером информационной системы, не являющейся автоматизированной, является система учета книг в библиотеке, реализованная обычными средствами (бумажные формуляры, каталоги и т.д.)
Литература
1. Гайдамакин Н.А. Автоматизированные системы, базы и банки данных.
Вводный курс: Учебное пособие. — М.:Гелиос АРВ, 2002. — 368с.
2. Грабер М. Введение в SQL — М.: Издательство «Лори», 1996. - 379с.
(перевод книги Gruber М. “Understanding SQL”, 1990, имеются и более новые издания этой же книги в русском переводе)
3.
Скляр А.Я. Введение в InterBase — М.: Горячая линия-Телеком, 2002. -
517с.
√ Нет сомнений, что книгой, где язык SQL описан наиболее доступно, является достаточно давняя книга Gruber М. “Understanding SQL”, 1990.
√ Наилучшим учебником по дисциплине «Автоматизированные системы» среди изданных на сегодня является, на наш взгляд, книга
Н.А.Гайдамакина.