MySQL IP字符串转数字存储过程

搜ijsio

　　DELIMITER $$USE `db_im`$$DROP PROCEDURE IF EXISTS `P_bi_parasIP`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `P_bi_parasIP`(IN Pi_ipstr VARCHAR(50),OUT Po_int1 INT,OUT Po_int2 INT,OUT Po_int3 INT,OUT Po_int4 INT,OUT Po_ret INT)BEGINDECLARE v_strtemp VARCHAR(50);DECLARE v_pos INT;DECLARE v_sub VARCHAR(10);DECLARE v_tail VARCHAR(50);DECLARE v_val INT;SET v_pos =0;SET Po_ret=1;SET v_strtemp =TRIM(Pi_ipstr);P:BEGIN/*192.168.1.1*/SET v_pos=INSTR(v_strtemp,'.');IF(v_pos>0 AND LENGTH(v_strtemp)>v_pos)THENSET v_sub=SUBSTRING(v_strtemp,1,v_pos-1);SET v_tail =SUBSTRING(v_strtemp,v_pos+1);SET v_val =v_sub+0;IF((v_sub REGEXP '^[0-9]*$') =0)THENSET Po_ret =-1;LEAVE P;ELSESET Po_int1=v_val;END IF; ELSESET Po_ret =-1;LEAVE P;END IF;/* 168.1.1*/SET v_pos=INSTR(v_tail,'.');IF(v_pos>0 AND LENGTH(v_tail)>v_pos)THENSET v_sub=SUBSTRING(v_tail,1,v_pos-1);SET v_tail =SUBSTRING(v_tail,v_pos+1);SET v_val =v_sub+0;  IF((v_sub REGEXP '^[0-9]*$') =0)THENSET Po_ret =-1;LEAVE P;ELSESET Po_int2=v_val;END IF; ELSESET Po_ret =-1;LEAVE P;END IF;/*1.1*/SET v_pos=INSTR(v_tail,'.');IF(v_pos>0 AND LENGTH(v_tail)>v_pos)THENSET v_sub=SUBSTRING(v_tail,1,v_pos-1);SET v_tail =SUBSTRING(v_tail,v_pos+1);SET v_val =v_sub+0;IF((v_sub REGEXP '^[0-9]*$') =0)THENSET Po_ret =-1;LEAVE P;ELSESET Po_int3=v_val;END IF; ELSESET Po_ret =-1;LEAVE P;END IF;SET v_val =v_tail+0;IF((v_tail REGEXP '^[0-9]*$') =0)THENSET Po_ret =-1;LEAVE P;ELSESET Po_int4=v_val;END IF;SET Po_ret=1; END P;END$$DELIMITER ;