MYSQL-- custom function

Custom function

User-defined functions are a way to extend MYSQL, using the same as built-in functions

The 1 function can return any type of value, and it can also receive these types of parameters

2 the function body is composed of legal SQL statements.

3 the function body can be a simple SELECT or INSERT statement

4 if the function body is a compound structure, use the begin.. end statement.

5 composite structures can contain declarations, loops, and copy structures

6. Create a custom function without parameters

The first step is CREATE FUNCTION function name () FETURNS string length such as VARCHAR (30)

The second step is RETURN type (such as DATE) _ FORMAT (NOW ()), and the function content is like'% Y year% m month% d day% H point:% I minute:% s second')

7 create a custom function with parameters

The first step is CREATE FUNCTION function name such as F1 (parameter type and number of parameters such as num1 character length such as SMALLINT character type such as UNSIGNED, parameter type and number of parameters such as num2 character length such as SMALLINT character type such as UNSIGNED)

In the second step, RETUPNS returns a value type such as FLOAT (10cine 2) UNSIGNED.

Step 3: RETUPNS returns values such as (num1+num2) / 2

8 call function

SELECT function name (parameter name such as 101015)

Modify the default delimiter of the current MYSQL

New delimiters for DELIMITER

9 create a custom function with the body of the compound structure function (change the default delimiter of MYSQL to / / before creation, and modify it back after creation)

First step CREATE FUNCTION adduser (username such as username character length such as VARCHAR (20)

Step 2 RETURNS data types such as INT character types such as UNSIGNED

Step 3 BEGIN

Step 4 INSERT table name (user name) VALUES (user name)

Step 5 RETURNLAST_INSERT_ID ()

Step 6 END

Step 7 / /