3.3.2 Проектування збережених процедур

Збережена процедура це спосіб інкапсуляції повторюваних дій. У збережених процедурах можна оголошувати змінні, управляти потоками даних, а також застосовувати інші техніки програмування.

Причина їх створення ясна і підтверджується частим використанням. З іншого боку, якщо ви поговорите з тими, хто працює з ними нерегулярно, то думки розділяться на два абсолютно протилежних флангу. Не забувайте про це.

Позитивні наслідки використання зберігаємих процедур:

1. Поділ логіки з іншими додатками. Збережені процедури інкапсулюють функціональність; це забезпечує звязність доступу до даних і управління ними між різними додатками.

2. Ізоляція користувачів від таблиць бази даних. Це дозволяє давати доступ до збереженим процедурам, але не до самих даних таблиць.

3. Забезпечує механізм захисту. Відповідно з попереднім пунктом, якщо ви можете отримати доступ до даних тільки через збережені процедури, ніхто інший не зможе стерти ваші дані через команду SQL DELETE.

4. Поліпшення виконання як наслідок скорочення мережевого трафіку. За допомогою збережених процедур безліч запитів можуть бути обєднані.

Негативні наслідки використовування процедур:

5. Підвищення навантаження на сервер баз даних у звязку з тим, що велика частина роботи виконується на серверної частини, а менша - на клієнтській.

6. Доведеться багато чого підучити. Вам знадобиться вивчити синтаксис MySQL виразів для написання своїх збережених процедур.

7. Дублювання логіку свого додатку в двох місцях: серверний код і код для збережених процедур, тим самим ускладнюючи процес маніпулювання даними.

8. Міграція з однієї СКБД на іншу (DB2, SQL Server та ін) може призвести до проблем.

9. Інструмент, в якому я працюю, називається MySQL Query Browser, він досить стандартний для взаємодії з базами даних. Інструмент командного рядка MySQL - це ще один чудовий вибір. Я розповідаю вам про це з тієї причини, що всіма улюблений phpMyAdmin не підтримує виконання збережених процедур.

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

CREATE DEFINER = root@localhost PROCEDURE `new_proc1`(

IN `z` INTEGER(4)

)

NOT DETERMINISTIC

CONTAINS SQL

SQL SECURITY DEFINER

COMMENT

BEGIN

IF(z>2) THEN

SELECT

`Reader_id`,

`first_Name_reader`,

`last_Name_reader`,

`telephone_reader`,

`adress_reader`,

`Biblarian_id`,

`Biblary_id`

FROM

`reader` where Biblary_id = 1;

ELSE

SELECT

`Reader_id`,

`first_Name_reader`,

`last_Name_reader`,

`telephone_reader`,

`adress_reader`,

`Biblarian_id`,

`Biblary_id`

FROM

`reader` where Biblary_id = 2;

end if;

END;

Після введення вхідного параметру "z" більше ніж два ми отримуємо перелік всіх читачів які відвідують першу бібліотеку що може бути потрібним користувачу під час редагування, систематизації даних, а також перегляду потрібних даних Результати збереженої процедури зображено на рисунках 3.3 та 3.4.

Рисунок 3.3 - Перелік читачів першої бібліотеки

Після введення вхідного параметру "z" менше чи дорівнює двом ми отримуємо перелік всіх читачів які відвідують другу бібліотеку.

Рисунок 3.4 - Перелік читачів другої бібліотеки

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

CREATE DEFINER = root@localhost PROCEDURE `new_proc2`(

IN `q` INTEGER(4)

)

NOT DETERMINISTIC

CONTAINS SQL

SQL SECURITY DEFINER

COMMENT

BEGIN

IF(q < 5) THEN

SELECT

`Reader_id`,

`first_Name_reader`,

`last_Name_reader`,

`telephone_reader`,

`adress_reader`,

`Biblarian_id`,

`Biblary_id`

FROM

`reader` where Biblarian_id = 1;

ELSE

SELECT

`Reader_id`,

`first_Name_reader`,

`last_Name_reader`,

`telephone_reader`,

`adress_reader`,

`Biblarian_id`,

`Biblary_id`

FROM

`reader` where Biblarian_id = 2;

END IF;

END;

Після введення вхідного параметру "q" менше пяти ми отримуємо перелік всіх читачів яких обслуговує перший бібліотекар що може бути потрібним користувачу під час редагування або систематизації даних.

Результати зображено на рисунках 3.5 та 3.6.

Рисунок 3.5 - Перелік читачів першого бібліотекаря

Після введення вхідного параметру "q" більше або дорівнює пяти ми отримуємо перелік всіх читачів яких обслуговує другий бібліотекар.

Рисунок 3.6 - Перелік читачів другого бібліотекаря

3.3.3 Розробка механізмів управління даними в базі за допомогою тригерів

Тригери призначені для запобігання, журналювання, аудиту, фіксації змін даних,реалізації бізнес правил, реплікації даних та підвищення продуктивності БД в цілому.

Тригер - збережена процедура, виклик якої відбувається автоматично при виконанні з базою даних певних дій: видалення, зміна, додавання записів.

Залежно від того, який оператор модифікації даних активізує тригер, він називається тригером вставки (insert trigger), тригером видалення (delete trigger) або тригером оновлення (update trigger).

Тригери знаходять різне застосування - від перевірки даних до забезпечення складних ділових правил.

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

У даному курсовому проекті для таблиці "Book" був розроблений тригер - "Triger1". Дія цього тригера направлена на запобігання помилок при додаванні запису до таблиці: при вводі порожньої назви записувати туди значення "не задана" Тригері зберігаються не в скрипті бази даних а на сервері My SQL.

Код тригера:

CREATE DEFINER = root@localhost TRIGGER `_before_ins_tr1`

BEFORE INSERT ON `book`

FOR EACH ROW

BEGIN

SET NEW.Name_book = IFNULL(NEW.Name_book, не вказана );

END;

Для здійснення тригера потрібно створити елемента таблиці "Book" з порожнім полем Name_book, що можна побачити на рисунку 3.3.3.1.Там книга з інформацією про печатне видання, автора, жанр та серію має порожнє поле назви книги.

Ситуація, що викликає тригер:

Рисунок 3.7 - Долучення елемента з порожнім полем

Після роботи тригера ми отримаємо замис з полем заповненим за змовчуванням значенням у полі "Name_book".В таблиці порожнє поле назви книги заміниться записом "не вказана"

Результат роботи тригера зображено на рисунку 3.8

Ще одним прикладом тригера у даному курсовому проекті для таблиці "Series" був розроблений тригер - "Triger2". Дія цього тригера направлена на оновлення інформації про кількість книг у серії. При вводі змін книг у серії він буде додавати попередню кількість книг.

Рисунок 3.8 - Результат роботи тригера

Код тригера:

CREATE DEFINER = root@localhost TRIGGER `Triger2` BEFORE

UPDATE ON `series`

FOR EACH ROW

BEGIN

SET NEW.Number = NEW.Number + OLD.Number;

END;

Початкова ситуація зображена на рисунку 3.9

Рисунок 3.9 - Початкова кількість книг в серії

До серії "Гаррі Поттер" потрібно додати одну книгу.

Ситуація потребуюча тригер зображена на рисунку 3.10:

Рисунок 3.10 - Долучення книги до серії

Після роботи тригера ми отримаємо загальну кількість книг у серії "Гаррі Поттер"

Результат зображено на рисунку 3.11

Рисунок 3.11 - Результат роботи тригера

Тригери є дуже зручним рішенням інформаційних аномалій, регулювання змін в базі даних та забезпечення виконання системи бізнес-правил.