Динамический курсор в хранимой процедуре

Я хотел бы использовать LIMIT в курсоре. Курсор должен использоваться и обновляться несколько раз в цикле, каждый раз с разными параметрами LIMIT. Вот код:

DELIMITER $$
CREATE PROCEDURE `updateIt`() READS SQL DATA
BEGIN

declare done int(1) default 0;
declare counter int(10) default 0;
declare xabc int(10) default 0;

declare tab1Cursor cursor for select abc from tab1 limit 100000*counter, 100000;
declare continue handler for not found set done=1;

loopCounter: LOOP
    set done = 0;
    open tab1Cursor;
    igmLoop: loop
        fetch tab1Cursor into xabc;
        if done = 1 then leave igmLoop; end if;
        -- do something
    end loop igmLoop;
    close tab1Cursor;

    if (counter = 1039) 
        leave loopCounter;
    end if;
    set counter = counter + 1;

END LOOP loopCounter;
END $$
DELIMITER ;

Это, однако, не работает (я также пробовал его с помощью курсора в LOOP counterLoop). Может ли Mysql работать с динамическими курсорами?

Ответы

Ответ 1

Из Руководство по MySQL

курсор не может использоваться для динамического оператора, который готов и выполняется с помощью PREPARE и EXECUTE. Заявление для курсора проверяется на время создания курсора, поэтому оператор не может быть динамическим.

Однако есть 2 способа.

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

Аналогично, временная таблица может быть создана в инструкции подготовки, и курсор может выбрать из временной таблицы. Только текущий сеанс может видеть временную таблицу, поэтому проблема с несколькими пользователями устранена. Но это решение может оказать значительное влияние на производительность, поскольку временная таблица должна создаваться каждый раз, когда выполняется proc.

Итог: нам все еще нужны курсоры для динамического создания!

Вот пример использования представления для передачи имени таблицы и имени столбца в курсор из mysql forums

DELIMITER // 
DROP PROCEDURE IF EXISTS test_prepare// 

CREATE PROCEDURE test_prepare(IN tablename varchar(255), columnname varchar(50)) 
BEGIN 
DECLARE cursor_end CONDITION FOR SQLSTATE '02000'; 
DECLARE v_column_val VARCHAR(50); 
DECLARE done INT DEFAULT 0; 
DECLARE cur_table CURSOR FOR SELECT * FROM test_prepare_vw; 
DECLARE CONTINUE HANDLER FOR cursor_end SET done = 1; 

SET @query = CONCAT('CREATE VIEW test_prepare_vw as select ', columnname, ' from ', tablename); 
select @query; 
PREPARE stmt from @query; 
EXECUTE stmt; 
DEALLOCATE PREPARE stmt; 

OPEN cur_table; 
FETCH cur_table INTO v_column_val; 
WHILE done = 0 DO 
SELECT v_column_val; 
FETCH cur_table INTO v_column_val; 
END WHILE; 
CLOSE cur_table; 

DROP VIEW test_prepare_vw; 

END; 
// 

DELIMITER ;