MySQL Stored procedure Learning

Basic grammar on their own Baidu bar, although I Baidu, but I basically did not see, or more knock code good AH

1, delimiter

Change the command terminator (because the default command Terminator-semicolon (;)) is often used in procedure
So when creating a procedure, you need to define a new terminator to explain the end of the command that created the procedure. Change the ending symbol to a dollar sign ($) Here

Variable:

The scope of the variable is similar to that in programming, where it is usually between the corresponding begin and end. After end this variable does not work and cannot be used. This is the same as programming.

Another variant is called Session variable, which is also called user-defined variables (defined variable). This variable must precede the variable name with the "@" symbol, called the session variable, which represents the entire session process, which is a bit like a global variable. This variable is widely used because it can share data between called stored procedures or code as long as it is within a session (that is, during a connection to an application).

Local variables

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

such as declear a int (2) default 2;

Note where the statement is used (it cannot be used in an external program)

Assignment statement Set

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

such as set A=5;set a=a+4;

All operations that are assigned are to use the SET statement, and the operation to use a=5 directly is illegal.

Note where the statement is used (it cannot be used in an external program)

@ operator

@a is used to define a global variable with the lifetime of the variable being connected only this time.

Set @a=3; Assignment statements

The set @a=b statement can exist in any block of statements

2. In MySQL, the basic form of creating stored procedures is as follows:

1. CREATE PROCEDURE sp_name ([proc_parameter[,...]])
2. [Characteristic ...] Routine_body

Where the Sp_name parameter is the name of the stored procedure, Proc_parameter represents the parameter list of the stored procedure, the characteristic parameter specifies the properties of the stored procedure, the Routine_body parameter is the contents of the SQL code, you can use the BEGIN ... End to flag the start and end of the SQL code.

Each parameter in the Proc_parameter is made up of 3 parts. These 3 sections are input and output types, parameter names, and parameter types, respectively. The form is as follows:

1. [In | Out | INOUT] Param_name Type

Where in represents the input parameter; out represents an output parameter; The inout represents either an input or an output; the Param_name parameter is the parameter name of the stored procedure, and the type parameter specifies the parameter type of the stored procedure, which can be any data type of the MySQL database.

Column:

Delimiter $

CREATE PROCEDURE mytest (out Pamar int)

Begin

Select COUNT (*) into param from user;

end$

Call:

Demiliter; (Command Terminator changed back)

Call MyTest (@num); (Session variable, reference front)

Select @num (read variable value)

drop procedure mytest; To delete a stored procedure

/************************************/

CREATE PROCEDURE mytest (inout Sexflag int)

Begin

SELECT * from user where Sex=sexflag

end$

Set @sexflag =xx

Call MyTest (@sexflag);

/****************************************/

CREATE PROCEDURE Test ()

Begin

declare i int//define Variable

Set i=0; Set the value of a variable

While i<10 do

Insert into User (Username,password) values (i, ' pwd ');

Or

Insert into User (Username,password) VALUES (concat (' user ', i), "pwd");

Set i=i+1;

End while;

end$

delimiter;

Call Test ();

/****************/

Delimiter $

CREATE PROCEDURE test1 (in param int)

Begin

(You can also define variables

declare i int;

Set i=param+1;

)

If Param=1 Then

Insert into User (Username,password) VALUES (' eeee ', ' 11111 ');

Else

Insert into User2 (Usename,password) VALUES (' TTT ', ' 222222222222 ');

Enf if;

end$

delimiter;

Call Test1 (1); or call Test1 (2);

/********************************/

Delimiter $

CREATE PROCEDURE mytest (in param int)

Begin

Set @num = 1;

While @num <20 do

If Param=1 Then

Insert into User (Username,password) VALUES (concat (' user ', @num), ' pwd ');

Else

Insert into User2 (Username,password) VALUES (concat (' user ', @num), ' pwd ');

End If;

Set @[email protected]+1;

End while;

end$

delimiter;

Call MyTest (1) or call MyTest (2)

MySQL Stored procedure Learning