mysql存储过程的动态视图

[herengao]

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的值如何变化，视图的数据都是第一个表的数据？

[herengao]

| dbIndex | @sql
|
+---------+---------------------------------------------------------------------
-----------------+
| 0 | create view view_temp as select userid,productid from userorderitem0
order by userid |
+---------+---------------------------------------------------------------------
-----------------+
1 row in set (0.02 sec)

+----------+
| count(*) |
+----------+
| 630 |
+----------+
1 row in set (0.08 sec)

+---------+---------------------------------------------------------------------
-----------------+
| dbIndex | @sql
|
+---------+---------------------------------------------------------------------
-----------------+
| 1 | create view view_temp as select userid,productid from userorderitem1
order by userid |
+---------+---------------------------------------------------------------------
-----------------+
1 row in set (0.08 sec)

+----------+
| count(*) |
+----------+
| 630 |
创建视图的语句没有问题

[herengao]

有没有知道原因的，帮忙指导一下，先谢谢了！

[herengao]

| dbIndex | count(*) |
+---------+----------+
| 0 | 630 |
+---------+----------+
1 row in set (0.03 sec)

+---------+----------+
| dbIndex | count(*) |
+---------+----------+
| 1 | 630 |
+---------+----------+
1 row in set (0.08 sec)

+---------+----------+
| dbIndex | count(*) |
+---------+----------+
| 2 | 630 |
+---------+----------+
1 row in set (0.13 sec)

Query OK, 0 rows affected (0.14 sec)

mysql> select count(*) from userorderitem0;
+----------+
| count(*) |
+----------+
| 630 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from userorderitem1;
+----------+
| count(*) |
+----------+
| 690 |
+----------+

[ACMAIN_CHM]

deallocate prepare stm;

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

end while C1;

[ACMAIN_CHM]

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');
select dbIndex,@sql;
prepare stm from @sql;

xecute stm;

deallocate prepare stm;

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

end while C1;

/*



看一下你的语句。

[herengao]

delimiter $
drop procedure if exists collectUserBoughts$
create procedure collectUserBoughts()
begin
declare dbIndex int(3) default 0;

drop view if exists view_temp;
set @sql=concat('create or replace view view_temp as select userid,productid from userorderitem',dbIndex,' order by userid');
prepare stm from @sql;
execute stm;
deallocate prepare stm;
select dbIndex,count(*) from view_temp;
set dbIndex = dbIndex + 1;

drop view if exists view_temp;
set @sql=concat('create or replace view view_temp as select userid,productid from userorderitem',dbIndex,' order by userid');
prepare stm from @sql;
execute stm;
deallocate prepare stm;
select dbIndex,count(*) from view_temp;
set dbIndex = dbIndex + 1;

drop view if exists view_temp;
set @sql=concat('create or replace view view_temp as select userid,productid from userorderitem',dbIndex,' order by userid');
prepare stm from @sql;
execute stm;
deallocate prepare stm;
select dbIndex,count(*) from view_temp;
set dbIndex = dbIndex + 1;

drop view view_temp;
end$
delimiter ;
很奇怪，如果把循环里面的语句都放到外面，执行三次，则能得到我想要的结果，能告诉我为什么吗，榔头老大？

[herengao]

[Quote=引用 5 楼 acmain_chm 的回复:]
deallocate prepare stm;

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

end while C1;
[/Quote]
_client | collation_connection |
+-----------+--------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------+--------------
--------+----------------------+
| view_temp | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_temp` AS select `userorderitem0`.`U
serId` AS `userid`,`userorderitem0`.`ProductId` AS `productid` from `userorderitem0` order by `userorderitem0`.`UserId` | utf8
| utf8_general_ci |
+-----------+--------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------+--------------
创建的视图确实都是从userorderitem0的，可为什么，是预处理语句的错误还是其他的？

[wwwwb]

修改后的代码是什么，检查变量中的值是否变化

[herengao]

[Quote=引用 8 楼 wwwwb 的回复:]

修改后的代码是什么，检查变量中的值是否变化
[/Quote]
变量中@sql变了，dbIndex变了，但是show create view view_temp显示的值没变，这是在循环中动态改变视图的结果，如果放在循环外面就不会这样，视图也会跟着变！