Parameters and Compound statements

CREATE PROCEDURE P5 ([    ] name1 data-type,    ...                                                       ,     [] Namen data-

The argument list can be either empty or multiple, keyword in | Out | INOUT, can be omitted, default in.

1. In input example

MySQL>CREATEPROCEDUREINT)    SET@x=  //mysql> call P5 (12345)//mysql>  SELECT@x//

The above code will show the input 12345 after execution. The procedure body assigns the value of the parameter p to the session variable x, and then invokes the procedure and passes 12345 to the parameter p, which displays the session variable @x after the call is complete.

The description variable name plus @ is represented as a session variable, and the scope is the session period for the entire user. Does not add @ Represents a user-defined local variable. About Session Variables:

1) You can save the value in the user variable and then reference it later, so that you can pass the value from one statement to another.

2) The user variable is related to the connection, that is, a client-defined variable cannot be seen or used by other clients. When the client exits, all the variables for that client connection are automatically freed.

3) The user variable is in the form of @var_name, where the variable name Var_name can be composed of literal numeric characters, '. ', ' _ ' and ' $ ' of the current character set. The default character set is cp1252 (Latin1). You can change the character set with the mysqld default-character-set option.

4) User variable name is not sensitive to case.
5) One way to set the user variable is to execute the SET statement:

SET @var_name = [, @var_name = expr] ...

For set, you can use = or: = as an assignment. The expr assigned to each variable can be an integer, a real number, a string, or a null value.

You can also assign a value to a user variable by using a statement instead of set. In this case, the quantifier must be: = instead of =, because in a non-set statement = is treated as a comparison operator:

Mysql> SET @t1=0,@t2=0,@t3=0; MySQL> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;

+-------------------------------+------+------+------+

| @t1: = (@t2: =1) [Email protected]:=4 | @t1 | @t2 | @t3 |

+-------------------------------+------+------+------+

|    5 |    5 |    1 | 4 |

+-------------------------------+------+------+------+

6) User variables can be used in an expression. Currently does not include a context that clearly requires literal values, such as the limit clause of a SELECT statement, or the Ignore number lines clause of the LOAD data statement.

7) If you use a variable that is not initialized, its value is null.

2. Example of out output

> CREATE PROCEDURE INT )         ,SET=-5//MySQL  > call P6 (@y//mysql>SELECT@y/ /  

In this example, p is the output parameter, and the calling procedure passes the value of P into the session variable @y.

3. Compound statements

CREATE PROCEDUREP7 ()BEGIN    SET @a = 5; SET @b = 5; INSERT  intoTVALUES(@a); SELECTS1* @a  fromTWHERES1>= @b;END;//   /*This statement will not be called*/ 

Compound statements are generally composed of begin/end blocks. You can use blocks to encapsulate multiple statements, and complete some SQL statements for variable definition and process control.

　　

Parameters and Compound statements