Mysql Stored Procedures

We all know that the MySQL stored procedure is starting from MySQL 5.0 to gradually add new features. Stored procedures are also more advantageous than disadvantages in practical applications. 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 an external program accesses the database (for example, PHP), many SQL statements are organized.

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, this MySQL stored procedure has two int type input parameter "a", "B", returns the and of these two parameters.

1. drop procedure if exists pr_add;

Calculate the sum of two numbers

delimiter // CREATE PROCEDURE Pr_add (aintBint) BEGIN DECLARE Cint; ifA is NULL ThenSetA =0; Endif; ifB is NULL ThenSetb =0; Endif; Setc = A +b; SelectC assum; /*return C; cannot be used in a MySQL stored procedure. Return can only appear in the function. */end;

Second, call the MySQL stored procedure

1. Call Pr_add (10, 20);
2. The output result is
   

   Sum
   30

   
   

Executes the mysql stored procedure with the stored procedure parameter as a MySQL user variable.

1. SET @a = 10;
2. SET @B = 20;
3. Call Pr_add (@a, @b);

Third, the MySQL stored procedure characteristic

The simple syntax for creating a MySQL stored procedure is:

1. Create procedure stored procedure name ()
2. (
3. [In|out|inout] Parameter datatype
4. )
5. Begin
6. MySQL statement;
7. 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. 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 "@".

1. CREATE PROCEDURE Pr_add
2. (
3. @a int,--Error
4. b INT--Correct
5. )

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.

1. CREATE PROCEDURE Pr_add
2. (
3. a int,
4. b int
5. )
6. As--error, MySQL does not need "as"
7. Begin
8. MySQL statement ...;
9. End

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

1. CREATE PROCEDURE Pr_add
2. (
3. a int,
4. b int
5. )
6. Begin
7. MySQL statement 1 ...;
8. MySQL statement 2 ...;
9. End

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

1. ...
2. declare c int;
3. If A is null then
4. Set a = 0;
5. End If;
6. ...
7. End

7. Comments in the MySQL stored procedure.

1. /*
2. This is a
3. Multi-line MySQL annotations.
4. */
5. declare c int; --This is a single-line MySQL note (note--there must be at least one space)
6. If a is null and then # This is also a single-line MySQL comment
7. Set a = 0;
8. End If;
9. ...
10. End

8. The "return" keyword cannot be used in a MySQL stored procedure.

1. Set c = A + b;
2. Select C as Sum;
3. /*
4. return C; --cannot be used in a MySQL stored procedure. Return can only appear in the function.
5. */
6. End

9. When calling the MySQL stored procedure, you need to add "()" After the procedure name, even if there is no parameter, "()"

1. Call Pr_no_param ();

10. Because the MySQL stored procedure parameter does not have a default value, the parameter cannot be omitted when the MySQL stored procedure is called. You can use NULL to replace

Mysql Stored Procedures