Simple Usage Example of MySQL Custom Function

This article illustrates the usage of MySQL custom functions. Share it for your reference, as follows:

Let's start with a simple, create a function to convert the datetime time in the format '2009-06-2300: 00:00' to the format 'June 23, 2009, 0: 00:0: 00sec':

DELIMITER $$
DROP FUNCTION IF EXISTS `sp_test`.`getdate`$$
CREATE FUNCTION `sp_test`.`getdate`(gdate datetime) RETURNS varchar(255)
BEGIN
DECLARE x VARCHAR(255) DEFAULT '';
SET x= date_format(gdate,'%Y Year %m Month %d Day %h Hour %i Points %s Seconds ');
RETURN x;
END $$
DELIMITER ;

Analysis:

The first sentence is to define an end identifier, because MySQL defaults to semicolons as the terminator of SQL statements, and semicolons are used in the function body, so it will conflict with the default SQL terminator, so it is necessary to define one other symbol as the terminator of SQL first;

The second sentence is that if this function already exists, delete it. sp_test is the name of the database, the function is associated with the database, and getdate is the name of the function;

The third sentence is to create a function, () is the name and type of parameters, RETURNS defines the type of return value of this function;

The function body must be placed between BEGIN and END;

DECLARE is the variable that defines the function body. Here, one variable x is defined, which is empty by default, and then SET assigns a value to x variable;

RETURN is the return value, where the variable x is returned, and the type of x must be the same as the return type 1 defined in the third sentence.

Call:

SELECT getdate('2009-06-23 00 : 00 : 00');

Return to 'June 23, 2009, 00: 00: 00: 00'

Branching structure

DELIMITER $$
DROP FUNCTION IF EXISTS `sp_test`.`cutString` $$
CREATE FUNCTION `sp_test`.`cutString`(s VARCHAR(255),n INT) RETURNS varchar(255)
BEGIN
IF(ISNULL(s)) THEN RETURN '';
ELSEIF CHAR_LENGTH(s)<n THEN RETURN s;
ELSEIF CHAR_LENGTH(S)=n THEN RETURN ' Equality ';
ELSE RETURN CONCAT(LEFT(s,n),'...');
END IF;
END $$
DELIMITER ;

Analysis:

This is a string interception function, with two parameters, one is the string s, one is the number n, the string s to retain the first n bits, if the number of strings less than n, then return the string s, if greater than n, then return the first n bits followed by....

Call:

SELECT cutString('abcdefghijklmnopqrstuvwxyz',5);

Returns' abced... '

Cyclic structure

DELIMITER $$
DROP FUNCTION IF EXISTS `sp_test`.`morestar`$$
CREATE FUNCTION `sp_test`.`morestar`(n INT) RETURNS text
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE s TEXT DEFAULT '';
myloop:LOOP
SET i=i+1;
SET s = CONCAT(s,'*');
IF i > n THEN LEAVE myloop;
END IF;
END LOOP myloop;
RETURN s;
END $$
DELIMITER ;

Parsing: Generate n *.

Call:

SELECT morestar(5);

Return '*****'

For more readers interested in MySQL related content, please check the topics on this site: "Summary of Common Functions of MySQL", "Encyclopedia of Log Operation Skills of MySQL", "Summary of Transaction Operation Skills of MySQL", "Encyclopedia of Stored Procedure Skills of MySQL" and "Summary of Database Lock Related Skills of MySQL"

I hope this article is helpful to everyone's MySQL database.