ПетрГУ, кафедра прикладної математики і кібернетики
2 Поняття процедури
Процедура представляє собою програму, написану на внутрішньому мові СУБД, що зберігається в базі даних у вигляді самостійного об'єкта. Такі процедури зазвичай називаються збереженими, приєднаними. Процедури можуть бути викликані прикладною програмою. Процедури виконуються на сервері бази даних. Процедури можуть містити параметри і повертати значення, в тому числі і повідомлення про помилку.
Переваги використання процедур:
- централізований контроль доступу до даних;
- прикладні програми можуть викликати процедуру, що скорочує час написання програм, при модифікації процедури, все викликають її програми отримають новий код, оптимізація коду;
- знижує трафік в мережі в системах «клієнт-сервер» за рахунок передачі тільки імені процедури і її параметрів замість обміну даними, а процедура виконується на сервері;
- приховування від користувача багатьох особливостей конкретного пристрою бази даних, що забезпечує більшу незалежність даних;
- більша безпека даних, користувач може мати право викликати
процедуру, але не керувати даними, які викликаються цією процедурою; Недолік: відсутність стандартів в реалізації процедур.
Конспект лекцій з дисципліни «Бази даних» (Процедури і тригери)
ПетрГУ, кафедра прикладної математики і кібернетики
3 Команди роботи з процедурами в MS SQL Server
CREATE PROCEDURE <имя процедуры>
[@<имя параметра> <тип данных> [OUTPUT]. ]
[DECLARE @<имя переменной> <тип данных>. ] BEGIN
Імена всіх змінних в MS SQL Server повинні починатися з символу
DROP PROCEDURE <имя процедуры>
Процедура підраховує кількість студентів, що навчаються на факультеті, ідентифікатор якого є вхідним параметром процедури @id, і повертає це значення в параметрі @total_sum.
Create Procedure prStudentsOfFaculty @id int, @total_sum int output AS
Set @total_sum = 0
Set @total_sum = (Select count (*) From tblStudent, tblGroup Where (tblStudent.GroupId = tblGroup.GroupId) and (tblGroup.FacultyId = @id)) End
Конспект лекцій з дисципліни «Бази даних» (Процедури і тригери)
ПетрГУ, кафедра прикладної математики і кібернетики
4 Поняття тригера
Тригер (правило) приєднується до таблиці і автоматично викликається системою управління базами даних, коли виконуються операції оновлення над таблицею (додавання, видалення, зміна записів таблиці).
Особливості реалізації тригерів в MS SQL Server
В MS SQL Server:
- тригер може бути викликаний або після виконання операції, або замість виконання операції;
- тригер викликається один раз для всіх записів таблиці, над якими повинна бути виконана операція;
- отже, змінювані записи зберігаються в двох автоматично створюваних при виклику тригера таблицях:
o таблиця Inserted - містить змінені або додані записи таблиці;
o таблиця Deleted - містить записи до виконання змін або видалені записи таблиці;
- в тілі тригера, визначеного для операції Insert, доступна тільки таблиця
- в тілі тригера, визначеного для операції Delete, доступна тільки таблиця
- в тілі тригера, визначеного для операції Update, доступні обидві таблиці
Inserted і Deleted;
- для одного і того ж події може бути створено будь-яку кількість тригерів, вони викликаються в випадковому порядку (можливо, в порядку їх створення).
Конспект лекцій з дисципліни «Бази даних» (Процедури і тригери)
ПетрГУ, кафедра прикладної математики і кібернетики
6 Приклади реалізації тригерів
Обмеження предметної області: стипендія студента не може бути збільшена більш ніж на 5% від попередньої стипендії.
CREATE TRIGGER tgrStudentGrantUpdate
ON tblStudent AFTER UPDATE
DECLARE @Grant_old float, @Grant_new float, @Id int;
Select @Grant_old = Grant from Deleted
Select @Grant_new = Grant, @Id = StudentId from Inserted
IF (@Grant_new - @Grant_old> 0.05 * @Grant_old)
UPDATE tblStudent SET Grant = 1.05 * @Grant_old
WHERE StudentId = @Id
Тригер tgrStudentGrantUpdate створений для таблиці tblStudent. Тригер буде спрацьовувати після виконання операції зміни даних.
У тригері визначені три локальні змінні: @Grant_old (дійсного типу) для зберігання старої стипендії студента, @Grant_new (дійсного типу) для зберігання нової стипендії студента, @Id (цілого типу) для зберігання ідентифікатора студента.
При виклику тригера СУБД створює дві таблиці: Deleted, що містить змінені записи до їх зміни, і Inserted, що містить змінені записи після їх зміни.
У тілі тригера, в першу чергу, з таблиці Deleted витягується значення стипендії студента до внесення змін, т. Е. Старої стипендії, далі з таблиці Inserted витягується значення стипендії студента після внесення змін, т. Е. Новою стипендії. Разом з витяганням нової стипендії з таблиці Inserted, витягується так само і ідентифікатор студента. Ідентифікатор студента з тим же успіхом можна було витягти і з таблиці Deleted.
Далі, в тілі тригера перевіряється умова про величину зміни стипендії. Якщо стипендія змінилася більш ніж на 5%, то тригер вносить поправку в дані - збільшує стипендію тільки на 5% в порівнянні з попереднім значенням стипендії студента. Ця дія виконується за допомогою виклику операції Update в таблиці tblStudent для відповідного студента.
Конспект лекцій з дисципліни «Бази даних» (Процедури і тригери)