在存储过程中使用游标来循环MySQL行 [英] Using a cursor in a stored procedure to loop rows MySQL

问题描述

场景::我有一个存储过程，该存储过程基于2个输入从表中获取数据:日期和字符串(这是列名).从另一个过程调用第一个过程，该过程使用游标在表的行中循环，并将每一行传递到第一个过程的字符串(要检查的列名).我在第二个过程(直接调用该过程)中输入的是日期.

Scenario: I have a stored procedure that gets data from a table based on 2 inputs: a date and a string (which is a column name). The first procedure is called from another procedure which uses a cursor to loop through rows of a table and pass each row to the string of the first procedure (column names to be checked). My input for the second procedure (which is the one to be called directly) is the date.

问题:当我自己调用它时，我的第一个过程运行良好.我的第二个步骤是抛出一些我不知道如何解决的语法错误.

Issue: My first procedure is running fine when I call it on its own. My second procedure is throwing some syntax errors that I don't know how to fix.

糟糕:我已经在此处查看了有关此主题的其他答案 例如在存储过程的循环中使用游标和如何循环浏览一张桌子? (MySQL).实际上，我的第二个过程现在是我在SE

Obs: I already check some other answers here on this topic such as Using Cursor in a Loop of a stored procedure and How can I loop through all rows of a table? (MySQL) . Actually my second procedure is now a modified version of a query I found on SE https://dba.stackexchange.com/questions/138549/mysql-loop-through-a-table-running-a-stored-procedure-on-each-entry

问题:当前，代码在我声明为@colval的第5行抛出错误.

Issue: Currently, the code is throwing an error at line 5, in my declare of @colval.

代码:

-- Procedure for looping through rows of `wanted_columns` table:
delimiter $$
drop procedure if exists `data_check_loop` $$
create procedure `data_check_loop`(`wanted_date` date)
begin

set @dateval = `wanted_date`;
declare colval string default null;

-- boolean variable to indicate cursor is out of data
declare done tinyint default false;

-- declare a cursor to select the desired columns from the desired source table
declare cursor1
    cursor for
        select t1.c1
        from `wanted_columns` t1; 

-- catch exceptions
        declare continue handler for not found set done = true;

-- open the cursor
        open cursor1;
            my_loop: 
            loop
                fetch next from cursor1 into colval;
                if done then 
                    leave my_loop; 
                else  
                    call `set_column_stats`(colval, dateval);
                end if;
            end loop;
        close cursor1;

end $$
delimiter ;

问题:有关如何解决此问题的任何想法?

Question: Any ideas on how to fix this?

推荐答案

您的过程中有几个问题.首先，如手册所述:

You have a couple of problems in your procedure. Firstly, as described in the manual:

仅在BEGIN ... END复合语句内部允许使用DECLARE，并且必须在其开始处以及其他任何语句之前.

DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.

所以您需要移动

set @dateval = `wanted_date`;

在所有DECLARE之后(包括光标和继续处理程序).

after all the DECLAREs (including the cursor and continue handler).

第二，您对colval的声明不正确，string不是有效的数据类型，应将其替换为text:

Secondly, your declaration of colval is incorrect, string is not a valid data type and should be replaced with text:

declare colval text default null;

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