论坛徽章:: 0

电梯直达

1楼 [收藏(0)] [报告]

发表于 2010-07-28 22:24 |只看该作者 |倒序浏览

本帖最后由 justin033 于 2010-07-28 22:30 编辑

如下这存储过程的body部分，问题：

如何把游标cur_1查出来的表名table_name值循环作为游标cur_2中的SQL语句from的表名

begin
declare l_str1 varchar(30) default ''; # table_name
declare l_str2 int default 0; # user_id
declare l_str3 int default 0; # count++
declare l_str4 int default 0; # router_id
declare db_table varchar(30) default '';
declare l_last_row_fetched int default 0;
declare cur_1 cursor for select distinct table_name from userlib.ul_user_router
where db_name='userlib' and table_name like 'ul_users_%';
declare cur_2 cursor for select user_id from db_table;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_row_fetched=1;
open cur_1;
cur1_loop: loop
fetch cur_1 into l_str1;
if l_last_row_fetched=1 then
leave cur1_loop;
else
set db_table=concat('userlib.',l_str1);
select router_id into l_str4 from userlib.ul_user_router where table_name=l_str1;
end if;
open cur_2;
set l_last_row_fetched=0;
cur2_loop: loop
fetch cur_2 into l_str2;
if l_last_row_fetched=1 then
leave cur2_loop;
else
set l_str3=l_str3+1;
if l_str3>110000 then
leave cur2_loop;
else
insert into userlib.ul_company(user_id,router_id,title) select l_str2 as user_id, l_str4 as router_id, title
from ztest.tmp_title where auto_id=floor(1 + rand() * 147);
end if;
end if;
end loop cur2_loop;
close cur_2;
set l_last_row_fetched=0;
end loop cur1_loop;
close cur_1;
end

复制代码

游标cur_1：
declare cur_1 cursor for select distinct table_name from userlib.ul_user_router
where db_name='userlib' and table_name like 'ul_users_%';

游标cur_2：
declare cur_2 cursor for select user_id from db_table;

set db_table=concat('userlib.',l_str1);

等候佳音。。。