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能推荐下地方去学习下么
