What is a stored procedure in mysql

In mysql, a stored procedure is a set of SQL statements designed to accomplish specific functions. The purpose of using stored procedures is to pre-write common or complex work with SQL statements and store them with a specified name. This procedure is compiled and optimized and stored in the database server, so it is called a stored procedure.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Stored Procedure is a database object that stores complex programs in the database so that they can be called by external programs.

A stored procedure is a set of SQL statements designed to accomplish a specific function. The purpose of using stored procedures is to pre-write common or complex work with SQL statements and store them with a specified name. This procedure is compiled and optimized and stored in the database server, so it is called a stored procedure. When you need the database to provide the same service as the defined stored procedure in the future, you only need to call "CALL stored procedure name" to automatically complete it.

SQL statements commonly used to operate the database need to be compiled first and then executed when executed. Stored procedures take another approach to executing SQL statements.

A stored procedure is a programmable function that is created and saved in the database. It generally consists of SQL statements and some special control structures. Stored procedures are particularly suitable when you want to perform the same specific function on different applications or platforms.

MySQL 5.0 version did not support stored procedures before, which greatly reduced the application of MySQL. MySQL has supported stored procedures since version 5.0, which not only improves the processing speed of the database, but also improves the flexibility of database programming.

Stored procedures are an important function in the database. Stored procedures can be used to convert data, It is similar to a programming language for data migration and report making. Once executed successfully, it can be called at any time to complete specified functional operations.

Using stored procedures can not only improve the efficiency of database access, but also improve the security of database use.

For the caller, the stored procedure encapsulates the SQL statement, and the caller does not need to consider the specific implementation process of the logical function. Just a simple call, it can be called from programming languages ​​such as Java and C#.

Create a stored procedure

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

CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>

[Procedure Parameters[,…] ] Format

[ IN | OUT | INOUT ] <参数名> <类型>

The syntax 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 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. Enter the SQL statement and The execution process is as follows.

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.

Delete stored procedures

After a stored procedure is created, it will be saved on the database server until it is deleted. When there is an obsolete stored procedure in the MySQL database, we need to delete it from the database.

Use the DROP PROCEDURE statement in MySQL to delete stored procedures that already exist in the database. The syntax format is as follows:

DROP PROCEDURE [ IF EXISTS ] <过程名>

The syntax description is as follows:

• Process name: Specify the name of the stored procedure to be deleted.

• IF EXISTS：指定这个关键字，用于防止因删除不存在的存储过程而引发的错误。

注意：存储过程名称后面没有参数列表，也没有括号，在删除之前，必须确认该存储过程没有任何依赖关系，否则会导致其他与之关联的存储过程无法运行。

实例

下面删除存储过程 ShowStuScore，SQL 语句和运行结果如下：

mysql> DROP PROCEDURE ShowStuScore;
Query OK, 0 rows affected (0.08 sec)

删除后，可以通过查询 information_schema 数据库下的 routines 表来确认上面的删除是否成功。SQL 语句和运行结果如下：

mysql> SELECT * FROM information_schema.routines WHERE routine_name='ShowStuScore';
Empty set (0.03 sec)

结果显示，没有查询出任何记录，说明存储过程 ShowStuScore 已经被删除了。

（推荐教程：mysql视频教程）

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