Shulou(Shulou.com)05/31 Report--

This article is about the syntax format of sql stored procedures in the database. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

In sql, you can use the "CREATE PROCEDURE" statement to create a stored procedure. The syntax format is "CREATE PROCEDURE procedure name ([procedure parameter [,]]) procedure body [procedure parameter [, …]] format [IN | OUT | INOUT] parameter name type".

A 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.

Writing stored procedures is not a simple thing, but using stored procedures can simplify the operation and reduce redundant operation steps. At the same time, it can also reduce errors in the operation process and improve efficiency. Therefore, you should learn to use stored procedures as much as possible.

The following is mainly about how to create stored procedures.

You can use CREATE PROCEDURE statements to create stored procedures in the following syntax format:

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.

Example:

Create a stored procedure named ShowStuScore. The function of the stored procedure is to query the student's performance information from the student performance information table.

Mysql > DELIMITER / / mysql > CREATE PROCEDURE ShowStuScore ()-> BEGIN-> SELECT * FROM tb_students_score;-> END / / Query OK, 0 rows affected (0.09 sec)

The results show that the ShowStuScore stored procedure has been created successfully.

Thank you for reading! This is the end of this article on "what is the syntax format of sql stored procedures in the database?". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out 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.