Архив / Пособие_Триггеры и процедуры (1)

ПетрГУ, кафедра прикладной математики и кибернетики

Конспект лекций по дисциплине «Базы данных»

Тема: Процедуры и триггеры (на примере условного языка SQL)

составитель: Л. В. Щёголева

Конспект лекций по дисциплине «Базы данных» (Процедуры и триггеры)

1

ПетрГУ, кафедра прикладной математики и кибернетики

СОДЕРЖАНИЕ

Введение .........................................................................................................		3
1	Описание структуры базы данных............................................................	4
2	Понятие процедуры ....................................................................................	6
3	Команды работы с процедурами ...............................................................	7
4	Понятие триггера ........................................................................................	8
5	Команды работы с триггерами ................................................................	10
6	Примеры реализации триггеров ..............................................................	12
	Пример 1...............................................................................................	12
	Пример 2...............................................................................................	16
	Пример 3...............................................................................................	17
	Пример 4...............................................................................................	17
	Пример 5...............................................................................................	19

Конспект лекций по дисциплине «Базы данных» (Процедуры и триггеры)

2

ПетрГУ, кафедра прикладной математики и кибернетики

Введение

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

сописанием их работы.

Все команды написаны в синтаксисе условного SQL.

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

разделе 1.

Конспект лекций по дисциплине «Базы данных» (Процедуры и триггеры)

3

ПетрГУ, кафедра прикладной математики и кибернетики

1 Описание структуры базы данных

Таблица tblFaculty содержит информацию о факультетах университета.

tblFaculty

Наименование	Домен	Ключ	Описание
атрибута
FacultyId	Integer	PK	Идентификатор факультета
FacultyName	Char(100)		Название факультета
DeenName	Char(50)		ФИО декана
FacultyRoomId	Integer	FK(tblRoom)	Номер кабинета деканата
FacultyPhone	Char(6)		Телефон деканата
FacultyStudent	Integer		Количество студентов факультета

Таблица tblStudent содержит информацию о студентах университета в одном учебном году.

tblStudent

Наименование атрибута	Домен	Ключ	Описание
StudentId	Integer	PK	Идентификатор студента
StudentName	Char(100)		ФИО студента
GroupId	Integer	FK(tblGroup)	Группа
Grant	Decimal(6,0)		Стипендия

Таблица tblGroup содержит информацию о студенческих группах университета в одном учебном году.

tblGroup

Наименование атрибута	Домен	Ключ	Описание
GroupId	Integer	PK	Идентификатор группы
GroupHead	Integer	FK(tblStudent)	Староста
GroupNumber	Char(10)		Номер группы
FacultyId	Integer	FK(tblFaculty)	Факультет
StudyYear	Integer		Курс

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

	tblSubject
	Наименование атрибута	Домен	Ключ	Описание
	SubjectId	Integer	PK	Идентификатор предмета
	SubjectName	Char(40)		Название предмета
	LecturesHour	Integer		Количество часов лекций
	PracticeHour	Integer		Количество часов практик
	FacultyId	Integer	FK(tblFaculty)	Факультет
	Конспект лекций по дисциплине «Базы данных» (Процедуры и триггеры)				4

ПетрГУ, кафедра прикладной математики и кибернетики

Таблица tblRoom содержит информацию об аудиториях университета.

tblRoom

Наименование атрибута	Домен	Ключ	Описание
RoomId	Integer	PK	Идентификатор аудитории
RoomNumber	Char(6)		Номер аудитории
PlaceCount	Integer		Количество рабочих мест
SmartBoard	Boolean		Наличие электронной доски
RoomFloor	Integer		Этаж

Таблица tblSchedule содержит информацию о расписании занятий студенческих групп.

tblSchedule

Наименование атрибута	Домен	Ключ	Описание
ScheduleId	Integer	PK	Идентификатор
RoomId	Integer	FK(tblRoom)	Аудитория
Weekday	Char(15)		День недели
Time	Char(15)		Время
SubjectId	Integer	FK(tblSubject)	Предмет
GroupId	Integer	FK(tblGroup)	Группа
TeacherName	Char(60)		ФИО преподавателя

Конспект лекций по дисциплине «Базы данных» (Процедуры и триггеры)

5

ПетрГУ, кафедра прикладной математики и кибернетики

2 Понятие процедуры

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

Преимущества использования процедур:

–централизованный контроль доступа к данным;

–прикладные программы могут вызывать процедуру, что сокращает время написания программ, при модификации процедуры, все вызывающие ее программы получат новый код, оптимизация кода;

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

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

