1、通过Repeat方式
DELIMITER $$
DROP PROCEDURE IF EXISTS insertxxx $$
CREATE PROCEDURE insertxxx()
BEGIN
declare indexSidOut int;
declare indexSidIn int;
DECLARE fidC int;
DECLARE nameC VARCHAR(200);
SET indexSidIn = 1;
set indexSidOut = 5486;
REPEAT
REPEAT
SET indexSidIn = indexSidIn + 1;
UNTIL indexSidIn > 31
END REPEAT;
SET indexSidOut = indexSidOut + 1;
UNTIL indexSidOut > 6421
END REPEAT;
END $$
DELIMITER ;
2、通过while方式实现
CREATE PROCEDURE insertSql()
BEGIN
declare indexSidOut int;
declare indexSidIn int;
DECLARE fidC int;
DECLARE nameC VARCHAR(200);
SET indexSidIn = 1;
set indexSidOut = 5486;
WHILE indexSidIn < 6422 DO
select fid , Word INTO fidC , nameC from words where Id =
indexSidOut;
WHILE indexSidIn < 32 DO
insert into words(Sid , Fid , Word)
VALUES(indexSidIn , fidC , nameC);
END WHILE;
END WHILE;
END;
3、通过游标实现
CREATE PROCEDURE INSERToTomMutil()
BEGIN
DECLARE _fid int;
DECLARE _word VARCHAR(200);
DECLARE _site_id VARCHAR(200);
DECLARE done INT DEFAULT FALSE;
DECLARE cur_words CURSOR FOR
BETWEEN 5000109 and 50900504;
DECLARE cur_site CURSOR FOR
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur_words;
read_loop_words: LOOP
FETCH cur_words INTO _fid , _word;
IF done THEN
LEAVE read_loop_words;
END IF;
OPEN cur_site;
read_loop_site: LOOP
FETCH cur_site INTO _site_id;
IF done THEN
LEAVE read_loop_site;
END IF;
INSERT INTO wordass(fid , word , sid )
values(_fid , _word , _site_id );
END LOOP;
SET done = 0;
CLOSE cur_site;
END LOOP;
CLOSE cur_words;
END