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变量未定义
prepare st1 from @q1;
deallocate prepare 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;
END
这是之后更新的代码,使用前面的变量更新,但老出现3个值均为空的现象
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;
prepare st1 from @q1;
deallocate prepare st1;
set Size=@size;
..