Custom MySQL Functions

1. mysql Create function syntax:

= {User | Current_User}] FUNCTION sp_name ([func_parameter[,...]) RETURNS type[characteristic ...] routine_bodyfunc_parameter:    param_name typetype: Any    valid MySQL data Typeroutine_body:    Valid SQL Routine statement

Note: custom functions cannot be used across libraries

Mysql> DELIMITER//MySQL>CREATE FUNCTION ver_compare (n int, M int)-RETURNSVARCHAR ( -) #声明返回的数据类型-BEGIN-DECLARE S VARCHAR ( -); IF n = m then SET s ='equals'; -ELSEIF n > m then SET s ='Greater'; ELSE SET s =' Less'; -END IF; SET s = CONCAT (' is', S,'than'); -END IF; SET s = CONCAT (n,' ', S,' 'M'.'); -RETURN S; #一个实际的返回值
-END//MySQL> DELIMITER;

From the above example, it is not difficult to see that the creation of a custom function is actually similar to the Create stored procedure.

2. Function call

Custom functions are similar to stored procedures in creating definitions, but are slightly different when called.

 mysql> call Ver_compare (1 , 2  ); ERROR  1305  (42000  ): PROCEDURE db1. Ver_compare does not existmysql  > set  @ax = ver_compare (1 , 2   > Span style= "COLOR: #0000ff" >select   @ax;  +-------------------+| @ax |+-------------------+| 1  is  less than 2 . |+-------------------+  

when we call the function, function needs to appear to the right of = (that is, the calling function needs to have a variable to receive its result )

3. Other

> view All custom functions:show function status\g;

> Delete specified custom function:drop function func_name;

Custom MySQL functions