Від автора: чого це ви спите на робочому місці! Ви не спите, а чекаєте, поки СУБД виконає запит? Так її потрібно розігнати. Збережені процедури MySQL застосовували? Не знаєте як? Ну, прокидайтеся, тому що зараз ми будемо розглядати як раз цю тему.
Що за процедури ще?
Якщо у вас фобія щодо медичних процедур, то дані структури «не з тієї теми». Так що можна не боятися. А якщо серйозно, то збережені процедури – це зручна і корисна для «здоров’я» СУБД річ. Їх ще називають «постійними функціями MySQL», але це не зовсім точне визначення. Хоча давайте розберемося з усім по порядку.
Збережені процедури дозволяють значно оптимізувати роботу сервера і підвищити швидкість її роботи, оскільки їх код після першого виконання зберігається в кеші оперативки. При всіх наступних викликах процедура буде вилучатись з кеша, а не вирушати знову на виконання.
В MySQL виклик збереженої процедури означає, що вписані в її код запити обработаются лише наполовину. І то, якщо буде змінено значення їх параметрів. Але не все так ідеально. Опишемо спочатку позитивні сторони використання процедур:
Інкапсуляція функціональності – весь код зберігається в одному місці, що полегшує доступ до нього для інших додатків. У цьому збережені процедури схожі з програмними функціями.
Ізоляція доступу до даних – всі користувачі отримують доступ до рядків таблиці, а тільки до збережених процедур. Що в свою чергу підвищує рівень захищеності всіх даних.
Підвищення швидкості роботи сервера – за рахунок кешування і об’єднання запитів.
В MySQL збережені процедури в теорії є структурами, що належать
до більш «високих матерій» — програмування СУБД. Так що ми з вами
(як професіонали) хоч потихеньку, але ростемо 
. Але
повернемося до процедур, і опишемо негативні сторони їх
використання:
Навантаження на сервер БД підвищується – велика частина коду процедур виконується на стороні сервера. Дана СУБД побудована на основі моделі «клієнт-сервер», в якій задіяні кілька пристроїв.
Ускладнюється маніпуляція даними – при розробці додатків доведеться прописувати частина коду і на клієнтській стороні.
Ускладнюється процес перенесення БД на інші рейки (СУБД).
Процедури в phpMyAdmin
Для початку розглянемо застосування в MySQL збережених процедур на прикладі phpMyAdmin. Таким чином, нам буде легше розібратися з цим типом структур. Стартуємо!
Запускаємо програмну оболонку, праворуч вибираємо тестову БД. У мене такий є база world. Потім в головному меню зверху переходимо по вкладці «Процедури». Тут тиснемо на «Додати процедуру».
Після цього з’явиться вікно діалогу «Додати процедуру». Заповнюємо в ній всі зазначені на знімку поля. Вказуємо ім’я процедури, тип. У графі «Визначення» вводимо обліковий запис користувача, і в коментарях (необов’язково) вказуємо для себе, що це всього лише приклад збереженої процедури.
Вже на цьому етапі ми знайомимося з особливостями синтаксису створення збережених процедур MySQL. У полі «Визначення» прописуємо тіло структури. Зверніть увагу, що виконується запит знаходиться між ключовими словами BEGIN та END:
BEGIN
SELECT ‘HELLO WORD!’;
END
Даний запит не виконує ніяких дій з базою, а лише виводить напис. Це ми вказали в полі «Доступ до SQL даними».
Для закінчення створення нашої першої процедури тиснемо внизу «Ок». Після цього програма виводить «зелененькі» повідомлення про вдале виконання запиту. Код представлений нижче. В MySQL збережені процедури і функції створюються за допомогою спеціальної команди CREATE PROCEDURE. Але про це пізніше.
Тепер запустимо створену структуру на виконання. Для цього в розділі «Процедури» натискаємо кнопку «Виконати». Але що це за неподобство! Куди подівся наш улюблений «зелененький»? Чому програма «лається» і «кричить», що їй не вистачає виділеної пам’яті?
Куди дивився автор даної публікації…! Вибачте, трохи заплутався.
Адже автор – це я . Спокій,
зараз все виправимо! Така помилка виникає із-за того, що у
головному конфігураційному файлі значення параметра thread_stack
залишено без змін. За замовчуванням для кожного потоку виділяється
128 Kb. Виділеного ліміту оперативки для виконання простих запитів
цілком вистачає, але для процедур мало.
Це ще раз доводить, що на виконання тригерів і збережених процедур в MySQL витрачається більше ресурсів.
Перейдіть в конфігураційний файл my.ini, і збільшити ліміт оперативки, встановленої для кожного потоку, до 256 kb. Тепер ще раз запустіть на виконання створену процедуру. В цей раз все пройшло як треба, і програма повернула результат без помилки.
Зверніть увагу, що для виклику використовується команда CALL із зазначенням імені процедури та прийнятих параметрів (у дужках).
Більш складний приклад
Але все ж phpMyAdmin по своїм можливостям більше підходить для швидкого складання процедур. А для розробки в MySQL збереженої процедури з динамічним кількістю аргументів (наприклад) буде потрібно більш зручне. Чому:
phpMyAdmin не хоче нормально «розуміти» процедури, створені не через спеціальний конструктор.
Програма не виконує структури, запущені під root і порожнім паролем, а в Денвері створити нового користувача і зайти під ним в phpMyAdmin ціла проблема.
Якщо ви уважно слідкуєте за моїми публікаціями і виконуєте всі прописані в них «побажання», то у вас повинен стояти MySQL Administrator. У прив’язку до нього залишилося скачати MySQL Query Browser за цим посиланням. Ці дві програми краще використовувати разом: у першій створювати процедури, а в іншій тестувати їх. Поїхали:
Запускаємо MySQL Administrator.
Авторізуємось.
Зліва вгорі переходимо через вкладку «Catalog».
Вибираємо потрібну БД, і у верхньому меню тиснемо на «Stored procedures», а внизу на «Create Stored Proc»
У вікні редактора вводимо код процедури і тиснемо «Execute SQL».
CREATE DEFINER=`roman`@`localhost` PROCEDURE `proc5`()
BEGIN
declare int a;
set a=’SELECT COUNT( * ) FROM city as a’;
if(a > 1000)THEN
SELECT ‘1000’;
END IF;
END
Ви, напевно, помітили, що ми не передаємо ніяких значень всередину процедури. Крім цього в MySQL може бути невідома кількість параметрів в збереженій процедурі, які потім можна передати через нові оголошення змінних, розташовані усередині циклів.
Для запуску процедури заходимо в MySQL Query Browser. Спочатку вводимо свою учетку і пароль, а потім ліворуч у «Object Explorer» знаходимо папку з потрібною базою. Інша черговість дії показана на наступному знімку.
Запуск процедури в PHP
Тепер розглянемо, як відбувається в PHP виклик збереженої процедури MySQL. Для цього нам доведеться трохи «перекроїти» код нашого попереднього прикладу. Ми додамо в процедуру параметр на висновок, а також змінимо код запиту:
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc6`(out col
decimal)
BEGIN
SELECT COUNT( * ) into col FROM city;
END
Для виклику з файлу PHP процедури і виведення результату будемо використовувати можливості класу PDOStatement, створеного спеціально для роботи з БД через SQL
Цей клас реалізований порівняно недавно, і підтримується PHP, починаючи з версії 5.1.0. Раджу перед використанням перевірити версію мови c допомогою вбудованої функції phpversion().
exec(‘CALL proc6(@var)’);
$result=$cn->query(‘select @var’)->fetchColumn();
print($result);
?>
Для асинхронного виклику збережених процедур MySQL краще
використовувати можливості розширення MySqli. Але в рамках цього
матеріалу цим ми не будемо займатися. Залишимо цю «смачну» тему на
потім. До речі, а чого це вам тепер на роботі не спиться? Значить,
наша стаття допомогла, і ви нарешті розігнали не тільки MySQL, але
і свою дрімоту .
