Detailed explanation of the definition and usage of MySQL storage function of user defined function

Storage function

What is a storage function: Encapsulate a segment of sql code, complete a specific function, return results.

Syntax for storing functions:

create function  Function ([ Function parameter [, … .]]) Returns  Return type 

 　　 Begin

  　　  　　  If(

   　　  　　   　　  Return ( Data returned )

  　　  　　  Else 

   　　  　　   　　  Return ( Data returned )

  　　  　　  end if;

 　　 end;

For example: create function count_news (hits int) returns int

Different from the stored procedure return parameter, the stored function does not directly declare which variable is the return parameter when defining, but only uses returns to declare the data type to which the return parameter belongs, and the return parameter is expressed in the form of the data variable to be returned by return in the function body. It is important to note that:
The stored function only supports input parameters, and there is no IN or INOUT before the input parameters.

Restrictions in storage functions

The flow control (Flow-of-control) statements (IF, CASE, WHILE, LOOP, WHILE, REPEAT, LEAVE, ITERATE) are also legal.

Variable declarations (DECLARE) and assignments (SET) are legal.

Conditional declarations are allowed.

Exception handling declarations are also allowed.

But remember here that the function has a constraint: You cannot access the table in the function. Therefore, it is illegal to use the following statement in the function.

ALTER 'CACHE INDEX' CALL COMMIT CREATE DELETE
DROP 'FLUSH PRIVILEGES' GRANT INSERT KILL
LOCK OPTIMIZE REPAIR REPLACE REVOKE
ROLLBACK SAVEPOINT 'SELECT FROM table'
'SET system variable' 'SET TRANSACTION'
SHOW 'START TRANSACTION' TRUNCATE UPDATE

Differences between stored functions and stored procedures

1. Stored functions have only 1 return value, while stored procedures cannot have a return value.

2. Functions can only have input parameters and cannot take in, while stored procedures can have multiple in, out, and inout parameters.

3. The statements in stored procedures are more powerful, and stored procedures can realize very complex business logic, while functions have many limitations, such as insert, update, delete, create and other statements cannot be used in functions; The storage function only completes the query work, and can accept the input parameters and return 1 result, that is, the function is more targeted.

4. Stored procedures can call stored functions. But functions cannot call stored procedures.

5. Stored Procedure 1 is typically executed as a separate part (call call). The function can be called as a part of the query statement.

Example 1:

Id	Name	QQ	phone
1	秦云	10102800	13500000
2	在路上	10378	13600000
3	LEO	10000	13900000

Id	Name	上机时间	管理员
1	秦云	2004-1-1	李大伟
2	秦云	2005-1-1	马化腾
3	在路上	2005-1-1	马化腾
4	秦云	2005-1-1	李大伟
5	在路上	2005-1-1	李大伟

Achieve the purpose:

Take the list of all people from Table 1, and take the number of times and administrators from Table 2.

List of people on the computer, administrator of number of times on the computer
Qin Yun 3 Li Dawei, Pony Ma, Li Dawei
On the road 2 Pony Ma, Li Dawei
LEO 0