help，mysql存储过程有关问题

help，mysql存储过程问题

SQL code

CREATE DEFINER=`root`@`localhost` PROCEDURE `test`(        IN id BIGINT(20),        IN newSize int(20),        IN newConut int(20),        IN newPrice FLOAT(8,4)    )BEGIN DECLARE Size INT; DECLARE Count INT; DECLARE Price FLOAT;set @q1=CONCAT('select size,count,price INTO Size,Count,Price from ',id,'_testschema.testtable where datetime BETWEEN "',CURDATE(),' 00:00:00" and "',CURDATE(),' 23:59:59"');prepare st1 from @q1;deallocate prepare st1;........end[code=SQL]

[/code]
存储过程如上，主要是先定义三个变量，然后将sql语句用concat生成，由于需要操作的数据是在另一个数据库中的，故该存储过程还接收数据库名字作为参数，现在这样写之后提示我Size变量未定义

[解决办法]
你没有SELECT 变量名，怎么会有结果
SET @q1=CONCAT('select size,count,price INTO @Size,@Count,@Price from ',id,'_testschema.testtable where datetime BETWEEN \'',CURDATE(),' 00:00:00 \' and \'',CURDATE(),' 23:59:59\'');
PREPARE st1 FROM @q1;
EXECUTE st1;
SET @q2=CONCAT('UPDATE ',accountid,'_account.T_UsedRecordForEveryDay SET space_size=',@Size,',machine_count=',@Conut,',day_price=',@Price ,'where datetime BETWEEN "',CURDATE(),' 00:00:00" and "',CURDATE(),' 23:59:59"');
PREPARE st2 FROM @q2;
EXECUTE st2 ;
DEALLOCATE PREPARE st2;
deallocate prepare st1;
[解决办法]
set @q1=CONCAT('select size,count,price INTO @Size,@Count,@Price from ',id,'_testschema.testtable where datetime BETWEEN "',CURDATE(),' 00:00:00" and "',CURDATE(),' 23:59:59"');
prepare st1 from @q1;
deallocate prepare st1;
set Size=@size;
..
[解决办法]

探讨

我对sql的存储过程了解比较浅，比较高级的功能就不太会用了，wwwwb能推荐下地方去学习下么

查看更多 下一篇