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;
..
——解决方案——————–
转载请注明原文链接：help，mysql存储过程有关问题
