原！mysql存储过程 批量导入数据

mysql需要导入某前缀例如12345为前缀的，后缀扩展2位 即00-99.

利用存储过程插入数据。

DROP PROCEDURE IF EXISTS insert_popsms_code;
DELIMITER //
CREATE PROCEDURE insert_popsms_code( in prefix VARCHAR(32) )
BEGIN

DECLARE i int default 0;
DECLARE channelId int; 
DECLARE codeNum VARCHAR(32); 
 
WHILE i<=99 do

 if(i<=9) then 
        set codeNum = CONCAT(prefix,'0',i);
 else 
   set codeNum = CONCAT(prefix,i);
 end if;

 if( prefix = '12345' ) then 
     set channelId=1;
 ELSEIF(prefix = '54321') then 
     set channelId=2;
 else 
   set channelId=0;
 end if;

INSERT INTO `open_codenumber` (`code`, `status`, `channelId`, `price`, `isLucky` , `isDelete`, `isPause`, `updateTime`, `createTime`, `codeRegion` , `sourceType`, `frozenDay`, `isPreemp`, `isPreempDel`, `preempDesc`) VALUES (codeNum, '1', channelId, '0.00', '2' , '0', '1', NOW(), NOW(), NULL , '1', '30', '0', '0', NULL);
set i = i + 1;
 
END WHILE;
 
END//
DELIMITER ;

 
#调用存储过程
call insert_popsms_code('12345');
call insert_popsms_code('54321');

 

-- 批量导入 语音码号 95096打头 可扩展4位 即0000-9999
DROP PROCEDURE IF EXISTS insert_voice_code;
DELIMITER //
CREATE PROCEDURE insert_voice_code(in prefix VARCHAR(32))
BEGIN

DECLARE i int default 0;
DECLARE codeNum VARCHAR(32); 
 
WHILE i<=9999 do

 if(i<=9) then 
        set codeNum = CONCAT(prefix,'000',i);
 elseif(10<= i and i <=99) then 
   set codeNum = CONCAT(prefix,'00',i);
 elseif(100<= i and i <=999) then 
   set codeNum = CONCAT(prefix,'0',i);
ELSEif(1000<=i and i <=9999) then 
 set codeNum = CONCAT(prefix,i);
 end if;

INSERT INTO `open_codenumber` (`code`, `status`, `channelId`, `price`, `isLucky` , `isDelete`, `isPause`, `updateTime`, `createTime`, `codeRegion` , `sourceType`, `frozenDay`, `isPreemp`, `isPreempDel`, `preempDesc`) VALUES (codeNum, '1', '3', '0.00', '2' , '0', '1', NOW(), NOW(), NULL , '1', '30', '0', '0', NULL);
set i = i + 1;
 
END WHILE;

END//
DELIMITER ;

-- 调用存储过程
call insert_voice_code('12345');