doujieju0397 2016-11-18 12:57 采纳率: 0%
浏览 634
已采纳

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.

  • 写回答

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

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 flink cdc无法实时同步mysql数据
  • ¥100 有人会搭建GPT-J-6B框架吗?有偿
  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名