MySQL Learning notes-custom functions

MySQL Learning notes- custom functions

1. Introduction to Custom Functions

Custom functions: User-defined Functions (user-defined function,udf) are a way to extend MySQL, using the same functionality as built-in functions

Two prerequisites for a custom Function: (1) parameter (2) return value

Custom functions:

Creating a Custom function

CREATE FUNCTION Function_namereturns{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 parameters

Create a date time is a month, day, and minute seconds format

Mysql> CREATE FUNCTION F1 () RETURNS VARCHAR (), RETURN Date_format (now (), '%y year%m month%d day%h point:%i min:% s seconds '); Query OK, 0 rows Affected (0.00 sec) mysql> SELECT F1 (); +-------------------------------------+| F1 () |+-------------------------------------+| November 28, 2016 08 points: 34 minutes: 55 seconds |+-------------------------------------+

3. Creating a custom function with parameters

To create a function that calculates the average of 2 numbers

mysql> CREATE FUNCTION F2 (num1 SMALLINT unsigned,num2 SMALLINT UNSIGNED), RETURNS FLOAT (10,2) UNSIGNED RETURN (NUM1+NUM2)/2; Query OK, 0 rows Affected (0.00 sec) mysql> SELECT F2 (10,16); +-----------+|     F2 (10,16) |+-----------+| 13.00 |+-----------+

4. Create a custom function with a conforming structure function body

You 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 function adduser (Username VARCHAR (20))     -> returns int unsigned    -> begin     -> insert test (username)  values (username);     - > RETURN LAST_INSERT_ID ();    -> end    ->  //  mysql> delimiter ;mysql> select adduser (' Tom '); +---------------- +| adduser (' Tom ')  |+----------------+|               3 |+----------------+ mysql> select adduser (' Rool '); +---------- -------+| adduser (' Rool ')  |+-----------------+|                4 |+-----------------+ mysql> select *  from user;+----+----------+| id | username |+----+----------+|  1 | drive     | |   2 | cve      | |   3 | tom      | |   4 | rool     |+----+----------+

This article is from the "Shei" blog, make sure to keep this source http://kurolz.blog.51cto.com/11433546/1929135

MySQL Learning notes-custom functions