Рассмотрим понятие курсора на примере, который иллюстрирует его применение.
Создадим хранимую процедуру, выясняющую, у какого заказа была максимальная общая стоимость, и возвращающую значение orderid.
Процедура для поиска orderid с максимальной суммой заказа
Эта задача выполняется с помощью функции max, но с помощью данной процедуры можно проиллюстрировать принципы использования хранимых процедур:
Данный код содержит помимо курсоров еще управляющие структуры (условия и циклы) и обработчики объявлений.
Процедура начинается объявлением несколько локальных переменных, которые можно использовать только внутри нее. В переменных this_amount и this_id будут храниться значения полей amount и orderid текущей строки. В переменных l_id и l_amount будут храниться идентификаторы и суммы соответствующего заказа. Т.к. максимальная сумма заказа вычисляется с помощью сравнения каждого значения с текущим наибольшим значением, то эта переменная будет иметь начальное значение, равное 0.
Переменной done присвоено начальное значение, равное 0 (false). Эта переменная является флагом цикла. В случае, когда строки для просмотра закончатся, ее значение станет равным 1 (true).
Строку
называют обработчиком объявления (declare handler). В хранимых процедурах он сравним с исключением.
В следующей части обработчика объявления указываются условия вызова данного обработчика. В данном примере он будет вызван, когда будет достигнуто состояние sqlstate '02000'. Это условие означает, что обработчик будет вызван, когда ни одна строка не будет найдена.
Результирующий набор будет обрабатываться поочередно строка за строкой, а когда строки для обработки закончатся, процедурой вызовется данный обработчик. Для достижения такого же результата можно указать FOR NOT FOUND. Также можно воспользоваться параметрами SQLEXCEPTION и SQLWARNING.
Далее следует курсор(cursor), который ничем не отличается от массива. Он позволяет извлечь результирующий набор запроса (такой же возвращает функция musqli_query()) и построчно его обработать (как при помощи функции mysqli_fetch_row()).
Рассмотрим курсор:
У курсора есть имя – cl, которое показывает на то, что он будет содержать. На данном этапе запрос выполняться не будет.
Запрос выполняет строка
open cl;
Чтобы получить каждую строку данных необходимо выполнить оператор fetch. Это выполняется в цикле repeat. В данном примере цикл выглядит таким образом:
Обратим внимание, что условие (until done) не будет проверяться до завершения процедуры. В хранимых процедурах также можно использовать циклы while, которые имеют следующую форму:
Третий вид циклов – loop:
Этот цикл не имеет встроенных условий, а выход из него можно выполнить при помощи оператора leave.
С помощью строки:
загружается строка данных.
С помощью данной строки извлекается строка из запроса курсора. Два атрибута, которые получены запросом, сохраняем в двух указанных локальных переменных.
Далее при помощи двух операторов IF выполняется проверка, была ли получена строка, затем сравнивается текущая сумма цикла с максимальной сохраненной суммой заказа:
Обратим внимание, что значение переменных устанавливает оператор set.
Кроме структуры if ... then в хранимых процедурах поддерживается также конструкция if ... then ... else, синтаксис которой следующий:
Возможно использование оператора case, синтаксис которого следующий:
Вернемся к примеру. После прерывания цикла нужно выполнить небольшую очистку:
Оператором close закрывается курсор.
В заключение устанавливается значение параметра OUT, которое становится равным вычисленному значению. Параметр не может использоваться как временная переменная, а лишь для хранения конечного значения.
После создания процедуры ее можно вызвать обычным образом:
