MySQL IP字符串转数字存储过程
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 ) BEGIN DECLARE 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)THEN SET 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)THEN SET Po_ret =-1; LEAVE P; ELSE SET Po_int1=v_val; END IF; ELSE SET 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)THEN SET 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)THEN SET Po_ret =-1; LEAVE P; ELSE SET Po_int2=v_val; END IF; ELSE SET 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)THEN SET 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)THEN SET Po_ret =-1; LEAVE P; ELSE SET Po_int3=v_val; END IF; ELSE SET Po_ret =-1; LEAVE P; END IF; SET v_val =v_tail+0; IF((v_tail REGEXP ‘^[0-9]*$’) =0)THEN SET Po_ret =-1; LEAVE P; ELSE SET Po_int4=v_val; END IF; SET Po_ret=1; END P; END$$ DELIMITER ;
