mysql存储过程数字转字符,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 ;