How to create a stored function in mysql

Mysql method to create a stored function: [CREATE FUNCTION function_name(param1) RETURNS datatype DETERMINISTIC statements SQL]. A stored function is itself an output function, so it cannot have output parameters.

[Recommended course: mysql video tutorial]

Storage function

Stored functions are very similar to stored procedures. They are code fragments composed of SQL statements and procedural statements, and can be called by applications and other SQL statements. Since the storage function itself is an output function, it cannot have output parameters. In addition, the storage function can be called directly without a call statement.

Creation of storage function

Grammar

CREATE FUNCTION function_name(param1,param2,…)    
RETURNS datatype   
[NOT] DETERMINISTIC statements
SQL

Grammar analysis ：

CREATE FUNCTION clause is followed by the name of the specified stored function

(param1,param2,…) ：Represents all parameters of the stored function. By default, all The parameters are all IN parameters. The IN, OUT or INOUT modifier cannot be specified for a parameter.

RETURNS datatype: Indicates: the data type of the return value, it can be any valid MySQL data type

[NOT] DETERMINISTIC: Indicates that the result is undefined, the same input may get different results Output. If no value is specified, the default is [NOT] DETERMINISTIC

SQL: Program body

Example: Create a stored function named demo, which returns the query of the SELECT statement As a result, the numeric type is string. The code is as follows:

mysql> DELIMITER  //
mysql> CREATE  FUNCTION  demo()
    -> RETURNS  CHAR(50)
    -> RETURN  (  SELECT  s_name  FROM  suppliers  WHERE  s_call='48075');
    -> //
Query OK, 0 rows affected (0.11 sec)
mysql> DELIMITER  ;

The above is the detailed content of How to create a stored function in mysql. For more information, please follow other related articles on the PHP Chinese website!