Довго мучився з цим питанням. Літератури в інтернеті мало, особливо російською мовою. Довелося розпитати на зарубіжних форумах, глибше покопатися в мануалах і роз'яснити для себе деякі незрозумілі моменти. Отже, коротко про збережених процедурах в MySQL.
Stored procedures - що це?
Збережені процедури з'явилися починаючи з 5 версії MySQL. Вони дозволяють автоматизувати складні процеси на рівні MySQL, ніж використовувати для цього зовнішні скрипти. Це дає нам найбільш високу швидкість виконання, тому що ми не ганяємо велику кількість запитів, а всього лише один раз викликаємо ту чи іншу процедуру (або функцію).
Що для цього потрібно? Встановіть MySQL сервер версії 5 або вище (dev.mysql.com/downloads). Процедури можна створювати як запити, наприклад через командний рядок MySQL, але для зручності раджу скачати MySQL GUI Tools (dev.mysql.com/downloads/gui-tools). Даний пакет включає в себе три програми - MySQL Administrator, MySQL Query Browser і MySQL Migration Toolkit. Нам знадобляться перші дві. (Хоча можна обійтися одним MySQL Query Browser, але всі ці $$ в збережених процедурах іноді можуть збити з пантелику).
Перша збережена процедура
Отже, відкриваємо MySQL Administrator, підключаємося до сервера MySQL і створюємо нову схему (базу даних): клацніть Catalogs, виберіть Create New Schema в області Schemata (Ctrl + N). Назвіть її як-небудь (наприклад db). Відкрийте щойно створену схему, виберіть вкладку Stored procedures і клацніть кнопку Create Stored Proc. Назвіть свою процедуру procedure1. У тіло процедури (між BEGIN і END) впишіть наступне:
І натисніть Execute SQL - процедура створена. Відкрийте MySQL Query Browser, виберіть свою схему (db) і впишіть наступний запит:
Змінні в MySQL
Для того, щоб витягти каку-то користь від збережених процедур в MySQL, вам доведеться попрацювати зі змінними. Так як це не входь в рамки даної статті, покажу лише кілька прикладів.
прості змінні
Різниця між простими і системними змінними в тому, що системні змінні доступні з поза збереженої процедури. Тобто, щоб витягти якісь дані потрібно користуватися системними, а змінні які потрібні тільки всередині процедури повинні бути простими.
Параметри в збережених процедурах
Тут теж все досить просто. Змінюємо перший рядок, що оголошує саму процедуру:
Тут, ключове слово IN вказує на те, що параметр входить. Далі з цим параметром працюємо як зі звичайною змінною всередині процедури:
Умови, Цикли. IF THEN ELSE, WHILE
Умови та цикли вам обов'язково знадобляться при написанні комплексних процедур, але зациклюватися на цій темі не буду. Думаю хоч якісь навички програмування у вас є, так що покажу лише синтаксис.
простий приклад
Один з хороших випадків застосування процедур - тоді, коли вам потрібно об'єднати декілька запитів в один, наприклад додавання теми в форум і збільшення загальної кількості тем. Припустимо таблиця threads
Тут title у нас буде заголовком нової теми. Ну і таблиця, наприклад з різними статистичними змінними сайту, в тому числі загальна кількість тем у формі.
Тут ніби все зрозуміло, припустимо у нас там є запис з name = threads і value = 0. Створимо нову збережену процедуру procedure2.
Пояснювати особливо нічого, просто два запити об'єднали в один. Тепер ми можемо викликати цю процедуру таким чином:
Таким чином, замість того, щоб передати два або більше запитів (наприклад через php), ми можемо передати один - оптимізація, чистий код і можна змінити в будь-який момент не зачіпаючи інші скрипти.
Курсори (MySQL Cursors)
Курсори дозволяють пройтися по всіх отриманих результатів запиту. На теорії пояснити складно, покажу на практиці. Додамо ще одну таблицю до нашої бази даних - hits:
Сюди ми будемо записувати всі теги з усіх тем. Процедура буде виглядати приблизно так:
Детально. Процедура пройде через кожну тему, кожен тег проб'є по таблиці tags, і якщо даний тег відсутній, то вона його додасть.
Відкриваємо курсор, і отримуємо перший запис. Далі в циклі - Вибираємо кількість збігів з таблиці тегів для поточного тега і поміщаємо результат в змінну iCount. Якщо результатів немає, то запитом INSERT вставляємо новий тег.
Зрештою закриваємо курсор і виходимо з процедури. Ну от і все.
витяг даних
Згадаймо системні змінні і розглянемо ще одну маніпуляцію над нашими таблицями - отримати загальну кількість тегів і тем. Перейдемо відразу до процедури: