Detailed explanation of the definition and usage of MySQL stored functions (custom functions)

Stored Functions

What is a stored function: It encapsulates a piece of SQL code, completes a specific function, and returns the result.

The syntax of a stored function is:

create function function([function parameters[,….]]) Returns return type Begin

    If(

      Return (returned data)

    Else 

      Return (returned data)

    end if;

  end;

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

Unlike stored procedure return parameters, stored functions do not directly declare which variable is the return parameter when they are defined. Instead, they only use returns to declare the data type of the return parameter. The return parameter is represented in the function body by using return to return the data variable to be returned. It is important to note that:
Stored functions only support input parameters, and there is no IN or INOUT before the input parameters.

Limitations in stored functions

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

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

Allows conditional statements.

Exception handling statements are also allowed.

But remember here that functions have restrictions: you cannot access tables in a function. Therefore, it is illegal to use the following statement in a 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

The difference between stored functions and stored procedures

1. A stored function has one and only one return value, while a stored procedure cannot have a return value.

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

3. The statements in stored procedures are more powerful. Stored procedures can implement very complex business logic, while functions have many limitations. For example, you cannot use insert, update, delete, create and other statements in functions. Stored functions only complete query work, can accept input parameters and return a result, that is, the functions implemented by functions are more targeted.

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

5. Stored procedures are generally executed as an independent part (call). Functions can be called as part of a query statement.

Example 1:

Achievement purpose:

Get the list of all personnel from Table 1, and the number of times they use the computer and the administrator from Table 2.

List of users Number of users Administrator Qin Yun 3 Li Dawei, Ma Huateng, Li Dawei on the way 2 Ma Huateng, Li Dawei
LEO 0