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

Збережена процедура – об'єкт бази даних, що представляє собою набір SQL-інструкцій, який компілюється один раз і зберігається на сервері. Збережені процедури дуже схожі на звичайні процедури мов високого рівня, у них можуть бути вхідні і вихідні параметри і локальні змінні, в них можуть вироблятися числові обчислення й операції над символьними даними, результати яких можуть присвоюватися змінним і параметрам. У збережених процедурах можуть виконуватися стандартні операції з базами даних (як DDL, так і DML). Крім того, в збережених процедурах можливі цикли і розгалуження, тобто в них можуть використовуватися інструкції управління процесом виконання.

Збережені процедури схожі на визначені користувачем функції (UDF). Основна відмінність полягає в тому, що UDF-функції можна використовувати як і будь-який інший вираз у SQL запиті, в той час як збережені процедури повинні бути викликані за допомогою функції «CALL».

Збережені процедури можуть повертати множини результатів, тобто результати запиту SELECT. Такі процедури можуть також містити оголошені змінні для обробки даних і курсорів, які дозволяють організувати цикл з кількох рядків у таблиці. Стандарт SQL надає для роботи вирази «IF», «LOOP», «REPEAT», «CASE» та багато інших. Збережені процедури можуть брати змінні, повертати результати або змінювати їх, залежно від того, де ця змінна оголошена. Вони дозволяють підвищити продуктивність, розширюють можливості програмування і підтримують функції безпеки даних.

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

Для обраної предметної області «Адресна книга» була створена наступна процедура – «deletePerson» для автоматичного видалення контактів.

 

CREATE DEFINER = 'root'@'localhost' PROCEDURE `deletePerson`(

IN `Person` INTEGER(10)

)

NOT DETERMINISTIC

CONTAINS SQL

SQL SECURITY DEFINER

COMMENT ''

BEGIN

DELETE from `person`

WHERE person.`Person_ID` = @Person;

DELETE from `user_person`

WHERE `user_person`.`Person_ID` = @Person;

END;

Таблиця «Person» до виконання процедури «deletePerson» виглядає так, як зображено на рисунку. 3.10.

 

 

Рисунок 3.10 – Таблиця «Person» до виконання процедури

 

Для використання цієї процедури необхідно ввести ідентифікатор персони, яку потрібно видалити, та безпосередньо викликати процедуру, як показано на прикладі нижче.

 

set @Person = 18;

CALL `deletePerson`(@Person);

 

В результаті будуть видалені усі дані про людину з ідентифікатором «18», а також запис у таблиці «User_Person» про цей контакт.

 

Після виконання процедури «deletePerson» таблиця «Person» виглядає так, як показано на рисунку 3.11.

 

 

Рисунок 3.11 – Результат виконання процедури «deletePerson»

 

Також була створена процедура «allActions». Користувач може створити один або декілька контактів, а може не створити жодного. Для зручного перегляду усіх дій певного користувача і створена процедура «allActions». Скрипт цієї процедури викладений нижче.

 

CREATE DEFINER = 'root'@'localhost' PROCEDURE `allActions`(

IN `User` INTEGER(10)

)

NOT DETERMINISTIC

CONTAINS SQL

SQL SECURITY DEFINER

COMMENT ''

BEGIN

SELECT *

from `person`

WHERE person.`Person_ID` IN

(SELECT Person_ID

from `user_person`

WHERE `user_person`.`User_ID`=@User);

END;

 

Виклик процедури «allActions» показаний нижче на прикладі користувача із ідентифікатором «8».

 

set @User = 8;

CALL `allActions`(@User);

 

Результатом такої процедури буде таблиця із усіма контактами, створеними обраним користувачем, що показано на рисунку 3.12.

 

 

Рисунок 3.12 – Результат виконання процедури «allActions»