mysql equal函数_mysql写过的自定义函数

delimiter $$

DROP FUNCTION IF EXISTS `getFloor`$$

CREATE FUNCTION getFloor(message VARCHAR(255)) RETURNS INT

BEGIN

DECLARE floor INT;

DECLARE b INT;

DECLARE e INT;

DECLARE s VARCHAR(255);

SET floor = 0;

SET b = LOCATE('[quote] 对', message);

IF (b > 0) THEN

SET s = SUBSTRING(message, 10);

SET s = SUBSTRING_INDEX(s,'楼',1);

SET floor = CAST(s AS SIGNED);

END IF;

RETURN(floor);

END

$$

delimiter $$

DROP FUNCTION IF EXISTS `getContent`$$

CREATE FUNCTION getContent(message VARCHAR(255)) RETURNS VARCHAR(255)

BEGIN

DECLARE r VARCHAR(255);

DECLARE b INT;

DECLARE s VARCHAR(255);

DECLARE pos INT;

SET r = '';

SET s = message;

cutQuote:LOOP

INSERT INTO `debug`(`msg`) VALUES(CONCAT('r=', r,',s=', s));

SET b = LOCATE('[quote] 对', s);

IF (b <= 0) THEN

SET r = CONCAT(r, s);

LEAVE cutQuote;

ELSEIF b = 1 THEN

SET pos = LOCATE('楼大人说：[/quote]', s) + 13;

SET s = SUBSTRING(s, pos);

ELSEIF b > 1 THEN

SET r = CONCAT(r, SUBSTRING(s, 1, b - 1));

SET s = SUBSTRING(s, b);

END IF;

END LOOP cutQuote;

RETURN(r);

END

$$

delimiter ;

delimiter $$

DROP FUNCTION IF EXISTS `getPath`$$

CREATE FUNCTION getPath(p_id INT) RETURNS VARCHAR(255)

BEGIN

DECLARE s VARCHAR(255);

DECLARE p INT;

DECLARE r INT;

SET s = '';

SET p = p_id;

getP:LOOP

IF (p > 0) THEN

SELECT `pid` INTO r FROM `wy_category_comment` WHERE `cid`=p;

SET p = r;

SET s = CONCAT('_', p, s);

ELSE

LEAVE getP;

END IF;

END LOOP getP;

SET s = SUBSTRING(s FROM 2);

RETURN(s);

END

$$

elimiter $$

DROP FUNCTION IF EXISTS `insertKeyWords`$$

CREATE FUNCTION insertKeyWords(str TEXT) RETURNS bit

BEGIN

DECLARE CRLF VARCHAR(10);

DECLARE pCRLF INT;

DECLARE s TEXT;

DECLARE sPre TEXT;

DECLARE sSuf TEXT;

DECLARE relItem VARCHAR(255);

DECLARE relword VARCHAR(255);

DECLARE equalPos INT;

SET CRLF = char(10);

SET pCRLF = 0;

SET s = str;

splitCRLF:LOOP

SET pCRLF = LOCATE(CRLF, s);

IF (pCRLF <= 0) THEN

SET equalPos = LOCATE('=',s);

SET relItem = SUBSTRING(s FROM 1 FOR equalPos-1);

SET relword = SUBSTRING(s FROM equalPos+1);

INSERT INTO `k`.`test`(`item`,`word`,`isreg`) VALUES(relItem,relword,0);

LEAVE splitCRLF;

ELSE

SET sPre = SUBSTRING(s FROM 1 FOR pCRLF-1);

SET sSuf = SUBSTRING(s FROM pCRLF+1);

SET equalPos = LOCATE('=',sPre);

SET relItem = SUBSTRING(sPre FROM 1 FOR equalPos-1);

SET relword = SUBSTRING(sPre FROM equalPos+1);

INSERT INTO `k`.`test`(`item`,`word`,`isreg`) VALUES(relItem,relword,0);

SET s = sSuf;

END IF;

END LOOP splitCRLF;

RETURN(0);

END

$$

delimiter ;

分享到：

2012-08-24 17:41

浏览 2407

分类:数据库

评论