MySQL Stored Procedures

The MySQL stored procedure is a new feature that has been added since MySQL 5.0. The advantages of a stored procedure are a whole bunch. But the main thing is the execution efficiency and the SQL code encapsulation. In particular, the SQL Code encapsulation feature, if there is no stored procedure, when the external program accesses the database (for example, PHP), to organize a lot of SQL statements. Especially when business logic is complicated, a lot of SQL and conditions are mixed in PHP code, which makes people shudder. Now that you have a MySQL stored procedure, the business logic can encapsulate the stored procedure, which is not only easy to maintain, but also highly efficient to execute. One, MySQL create stored procedure

--"Pr_add" is a simple MySQL stored procedure that has two input parameters of type int "A", "B", and returns the and of the two parameters. Drop procedure if existsPr_add;--calculate the sum of two numbersCreate procedurePr_add (AintBint)begin   DeclareCint; ifA is NULL  Then      SetA= 0; End if; ifB is NULL  Then      SetB= 0; End if; SetC=A+b; SelectC as sum; /*return c;-cannot be used in MySQL stored procedures.　　Return can only appear in the function. */End;

Second, call the MySQL stored procedure

Call Pr_add (ten); -- executes the mysql stored procedure with the stored procedure parameter as a MySQL user variable.  set@a=ten; Set @b =  - ; call Pr_add (@a@b);

Third, the MySQL stored procedure characteristic

Create procedure -- stored Procedure name () (   --  [in|out|inout] parameter datatype)begin   -- MySQL statement; End;

MySQL stored procedure Parameters If you do not explicitly specify "in", "Out", and "InOut", the default is "in". In practice, we do not explicitly specify parameters that are "in". 1. The "()" after the name of the MySQL stored procedure is required, even if there is no parameter, "()" 2 is required. MySQL stored procedure parameter, cannot add "@" before parameter name, for example: "@a int". The following create stored procedure syntax is wrong in MySQL (correct in SQL Server). The variables in the MySQL stored procedure do not need to be "@" before the variable name, although the MySQL client user variable should be added "@".

Create procedure Pr_add (   @aint,-  error    int   -  correct) 

3. The parameters of the MySQL stored procedure cannot specify a default value. 4. The MySQL stored procedure does not need to precede the procedure body with "as". The SQL Server stored procedure must be added with the "as" keyword.

Create procedure Pr_add (   int,   int)as             - error, MySQL does not need "as"begin   mysql statement ...; End;

5. If the MySQL stored procedure contains more than one MySQL statement, the BEGIN END keyword is required.

Create procedure Pr_add (   int,   int)begin1     ...;    2  ...; End;

6. At the end of each statement in the MySQL stored procedure, add a semicolon ";"

   ...    Declare int ;    if  is NULL  then      Set = 0 ;    End if ;   ... End ;

7. Comments in the MySQL stored procedure.

/*      This is a     multi-line MySQL comment.  /   declare c int;    -This is a single-line MySQL note (note-After at least one space)   if A is null then this is also a single-line MySQL comment      set a = 0;   End If;   ... end;

MySQL Stored Procedures