Форум пользователей MySQL

FiMko · 01.01.2014 23:59:58

Всем привет! Подскажите, пожалуйста, что за странное поведение. Имеется хранимая процедура:

CREATE PROCEDURE `proc` ()

    DECLARE _word VARCHAR(70);

    DECLARE _no_more_rows BOOLEAN DEFAULT false;

    DECLARE _cur CURSOR FOR SELECT word FROM `table1`;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET _no_more_rows = TRUE;

    SELECT id INTO @var FROM `table1` WHERE word = 'non existing word';

    OPEN _cur;

the_loop:

    LOOP

    FETCH _cur INTO _word;

    IF _no_more_rows THEN

        CLOSE _cur;

        LEAVE the_loop;

        END IF;

        SELECT _word; -- печатаем данные таблицы `table1`

    END LOOP the_loop;

END

и таблица:

mysql> show create table `table1`;

+-------+-----------------------------

| Table | Create Table

+-------+-----------------------------

| table1 | CREATE TABLE `table1` (

  `id` int(11) NOT NULL,

  `word` varchar(100) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

+-------+-----------------------------

mysql> select * from `table1`

+----+-------+

| id | word  |

+----+-------+

|  1 | word1 |

|  2 | word2 |

+----+-------+

Несмотря на то, что таблица содержит две записи, вызов хранимой процедуры выдает пустой результат. Однако, достаточно закомментировать в хранимой процедуре строку:

-- SELECT id INTO @var FROM `table1` WHERE word = 'non existing word';

И вызов процедуры будет выдавать как положено построчно данные таблицы `table1`. Почему так?
Также будет правильно выдавать строки таблицы `table1`, если написать без INTO:

SELECT id FROM `table1` WHERE word = 'non existing word';

---

mysql> select version();

+------------+

| version()  |

+------------+

| 5.5.31-log |

+------------+

Отредактированно FiMko (02.01.2014 00:18:17)

vasya · 02.01.2014 01:12:07

Лучше один раз увидеть:

MariaDB [test]> select * from test;

+------+------+

| id   | name |

+------+------+

|    1 | a    |

|    2 | a    |

|    3 | b    |

|    4 | c    |

|    5 |  bb  |

+------+------+

5 rows in set (0.09 sec)

MariaDB [test]> select id into @var from test where name='zz';

Query OK, 0 rows affected, 1 warning (0.11 sec)

MariaDB [test]> show warnings;

+---------+------+-----------------------------------------------------+

| Level   | Code | Message                                             |

+---------+------+-----------------------------------------------------+

| Warning | 1329 | No data - zero rows fetched, selected, or processed |

+---------+------+-----------------------------------------------------+

1 row in set (0.00 sec)

MariaDB [test]> select id from test where name='zz';

Empty set (0.00 sec)

Для наглядности можете сделать в процедуре
SELECT id into @var FROM `test` WHERE name = 'non existing word';
select _no_more_rows;

FiMko · 02.01.2014 01:18:42

Да, действительно.

-- в хранимой процедуре

...

DECLARE CONTINUE HANDLER FOR NOT FOUND SET _no_more_rows = TRUE;

SELECT 1 INTO @dummy from `table1` limit 0;

SELECT _no_more_rows;

OPEN _cur;

...

mysql> call proc();

+---------------+

| _no_more_rows |

+---------------+

|             1 |

+---------------+

и, соответственно,

...

DECLARE CONTINUE HANDLER FOR NOT FOUND SET _no_more_rows = TRUE;

-- SELECT 1 INTO @dummy from `table` limit 0;

    SELECT _no_more_rows;

OPEN _cur;

...

mysql> call proc();

+---------------+

| _no_more_rows |

+---------------+

|             0 |

+---------------+

А почему?

Отредактированно FiMko (02.01.2014 01:27:48)

vasya · 02.01.2014 01:25:36

FiMko написал:
А почему?

Автор напрягался, слова придумывал, а его не читают
Посмотрите внимательно на пример в моем прошлом сообщении.

P.S. Кстати, в доке это описано

http://dev.mysql.com/doc/refman/5.1/en/declare-handler.html написал:
NOT FOUND is shorthand for the class of SQLSTATE values that begin with '02'. This is relevant within the context of cursors and is used to control what happens when a cursor reaches the end of a data set. If no more rows are available, a No Data condition occurs with SQLSTATE value '02000'. To detect this condition, you can set up a handler for it (or for a NOT FOUND condition). For an example, see Section 13.6.6, “Cursors”. This condition also occurs for SELECT ... INTO var_list statements that retrieve no rows.

FiMko · 02.01.2014 01:35:33

vasya написал:
This condition also occurs for SELECT ... INTO var_list statements that retrieve no rows.

Вот оно как, спасибо! Дополнительно можно заметить, что SELECT ... INTO var_list отработает аналогичным же образом, если даже SELECT 1 INTO @dummy from `table1` limit 0; находится в отдельной вызываемой хранимой процедуре.

Как от этого "защититься", если нужен INSERT INTO в переменную? Что-нибудь вроде: DECLARE CONTINUE HANDLER FOR SQLSTATE 'XXXXXX' SET @dummy = 1; ?

Отредактированно FiMko (02.01.2014 01:36:08)

vasya · 02.01.2014 01:40:39

Что-то смысл последнего сообщения от меня ускользнул. Не понял вопроса.

FiMko · 02.01.2014 01:46:06

vasya написал:
Что-то смысл последнего сообщения от меня ускользнул. Не понял вопроса.

Хочется как-то различить в результате чего произошел NOT FOUND SQLSTATE и избежать _no_more_rows = TRUE для случая INSERT INTO @variable. Хочется получать _no_more_rows = TRUE только при работе курсора. Сейчас я получаю NOT FOUND SQLSTATE до курсора для случая SELECT id INTO @var FROM `table1` WHERE word = 'non existing word';

Отредактированно FiMko (02.01.2014 01:49:58)

vasya · 02.01.2014 02:18:04

Имхо, единственный вариант - проверять в теле процедуры каждый SELECT ... INTO var_list

Напрмер,
SELECT count(*) into @col FROM `table1` WHERE word = 'non existing word';
if @col =0 ...

FiMko · 02.01.2014 15:22:02

Да, спасибо! Вообще весьма неудобная "фича", считай недоработка архитектуры движка, хоть и документированная (по моему мнению).

В принципе, чтобы лишних телодвижений не делать, можно просто взять за правило:

...

SET _no_more_rows = FALSE;

OPEN _cur;

...

Форум пользователей MySQL

#1 01.01.2014 23:59:58

Непонятное поведение курсора

#2 02.01.2014 01:12:07

Re: Непонятное поведение курсора

#3 02.01.2014 01:18:42

Re: Непонятное поведение курсора

#4 02.01.2014 01:25:36

Re: Непонятное поведение курсора

FiMko написал:

http://dev.mysql.com/doc/refman/5.1/en/declare-handler.html написал:

#5 02.01.2014 01:35:33

Re: Непонятное поведение курсора

vasya написал:

#6 02.01.2014 01:40:39

Re: Непонятное поведение курсора

#7 02.01.2014 01:46:06

Re: Непонятное поведение курсора

vasya написал:

#8 02.01.2014 02:18:04

Re: Непонятное поведение курсора

#9 02.01.2014 15:22:02

Re: Непонятное поведение курсора

Board footer