php - MySQL游标在select语句中带有变量

Question

我在存储过程中有以下代码:

<p></p>

<pre><code>DECLARE done INT DEFAULT FALSE;
declare v_degree int(11);
declare v_start_age smallint(6);
declare v_end_age smallint(6);
declare v_gender varchar(20);
declare v_calctable varchar(200);

SELECT calculationtable into v_calctable FROM wac.degrees where tablename = concat("cdb_" + arg_tablename);

declare cur CURSOR for select degree, start_age, end_age, belt, gender from v_calctable;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
</code></pre>

<p></p>

像这样，我会在“SELECT CalculationTable into v_calctable FROM wac. Degrees where tablename = concat("cdb_"+ arg_tablename);”行中出现语法错误

如果我把它放在“声明游标”之后，就没有语法错误，但我需要在 de Cursor select 语句上使用结果。

我怎样才能做到这一点？

谢谢。

Answer 1

CREATE DEFINER=`root`@`localhost` PROCEDURE `my_procedure`()
BEGIN
DECLARE done INT DEFAULT FALSE;
declare v_degree int(11);
declare v_start_age smallint(6);
declare v_end_age smallint(6);
declare v_gender varchar(20);
declare v_calctable varchar(200);

SELECT calculationtable into v_calctable FROM wac.degrees where tablename = concat("cdb_" + arg_tablename);
begin                                                                   -- write here begin keyword
declare cur CURSOR for select degree, start_age, end_age, belt, gender from v_calctable;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
end;                                                                    -- end here inner block

END

当您通过另一个begin和end关键字使用内部 block 时那么它就没有语法错误。但这个过程是否会给你正确的结果我不清楚