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 ;