mysql存过游标 循环_在存储过程中使用游标来循环MySQL行

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.

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

Code:

-- 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:

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

So you need to move your

set @dateval = `wanted_date`;

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

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;