Create a stored procedure instance in MySQL

Create a stored procedure instance in MySQL

The MySQL stored procedure is a new feature added since MySQL 5.0. The advantages of stored procedures are as follows. However, the most important thing is the execution efficiency and SQL code encapsulation. In particular, the SQL code encapsulation function. If there is no stored procedure, many SQL statements need to be organized when external programs access the database (such as PHP. Especially when the business logic is complex, a lot of SQL statements and conditions are mixed in PHP code, which is chilling. With the MySQL stored procedure, the business logic can encapsulate the stored procedure, which is not only easy to maintain, but also highly efficient in execution.

I. MySQL Stored Procedure
"Pr_add" is a simple MySQL stored procedure, which has two int-type input parameters: "a" and "B", and returns the sum of the two parameters.

Drop procedure if exists pr_add;

-- Calculate the sum of two numbers

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;-it cannot be used in MySQL stored procedures. Return can only appear in functions.
/
End;
Ii. Call the MySQL Stored Procedure
Call pr_add (10, 20 );
Execute the MySQL stored procedure. The stored procedure parameter is a MySQL user variable.

Set @ a = 10;
Set @ B = 20;

Call pr_add (@ a, @ B );
Iii. MySQL stored procedure features
The simple syntax for creating a MySQL stored procedure is:

Create procedure stored procedure name ()
(
[In | out | inout] parameter datatype
)
Begin
MySQL statement;
End;
If "in", "out", and "inout" are not explicitly specified for MySQL stored procedure parameters, the default value is "in ". Traditionally, we will not explicitly specify the "in" parameter.

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

2. MySQL stored procedure parameters cannot be prefixed with "@", for example, mailto: % E2 % 80% 9C @ a int ". The following syntax for creating a stored procedure is incorrect in MySQL (correct in SQL Server ). You do not need to add "@" before the variable name in the MySQL stored procedure, although the MySQL client user variable requires "@".

Create procedure pr_add
(
@ A int,-Error
B int-correct
)
3. The default value cannot be specified for MySQL stored procedure parameters.

4. You do not need to add "as" before procedure body to the MySQL stored procedure ". The SQL Server Stored Procedure must contain the "as" keyword.

Create procedure pr_add
(
A int,
B int
)
As-error. MySQL does not need ""
Begin
Mysql statement ...;
End;
5. If the MySQL Stored Procedure contains multiple MySQL statements, the begin end keyword is required.

Create procedure pr_add
(
A int,
B int
)
Begin
Mysql statement 1 ...;
Mysql statement 2 ...;
End;
6. Add a semicolon (;) to the end of each statement in the MySQL stored procedure.

...

Declare c int;

If a is null then
Set a = 0;
End if;

...
End;
7. Notes in the MySQL stored procedure.

/*
This is
Comment on multiple MySQL lines.
/

Declare c int;-this is a single-line MySQL comment (Note that at least one space is required after)

If a is null then this is also a single-row MySQL comment
Set a = 0;
End if;

...
End;
8. The "return" keyword cannot be used in MySQL stored procedures.

Set c = a + B;

Select c as sum;

/*
Return c;-it cannot be used in MySQL stored procedures. Return can only appear in functions.
/
End;
9. when calling the MySQL stored procedure, you need to add "()" after the process name. Even if there is no parameter, you also need "()"

Call pr_no_param ();
10. Because there is no default value for the MySQL stored procedure parameters, you cannot omit the parameters when calling the MySQL stored procedure. It can be replaced by null.

Call pr_add (10, null );

Compile and install MySQL 5.6.14 in CentOS 6.4

This article permanently updates the link address: