今天学习下mysql中的自定义函数（UDF），有了它，可以写好一些方法或函数，然后进行调用，而且是在sql语句中可以进行调用。
例如：
复制代码 代码示例:
DROP FUNCTION CalculateAlinuxjishu/9952.html target=_blank class=infotextkey>mount
CREATE FUNCTION CalculateAmount(userid INT) RETURNS float(10,2)
BEGIN
DECLARE totalCredits FLOAT;
SELECT SUM(amount) INTO totalAmount FROM credit_user WHERE id =userid;
RETURN totalAmount;
END
注意：在UDF中，不要定义与数据表中重名的列。而在SQL中，则可以如SELECT CalculateAmount(1);这样调用。
例2，自定义函数
复制代码 代码示例:
//代码1
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
$$
//代码2
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 ;
//代码3
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
$$
//代码4
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 ;
