Так як курсори є частиною процедури, що, то в цій статті ми ще детальніше розглянемо ХП. Зокрема, як витягти з ХП набір даних.
Що таке курсор?
Курсор не може використовуватися в MySQL сам по собі. Він є важливим компонентом збережених процедур. Я б порівняв курсор з «покажчиком» в C / C + + або ітератором в PHP-операторі foreach.
За допомогою курсору ми можемо перебрати набір даних і обробити кожен запис відповідно до визначених завдань.
Така операція з обробки запису може бути також виконана на PHP-рівні, що значно зменшує обсяг переданих на PHP-рівень даних, так як ми можемо просто повернути оброблений зведений / статистичний результат назад (тим самим усуваючи процес обробки select - foreach на стороні клієнта) .
Оскільки курсор реалізується в збереженій процедурі, він має всі переваги (і недоліки), властиві ХП (контроль доступу, пре-компіляція, труднощі налагодження і т.д.)
Приклад практичного застосування
На моєму персональному сайті є сторінка з результатами ігор моєї улюбленої команди НБА: Лейкерс.
Структура таблиці цієї сторінки досить проста:
Рис 1. Структура таблиці результатів ігор Лейкерс
(Я використовую MySQL Workbench як GUI-інструменту для управління базою даних MySQL. Ви можете використовувати інший інструмент за своїм вибором).
Це, звичайно, не нездійсненне завдання, однак якщо умови ускладняться, і таблиця даних буде набагато більше, то це займе більше часу, та ймовірність помилки також збільшується.
Чи можемо ми зробити це через PHP? Так звичайно. Ми можемо отримати дані по іграх (конкретно, стовпець winlos) цього сезону і перебрати записи для обчислення тривалості поточної серії перемог / поразок поспіль.
Але щоб зробити це, нам доведеться охопити всі дані за цей рік і велика частина даних буде для нас марна (не дуже ймовірно, що якась команда матиме серію довше, ніж 20+ ігор поспіль в регулярному сезоні, який складається з 82 матчів ).
Проте, ми не знаємо напевно, скільки записів повинно бути вилучено в PHP для визначення серії. Так що нам не обійтися без марного вилучення непотрібних даних. І, нарешті, якщо поточна кількість виграшів / поразок поспіль це єдине, що ми хочемо дізнатися з цієї таблиці, навіщо нам тоді витягувати всі рядки даних?
Чи можемо ми зробити це іншим способом? Та це можливо. Наприклад, ми можемо створити резервну таблицю, спеціально призначену для зберігання поточного значення кількості перемог / поразок поспіль.
Додавання кожного нового запису буде автоматично оновлювати і цю таблицю. Але це занадто громіздкий і багате помилками спосіб.
Так як же можна зробити це краще?
Використання курсору в збереженій процедурі
Як ви могли здогадатися з назви нинішньої статті, кращою альтернативою (на мій погляд) для вирішення цієї проблеми є використання курсора в збереженій процедурі.
Давайте створимо в MySQL Workbench першу ХП:
У цій ХП у нас є один вхідний параметр і два вихідних. Це визначає підпис ХП.
У тілі ХП ми також оголосили кілька локальних змінних для серії результатів (виграшів або програшів, current_win), поточної серії і поточного статусу виграш / програш конкретного матчу:
Хоча це не видно наочно, але ми можемо собі уявити, що цей набір даних буде містити послідовність значень «L» і «W». На підставі даних, наведених на малюнку 2, вона повинна бути наступною: «LLLLLLWLL. »(6 значень« L », 1« W »і т.д.)
Для розрахунку кількості перемог / поразок поспіль ми починаємо з останнього (і першого в наведеному наборі даних) матчу. Коли курсор відкритий, він завжди починається з першого запису у відповідному наборі даних.
Після того, як перші дані завантажені, курсор переміщується до наступного запису. Таким чином, поведінка курсора схоже на чергу, перебирають набір даних по системі FIFO (First In First Out). Це саме те, що нам потрібно.
Після отримання поточного статусу перемога / поразка і кількості послідовних однакових елементів в наборі, ми продовжуємо обробляти по циклу (перебирати) решту набору даних. У кожній ітерації циклу курсор буде «переходити» на наступний запис, поки ми не розірвемо цикл або поки всі записі не будуть перебрані.
Якщо статус наступному записі такий же, як у поточного послідовного набору перемог / поразок, це означає, що серія триває, тоді ми збільшуємо кількість послідовних перемог (або поразок) ще на 1 і продовжуємо перебирати дані.
Якщо статус відрізняється, це означає, що серія перервана, і ми можемо зупинити цикл. Нарешті, ми закриваємо курсор і залишаємо вихідні дані. Після цього виводиться результат.
Далі ми можемо підвищити контроль доступу ХП, як це описано в моїй попередній статті.
Щоб перевірити роботу цієї ХП, ми можемо написати короткий PHP-скрипт:
Результат обробки повинен виглядати приблизно так, як показано на наступному малюнку:
Висновок набору даних з збереженої процедури
Кілька разів по ходу цієї статті розмова стосувалася того, як вивести набір даних з ХП, яка становить набір даних з результатів обробки декількох послідовних викликів інший ХП.
Користувач може захотіти отримати за допомогою раніше створеної нами ХП більше інформації, ніж просто безперервна серія перемог / поразок за рік; наприклад ми можемо сформувати таблицю, в якій будуть виводитися серії перемог / поразок за різні роки:
(В принципі, більш корисною інформацією буде тривалість найдовшою серії перемог або поразок в певному сезоні. Для вирішення цього завдання можна легко розширити описану ХП, тому я залишу цю задачу тим читачам, кому це буде цікаво. В рамках поточної статті ми продовжимо обробку поточної серії перемог / поразок).
Збережені процедури MySQL можуть повертати лише скалярні значення (ціле число, рядок, і т.д.), на відміну від операторів select. from. (Результати перетворюються в набір даних). Проблема в тому, що таблиця, в якій ми хочемо отримати результати, в існуючій структурі бази даних не існує, вона складається з результатів обробки збереженої процедури.
Для вирішення цієї проблеми нам потрібна тимчасова таблиця або, якщо це можливо і необхідно, резервна таблиця. Давайте подивимося, як ми можемо вирішити наявну завдання за допомогою тимчасової таблиці.
Спочатку ми створимо другу ХП, код якої показаний нижче:
Кілька істотних зауважень до наведеного вище коду:
- Ми визначаємо найбільш ранній і найпізніший року для вибірки з таблиці lakers;
- Ми створюємо тимчасову таблицю для зберігання вихідних даних з необхідною структурою (season, streak, win);
- У циклі ми спочатку виконуємо раніше створену ХП з необхідними параметрами (call streak (cur_year, @l, @s);), потім захоплюємо повертаються дані і вставляємо їх у тимчасову таблицю (insert into yearly_streak values (cur_year, @l, @s) ;);
- Нарешті, ми вибираємо з тимчасової таблиці і повертаємо набір даних, після чого робимо деяку настройку (DROP TEMPORARY TABLE IF EXISTS yearly_streak;).
Щоб отримати результати, ми створюємо ще один невеликий PHP-скрипт, код якого показаний нижче:
Виведені на екран результати будуть виглядати приблизно наступним чином:
Зверніть увагу, що наведений вище спосіб трохи відрізняється від виклику нашої першої ХП.
Перша ХП не повертає набір даних - тільки два параметри. В цьому випадку ми використовуємо PDO exec. а потім query для виведення даних; в другій ХП, ми виводимо через неї набір даних, тому ми використовуємо PDO query безпосередньо через виклик в ХП.
Вуаля! Ми зробили це!
висновок
На сайті MySQL ви можете знайти офіційну документацію по синтаксису процедури, що і різним операторам. З матеріалами по створенню збереженої процедури ви можете ознайомитися в цьому документі. а ще більше інформації для більш глибокого розуміння даної теми ви можете знайти тут.
Переклад статті «Cursors in MySQL Stored Procedures» був підготовлений дружною командою проекту Сайтобудування від А до Я.