Mysql Stored Procedure instance

Mysql Stored Procedure instance

Mysql Stored Procedure instance
Technical Points

A stored procedure includes a name, a list of parameters, and a set of SQL statements that can contain many SQL statements. The following is a stored procedure definition process:

Create procedure proc_name (in parameter integer) begindeclare variable varchar (20); if parameter = 1 thenset variable = 'mysql'; elseset variable = 'php'; end if; 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

 

-- Create a table
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 );