存储过程中的动态游标 [英] Dynamic cursor in stored procedure

问题描述

我想在游标中使用LIMIT。光标应在循环中使用和更新多次，每次使用不同的参数LIMIT。这里有一些代码：

I would like to use LIMIT in a cursor. The cursor should be used and updated several times within a loop, each time with different parameters of LIMIT. Here some code:

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是否可以处理动态游标？

This, however, does not work (I also tried it with the cursor in the LOOP counterLoop). Can Mysql deal with dynamic cursors?

推荐答案

从 MySQL手册

游标不能用于准备的动态语句，
可以用PREPARE和EXECUTE执行。

a cursor cannot be used for a dynamic statement that is prepared and executed with PREPARE and EXECUTE. The statement for a cursor is checked at cursor creation time, so the statement cannot be dynamic.

但是，在游标创建时检查游标的语句是
，所以语句不能是动态的。方法。

However there are 2 ways.

第一种方法适用于一次只有一个用户运行过程的情况。 prepare语句可用于使用动态SQL创建视图，并且游标可以从此静态命名视图中进行选择。几乎没有性能影响。不幸的是，这些视图对其他用户也是可见的（没有像临时视图这样的东西），因此这不会为多个用户工作。

The first is for cases where absolutely only one user at a time will be running the procedure. A prepare statement can be used to create a view with the dynamic SQL and the cursor can select from this statically-named view. There's almost no performance impact. Unfortunately, these views are also visible to other users (there's no such thing as a temporary view), so this won't work for multiple users.

类似地，可以在prepare语句中创建一个临时表，游标可以从临时表中进行选择。只有当前会话可以看到临时表，所以多个用户问题得到解决。但是此解决方案可能会产生重大的性能影响，因为每次运行proc时都必须创建一个临时表。

Analogously, a temporary table can be created in the prepare statement and the cursor can select from the temporary table. Only the current session can see a temporary table, so the multiple user issue is resolved. But this solution can have significant performance impact since a temp table has to be created each time the proc runs.

底线：我们仍然需要光标才能动态创建！

Bottom line: We still need cursors to be able to be created dynamically!

下面是一个使用视图将表名和列名传递到 mysql forums

Here's an example of using a view to pass the table name and column name into a cursor from the 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 ;

这篇关于存储过程中的动态游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！