Shulou(Shulou.com)06/01 Report--

This article will explain in detail what the syntax format of mysql stored procedures is. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

MySQL stored procedure is a collection of SQL statements, for example, sometimes we may need a long list of SQL statements, or we need to set the values of some variables in the process of writing SQL statements, so it is absolutely necessary to write a stored procedure.

Syntax format:

You can use CREATE PROCEDURE statements to create stored procedures.

The syntax format is as follows:

CREATE PROCEDURE ([process parameters [,...] ]) [process parameters [,...] ] format [IN | OUT | INOUT]

The syntax is as follows:

1) procedure name

The name of the stored procedure, created by default in the current database. If you need to create a stored procedure in a specific database, add the name of the database, that is, db_name.sp_name, before the name. It is important to note that the name should avoid choosing the same name as the MySQL built-in function, otherwise an error will occur.

2) process parameters

A list of parameters for the stored procedure. Where is the name of the parameter and the type of the parameter (which can be any valid MySQL data type). When there are multiple parameters, the parameter list is separated from each other by a comma. A stored procedure can have no parameters (at this point the name of the stored procedure still needs to be followed by a pair of parentheses), or it can have one or more parameters.

MySQL stored procedures support three types of parameters, namely, input parameters, output parameters, and input / output parameters, identified by the IN, OUT, and INOUT keywords, respectively. Among them, the input parameter can be passed to a stored procedure, the output parameter is used in the case where the stored procedure needs to return an operation result, and the input / output parameter can act as both input parameter and output parameter. It is important to note that the parameter name is not the same as the column name of the data table, otherwise, although no error message is returned, the SQL statement of the stored procedure treats the parameter name as a column name, causing unpredictable results.

3) process body

The body of a stored procedure, also known as the body of a stored procedure, contains SQL statements that must be executed when the procedure is called. This section begins with the keyword BEGIN and ends with the keyword END. If there is only one SQL statement in the body of the stored procedure, the BEGIN-END flag can be omitted.

In the creation of stored procedures, a very important MySQL command, the DELIMITER command, is often used, especially for users who operate the MySQL database through the command line.

In MySQL, the server processes SQL statements with semicolons as the end flag by default. However, when creating a stored procedure, the body of the stored procedure may contain multiple SQL statements. If these SQL statements still use a semicolon as the statement Terminator, then the MySQL server will use the semicolon at the end of the first SQL statement encountered as the Terminator of the whole program, instead of dealing with the later SQL statements in the stored procedure body. To solve this problem, you can usually use the DELIMITER command to change the end command to other characters.

The syntax format is as follows:

DELIMITER $$

The syntax is as follows: $$is a user-defined Terminator, and usually this symbol can be some special symbol, such as two "?" Or two "¥" and so on. When using the DELIMITER command, you should avoid using the backslash "\" character because it is an escape character for MySQL.

Enter the following SQL statement on the MySQL command line client.

Mysql > DELIMITER?

After the successful execution of this SQL statement, the closing flag of any command, statement, or program is replaced by two question marks. Yes.

If you want to change back to the default semicolon ";" as the closing flag, enter the following statement on the MySQL command line client:

Mysql > DELIMITER

Note: there must be a space between the DELIMITER and the semicolon ";". When you create a stored procedure, you must have CREATE ROUTINE permission. You can use the SHOW PROCEDURE STATUS command to see which stored procedures exist in the database, and to see specific information about a stored procedure, you can use SHOW CREATE PROCEDURE.

Create a stored procedure without parameters

The function of the stored procedure is to query the student's score information from the student performance information table. The input SQL statement and execution process are shown below.

Mysql > DELIMITER / / mysql > CREATE PROCEDURE ShowStuScore ()-> BEGIN-> SELECT * FROM tb_students_score;-> END / / Query OK, 0 rows affected (0.09 sec) on what the syntax format of mysql stored procedures is shared here, I hope the above content can be helpful to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.