MySQL stored procedure instance

MySQL stored procedure instance
Technical points

A stored procedure includes a name, a list of arguments, and a set of SQL statements that can include many SQL statements. The following is the process of defining a stored procedure:

CREATE PROCEDURE Proc_name (in parameter integer) begindeclare variable varchar (a); if Parameter=1 Thenset ' MySQL '; elseset variable= ' PHP '; end If;insert to TB (name) values (variable);

The establishment 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 () represent the same stored procedure name. The stored procedure name cannot be the same as the built-in function in the MySQL database.

The parameters of a stored procedure are generally composed of 3 parts. The first part can be in, out, or inout. In indicates that parameters are passed in to a stored procedure, out represents an outgoing parameter, inout represents a defined parameter that can be passed in to a stored procedure, and can be modified by a stored procedure, and the stored procedure defaults to an incoming parameter, so parameter in can be omitted. The second part is the name of the parameter. The third part is the type of the parameter, which is the type of all the fields available in the MySQL database, and can be separated by commas if there are multiple parameters.

The statement block of the MySQL stored procedure begins with the beginning 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 used before the stored procedure is defined. Change to a different character, and the chance that the character will appear in the stored procedure should be lower and 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, which refers to the stored procedure name (proc_name).

drop procedure Proc_name

Implementation process

(1) The MySQL stored procedure is created under 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 at the command prompt, enter the following command:

Mysql–u User name –p user password

(3) Change the statement closing symbol, this instance changes the statement terminator to "//." The code is as follows:

Delimiter//

(4) A database should be selected before the stored procedure is created. The code is as follows:

Use database name

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

Extrapolate

--Build the table
Use test;
CREATE TABLE User (
ID Mediumint (8) unsigned NOT NULL auto_increment,
Name char NOT null default ',
Pass char (+) NOT null default ',
Note text is not NULL,
Primary KEY (ID)
) Engine=innodb Charset=utf8;

--Example One
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 Two
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);