Mysql Procedure for populating ip address

i had to populate ip numbers(123456) when i have only ip adress in text format(127.0.0.1) between start and end number.

so i just made this scripts maybe you will need it anytime. i have turkish ip adress start and end ranges but i prefer to do not do it in runtime just because that request will made very frequently.

 

here is it.

 

DROP PROCEDURE IF EXISTS IPDBTOOLS.IP_CREATE;
CREATE PROCEDURE IPDBTOOLS.`IP_CREATE`()
BEGIN   
      DECLARE V_COUNTER INT(11) UNSIGNED;
      DECLARE BASLAMAINT INT(11) UNSIGNED;
      DECLARE BITISINT INT(11) UNSIGNED;
      DECLARE FINISHED INTEGER DEFAULT 0;
      DECLARE CUR_BLOK CURSOR FOR SELECT INET_ATON(START_FROM_STR),INET_ATON(END_STR) FROM  TURKISH_IP;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET FINISHED = 1;
       OPEN CUR_BLOK;
       GET_IP: LOOP
        FETCH CUR_BLOK INTO BASLAMAINT,BITISINT;
         SET V_COUNTER=BASLAMAINT;
         WHILE V_COUNTER < BITISINT DO
           INSERT INTO TURK_IP_ADRESLERI(ID,IP,COMP_ID,IPTEXT)VALUES(0,V_COUNTER,1,INET_NTOA(V_COUNTER));
           SET V_COUNTER=V_COUNTER+1;
         END WHILE;
        END LOOP GET_IP;
       CLOSE CUR_BLOK;
      END;
Mysql while loop inside of a cursor wich inserting data

Bir Cevap Yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir