Simple usage example of MySQL Custom function

This paper gives an example to illustrate the usage of MySQL custom function. Share with you for your reference, the details are as follows:

To start with a simple one, create a function to convert the datetime time in the format '2009-06-23 00 datetime' to the format 'June 23, 2009, 00:00:00':

DELIMITER $$DROP FUNCTION IF EXISTS `sp_ test`.`getdate` $$CREATE FUNCTION `sp_ test`.`getdate` (gdate datetime) RETURNS varchar (255mm) BEGINDECLARE x VARCHAR (255H) DEFAULT'; SET x = date_format (gdate,'%Y year% m / d% h% I% s second'); RETURN x = end $DELIMITER

Parsing:

The first sentence defines a closing identifier, because MySQL defaults to a semicolon as the Terminator of a SQL statement, while a semicolon is used inside the function body, so it conflicts with the default SQL Terminator, so you need to define another symbol as the SQL Terminator first

The second sentence is if the 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 the parameter, and RETURNS defines the type of value returned by this function.

The function body must be placed between BEGIN END

DECLARE is a variable that defines the body of a function. Here, a variable x is defined, which is empty by default, and then SET assigns a value to the 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 defined in the third sentence.

Call:

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

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

Branching structure

DELIMITER $$DROP FUNCTION IF EXISTS `sp_ test`.`cutString` $$CREATE FUNCTION `sp_ test`.`cutString` (s VARCHAR, n INT) RETURNS varchar (255) BEGINIF (ISNULL (s)) THEN RETURN'; ELSEIF CHAR_LENGTH (s) n THEN LEAVE myloop;END IF;END LOOP myloop;RETURN smitt end $$DELIMITER

Parsing: n * are generated.

Call:

SELECT morestar (5)

Return'*'

More readers who are interested in MySQL-related content can check out this site's special topics: "A Summary of MySQL Common functions", "A Collection of MySQL Log Operation skills", "MySQL transaction Operation skills Summary", "MySQL stored procedure skills Collection" and "MySQL Database Lock related skills Summary"

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.