Basic syntax for Mysql database Stored Procedures

Basic syntax for Mysql database Stored Procedures

drop procedure sp_name//

Before that, I have told you the basic knowledge of MYSQL syntax. In this article, I will explain the basic syntax knowledge to readers through the code in practice through an example below.

In general, MYSQL ends with; indicates that the input is confirmed and the statement is executed, but it is stored in the stored procedure; it does not indicate that the end is complete, so you can use this command; // indicates that the input is confirmed and executed. Stored procedures, such as the same programming language, also contain data types, process control, input and output, and their own function libraries.

1. Create a stored procedure

1. Basic Syntax:

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

2. parameter transfer
Ii. Call the Stored Procedure

1. Basic Syntax:

call sp_name()

Note: The stored procedure name must be enclosed in parentheses, even if the stored procedure has no parameters

Iii. delete stored procedures

1. Basic Syntax:

2. Notes
(1) You cannot delete another stored procedure in one stored procedure. You can only call another stored procedure.
4. blocks, conditions, and loops

1. Block definition, commonly used

begin......end;

You can also create an alias for the block, such:

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

You can use leave lable to jump out of the block and execute 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
[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 basic information about all stored procedures in the database, including the database, stored procedure name, and creation time.
2. show create procedure sp_name
Displays detailed information about a stored procedure.
The following is an example.
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.

Delimiter // -- change the delimiter

Drop procedure if exists pr_add // -- delete a stored procedure if it was created earlier

Calculate the sum of two numbers

create procedure pr_add (a int,b int)begindeclare c int;if a is null thenset a = 0;end if;if b is null thenset b = 0;end if;set c = a + b;select c as sum;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. For 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 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 "as" beginmysql 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)beginmysql 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 thenset a = 0;end if;...end;

7. Notes in the MySQL stored procedure.

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.

Set c = a + B;
Select c as sum;
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.

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

1. The following is a stored procedure definition process:

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

The creation of a stored procedure in MySQL starts with the keyword create procedure, followed by the name and parameters of the stored procedure. MySQL Stored Procedure names are case-insensitive. For example, PROCE1 () and proce1 () indicate the same stored procedure name. The stored procedure name cannot be the same as the built-in function name in the MySQL database.

Stored procedure parameters are generally composed of three parts. The first part can be in, out, or inout. In indicates that parameters are passed into the stored procedure; out indicates that parameters are passed out; inout indicates that the defined parameters can be passed into the stored procedure, and can be modified by the stored procedure before being passed out, the stored procedure is an input parameter by default, so the in parameter 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 the MySQL stored procedure starts with begin and ends with end. The statement body can contain variable declarations, control statements, and SQL query statements. Since the internal statements of a stored procedure should end with a semicolon, you should change the statement ending mark ";" to other characters before defining the stored procedure, the probability of this character appearing in the stored procedure is also low. You can use the keyword delimiter to change it. For example:

Mysql> delimiter //

After a stored procedure is created, you can use the following statement to delete it. The proc_name parameter indicates the name of the stored procedure.

Drop procedure proc_name

Implementation Process

(1) The MySQL stored procedure is 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 on 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 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 a stored procedure.
(6) call the stored procedure through the call statement.

Let alone

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)beginif parameter=0 thenselect * from user order by id asc;elseselect * 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) begindeclare variable varchar (20); if parameter = 1 thenset variable = 'windows'; elseset variable = 'linux '; end if; select parameter; end; // delimiter; show warnings; call proc_name (1); call proc_name (0); Delete drop procedure proc_name;

Note:

1. show procedure status;
Displays basic information about all stored procedures in the database, including the database, stored procedure name, and creation time.
2. show create procedure sp_name
Displays detailed information about a stored procedure.

The above is all about MYSQL basic syntax. If you feel good, share it with your friends.