Explanation of the basic syntax of Mysql database stored procedures

drop procedure sp_name//

Before this, I have told you the basic knowledge of MYSQL syntax. In this article, I will use the following example to explain the basic syntax knowledge to readers through actual code.

Generally, MYSQL ends with a ; to confirm the input and execute the statement, but in a stored procedure, the ; does not indicate the end, so you can use this command to change the ; sign to // to confirm the input and execute. A stored procedure is like a programming language, and also includes data types, flow control, input and output, and its own function library.

1. Create a stored procedure

1. Basic syntax:

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

2. Parameter passing 2. Calling stored procedures

1. Basic syntax:

call sp_name()

Note: Parentheses must be added after the stored procedure name, even if the stored procedure has no parameters.

3. Delete the stored procedure

1. Basic syntax:

2. Notes
(1) You cannot delete another stored procedure from within a stored procedure. You can only call another stored procedure. 4. Blocks, Conditions, and Loops

1. Block definition, commonly used

begin
......
end;

You can also give blocks aliases, such as:

label:begin
...........
end label;

You can use leave label; to 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 Statement
(1).while loop

[label:] WHILE expression DO

statements

END WHILE [label];

(2) loop
[label:] LOOP

statements

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

statements

UNTIL expression

END REPEAT [label];
5. Other commonly used commands

1.show procedure status
Displays 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, etc.
2.show create procedure sp_name
Display detailed information of a stored procedure. Let's take a look at an example. 1. Create a stored procedure in MySQL

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

delimiter // -- Change the delimiter

drop procedure if exists pr_add// -- If this stored procedure has been created before, delete it

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. Calling MySQL stored procedures

call pr_add(10, 20);
Execute MySQL stored procedure, the stored procedure parameters are MySQL user variables.

set @a = 10;
set @b = 20;
call pr_add(@a, @b);
3. Characteristics of MySQL stored procedures

The simple syntax for creating a MySQL stored procedure is:

create procedure stored procedure name()
(
[in|out|inout] parameter datatype
)
begin
MySQL statements;
end;
If a MySQL stored procedure parameter is not explicitly specified as "in", "out", or "inout", the default is "in". Conventionally, we do not explicitly specify any "in" parameters.

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

2. For MySQL stored procedure parameters, you cannot add “@” before the parameter name, such as “@a int”. The following syntax for creating a stored procedure is incorrect in MySQL (it is correct in SQL Server). Variables in MySQL stored procedures do not need to be preceded by an "@" in the variable name, although MySQL client user variables must have an "@" in front of them.

create procedure pr_add
(
@a int, -- error
b int -- correct
)
3. MySQL stored procedure parameters cannot specify default values.

4. MySQL stored procedures do not need to add "as" in front of the procedure body. The SQL Server stored procedure must have the "as" keyword.

create procedure pr_add
(
a int,
b int
)
as -- Wrong, MySQL does not require "as"
begin
mysql statement ...;
end;

5. If the MySQL stored procedure contains multiple MySQL statements, the begin and end keywords are required.

create procedure pr_add
(
a int,
b int
)
begin
mysql statement 1 ...;
mysql statement 2 ...;
end;

6. Add a semicolon ";" at the end of each statement in a MySQL stored procedure

...
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 that there must be at least one space after the --)
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 MySQL stored procedures.

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

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

Let's use an example to deepen the above knowledge points:

1. The following is the definition process of a 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 creation of a stored procedure in MySQL begins with the keyword create procedure, followed by the name and parameters of the stored procedure. MySQL stored procedure names are not case sensitive. For example, PROCE1() and proce1() represent the same stored procedure name. The name of the stored procedure cannot be the same as the built-in function in the MySQL database.

The parameters of a stored procedure generally consist of three parts. The first part can be in, out, or inout. in means passing parameters into the stored procedure; out means passing parameters out; inout means the defined parameters can be passed into the stored procedure and can be modified by the stored procedure before being passed out of the stored procedure. The stored procedure defaults to passing in parameters, so the parameter in can be omitted. The second part is the parameter name. The third part is the parameter type, which is all available field types in the MySQL database. If there are multiple parameters, the parameters can be separated by commas.

The statement block of a MySQL stored procedure starts with begin and ends with end. The statement body can contain variable declarations, control statements, SQL query statements, etc. Since the statements inside the stored procedure must end with a semicolon, the statement end mark ";" should be changed to other characters before defining the stored procedure. The probability of this character appearing in the stored procedure should also be low. You can use the keyword delimiter to change it. For example:

mysql>delimiter //

After a stored procedure is created, it can be deleted using the following statement, where the parameter proc_name refers to the name of the stored procedure.

drop procedure proc_name

Implementation process

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

mysql –u username –p user password

(3) Change the statement terminator. In this example, change the statement terminator to “//”. The code is as follows:

delimiter //

(4) Before creating a stored procedure, you should first select a database. The code is as follows:

use database name

(5) Create a stored procedure.
(6) Call the stored procedure through the call statement.

learn by analogy

use test;
create table user(
id mediumint(8) unsigned not null auto_increment,
name char(15) not null default ”,
pass char(32) not null default ”,
note text not null,
primary key (id)
)engine=Innodb charset=utf8;

Example 1

delimiter //
create procedure proc_name (in parameter integer)
begin
if parameter=0 then
select * from user order by id asc;
else
select * from user order by id desc;
end if;
end;
//

delimiter ;
show warnings;
call proc_name(1);
call proc_name(0);

Example 2

drop procedure proc_name;
delimiter //
create procedure proc_name (in parameter integer)
begin
declare variable varchar(20);
if parameter=1 then
set variable='Windows';
else
set variable='Linux';
end if;
select parameter;
end;
//

delimiter ;
show warnings;
call proc_name(1);
call proc_name(0);

 Delete drop procedure proc_name;

Notes:

1.show procedure status;
Displays 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, etc.
2.show create procedure sp_name
Display detailed information about a stored procedure

The above is all the content of this article on basic MYSQL syntax. If you feel it is good, share it with your friends.