Mysql Database Stored Procedure Basic Syntax Explanation

drop procedure sp_name//

Before this, this site tells you about the basic knowledge of MYSQL grammar. This content, this site through the following example, gives readers a basic grammar knowledge through actual combat code.

1 Under normal circumstances, MYSQL is; The end indicates that the input is confirmed and the statement is executed, but in the stored procedure; Does not mean the end, so you can use this command to set; The number is changed to//to confirm the input and execute. Stored procedures, like a programming language, contain data types, flow control, inputs and outputs, and their own library of functions.

1. Create a stored procedure

1. Basic syntax:

create procedure sp_name()
begin
.........
end

2. Parameter passing
2. Call stored procedures

1. Basic syntax:

call sp_name()

Note: The stored procedure name must be followed by parentheses, even if the stored procedure has no parameters passed

3. Delete stored procedures

1. Basic syntax:

2. Considerations
(1) You cannot delete another stored procedure in one, you can only call another stored procedure
4. Blocks, conditions, cycles

1. Block definition, commonly used

begin
......
end;

You can also give blocks aliases, such as:

lable:begin
...........
end lable;

leave lable can be used; Jump out of the block and execute the code after the block
2. Conditional statements

if Condition then
statement
else
statement
end if;

3. Loop statements
(1). while loop

[label:] WHILE expression DO

statements

END WHILE [label] ;

(2). loop cycle
[label:] LOOP

statements

END LOOP [label];
(3). repeat until loop
[label:] REPEAT

statements

UNTIL expression

END REPEAT [label] ;
5. Other common commands

1.show procedure status
Displays the basic information of all stored procedures in the database, including the database to which they belong, the name of the stored procedure, the creation time, and so on
2.show create procedure sp_name
Displays details of a 1 stored procedure
Let's look at an example below
1. MySQL Create Stored Procedures

"pr_add" is a simple MySQL stored procedure. This MySQL stored procedure has two input parameters of type int, "a" and "b", and returns the sum of these two parameters

delimiter//--Changing the divider

drop procedure if exists pr_add//--Delete if this stored procedure was previously created

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;
end
//

2. Call the MySQL stored procedure

call pr_add(10, 20);
Executes the MySQL stored procedure with MySQL user variables as the stored procedure parameters.

set @a = 10;
set @b = 20;
call pr_add(@a, @b);
3. MySQL Stored Procedure Features

The simple syntax for creating an MySQL stored procedure is:

create procedure Stored Procedure Name ()
(
[inoutinout] Parameter datatype
)
begin
MySQL statement;
end;
The MySQL stored procedure parameter defaults to "in" if it does not explicitly specify "in", "out", "inout". Traditionally, we don't explicitly specify parameters that are "in".

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

2. MySQL stored procedure parameters. You cannot prefix the parameter name with "@", such as "@ a int". The following syntax for creating stored procedures is incorrect in MySQL (correct in SQL Server). Variables in MySQL stored procedures do not need to be preceded by an "@", although MySQL client user variables need to be preceded by an "@".

create procedure pr_add
(
@ a int,--Error
b int--Correct
)
3. Default values cannot be specified for parameters of MySQL stored procedures.

4. The MySQL stored procedure does not require "as" before procedure body. The SQL Server stored procedure must have the keyword "as" added.

create procedure pr_add
(
a int,
b int
)
as --  Errors, MySQL  No need   " as " 
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. At the end of each statement in an MySQL stored procedure, a semicolon ";"

...
declare c int;
if a is null then
set a = 0;
end if;
...
end;

7. Comments in MySQL stored procedures.

declare c int; --This is a single-line MySQL comment (note--at least 1 space after it)
if a is null then # This is also a single line MySQL comment
set a = 0;
end if;
...
end;
8. You cannot use the "return" keyword in an MySQL stored procedure.

set c = a + b;
select c as sum;
end;
9. When calling an MySQL stored procedure, you need to add "()" after the procedure name, even if you don't have a parameter, you need "()"

call pr_no_param();
10. Because MySQL stored procedure parameters do not have default values, parameters cannot be omitted when calling MySQL stored procedures. It can be replaced by null.

Let's deepen the above knowledge points through an example:

1. The following is the definition procedure of 1 stored procedure:

create procedure proc_name (in parameter integer)
begin 
declare variable varchar(20);
if parameter=1 then set variable='MySQL';
else set variable='PHP';
end
if;
insert into tb (name) values (variable);
end;

The establishment of a stored procedure in MySQL begins with the keyword create procedure, followed by the name and parameters of the stored procedure. The stored procedure names of MySQL are not case sensitive, for example, PROCE1 () and proce1 () represent the same stored procedure name. The stored procedure name cannot be the same as the built-in function in the MySQL database.

Parameter 1 of a stored procedure generally consists of three parts. Part 1 can be in, out, or inout. in represents passing parameters into stored procedures; out represents outgoing parameters; inout means that the defined parameters can be passed into the stored procedure, and can be modified by the stored procedure and then passed out. The stored procedure defaults to the passed-in parameters, so the parameter in can be omitted. Part 2 is the parameter name. Part 3 is the parameter type, which is all the available field types in the MySQL database. If there are multiple parameters, the parameters can be separated by commas.

The statement block of the MySQL stored procedure begins with begin and ends with end. The statement body can contain declarations of variables, control statements, SQL query statements, and so on. Because the internal statement of the stored procedure ends with a semicolon, the statement end flag ";" should be put before defining the stored procedure. Change to another character, and the character should be less likely to appear in the stored procedure, which can be changed with the keyword delimiter. For example:

mysql > delimiter //

After the stored procedure is created, it can be deleted with the following statement, with the parameter proc_name referring to the stored procedure name.

drop procedure proc_name

Implementation process

(1) MySQL stored procedures are created at the Command Prompt, so the Command Prompt window should be opened first.
(2) After entering the "Command Prompt" window, you should first log in to the MySQL database server and enter the following command at the "Command Prompt":

mysql u User Name p User Password

(3) Change the statement terminator. This example changes the statement terminator to "//". The code is as follows:

delimiter //

(4) Select a database before creating a stored procedure. The code is as follows:

use database name

(5) Create stored procedures.
(6) The stored procedure is invoked through the call statement.

Cite 1 against 3

create procedure sp_name()
begin
.........
end

0

Example 1

create procedure sp_name()
begin
.........
end

1

Example 2

create procedure sp_name()
begin
.........
end

2

Notes:

1.show procedure status;
Displays the basic information of all stored procedures in the database, including the database to which they belong, the name of the stored procedure, the creation time, and so on
2.show create procedure sp_name
Displays details of a 1 stored procedure

The above is the MYSQL basic grammar related knowledge of the whole content, feel good to share with your friends.