mysql写过的自定义函数
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) THENSET 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(' 对', 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:LOOPSET 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 ;
页:
[1]