MySQL Custom function

Custom Functions (user-defined function UDFs) simply extend MySQL with a new function that acts like an intrinsic (built-in) function such as ABS () or CONCAT ().

So UDF is an extension to MySQL functionality

Create and delete custom function syntax:

To create a UDF:

CREATE [AGGREGATE] FUNCTION function_name (parameter_name type,[parameter_name type,...])

RETURNS {string| integer| REAL}

Runtime_body

In simple terms:

CREATE function name (parameter list)

RETURNS return value type

function body

To delete a UDF:

DROP FUNCTION function_name

Call Custom Function Syntax:

SELECT function_name (Parameter_value,...)

Syntax Examples:

Create a simple parameterless UDF

Simplefun () Varvhar () "Hello world!";

Description

UDF can do more than this, the UDF has two key points, one is a parameter, one is the return value, the UDF can have no parameters, but the UDF must have and only one return value

In the function weight we can use more complex syntax, such as composite structure/Process Control/Any SQL statement/definition variable, etc.

Composite structure definition Syntax:

In the body of a function, if you have more than one statement, we need to put multiple statements into the BEGIN ... The End statement block

DELIMITER//create FUNCTION IF EXIST Deletebyid (uid SMALLINT UNSIGNED) RETURNS VARCHAR (begindelete) from son WHERE id = Uid RETURN (SELECT COUNT (id) from son); end//

To modify the default terminator syntax:

DELIMITER//means to modify the default Terminator ";" To "//" and future SQL statements are terminated with "//"

Special Note:

UDF, the Reurn statement is also included in the Begin ... End in

Define local variable syntax in a custom function:

DECLARE Var_name[,varname]...date_type [DEFAULT VALUE];

In simple terms:

DECLARE variable 1[, variable 2,...] Variable type [default defaults]

The scope of these variables is at begin ... End program, and the definition of a local variable statement must be at begin ... The first line of the end defines

Example:

DELIMITER//create FUNCTION addtwonumber (x SMALLINT UNSIGNED, Y SMALLINT UNSIGNED) RETURNS smallintbegindeclare A, b SMALL INT UNSIGNED DEFAULT 10; SET a = x, b = y; RETURN a+b; end//

The code above just adds two numbers, and of course, there is no need to write this, just to illustrate the use of local variables, or to illustrate the following: the scope of these local variables is at begin ... End Program

To assign a value to a variable syntax:

SET parameter_name = Value[,parameter_name = value ...]

SELECT into Parameter_name

eg

... In a UDF ... DECLARE x int; SELECT COUNT (ID) from tdb_name to X; RETURN x; end//

User variable definition syntax: (can be understood as a global variable)

SET @param_name = value

SET @allParam = 100; SELECT @allParam;

The above definition and displays the @allparam user variable whose scope is valid only for the current user's client

The Process Control statement syntax in a custom function:

Process controls can be used in stored procedures and functions to control the execution of statements.

In MySQL, you can use if statements, case statements, loop statements, leave statements, iterate statements, repeat statements, and while statements for process control.

Each process may contain a single statement, or use the BEGIN ... End constructs a compound statement that can be nested

1. If statement

The IF statement is used to make conditional judgments. Depending on whether the condition is met, different statements are executed. The basic form of its syntax is as follows:

IF search_condition then statement_list [ELSEIF search_condition then statement_list] ... [ELSE Statement_list] END IF

Where the search_condition parameter represents the conditional judgment statement; The Statement_list parameter represents the execution statement for different conditions.

Note: MySQL also has an if () function that differs from the IF statement described here

The following is an example of an if statement. The code is as follows:

IF age>20 then SET @[email protected]+1;  ELSEIF age=20 then SET @[email protected]+1;  ELSE SET @[email protected]+1; END IF;

The example executes different set statements based on the size relationship between age and 20.

If the age value is greater than 20, then the value of Count1 is added 1, and if the age value equals 20, the value of Count2 is added to 1;

In other cases, the value of Count3 is added to 1. The IF statement needs to end with End If.

2. Case statement

Case statements are also used to make conditional judgments, which can be more complex than if statements. The basic form of the case statement is as follows:

Case Case_value while When_value then Statement_list [when When_value then Statement_list] ... [ELSE Statement_list] END case

wherein, the Case_value parameter denotes the variable of the condition judgment;

The When_value parameter represents the value of the variable;

The Statement_list parameter represents the execution statement for different when_value values.

There is another form of case statement. The syntax for this form is as follows:

