MySQL Learning notes-custom functions

MySQL Learning notes- custom Functions1. Introduction to Custom FunctionsCustom functions: User-defined Functions (user-defined function,udf) are a way to extend MySQL, using the same functionality as built-in functionstwo prerequisites for a custom Function: (1) parameter (2) return valueCustom functions:Creating a Custom Function

CREATE FUNCTION function_name RETURNS {STRING| INTEGER | REAL | DECIMAL }routine_body

about the function body:1. The function body can be constituted by a valid SQL statement;2. The function body can be a simple select or INSERT statement;3. Function Body If it is a composite structure, use begin ... End statement;4. A composite structure can contain declarations, loops, and control structures. 2. Creating a custom function with no parametersCreate a date time is a month, day, and minute seconds format

Mysql> CREATE FUNCTIONF1 ()RETURNS VARCHAR( -)                      - RETURNDate_format (now (),'%y%m Month%d day%h point:%i min:%s sec'); Query OK,0Rows Affected (0.00sec) MySQL> SELECTF1 ();+-------------------------------------+|F1 ()|+-------------------------------------+|November 28, 2016 08 points: 34 minutes: 55 seconds|+-------------------------------------+

3. Creating a custom function with parametersto create a function that calculates the average of 2 numbers

Mysql> CREATE FUNCTIONF2 (NUM1SMALLINTUnsigned,num2SMALLINTUNSIGNED) - RETURNS FLOAT(Ten,2) UNSIGNED - RETURN(NUM1+NUM2)/2; Query OK,0Rows Affected (0.00sec) MySQL> SELECTF2 (Ten, -);+-----------+|F2 (Ten, -)|+-----------+|     13.00 |+-----------+

4. Create a custom function with a conforming structure function bodyYou can modify the default terminator in MySQL with the "DELIMITER delimiter"To delete a function:

DROP FUNCTION [IF EXISTS] function_name

if a custom function exists that conforms to the structure, multiple statements, the body of the function to be included in the BEGIN ... End, at the same time, the default terminator needs to be passed through the delimiter; Modified to other symbols, such as://$$, lest the function due to the end of the statement; The number of interrupts caused

Mysql>DELIMITER//MySQL> CREATE FUNCTIONAddUser (usernameVARCHAR( -))     - RETURNS INTUNSIGNED - BEGIN     - INSERTTest (username)VALUES(username);  - RETURNlast_insert_id ();  - END     - //MySQL>DELIMITER; MySQL> SELECTAddUser'Tom');+----------------+|AddUser'Tom')|+----------------+|              3 |+----------------+MySQL> SELECTAddUser'Rool');+-----------------+|AddUser'Rool')|+-----------------+|               4 |+-----------------+MySQL> SELECT *  from User;+----+----------+|Id|Username|+----+----------+|  1 |Drive||  2 |Cve||  3 |Tom||  4 |Rool|+----+----------+

MySQL Learning notes-custom functions