СУБД/лекция 5 семестр 2
Содержание |
Курсоры
Курсоры используются для прохождения по набору строк, возвращенному запросом, а также обработки каждой строки.
MySQL поддерживает курсоры в хранимых процедурах. Вот краткий синтаксис создания и использования курсора.
DECLARE cursor-name CURSOR FOR SELECT ...; /*Объявление курсора и его заполнение */ DECLARE CONTINUE HANDLER FOR NOT FOUND /*Что делать, когда больше нет записей*/ OPEN cursor-name; /*Открыть курсор*/ FETCH cursor-name INTO variable [, variable]; /*Назначить значение переменной, равной текущему значению столбца*/ CLOSE cursor-name; /*Закрыть курсор*/
В этом примере мы проведем кое-какие простые операции с использованием курсора:
DELIMITER // CREATE PROCEDURE `proc_CURSOR` (OUT param1 INT) BEGIN DECLARE a, b, c INT; DECLARE cur1 CURSOR FOR SELECT col1 FROM table1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur1; SET b = 0; SET c = 0; WHILE b = 0 DO FETCH cur1 INTO a; IF b = 0 THEN SET c = c + a; END IF; END WHILE; CLOSE cur1; SET param1 = c; END //
У курсоров есть три свойства, которые вам необходимо понять, чтобы избежать получения неожиданных результатов:
- Не чувствительный: открывшийся однажды курсор не будет отображать изменения в таблице, происшедшие позже. В действительности, MySQL не гарантирует то, что курсор обновится, так что не надейтесь на это.
- Доступен только для чтения: курсоры нельзя изменять.
- Без перемотки: курсор способен проходить только в одном направлении - вперед, вы не сможете пропускать строки, не выбирая их.
Курсоры 1
Курсоры прекрасно поддерживаются в хранимых процедурах, функциях и триггерах. Синтаксис такой же, как и во внедренном SQL. Курсоры пока только для чтения, однонаправленные (т.е по набору можно ходить только вперед без возможности вернуться) и невосприимчивы. Невосприимчивость означает, что сервер может создавать копию результатирующей таблицы, а может и не создавать, формируя ее на лету .
Курсоры должны быть объявлены до их использования. Переменные с условиями объявляются прежде курсоров. Обработчики объявляются строго после объявления курсоров.
CREATE PROCEDURE curdemo() BEGIN DECLARE done INT DEFAULT 0; DECLARE a CHAR(16); DECLARE b,c INT; DECLARE cur1 CURSOR FOR SELECT id,DATA FROM test.t1; DECLARE cur2 CURSOR FOR SELECT i FROM test.t2; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN cur1; OPEN cur2; REPEAT FETCH cur1 INTO a, b; FETCH cur2 INTO c; IF NOT done THEN IF b < c THEN INSERT INTO test.t3 VALUES (a,b); ELSE INSERT INTO test.t3 VALUES (a,c); END IF; END IF; UNTIL done END REPEAT; CLOSE cur1; CLOSE cur2; END
Объявление курсоров
DECLARE cursor_name CURSOR FOR select_statement
Это выражение объявляет курсор c именем cursor_name. select_statement указывает на конструкцию типа SELECT ... FROM ... Можно объявить много курсоров в подпрограмме, но каждый курсор в данном блоке должен иметь уникальное имя. Выражение SELECT не должно содержать указание INTO.
Открывание курсоров
OPEN cursor_name
FETCH cursor_name INTO var_name [, var_name] ...
Это выражение выбирает следующую строку (если строка существует), используя указанный открытый курсор, и продвигает указатель курсора. Если более строк не доступно, происходит изменение значения переменной SQLSTATE в 02000. Для отлова этого события вы должны установить обработчик: HANDLER FOR SQLSTATE '02000'
Закрытие курсора
CLOSE cursor_name
Закрывает курсор cursor_name. Если явно не указано, то курсор закрывается автоматически при закрытии соответствующего блока подпрограммы. Как использовать курсоры
Применение курсора в процедурах осуществляется путем последовательного выполнения следующих шагов:
- При помощи оператора DECLARE объявляется курсор для отдельного оператора SELECT или для отдельной процедуры.
- Оператором OPEN производится открытие курсора.
- Используя оператор FETCH, осуществляется установление указателя на требуемую запись курсора. При этом значения полей текущей записи присваиваются переменным, указываемым в операторе FETCH. Обычно это конструкция помещается в итеративный элемент (проще говоря цикл), который прерывается по некоторому условию. См. пример выше.
- В процессе перемещения указателя текущей записи курсора при выходе указателя за пределы курсора устанавливается значение SQLSTATE = 02000.
- После того как курсор становится ненужным, он закрывается оператором CLOSE.
Примеры курсоров
Приведу еще один пример курсора. Курсор предназначен для выборки данных (идентификаторов записей) в строку с разделителем ввиде запятой по переданным параметрам. Курсор находится внутри хранимой функции get_pedplan(). В нее передается три параметра: lip-номер лаборатории, ti-номер пары и dt - дата проведения занятия.
CREATE DEFINER = 'for_spammers'@'zoonman.ru' FUNCTION `get_pedplan`(lip INTEGER(11), ti INTEGER(11), dt DATE) RETURNS CHAR(64) CHARSET latin1 DETERMINISTIC CONTAINS SQL SQL SECURITY INVOKER COMMENT 'Функция возвращает список id из таблицы raspisanie' BEGIN DECLARE done INT DEFAULT 0; DECLARE a INT; DECLARE retv CHAR(64); DECLARE flg INT; DECLARE cur1 CURSOR FOR SELECT id FROM raspisanie WHERE timeintv=ti AND rdate=dt AND labip=lip ; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN cur1; SET retv:=''; SET flg:=0; REPEAT FETCH cur1 INTO a; IF NOT done THEN IF flg!=0 THEN SET retv:= CONCAT(retv,',' , a); ELSE SET retv:=a; SET flg:=1; END IF; END IF; UNTIL done END REPEAT; CLOSE cur1; RETURN retv; END;
Курсор определен на строке 14. Открыт на 16 строчке. С 19-й начат проход по выборке полученной курсором. На каждом шаге цикла происходит считывание записи (21-я). Затем, если не достигнут конец выборки (22-я), выполняется проверка флага на первую запись (23-я). Если запись первая, то присваиваем retv текущее значение выборки (26-я) и устанавливаем флаг (27-я), иначе объединяем значение retv с текущим значением выборки (строка 24). После прохода по курсору закрываем его (31-я) и возвращаем значение (32-я строка).
