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

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

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

Возврат набора данных хранимой процедурой

Немного подробнее рассмотрим то, как возвращать набор данных из ХП, если он создается как последовательность данных, получаемых в результате нескольких вызовов другой ХП.

Наша ранее созданная ХП возвращает полосу побед/поражений всего за один год. Но пользователь может захотеть узнать больше. Поэтому нам следует вывести табличку с полосами побед/поражений за все годы в следующей форме:

(Разумеется, лучше выводить самые длинные полосы побед и поражений в соответствующем сезоне. Чтобы этого добиться, мы можем легко расширить предыдущую ХП, но я оставлю ее реализацию для интересующихся. Для целей этой статьи нам достаточно получать последние цепочки побед/поражений.)

ХП в MySQL могут возвращать только скалярные результаты (числа, строки и т.п.). Они не могут вернуть результат выполнения запроса select ... from ..., который является набором данных. Кроме того, в нашей структуре базы нет табличных данных в той форме, которая нам нужна. Эти данные будут генерируются другой ХП.

Чтобы сделать это, воспользуемся временной таблицей или (если это можно и нужно) избыточной таблицей. Давайте посмотрим, как добиться нужного результата с помощью временной таблицы.

Сначала создадим вторую ХП, как показано ниже:

DELIMITER $$

CREATE DEFINER=`root`@`%` PROCEDURE `yearly_streak`()
begin
    declare cur_year, max_year, min_year int;

    select max(year), min(year) from lakers into max_year, min_year;

    DROP TEMPORARY TABLE IF EXISTS yearly_streak;
    CREATE TEMPORARY TABLE yearly_streak (season int, streak int, win char(1));

    set cur_year=max_year;

    year_loop: loop
        if cur_year<min_year then
            leave year_loop;
        end if;

        call streak(cur_year, @l, @s);
        insert into yearly_streak values (cur_year, @l, @s);

        set cur_year=cur_year-1;
    end loop;

    select * from yearly_streak;
    DROP TEMPORARY TABLE IF EXISTS yearly_streak;

END

Обратим внимание на несколько ключевых моментов:

1. Мы определяем максимальный и минимальный год, выбирая их из таблицы lakers;
2. Затем мы создаем временную таблицу для хранения вывод с соответствующей структурой (season, streak, win);
3. Далее в цикле мы сначала выполняем нашу предыдущую ХП с соответствующими параметрами:

   call streak(cur_year, @l, @s);

   а затем вставляем возвращенные ею данные во временную таблицу:

   insert into yearly_streak values (cur_year, @l, @s);

4. Наконец, мы выбираем и возвращаем набор данных из временной таблицы, а затем очищаем ее:

   DROP TEMPORARY TABLE IF EXISTS yearly_streak;

Для получения нужного результата мы создадим еще один небольшой скрипт PHP, как показано ниже:

<?php
... // Здесь получаем параметры коннекта к БД

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

$res=$cn->query('call yearly_streak')->fetchAll();

foreach ($res as $r)
{
    echo sprintf("В %d году длиннейшая череда W/L составила %d %s", $r['season'], $r['streak'], $r['win']);
}

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

Пожалуйста заметьте, что приведенный код немного отличается от вызова нашей первой ХП.

Первая ХП возвращает не набор данных, а лишь два параметра. В этом случае мы используем в PDO сначала exec, затем query для получения вывода. Наша вторая ХП сама возвращает набор данных, так что мы используем непосредственно query для выполнения этой ХП.

Вуаля! Мы сделали это!

Выводы

В этой статье мы углубились в хранимые процедуры MySQL, рассмотрели функциональность курсоров. Мы продемонстрировали, как получать скалярные данные с помощью параметров вывода (заданных как out var_name vartype в объявлении ХП), а также, как получать вычисляемые наборы данных посредством временных таблиц. Попутно были рассмотрены некоторые другие аспекты хранимых процедур.

Официальная документация по синтаксису хранимых процедур и других выражений может быть найдена на сайте MySQL. При создании хранимых процедур, пожалуйста, руководствуйтесь этим документом, а для лучшего понимания синтаксиса, прочтите также этот.

Не стесняйтесь комментировать и высказывать свои мысли!

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

Тэйлор Рен

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

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

Последнее редактирование: 2014-03-26 05:13:57

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

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

Представиться как

Антибот:

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