mysql写过的自定义函数

ling凌yue月

 
 
delimiter $$DROP FUNCTION IF EXISTS `getFloor`$$CREATE FUNCTION getFloor(message VARCHAR(255)) RETURNS INTBEGIN DECLARE floor INT; DECLARE b INT; DECLARE e INT; DECLARE s VARCHAR(255); SET floor = 0; SET b = LOCATE('

对', 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('楼大人说：

', 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 bitBEGIN 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 ;