CodeNix

692378482

codenix.sv

admin@codenix.net

Збережені процедури та функції в MySQL

Опубліковано: Категорія: БД Переглядів: 1653

бд mysql procedure function

MySQL підтримує збережені процедури. Під цим терміном розуміється послідовність операцій, що зберігається як єдине ціле в каталозі бази даних на сервері. Для запуску збереженої процедури використовується SQL вираз виклику.

Збережені процедури можуть мати ІN, INOUT і OUT параметри залежно від версії MySQL.

Необхідні права:

  • CREATE ROUTINE для створення збережених процедур(функцій)
  • ALTER ROUTINE необхідні для зміни або видалення процедур. Цей привілей автоматично призначається творцеві процедури (функції)
  • EXECUTE привілей потрібно для виконання підпрограми. Тим не менш, автоматично призначається творцеві процедури (функції). Також, за замовчуванням, користувачі, що мають доступ до БД, можуть викликати підпрограми, асоційовані з цією БД.

Переваги та недоліки використання збережених процедур та функцій

Переваги

  • Поділ логіки з іншими додатками. Збережені процедури інкапсулюють функціональність; це забезпечує зв'язність доступу до даних і управління ними між різними додатками.
  • Ізоляція користувачів від таблиць бази даних. Це дозволяє давати доступ до збережених процедур, але не до самих даних таблиць.
  • Забезпечує механізм захисту. Відповідно з попереднім пунктом, якщо ви можете отримати доступ до даних тільки через збережені процедури, ніхто інший не зможе стерти ваші дані через команду DELETE.
  • Поліпшення виконання як наслідок скорочення мережевого трафіку. За допомогою збережених процедур безліч запитів можуть бути об'єднані.

Недоліки

  • Підвищення навантаження на сервер баз даних у зв'язку з тим, що більша частина роботи виконується на серверній частині, а менша - на клієнтській.
  • Доведеться багато чого підучити. Вам знадобиться вивчити синтаксис MySQL виразів для написання своїх збережених процедур.
  • Ви дублюєте логіку свого додатку в двох місцях: серверний код і код для збережених процедур, тим самим ускладнюючи процес маніпулювання даними.
  • Міграція з однієї СУБД на іншу (DB2, SQL Server та ін.) Може призвести до проблем.

Збережені процедури створюються за допомогою виразу CREATE PROCEDURE, функції - CREATE FUNCTION. Збережена процедура викликається, використовуючи вираз CALL, причому тільки повертаючі значення змінні використовуються в якості вихідних. Функція може бути викликана подібно будь-якій інший функції і може повертати скалярну величину. Збережені процедури(функції) можуть викликати інші збережені підпрограми.

Для відділення внутрішнього запиту від зовнішнього завжди використовують обмужувач відмінний від звичайного (для задання використовують команду DELIMITER). Обмежувач - це символ або рядок символів, який використовується для вказівки клієнту MySQL, що ви завершили написання виразу SQL.

Створення збережених процедур та функцій

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

DELIMITER |

CREATE PROCEDURE `proc` ()  
LANGUAGE SQL  
DETERMINISTIC  
SQL SECURITY DEFINER  
COMMENT 'A procedure'  
BEGIN  
    SELECT 'Hello World !';  
END |

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

Назви збережених процедур чутливі до регістру. Вам також не можна створювати кілька процедур з однаковою назвою. Усередині збереженої процедури не може бути виразів, що змінюють саму базу даних.

Параметри

  • Language: з метою забезпечення переносимості, за замовчуванням вказано SQL.
  • Deterministic: якщо процедура весь час повертає один і той же результат, і приймає одні й ті ж вхідні параметри. Це для реплікації і процесу реєстрації. Значення за умовчанням - NOT DETERMINISTIC.
  • SQL Security: під час виклику йде перевірка прав користувача. INVOKER - це користувач, що викликає збережену процедуру. DEFINER - це "творець" процедури. Значення за замовчуванням - DEFINER.
  • Comment: коментар, значення за замовчуванням - ""

Виклик збереженої процедури

Щоб викликати збережену процедуру, необхідно викристати ключове слово CALL, а потім назву процедури, а в дужках вказати параметри (змінні або значення). Дужки обов'язкові.

Видалення процедури

Видалити процедуру можна за допомогою команди DROP PROCEDURE:

DROP PROCEDURE IF EXISTS proc;

IF EXISTS відловлює помилку у випадку, якщо такої процедури не існує.

Створення функцій:

DELIMITER |

DROP FUNCTION IF EXISTS func |
CREATE FUNCTION func() RETURNS VARCHAR(20)
BEGIN
    RETURN 'Hello!';
END |

Виклик функції

SELECT func();

Видалення функцій

Видалити функцію можна за допомогою команди DROP FUNCTION:

 DROP FUNCTION IF EXISTS proc;

Параметри

  • CREATE PROCEDURE proc (): порожній список параметрів
  • CREATE PROCEDURE proc (IN varname DATA-TYPE): один вхідний параметр. Слово IN необов'язкове, тому що параметри за замовчуванням - IN (вхідні).
  • CREATE PROCEDURE proc (OUT varname DATA-TYPE): один вертаємий параметр.
  • CREATE PROCEDURE proc (INOUT varname DATA-TYPE): один параметр, одночасно вхідний і вертаємий.

Змінні

Змінні створюються за допомогою команди DECLARE varname DATA-TYPE DEFAULT defaultvalue;


Коментарі