How to use mysql stored functions?

What is a stored function:

Encapsulates a piece of SQL code to complete a specific function and return the result.

The syntax of stored functions:

　　create function 函数([函数参数[,….]]) Returns 返回类型
　　Begin
　　　　If(
　　　　　　Return (返回的数据)
　　　　Else 
　　　　　　Return (返回的数据)
　　　　end if;
　　end;

For example:

 create function count_news(hits int) returns int

The difference from stored procedure return parameters is that stored functions do not need to be directly declared when defining Which variable is the return parameter? Instead, returns are used to declare the data type of the return parameter. The return parameter is expressed in the function body by using return to return the data variable to be returned. What needs to be noted is:
Stored functions only support input parameters, and there is no IN or INOUT before the input parameters.

Restrictions in stored functions

Flow-of-control statements (IF, CASE, WHILE, LOOP, WHILE, REPEAT, LEAVE, ITERATE) are also legal.
Variable declaration (DECLARE) and assignment (SET) are legal.
Conditional declarations are allowed.
Exception handling declarations are also allowed.
But remember here that the function has restricted conditions: the table cannot be accessed in the function. Therefore, it is illegal to use the following statements 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

Related learning recommendations: mysql database

The above is the detailed content of How to use mysql stored functions?. For more information, please follow other related articles on the PHP Chinese website!