How to write mysql stored procedure

MySQL stored procedure is a collection of some SQL statements. For example, sometimes we may need a large series of SQL statements, or we need to set the values ​​of some variables during the process of writing SQL statements. At this time, we are completely necessary Write a stored procedure. Let's introduce how to create a stored procedure.

Syntax format:

You can use the CREATE PROCEDURE statement to create a stored procedure.
The syntax format is as follows:

CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>
[过程参数[,…] ] 格式
[ IN | OUT | INOUT ] <参数名> <类型>

The syntax description is as follows:

1) Procedure name

The name of the stored procedure , created in the current database by default. If you need to create a stored procedure in a specific database, prepend the name with the name of the database, db_name.sp_name. It should be noted that the name should try to avoid choosing the same name as the MySQL built-in function, otherwise an error will occur.

2) Process parameters

The parameter list of the stored procedure. Among them, is the parameter name, and is the type of the parameter (can be any valid MySQL data type). When there are multiple parameters, separate them with commas in the parameter list. A stored procedure can have no parameters (in this case, a pair of parentheses still need to be added after the name of the stored procedure), 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, which are identified by the three keywords IN, OUT and INOUT respectively. Among them, input parameters can be passed to a stored procedure, output parameters are used when the stored procedure needs to return an operation result, and input/output parameters can serve as both input parameters and output parameters. It should be noted that the parameter name should not be the same as the column name of the data table. Otherwise, although no error message will be returned, the SQL statement of the stored procedure will treat the parameter name as a column name, causing unpredictable results.

3) Procedure body

The main part of the stored procedure, also called the stored procedure body, contains the 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 stored procedure body, the BEGIN-END flag can be omitted.

In the creation of stored procedures, a very important MySQL command is often used, namely the DELIMITER command. Especially for users who operate the MySQL database through the command line, they must learn to use this command. Order.

In MySQL, when the server processes SQL statements, the semicolon is used as the end mark of the statement by default. However, when creating a stored procedure, the stored procedure body may contain multiple SQL statements. If these SQL statements still use a semicolon as the statement end character, the MySQL server will end with the first SQL statement encountered during processing. The semicolon is used as the terminator of the entire program, and the subsequent SQL statements in the stored procedure body are no longer processed. This is obviously not possible. To solve this problem, you can usually use the DELIMITER command to modify the end command to another character.

The syntax format is as follows:

DELIMITER $$

The syntax description is as follows: $$ is a user-defined terminator. Usually this symbol can be some special symbols, such as two " ?" or two "￥", etc. When using the DELIMITER command, you should avoid using the backslash "\" character because it is a MySQL escape character.
Enter the following SQL statement in the MySQL command line client.

mysql > DELIMITER ??

After successfully executing this SQL statement, the end mark of any command, statement or program will be replaced by two question marks "??".

If you want to change back to the default semicolon ";" as the end mark, enter the following statement in the MySQL command line client:

mysql > DELIMITER ;

Note: DELIMITER and semicolon ";" There must be a space between them. When creating a stored procedure, you must have CREATE ROUTINE permission. You can use the SHOW PROCEDURE STATUS command to view which stored procedures exist in the database. To view 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 student grade information from the student grade information table. The input SQL statement and execution process are as follows shown.

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

The above is the detailed content of How to write mysql stored procedure. For more information, please follow other related articles on the PHP Chinese website!