Интернет, компьютеры, софт и прочий Hi-Tech

Подписаться через RSS2Email.ru

Курсоры в хранимых процедурах MySQL. Часть 2

Тэйлор Рен (Taylor Ren), 05.02.2014

часть 1 | часть 2 | часть 3

Использование курсора в хранимой процедуре

Соответственно названию этой статье, мы рассмотрим лучший, на мой взгляд, способ решения проблемы: использование курсора в хранимой процедуре.

Давайте создадим нашу первую ХП в MySQL Workbench как показано ниже:

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `streak`(in cur_year int, out longeststreak int, out status char(1))
BEGIN
    declare current_win char(1);
    declare current_streak int;
    declare current_status char (1);

    declare cur cursor for select winlose from lakers where year=cur_year and winlose<>'' order by id desc;

    set current_streak=0;

    open cur;

    fetch cur into current_win;
    set current_streak = current_streak +1;

    start_loop: loop
        fetch cur into current_status;
            if current_status <> current_win then 
                leave start_loop;
            else
                set current_streak=current_streak+1;
            end if;

    end loop;

    close cur;

    select current_streak into longeststreak;
    select current_win into `status`;
END

Эта ХП имеет один параметр ввода и два параметра вывода. Они описаны в определении ХП.

В теле ХП мы также объявили несколько локальных переменных для хранения статуса цепочки (победы или поражения, current_win), ее длины и текущего статуса побед/поражений для конкретного матча.

В следующей строке мы объявляем курсор:

declare cur cursor for select winlose from lakers where year=cur_year and winlose<>'' order by id desc;

Мы объявили курсор с именем cur и привязали к нему набор данных, — статусы побед/поражений сыгранных матчей (здесь это столбец winlose, содержащий, соответственно «W» или «L») в указанном году и отсортированные по убыванию id (последние сыгранные матчи имеют наибольший ID).

Хотя это не указано явно, мы можем представить себе, что этот набор данных содержит последовательность «L» и «W». Соответственно данным, отображенным на рисунке 2, она должна быть такой: «LLLLLLWLL…» (6 поражений, 1 победа и т.д.)

Вычисление череды побед/поражений мы начинаем с последнего матча (первого в нашем наборе данных). Когда курсор открывается, он всегда принимает значение первой записи в ассоциированном с ним наборе данных.

После получения первых данных, курсор переместится на следующую запись. Таким образом, курсор ведет себя очень похоже на очередь, перемещаясь по набору данных по принципу FIFO (First In First Out, — первый уходит первым). Это именно то, что нам надо.

После получения текущего статуса «победа/поражение» и установления длины цепочки, мы продолжаем обходить набор данных в цикле. С каждой его итерацией курсор будет указывать на следующую запись пока мы не выйдем из цикла или пока не переберем все записи.

Если следующий статус «победа/поражение» совпадает с текущим статусом «победа/поражение», то наша череда продолжается, мы увеличиваем ее длину на 1 продолжаем итерации. Иначе наша череда заканчивается и мы выходим из цикла.

Наконец, мы закрываем курсор и освобождаем ресурсы. Затем мы возвращаем нужный результат.

Далее мы можем установить контроль доступа к этой ХП, как это было описано в моей предыдущей статье.

Для проверки вывода этой ХП напишем маленький PHP-скрипт:

<?php
$dbms = 'mysql';

$host = 'localhost';
$db = 'sitepoint';
$user = 'root';
$pass = 'your_pass_here';
$dsn = "$dbms:host=$host;dbname=$db";

$cn=new PDO($dsn, $user, $pass);

$cn->exec('call streak(2013, @longeststreak, @status)');
$res=$cn->query('select @longeststreak, @status')->fetchAll();

var_dump($res); // Смотрим, что мы получили

$win=$res[0]['@status']='L'?'проиграли':'выиграли';
$streak=$res[0]['@longeststreak'];

echo "Лейкерс $win $streak раз подряд.\n";

Этот код выведет что-то вроде следующего:

Лейкерс проиграли 6 раз подряд

(Этот вывод соответствует данным Лейкерс на 15 января 2014 г.)

часть 1 | часть 2 | часть 3

Автор: Тэйлор Рен, 05.02.2014
Перевод с английского: Дмитрий Скоробогатов, специально для xBB.uz, 21.03.2014
Оригинальный текст может быть найден по адресу http://www.sitepoint.com/cursors-mysql-stored-procedures/

Тэйлор Рен

Тэйлор — свободный разработчик веб- и десктопных приложений, проживающий в Сужоу в восточном Китае. Начинал со средств разработки Borland (C++Builder, Delphi), опубликовал книгу по InterBase. С 2003 является сертифицированным экспертом Borland. Затем переключился на веб-разработку в типичной конфигурации LAMP. Позднее начал работать с jQuery, Symfony, Bootstrap, Dart и т.д.


Предыдущие публикации:

Биржа долевых инвестиций SIMEX.

Последнее редактирование: 2014-03-24 06:42:25

Метки материала: процедуры, курсоры, хранимые процедуры, mysql, хранимки, процедуры mysql, cursor, sql, бд, хранимые процедуры mysql, db, хп, procedure

Оставьте, пожалуйста, свой комментарий к публикации

Представиться как     Антибот:
   

Просьба не постить мусор. Если вы хотите потестить xBB, воспользуйтесь кнопкой предварительного просмотра на панели инструментов xBBEditor-а.


© 2007-2017, Дмитрий Скоробогатов.
Разрешается воспроизводить, распространять и/или изменять материалы сайта
в соответствии с условиями GNU Free Documentation License,
версии 1.2 или любой более поздней версии, опубликованной FSF,
если только иное не указано в самих материалах.