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!