Shulou(Shulou.com)06/01 Report--

Advanced MySQL Database (2)-- Custom function I. introduction to Custom function

Custom functions (user-defined function UDF) are a way to extend MySQL in the same way as built-in functions.

Two necessary conditions for a custom function:

A, parameters

B, return value (must have). Function can return any type of value.

2. The use of custom functions 1. Custom function syntax CREATE FUNCTION function_name (parameter_nametype, [parameter_nametype,...]) RETURNS {STRING | INTEGER | REAL} runtime_body

More complex syntax can be used in the function body, such as composite structure / flow control / any SQL statements / defining variables, etc.

The syntax for creating a custom function with a composite structure is as follows:

DELIMITER / / CREATE FUNCTION function_name (parameter_nametype, [parameter_nametype,...]) RETURNS {STRING | INTEGER | REAL} BEGIN//bodyEND// / * the "/ /" here tells the system that the function definition ends * /

When multiple statements need to be executed in the function body, use the begin.. end statement; and when writing the content of the function body, you need to use the DELIMITER keyword to change the delimiter to something else, otherwise it will be directly executed when writing the statement, resulting in the failure of function writing.

2. Create a custom function of the function body with compound structure.

In the function body, if you have more than one statement, you need to put multiple statements in the BEGIN...END statement block.

The composite structure can include declaration, loop, and control structure.

3. Define local variables in custom functions

Syntax for variable definition:

DECLARE var_name [, varname]... date_type [DEFAULT VALUE]

Syntax for assigning values to variables:

SET parameter_name = value [, parameter_name = value...] SELECT INTO parameter_name

Example:

DECLARE x int;SELECT COUNT (id) FROM tdb_name INTO x Ting set @ x = 100 Ting 4, process control

Process control can be used in custom functions to control the execution of statements.

If statements, CASE statements, LOOP statements, LEAVE statements, ITERATE statements, REPEAT statements, and WHILE statements can be used in MySQL for flow control.

A, IF statement

The IF statement is used to determine the condition. Different statements are executed depending on whether the condition is met. The basic form of its grammar is as follows:

IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list]... [ELSE statement_list] END IF

The search_condition parameter represents a conditional judgment statement; the statement_list parameter represents an execution statement with different conditions.

All IF statements need to end with END IF.

Example of IF statement:

IF age > 20 THEN SET @ count1=@count1+1; ELSEIF age=20 THEN SET @ count2=@count2+1; ELSE SET @ count3=@count3+1; END IF

B, CASE statement

CASE statements are also used for conditional judgment, which can achieve more complex conditional judgments than IF statements. The basic form of the CASE statement is as follows:

CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list]... [ELSE statement_list] END CASE

The case_value parameter represents the variable for conditional judgment, and the when_value parameter represents the value of the variable.

The statement_list parameter represents execution statements with different when_ value values.

Example of CASE statement:

CASE age WHEN 20 THEN SET @ count1=@count1+1; ELSE SET @ count2=@count2+1; END CASE

The CASE statement has another form, and the syntax is as follows:

CASE WHEN search_condition THEN statement_list [WHEN search_condition THEN statement_list]... [ELSE statement_list] END CASE

The search_condition parameter represents a conditional judgment statement; the statement_list parameter represents an execution statement with different conditions.

Example of CASE statement:

CASE WHEN age=20 THEN SET @ count1=@count1+1; ELSE SET @ count2=@count2+1; END CASE

C, LOOP statement

The LOOP statement can cause some specific statements to be executed repeatedly, implementing a simple loop. However, the LOOP statement itself does not have a statement to stop the loop, and you must encounter a level statement in order to stop the loop.

The basic syntax of the LOOP statement is as follows:

[begin_label:] LOOP statement_list END LOOP [end_label]

The begin_label parameter and the end_label parameter represent the start and end flags of the loop, respectively, which must be the same and can be omitted; the statement_list parameter represents the statement that needs to be executed in the loop.

Example of LOOP statement:

Add_num: LOOP SET @ count=@count+1; END LOOP add_num

D, LEAVE statement

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

LEAVE label

The label parameter represents the flag of the loop.

Example of LEAVE statement:

Add_num: LOOP SET @ count=@count+1; IF @ count=100 THEN LEAVE add_num; END LOOP add_num

E, ITERATE statement

The ITERATE statement is also used to jump out of the loop. However, the ITERATE statement is to jump out of this loop and go straight to the next loop.

ITERATE statements can only appear within LOOP, REPEAT, WHILE statements.

The basic syntax of the ITERATE statement is as follows:

ITERATE label

Where the label parameter represents the flag of the loop.

Example of ITERATE statement:

Add_num: LOOP SET @ count=@count+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 LEAVE statement is to jump out of the loop and then execute the program that follows the loop. The ITERATE statement is to jump out of this loop and move on to the next loop.

F, REPEAT statement

REPEAT statements are conditional loop statements. When certain conditions are met, the loop statement is jumped out. The basic syntax of the REPEAT statement is as follows:

[begin_label:] REPEAT statement_list UNTIL search_condition END REPEAT [end_label]

The REPEAT loop ends with END REPEAT.

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

REPEAT SET @ count=@count+1; UNTIL @ count=100 END REPEAT

G, WHILE statement

WHILE statements are also conditional loop statements. But the WHILE statement is different from the REPEAT statement.

A WHILE statement is a statement that executes within a loop when a condition is met.

The basic syntax of the WHILE statement is as follows:

[begin_label:] WHILE search_condition DO statement_list END WHILE [end_label]

The WHILE loop needs to end with END WHILE.

Where the parameter search_condition represents the condition under which the loop executes, and when the condition is satisfied, the loop executes

The statement_list parameter represents the execution statement of the loop.

WHILE @ count 128THEN SET mycode = 65536-lcode * 256rcode; SELECT CONCAT (mypy,pin_yin_) INTO mypy FROM t_base_pinyin WHERE CODE_ > = ABS (mycode) ORDER BY CODE_ ASC LIMIT 1; SET lp = lp + 4; ELSESET mypy = CONCAT (CAST (ASCII (UNHEX (NAME (NAME, lp, 2)) AS UNSIGNED); SET lp = lp + 2; END IF; END WHILE RETURN LOWER (mypy); END;3, Arabic numerals to Chinese create FUNCTION tohanzi (n_LowerMoney DECIMAL) RETURNS VARCHAR BEGIN Declare v_LowerStr VARCHAR; Declare v_UpperPart VARCHAR; Declare v_UpperStr VARCHAR; Declare I int; set v_LowerStr = LTRIM (ROUND); set I = 1; set v_UpperStr =''; while

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.