Introduction to the basic usage of MySQL stored procedures _mysql

When an external program accesses a database (for example, PHP), you organize a lot of SQL statements.

In particular, the complexity of business logic, a lot of SQL and conditions mixed in the PHP code, makes people shudder. Now that you have a MySQL stored procedure, business logic can encapsulate stored procedures so that it is not only easy to maintain, but also efficient to perform.

First, MySQL Create stored procedures

"Pr_add" is a simple MySQL stored procedure, this MySQL stored procedure has two int type input parameter "a", "B", returns these two parameter's and.

Copy Code code as follows:

drop procedure if exists pr_add;

Calculate the sum of two numbers

Copy Code code as follows:

CREATE PROCEDURE Pr_add
(
a int,
b int
)
Begin
declare c int;
If A is null then
Set a = 0;
End If;
If B is null then
Set B = 0;
End If;
Set C = a + B;
Select C as Sum;
/*
return C;
cannot be used in a MySQL stored procedure. Return can only appear in the function.

*/
End

Second, call the MySQL stored procedure

Copy Code code as follows:

Call Pr_add (10, 20);

Execute the MySQL stored procedure, the stored procedure parameter is the MySQL user variable.

Copy Code code as follows:

Set @a = 10;
Set @b = 20;
Call Pr_add (@a, @b);

The characteristics of MySQL stored procedure

The simple syntax for creating a MySQL stored procedure is:

Copy Code code as follows:

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 ', ' inout ', the default is ' in '. In practice, we do not explicitly specify the parameters that are "in".

1. The "()" after the MySQL stored procedure name is required, even if there is no parameter, "()"

2. The MySQL stored procedure parameter, cannot add "@" before the parameter name, for example: "@a int". The following create stored procedure syntax is incorrect in MySQL (correct in SQL Server). MySQL stored procedure variables, do not need to add "@" before the variable name, although the MySQL client user variable to add a "@".

Copy Code code as follows:

CREATE PROCEDURE Pr_add
(
@a int,--Error
b INT--Correct
)

3. The parameters of the MySQL stored procedure cannot specify a default value.

4. The MySQL stored procedure does not need to add "as" in front of the procedure body. SQL Server stored procedures must be added with the "as" keyword.

Copy Code code as follows:

CREATE PROCEDURE Pr_add
(
a int,
b int
)
As--error, MySQL does not need "as"
Begin
MySQL statement ...;
End

5. If the MySQL stored procedure contains more than one MySQL statement, you need the begin end keyword.

Copy Code code as follows:

CREATE PROCEDURE Pr_add
(
a int,
b int
)
Begin
MySQL statement 1 ...;
MySQL statement 2 ...;
End

6. The end of each statement in the MySQL stored procedure is preceded by a semicolon ";"

Copy Code code as follows:

...
declare c int;
If A is null then
Set a = 0;
End If;
...
End

7. The annotations in the MySQL stored procedure.

Copy Code code as follows:

/*
This is a
Multi-line MySQL annotation.
*/
declare c int; --This is a single MySQL note (note--at least one space is needed)
If A is null then # This is also a single line MySQL note
Set a = 0;
End If;
...
End

8. You cannot use the "return" keyword in a MySQL stored procedure.

Copy Code code as follows:

Set C = a + B;
Select C as Sum;
/*
return C; --cannot be used in a MySQL stored procedure. Return can only appear in the function.
*/
End

9. When invoking a MySQL stored procedure, you need to add "()" after the process name, even if there is no parameter, "()"

Copy Code code as follows:

Call Pr_no_param ();

10. Because the MySQL stored procedure parameter has no default value, you cannot omit the parameter when invoking the MySQL stored procedure. can be substituted with null.