Create user-defined functions in mysql

There is always some complex logic that we need to use in many places, which shows the importance of the function.

Mysql function name parameter list function body return value definition syntax create function fun_name (parameter list) returns return value type function body example without parameter delimiter $$create function sayhello () returns varchar (20) beginreturn 'hello';end$$ delimiter

Call example:

Select sayhello ()

Sayhello () hello with parameters (parameters need to indicate data type) delimiter $$create function sayHelloToSomeOne (name varchar (20)) returns varchar (25) begin set @ str = concat ('hello', name); return @ str; end $$delimiter

Call example:

Select sayHelloToSomeOne ('Dany')

SayHelloToSomeOne ('Dany') hello Dany

After the function call, let's verify that the @ str variable is still accessible.

Select @ str

@ strhello Dany

@ str is still available, indicating that the scope of @ str is global.

With multiple parameters

Multiple parameters are separated by commas.

A local variable parameter in a function (barely considered a local variable), because the parameters passed in can only be used within the function. Declare defines local variables

We have previously studied that variables that start with @ are global variables, because even variables defined inside the function, such as @ aa, can still get the value of @ aa externally after the function has been called, which clearly tells us that @ aa is a global variable.

Global variables are easy to cause variable pollution, so we need local variables to ensure program independence. Declare is designed to solve this problem.

Syntax:

DECLARE var_name [, var_name]... Type [DEFAULT value]

For example:

Delimiter $$create function sayHelloToSomeOneVarLocal (name varchar (20)) returns varchar (30) begin declare str varchar (30); set str = concat ('hello', name); return str; end $$delimiter

Select sayHelloToSomeOneVarLocal ('xiaogang')

SayHelloToSomeOneVarLocal ('xiaogang') hello xiaogang

After the function call, we try to access the variable str, and an error will be reported.