
平臺論壇博客文庫

› 論壇 › 數(shù)據(jù)庫技術 › MySQL › 一個嵌套游標的引發(fā)事兒：MySQL存儲過程so power！�。�

一個嵌套游標的引發(fā)事兒：MySQL存儲過程so power�。�！ [復制鏈接]

justin033

小富即安

論壇徽章:: 0

電梯直達

1樓 [收藏(0)] [報告]

發(fā)表于 2010-07-28 22:24 |只看該作者 |倒序瀏覽

本帖最后由 justin033 于 2010-07-28 22:30 編輯

如下這存儲過程的body部分，問題：

如何把游標cur_1查出來的表名table_name值循環(huán)作為游標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);

等候佳音。。。