Mysql stored Procedure Basic syntax

Delimiter//
In general, MySQL ends with a confirmation input and executes a statement, but in a stored procedure ; It is not the end, so you can use the command to change the number to // to confirm the input and execute.

I. Creating a stored Procedure

1. Basic syntax:
CREATE PROCEDURE Sp_name ()
Begin
.........
End2. Parameter passing

Two. Calling a stored procedure

1. Basic syntax: Call Sp_name ()
Note: The stored procedure name must be appended with parentheses, even if the stored procedure has no parameters passed

Three. Delete a stored procedure

1. Basic syntax:
drop procedure sp_name//
2. Precautions
(1) cannot delete another stored procedure in one stored procedure, only another stored procedure can be called

Four. blocks, conditions, loops

1. Block definition, commonly used
Begin
......
End
You can also alias chunks, such as:
Lable:begin
...........
End lable;
You can use leave lable, jump out of chunks, execute code after chunk
2. Conditional statements

if condition Then
Statement
Else
Statement
end if;

3. Looping statements
(1). While loop

[Label:]while expression do

Statements

END While [label] ;

(2). Loop loop
[Label:]LOOP

Statements

END LOOP [label]; (3). Repeat until cycle
[Label:]REPEAT

Statements

UNTIL expression

END REPEAT [label] ;

Five. Other common commands

1.show Procedure Status
Displays basic information about all stored procedures in the database, including the owning database, stored procedure name, creation time, etc.
2.show CREATE PROCEDURE Sp_name
Show details of a stored procedurelet's look at an example

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", return these two parameter's and

Delimiter//--Change the separator

drop procedure if exists pr_add//--delete if the stored procedure was previously created

Calculate the sum of two numbers

1. CREATE PROCEDURE Pr_add (a int,bint)
2. Begin
3. declare CInt;
4. if A is null then
5. Set a =0;
6. End If;
7. if b is null and then
8. Set b = 0;
9. endif
10. Set c = a +b;
11. Select C as Sum;
12. End
13. //

Second, call the MySQL stored procedure

1. Call Pr_add (10, 20);

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

1. SET @A =ten;
2. SET @b = +;
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 Procedurepr_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 Procedurepr_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 Procedurepr_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. declare c int; --This is a single-line MySQL note (note--there must be at least one space)
2. If a is null and then # This is also a single-line MySQL comment
3. Set a =0;
4. End If;
5. ...
6. End

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

1. Set c =a+ b;
2. Select C as Sum;
3. 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. can be substituted with null.

Mysql stored Procedure Basic syntax