Mysql stored procedure creation and precautions

Mysql tutorial storage process creation and precautions
First, the mysql stored procedure is a little different from the stored procedure previously written in sqlserver 2005;

Second, I realized that when I used the mysql third-party Enterprise Manager sqlyogent.exe for debugging, the stored procedure and the general query statement should be separated, it may be caused by the habit of using the sqlserver 2005 Enterprise Manager editor ). When writing a stored procedure, pay attention to the following points:

First, define delimiter //, which means to use // as the end character of a statement. (This is very important. The Stored Procedure syntax I wrote is correct. However, it cannot be used, the original semicolon:; delimiter indicates that the mysql client has completed the input, and many of my stored procedures contain semicolons. When the result is running, is to divide a stored procedure into multiple segments for execution)

Second, if you want to select sqlyogent.exe from mysqlclient, you must right-click the stored procedure in the database tutorial directory and select create stored procedure. Then, in the pop-up window on the right, type your Stored Procedure statement. The input in the common query analyzer is invalid, this is a painful lesson

Third, if the stored procedure parameter is input in Chinese, the character set gbk encoding must be added after the stored procedure is defined. Otherwise, an error occurs when the stored procedure uses the Chinese parameter, for example:

 

Create procedure countpro (out a_out int, in B _date date, in unit_name varchar (45) character set gbk)
Fourth, if you need fuzzy query in your stored procedure, use like '% content %' instead of writing '%' behind the where clause of the select statement. Define a parameter and use: set wherestr = "'%" + wherestr + "%'"; concatenate the fifth statement, and restore the Terminator: delimiter; Is; as the statement Terminator

The following describes the mysql Stored Procedure instance.

1. Create an instance Database
Create database db5;
Use db5;

2. Create a simple worksheet and insert data
Create table t (s1 int );
Insert into t values (5 );

3. create procedure example
Create procedure p1 () select * from t;
The first part of the SQL statement stored procedure is create procedure.
The second part is the process name: the name of the new stored procedure above is p1.
The third part is the parameter list (), the fourth part is the main body of the program, "select * from t"

* *** What mysql statements are valid in the Stored Procedure body?
The stored procedure body can contain all legal SQL database definition languages, insert, update, delete, drop, create, replace, and other statements.
Including (set, commit, rollback). However, if the Code contains the mysql extension function, the Code cannot be transplanted.

4. To call a stored procedure, you must enter the call, your procedure name, and a bracket.
Call p1 ();

5. Feature clauses in the process
Create procedure p2 () language SQL
Not deterministic
SQL security definer
Comment''
Select current_date, rand () from t;

6. parameters
Create procedure p5 () ------; // The parameter list is empty.
Create procedure p5 ([in] name data-type) ---- // The input parameter in is optional. The default parameter is in.
Create procedure p5 (out name data-type) ----- // output parameter out
Create procedure p5 (inout so data-type) ----- // you can make input parameters or output parameters.

---- Input parameter in example.
Create procedure p5 (p int) set @ x = p;
Call p5 (12345 );
Select @ x;

---- Output parameter out example
Create procedure p6 (out p int) set p =-5;
Call p6 (@ y );
Select @ y;

7. Compound statement: If you have multiple statements in the process, you need the begin/end block. Here you can define variables and control processes.
First run the command delimiter //
Create procedure p7 ()
Begin
Set @ a = 6;
Set @ B = 5;
Insert into t values (@ );
Select s1 * @ a from t where s1 >=@ B;
End ;//

----- Declare the variable in the composite statement
Create procedure p8 ()
Begin
Declare a int;
Declare B int;
Set a = 5;
Set B = 5;
Insert into t values ();
Select s1 * a from t where s1> = B;
End ;//

Example of a statement with default statement tongue
Create procedure p9 ()
Begin
Declare a, B int default 5;
Insert into t values ();
Select s1 * a from t where s1> = B;
End ;//

8. scope problems: internal variables have a higher priority within the scope of the scope, when executed to the end
The internal variable disappears and is out of its scope, and the variable is no longer visible.
The declarative variable can no longer be found outside the process, but you can use the out parameter or assign its value
Session variables to save their values.
Create procedure p11 ()
Begin
Declare x1 char (5) default 'outer ';
Begin
Declare x1 char (5) default 'inner ';
Select x1;
End;
Select x1;
End ;//

/*********************************** Conditional statements in the stored procedure * **************************************/

1. if-then-else statement
Create procedure p12 (in parameter int)
Begin
Declare var int;
Set var = parameter + 1;
If var = 0 then
Insert into t values (17 );
End if;
If parameter = 0 then
Update t set s1 = s1 + 1;
Else
Update t set s1 = s1 + 2;
End if;
End ;//

2. case command: We can use the case statement to determine whether more conditions are true or false.
Create procedure p13 (in parameter int)
Begin
Declare var int;
Set var = parameter + 1;
Case var
When 0 then insert into t values (17 );
When 1 then insert into t values (18 );
Else insert into t values (19 );
End case;
End ;//

******** ****************************/
1. while... end while; loop statement
Create procedure p14 ()
Begin
Declare var int;
Set var = 0;
While var <6 do
Insert into t values (var );
Set var = var + 1;
End while;
End ;//

2. repeat · end repeat; it checks the result after the operation is executed, while is the result before the execution.
Create procedure p15 ()
Begin
Declare v int;
Set v = 0;
Repeat
Insert into t values (v );
Set v = v + 1;
Until v> = 5
End repeat;
End ;//

3. loop · end loop; the loop does not require initial conditions, which is similar to the while loop and repeat at the same time.
If the loop is the same, the end condition is not required. The leave statement means to exit the loop,
Create procedure p16 ()
Begin
Declare v int;
Set v = 0;
Loop_lable: loop
Insert into t values (v );
Set v = v + 1;
If v> = 5 then
Leave loop_lable;
End if;
End loop;
End ;//

4. lables label: the label can be used before the begin repeat while or loop statement.
The statement is valid. You can jump out of the loop to make the running command the last step of the compound statement.

/**************************** Iterate iteration ******** *******************************/

1. iterate: reference the label of a compound statement to start a compound statement.
Create procedure p20 ()
Begin
Declare v int;
Set v = 0;
Loop_lable: loop
If v = 3 then
Set v = v + 1;
Iterate loop_lable;
End if;
Insert into t values (v );
Set v = v + 1;
If v> = 5 then
Leave loop_lable;
End if;
End loop;
End ;//