doujieju0397
2016-11-18 12:57
浏览 405
已采纳

MySQL Cursor在select语句中带有变量

I've the following code in a stored procedure:



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;

Like this I'll get a syntax error in the line "SELECT calculationtable into v_calctable FROM wac.degrees where tablename = concat("cdb_" + arg_tablename);"

If I put it after the "Declare cursor" there's no syntax error, but I need the result to be used on de Cursor select statement.

How can I accomplish this?

Thanks.

图片转代码服务由CSDN问答提供 功能建议

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

  \  n 
 
  DECLARE完成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可计算到v_calctable FROM wac.degrees其中tablename = concat(“cdb_”+ arg_tablename); 
 
declare cur CURSOR for select degree,start_age,end_age,belt,sex from v_calctable; 
DECELARE  CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; 
   
 
 

这样我会收到语法错误 行“SELECT calculationtable into v_calctable FROM wac.degrees where tablename = concat(”cdb_“+ arg_tablename);”

如果我把它放在“Declare cursor”之后没有语法错误,但我需要 结果将用于de Cursor select语句。

如何实现这一目标?

谢谢。

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • duaijiao0648 2016-11-18 13:16
    已采纳
    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
    

    when you use inner block by another begin and end keyword then it have no syntask error . but this procedure give you correct result or not i am not clear

    已采纳该答案
    打赏 评论

相关推荐 更多相似问题