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,'',i);

 else

   set codeNum = CONCAT(prefix,i);

 end if;

 if( prefix = '' ) then

     set channelId=1;

 ELSEIF(prefix = '') 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, '', channelId, '0.00', '' , '', '', NOW(), NOW(), NULL , '', '', '', '', NULL);

set i = i + 1;

END WHILE;

END//
DELIMITER ;

#调用存储过程

call insert_popsms_code('');

call insert_popsms_code('');

-- 批量导入 语音码号 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,'',i);

 elseif(10<= i and i <=99) then

   set codeNum = CONCAT(prefix,'',i);

 elseif(100<= i and i <=999) then

   set codeNum = CONCAT(prefix,'',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, '', '', '0.00', '' , '', '', NOW(), NOW(), NULL , '', '', '', '', NULL);

set i = i + 1;

END WHILE;

END//
DELIMITER ;

-- 调用存储过程

call insert_voice_code('');