Basic usage of MySQL Stored Procedure

Source: Internet
Author: User
We all know that the MySQL stored procedure has gradually added new features since MySQL5.0. Stored Procedures also have advantages and disadvantages in practical applications. 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.

We all know that the MySQL stored procedure has gradually added new features since MySQL 5.0. Stored Procedures also have advantages and disadvantages in practical applications. 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.

When an external program accesses a database (such as PHP), many SQL statements need to be organized.

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. This MySQL stored procedure has two int-type input parameters: "a" and "B". The sum of the two parameters is returned.
The Code is as follows:
Drop procedure if exists pr_add;

Calculate the sum of two numbers
The Code is 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;
It cannot be used in MySQL stored procedures. Return can only appear in functions.

*/
End;

Ii. Call the MySQL Stored Procedure
The Code is as follows:
Call pr_add (10, 20 );

Execute the MySQL stored procedure. The stored procedure parameter is a MySQL user variable.
The Code is as follows:
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:
The Code is as follows:
Create procedure stored procedure name ()
(
[In | out | inout] parameter datatype
)
Begin
MySQL statement;
End;

If you do not explicitly specify "in", "out", and "inout" 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. You cannot add "@" before the parameter name, for example, "@ 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 you need to add "@" to the MySQL client user variable "@".
The Code is as follows:
Create procedure pr_add
(
@ A int, -- Error
B int -- correct
)

3. The default value cannot be specified for MySQL stored procedure parameters.

4. MySQL stored procedures do not need to add "as" before procedure body ". The SQL Server Stored Procedure must contain the "as" keyword.
The Code is as follows:
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.
The Code is as follows:
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 ";"
The Code is as follows:
...
Declare c int;
If a is null then
Set a = 0;
End if;
...
End;

7. Notes in the MySQL stored procedure.
The Code is as follows:
/*
This is
Comment on multiple MySQL lines.
*/
Declare c int; -- this is a single-line MySQL comment (Note that there must be at least one space 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.
The Code is as follows:
Set c = a + B;
Select c as sum;
/*
Return c; -- 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 procedure name. Even if there is no parameter, you also need "()"
The Code is as follows:
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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.