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

This article mainly introduces the example analysis of stored procedures in mysql, which has a certain reference value. Interested friends can refer to it. I hope you will gain a lot after reading this article.

In mysql, a stored procedure is a collection of SQL statements that perform specific functions. The purpose of using stored procedures is to write common or complex work in advance in SQL statements and store them with a specified name. This process is compiled and optimized and stored in the database server, so it is called stored procedures.

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

A stored procedure (Stored Procedure) is a database object that stores complex programs in a database for external programs to call.

A stored procedure is a collection of SQL statements that perform specific functions. The purpose of using stored procedures is to pre-write common or complex work in SQL statements and store them with a specified name. This process 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 do it automatically.

The SQL statements commonly used to manipulate the database need to be compiled and then executed. Stored procedures execute SQL statements in a different way.

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

Stored procedures were not supported before MySQL version 5. 0, 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 procedure is an important function in database, which can be used to transform data, migrate data and make reports. It is similar to programming language. Once executed successfully, it can be called at any time to complete the specified functional operation.

The use of 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 logic function. It's just a simple call, which can be called by programming languages such as Java and C #.

Create a stored procedure

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 score information from the student score 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)

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

Delete stored procedure

After the stored procedure is created, it is 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.

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

DROP PROCEDURE [IF EXISTS]

The syntax is as follows:

Procedure name: specifies the name of the stored procedure to delete.

IF EXISTS: specify this keyword to prevent errors caused by deleting stored procedures that do not exist.

Note: there is no parameter list or parentheses after the stored procedure name, and you must verify that the stored procedure does not have any dependencies before deleting it, otherwise it will cause other stored procedures associated with it to fail to run.

Example

The following delete the stored procedure ShowStuScore,SQL statement and run the result as follows:

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

After deletion, you can confirm whether the above deletion is successful by querying the routines table under the information_schema database. The SQL statement and the run result are as follows:

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

The results show that no records are queried, indicating that the stored procedure ShowStuScore has been deleted.

Thank you for reading this article carefully. I hope the article "sample Analysis of stored procedures in mysql" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.