–большая безопасность данных, пользователь может иметь право вызывать

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

Конспект лекций по дисциплине «Базы данных» (Процедуры и триггеры)

6

ПетрГУ, кафедра прикладной математики и кибернетики

3 Команды работы с процедурами

Создание процедуры

CREATE PROCEDURE <имя процедуры>

[ ({ in | out | inout } <имя параметра> <тип параметра>, ...)] BEGIN

[DECLARE <имя переменной> <тип переменной>; ...] <операторы>

END;

Процедура должна иметь уникальное в пределах базы данных имя. Процедура может иметь параметры. Для каждого параметра указывается его вид: in – входной параметр, out – выходной параметр, inout – одновременно входной и выходной параметр. В теле процедуры могут быть определены локальные переменные с указанием их типа.

Вызов процедуры

CALL <имя процедуры> [ ( <значение параметра> , ...)]

Удаление процедуры

DROP PROCEDURE <имя процедуры>

Пример

Процедура prStudentsOfFaculty подсчитывает количество студентов, обучающихся на факультете, идентификатор которого является входным параметром процедуры (параметр с именем id), и возвращает это значение в параметре total_sum.

Create Procedure prStudentsOfFaculty (in id int, out total_sum int) Begin

Set total_sum = 0

Set total_sum = (Select count(*) From tblStudent, tblGroup Where (tblStudent.GroupId = tblGroup.GroupId) and (tblGroup.FacultyId = id)) End

Пример вызова процедуры представлен в примере 4 для триггеров далее по тексту.

Конспект лекций по дисциплине «Базы данных» (Процедуры и триггеры)

7

ПетрГУ, кафедра прикладной математики и кибернетики

4 Понятие триггера

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

Преимущества использования триггеров:

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

–переносит операции по проверке значений из прикладной программы в СУБД и делает их доступными для всех программ;

–автоматическое уведомление об изменениях в базе данных с вызовом события;

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

Триггер вызывается для операций:

–INSERT,

–UPDATE,

–DELETE.

Триггер может иметь условие, при истинности которого выполняется тело триггера.

Существует две стратегии вызова триггера:

–немедленная – триггер вызывается сразу при наступлении события,

–отложенная – триггер вызывается после фиксации транзакции.

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

–вызов триггера до выполнения действий над таблицей,

–вызов триггера после выполнения действий над таблицей,

–вызов триггера вместо выполнения действий над.

Действия выполняются на двух уровнях:

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

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

Можно определить несколько триггеров на одно и тоже событие, которые будут вызываться по очереди. СУБД либо устанавливает порядок выполнения триггеров, либо выбирает случайным образом. В случае наличия нескольких триггеров для одного события могут быть применены две стратегии проверки условия:

Конспект лекций по дисциплине «Базы данных» (Процедуры и триггеры)

8

ПетрГУ, кафедра прикладной математики и кибернетики

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

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

Конспект лекций по дисциплине «Базы данных» (Процедуры и триггеры)

9

ПетрГУ, кафедра прикладной математики и кибернетики

5 Команды работы с триггерами

Создание

CREATE TRIGGER <имя триггера>

{BEFORE | AFTER} {DELETE | INSERT | UPDATE [ OF <список столбцов>]}

ON <имя таблицы>

BEGIN

[DECLARE <имя переменной> <тип переменной>; ...] <операторы>

END;

Триггер вызывается для каждой записи.

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

Автоматически СУБД создает две переменных с именами OLD и NEW, которые имеют тип структуры с полями, соответствующими полям таблицы, для которой создан триггер. Переменная OLD хранит значения записи, для которой был вызван триггер, до выполнения операции, переменная NEW хранит значения записи, для которой был вызван триггер, после выполнения операции, (переменная OLD определена, если триггер создан для операций удаления и изменения, NEW – для операций добавления и изменения).

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

1.Пользователь передает СУБД команду Insert;

2.СУБД заполняет переменную NEW4

3.СУБД вызывает триггер4

4.СУБД записывает новую запись в таблицу, при этом значения новой записи берет из переменной NEW.

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

1.Пользователь передает СУБД команду Insert;

2.СУБД записывает новую запись в таблицу;

3.СУБД заполняет переменную NEW;

4.СУБД вызывает триггер.

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

1.Пользователь передает СУБД команду Update;

2.СУБД заполняет переменные OLD и NEW;

3.СУБД вызывает триггер;

4.СУБД записывает новые значения записи из переменной NEW в обновляемую

таблицу.

Конспект лекций по дисциплине «Базы данных» (Процедуры и триггеры)

10