Mysql-Stored Procedure

Mysql-before learning about the stored procedure, take a look at the mysql control structure. C language (if ...... Else, while loop, etc.) SQL also has its own control structure. If ...... Else control structure: (1) spanstylefont-family: FangSong_GB2312; if judgment Expression then execution statement; endif;

Mysql-before learning about the stored procedure, take a look at the mysql control structure. C language (if ...... Else, while loop, etc.) SQL also has its own control structure. If ...... Else control structure: For example: (1) span style = font-family: FangSong_GB2312; if judgment Expression then execution statement; end if;

Mysql-Stored Procedure

Before learning about the stored procedure, first understand the mysql control structure.

Similar to C language (if ...... Else, while loop, etc.) SQL also has its own control structure.

If ...... Else control structure:

For example:

(1)

If judgment Expression then execution statement; end if; comparison with C language if (judgment Expression) execution statement;

(2)

If judgment Expression 1 then execution statement 1; else then execution statement 2; end if; comparison with C language if (judgment Expression 1) execution statement 1; else execution statement 2;

(3)

If judgment Expression 1 then execution statement 1; elseif judgment Expression 2 then execution statement 2 ;...... Elseif judgment Expression N then execution statement N; else execution statement N + 1; end if; comparison with C language if (judgment Expression 1) execution statement 1; else if (judgment Expression 2) execute Statement 2 ;...... Else if (judgment Expression N) execution statement N; else execution statement N + 1;

Note that all the execution statements and end if must end with ';', and the judgment Expression is followed by then. What's different from the C language is that there is no space between elseif.

Mysql also has some if-related functions.

If (judgment Expression, value 1, value 2)If the expression is "true", "value 1" is returned. If the expression is "false", "value 2" is returned ". Similar to the three-object operator in C.

Ifnull (expression 1, expression 2)If expression 1 is not empty, expression 1 is returned. If expression 1 is null, expression 2 is returned.

Nullif (expression 1, expression 2)If expression 1 = expression 2, null is returned; otherwise, expression 1 is returned.

Case when control structure:

There are two forms

(1)

Case value to be determined when value 1 then output 1 when value 2 then output 2 ...... When N then outputs Nelse outputs end by default; # If the statement is output, the end of the statement must be changed to end case. The output value is the end value, which is compared with the switch of C language (to be determined) {case value 1: Output 1 break; case value 2: Output 2 break ;...... Case value N: Output N break; default: default output}

(2)

Casewhen judgment Expression 1 then output 1 when judgment Expression 2 then output 2 ...... When the expression N then outputs Nelse, the end case is output by default; # If the statement is output, the end case is the end case. The output value is end.

While loop structure:

While judgment Expression do loop body end while; C language while loop while (judgment Expression) {loop body ;}

Loop structure: unconditional loop

Repeat loop structure:

Repeat loop body; until judgment Expression end repeat;

Now I will introduce the stored procedure. In fact, the stored procedure is similar to the function.

View the status of the current stored procedure: show procedure status;

Create a stored procedure:

Create procedure name (parameter list) begin statement set end;

The parameter list always exists. If there is no parameter, it should be a null parameter list (). The parameter must specify the data type and each parameter is an in parameter by default. To specify another parameter, you can add the out or inout keyword before the parameter. DefaultIn is similar to passing by value.The parameters are modified in the stored procedure, which is invisible to the caller. The out parameter is only used to return data from the stored procedure,No matter what value is input to the parameter, the initial value of this parameter is always null.. For the inout parameter, the caller can not only set the initial value of the parameter, but also modify the parameter during the process. The caller can see thatSimilar to passing by address.

Delete stored procedure: drop procedure name;

View stored procedure: show create procedure name \ GSimilar to the show create table name \ G, it is used for horizontal display.

Call stored procedure: call name (parameter );

Declare variables:

(1) declare variable name default value of variable type;Declared variables must be defined at the beginning. If no default value exists, the initial value is null. The function scope is in begin ...... Within end

(2) set @ variable name = initial value;User variables are defined, and SQL statements other than stored procedures can also be called.

Variable Value assignment: set variable name = variable valueDo not assign values to variables directly (variable name = variable value)

There is also a way to assign values to one or more variables:Exploitation"Select specifies the column into specifies the variable"So the select result must be a single row.

Example:

All examples are implemented to set the delimiter to '$'

Delimiter $

1. Test the if-else control structure

2. test case ...... When

First case:

The output is a value and end is used. Generally used for select

The output is a statement and the end is an end case. Generally used for Stored Procedures

Case 2:

The output is a statement and the end is an end case. Generally used for Stored Procedures

The output is a value and end is used. Generally used for select

3. Test the while LOOP

4. Test loop

5. Test repeat

6. Stored Procedures with Parameters

Default Value: in.

After the variable tmp whose initial value is 0 is passed into the stored procedure as a parameter, although it is modified in the stored procedure, when the caller checks the tmp again, the value is still 0 and remains unchanged.

Out parameter:

As can be seen from the first select, The out parameter cannot pass the value of the real parameter into the stored procedure. The second and third select statements show that the Stored Procedure variables can be returned to the caller after being modified.

Different from address-based transfer, out only allows the return value and does not allow the input value.

Inout parameter: passed by address. If the parameter value is changed, the value of the real parameter is changed.

The first select result is 0, indicating that the values of real parameters are passed into the stored procedure. The second and third select results show that inout can modify the value of the form parameter within the stored procedure, thus affecting the real parameter, similar to passing by address.