mysql游标循环 插入数据_MYSQL游标嵌套循环插入数据

OS version :WIN xp sp2

MYSQL version:mysql 5.1.33-win32

Store procedure:using cursor and while Cyc. to get test data

DELIMITER $$

DROP PROCEDURE IF EXISTS `ad_analyse_temp`.`pp_reg_list_test_data`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `pp_reg_list_test_data`()

begin

declare l_pp int;

declare l_ad_code varchar(30);

declare stop int default 0;

declare l_cur cursor for  select distinct ad_code from ad_info;

---------------------curosr

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' set stop=1;

---------------------exception

open l_cur;

fetch l_cur into l_ad_code;

while (stop<>1) do

---------------------judge cursor

set l_pp=10000;

while (l_pp>=10000 and l_pp<=20000) do

insert into passport_reg_list

select null,l_ad_code,'a1','aa',l_pp,now(),'201.50.14.22';

set l_pp=l_pp+1;

end while;

fetch l_cur into l_ad_code;

end while;

close l_cur;

--------------code

end$$

DELIMITER ;