How to write sql stored procedure

The writing method is "CREATE PROCEDURE process name ([process parameter]) process body [process parameter] format [IN|OUT|INOUT] parameter name type". The procedure name should try to avoid choosing the same name as the built-in function, otherwise an error will occur; the procedure body starts with BEGIN and ends with END.

The operating environment of this tutorial: Windows 7 system, mysql version 5.8, Dell G3 computer.

(Recommended tutorial: mysql video tutorial)

MySQL stored procedure is a collection of SQL statements. For example, sometimes we may need a large series of SQL statements, or In the process of writing SQL statements, we need to set the values ​​of some variables. At this time, it is completely necessary for us to write a stored procedure.

Writing stored procedures is not a simple matter, but using stored procedures can simplify operations and reduce redundant operating steps. At the same time, it can also reduce errors during operations and improve efficiency, so it should be done as much as possible Learn to use stored procedures.

The following mainly introduces how to create a stored procedure.

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, 4a82531f3fde107146fdd03610e22e4b is the parameter name, and 30690cee1a11d5dfbdced93b89f678ee 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 name of the parameter 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 regard the parameter name as a column name, causing an error. Predictable 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.

Example:

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

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

The result shows that the ShowStuScore stored procedure has been created successfully.

For more programming-related knowledge, please visit: Introduction to Programming! !

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