mysql isreg_mysql中自定义函数的实例代码

今天学习下mysql中的自定义函数(UDF)，有了它，可以写好一些方法或函数，然后进行调用，而且是在SQL语句中可以进行调用。

例如：

复制代码 代码示例:

DROP FUNCTION CalculateAmount

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 ;