When the case is search_condition then statement_list [when Search_condition then Statement_list] ... [ELSE Statement_list] END case

wherein, the search_condition parameter indicates the conditional judgment statement;

The Statement_list parameter represents the execution statement for different conditions.

The following is an example of a case statement. The code is as follows:

case, age, and then SET @[email protected]+1; ELSE SET @[email protected]+1; END case;

The code can also be in the following form:

When the case is age=20 then SET @[email protected]+1; ELSE SET @[email protected]+1; END case;

In this example, if the age value is 20,count1 plus 1, the value of Count2 is added 1. The case statement ends with the end case.

Note: The case statement here differs slightly from the case statement in the SQL case expression described in control flow functions. The case statement here cannot have an else NULL clause

and use end case instead of end to terminate!!

3. Loop statement

The Loop statement enables certain statements to be executed repeatedly, implementing a simple loop.

However, the Loop statement itself does not have a statement to stop looping, it must encounter a leave statement, and so on to stop the loop.

The basic form of the loop statement syntax is as follows:

[Begin_label:] Loop statement_list END Loop [End_label]

Among them, the Begin_label parameter and the End_label parameter represent the sign of the loop start and end respectively, the two flags must be the same, and can be omitted;

The Statement_list parameter represents a statement that needs to be executed in a loop.

The following is an example of a loop statement. The code is as follows:

Add_num:loop SET @[email protected]+1; END LOOP Add_num;

The example loops through the Count plus 1 operation. Because there is no statement that jumps out of the loop, the loop becomes a dead loop.

Loop loops end With End loop.

4. Leave statements

The leave statement is primarily used to jump out of loop control. Its grammatical form is as follows:

LEAVE Label

Where the label parameter represents the loop's flag.

The following is an example of a leave statement. The code is as follows:

Add_num:loop SET @[email protected]+1; IF @count =100 then LEAVE add_num; END LOOP Add_num;

The example loops through the Count plus 1 operation. When the value of Count equals 100, the leave statement jumps out of the loop.

5. Iterate statements

The iterate statement is also a statement used to jump out of a loop. However, the iterate statement jumps out of the loop and then goes directly to the next loop.

The iterate statement can only appear within the loop, REPEAT, and while statements.

The basic syntax form of the iterate statement is as follows:

Iterate label

Where the label parameter represents the loop's flag.

The following is an example of a iterate statement. The code is as follows:

Add_num:loop SET @[email protected]+1; IF @count =100 then LEAVE add_num; ELSE IF MOD (@count, 3) =0 then iterate add_num; SELECT * from employee; END LOOP Add_num;

The example loops through the Count plus 1 operation, and the count value is 100 when the loop ends. If the value of count is divisible by 3, it jumps out of the loop and no longer executes the following SELECT statement.

Note: Both the leave statement and the iterate statement are used to jump out of a looping statement, but the function is different.

The leave statement is to jump out of the entire loop and then execute the program behind the loop. The iterate statement jumps out of the loop and then into the next loop.

Be sure to distinguish between the two statements when using them.

6. Repeat statements

The repeat statement is a conditional-controlled loop statement. When certain conditions are met, the loop statement is jumped out. The basic syntax form of the repeat statement is as follows:

[Begin_label:] REPEAT statement_list UNTIL search_condition END REPEAT [End_label]

Where the statement_list parameter represents the execution statement of the loop, and the search_condition parameter represents the condition that ends the loop, and the loop ends when the condition is met.

The following is an example of a iterate statement. The code is as follows:

REPEAT SET @[email protected]+1; UNTIL @count =100 END REPEAT;

The example loops through the Count plus 1 operation, and the count value is 100 when the loop ends.

The repeat loop ends with end repeat.

7. While statement

The while statement is also a conditional-controlled loop statement. But the while statement and the repeat statement are not the same.

The While statement executes the statement within the loop when the condition is met.

The basic syntax form of the while statement is as follows:

[Begin_label:] While Search_condition does statement_list END while [End_label]

Where the search_condition parameter indicates the condition of the loop execution, and the loop executes when the condition is satisfied;

The Statement_list parameter represents the execution statement for the loop.

The following is an example of a iterate statement. The code is as follows:

While @count <100 do SET @[email protected]+1; END while;

The example loops through the Count plus 1 operation, and the count value is less than 100 when the loop is executed.

If the count value equals 100, it jumps out of the loop. The while loop needs to end with the end while.

Reprinted from Http://www.cnblogs.com/caoruiy/p/4485273.html

MySQL Custom function