MySQL и особенность поведения в хранимых процедурах и функциях

Хранимые процедуры (функции) появились в СУБД MySQL в пятой версии, приблизив данную систему управления базами данных ещё ближе к полноценному ЯП. О целесообразности данного шага можно поспорить, но очевидно одно: крупные проекты стало разрабатывать удобнее. В коммерческих системах, например, Oracle, данная функциональность была введена гораздо раньше.

Язык, активно продвигающийся в прошлом веке как «программирование для непрограммистов», предлагал всё больше возможностей. В итоге, сейчас задействовать SQL «на полную» простой пользователь вряд ли сможет.

Условно, языки делятся на поколения. К первому поколению относят программирование в машинных кодах, ко второму — языки ассемблера, к третьему — средне- и высокоуровневые: Фортран, Паскаль, Си и т. п. К четвёртому поколению относится, в частности, язык SQL. Со временем он перерос представление «языка запросов». В результате, полноценное использование SQL сейчас доступно лишь программистам; в язык добавлили множество новых особенностей, включая работу с переменными, триггеры, хранимые процедуры/функции и многое другое.

После столь долгого и внушительного вступления можно вернуться к проблеме. Итак, пусть мы имеем таблицу — treecat, в которой каждый элемент может принадлежать какому-то другому. Яркий пример — дерево каталогов. Допустим, есть такая таблица:

Здесь id — уникальный числовой идентификатор, name — имя категории, parent — идентификатор «хозяина». В данном случае, есть Главная страница, к ней прикреплены ещё две — Новости и Статьи. Категория Новости партнёров содержит битые идентификатор родителя — id=12 в таблице не существует.

Вложенность никак не лимитируется, поэтому будет весьма удобно получить id главного родителя, у которого id=0.

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

DECLARE p INT DEFAULT did;
WHILE p > 0 DO
  SELECT `id`, `parent` INTO did, p FROM `treecat` WHERE `id` = p;
END WHILE;
RETURN did;

Построчное объяснение кода:

1. объявляем переменную, в которой будет хранится идентификатор «родителя», текущее значение — запрашиваемый id;
2. цикл, пока не найдём «старшего»;
3. выбираем очередную строку из таблицы и помещаем значения полей id и parent в переменные did и p соответственно;
4. повторяем цикл;
5. цикл закончен, возвращаем найденное значение. Если это 0 (ноль), ничего не нашлось.

Вроде бы всё просто. При вызове функции со значением 1 получим один — это уже старший узел иерархии. Значения 2 и 3 тоже вернут 1. Несуществующие идентификаторы, например, 12, тоже возвращают 0.

Самое интересно происходит, когда запрашивается несуществующий идентификатор, либо одно из значений parent указывает на несуществующего предка. Заместо логично поведения — присвоить did и p хотя бы NULL, MySQL не делает ничего. В том смысле, что не получив результат выборки, оставляет параметры с теми значениями, которые есть.

Пример для наглядности. Вызываем функцию со значением 4. Число присваивается входной параметру did и внутреннему p. Далее выполняется условие p > 0 и происходит переход к третьей строке, в которой параметр p получает значение 12. Строка 4 перезапускает цикл, но теперь ищется запись с идентификатором id = 12, которого нет! Как итог, MySQL использует имеющиеся значения — did = 4 и p = 12. Цикл снова перезапускается, и проверка оказывается опять успешной. И ещё раз. Получили вечный цикл.

Самое простое, что придумалось сразу, хранить предыдущее значение параметра p. Если оно совпадёт с текущим, значит, где-то ошибка, и можно вернуть ноль (или что-то другое). Итоговый код:

DECLARE p INT DEFAULT did;
DECLARE prev_p INT DEFAULT 0;
WHILE p > 0 DO
  SELECT `id`, `parent` INTO did, p FROM `treecat` WHERE `id` = p;
  IF (prev_p = p) THEN RETURN 0; END IF;
  SET prev_p = p;
  END WHILE;
RETURN did;

Второй строкой добавили новый параметр, в строке 5 производим сравнение и возвращаем ноль в случае форс-мажора, а в строке 6 обновляем наш параметр.

Теоретически, возможен вариант циклических ссылок, например, запись 7 ссылается на 6, та — на 5, которая содержит значение parent = 7. Программно отслеживать подобное сложно, но это и не нужно — будем подсчитывать число циклов, и при превышении какого-то заведомо большого значения возвращать ноль (или что-то ещё, если реализуете соответствующую логику).

DECLARE p INT DEFAULT did;
DECLARE prev_p INT DEFAULT 0;
DECLARE cnt INT DEFAULT 0;
WHILE p > 0 DO
  SELECT `id`, `parent` INTO did, p FROM `treecat` WHERE `id` = p;
  IF (prev_p = p) THEN RETURN 0; END IF;
  SET prev_p = p;
  SET cnt = cnt + 1;
  IF (cnt > 10) THEN RETURN 0; END IF;
  END WHILE;
RETURN did;

На этом всё.

Добра!

Поддержка сайта

С удовольствием создаю годный контент. Буду очень признателен, если вы поддержите мои усилия:

днём интернета
шоколадкой для работы мозга
коробочкой ароматного чая для бодрости
продлением хостинга на +1 месяц

Автор публикации

2 070

не в сети 3 недели

x64 (aka andi)

Комментарии: 2893Публикации: 405Регистрация: 02-04-2009

(1 голосов, в среднем: 5,00 из 5)

Загрузка...