mysql存储过程的动态视图
delimiter $

drop procedure if exists collectUserBoughts$

create procedure collectUserBoughts()

begin

declare dbIndex int(3) default 0;

C1:while dbIndex < 3 do

 
/*

创建视图
*/

select dbIndex;
//显示表号
drop view if exists view_temp;

set @sql=concat('create view view_temp as select userid,productid from userorderitem',dbIndex,' order by userid');

prepare stm from @sql;

xecute stm;

deallocate prepare stm;

select count(*) from view_temp;/*该语句结果表明无论dbIndex如何变化，视图里的数据都是来自*userorderitem0的数据*/
set dbIndex = dbIndex + 1;

end while C1;

/*

删除视图

*/

drop view view_temp;

end$

delimiter;
为什么无论dbIndex的值如何变化，视图的数据都是第一个表的数据？

------解决方案--------------------

deallocate prepare stm;

show create view view_temp;
select count(*) from view_temp;/*该语句结果表明无论dbIndex如何变化，视图里的数据都是来自*userorderitem0的数据*/
set dbIndex = dbIndex + 1;

end while C1;

------解决方案--------------------
修改后的代码是什么，检查变量中的值是否变化