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

This article mainly introduces the relevant knowledge of "how to create mysql stored procedures". The editor shows you the operation process through an actual case. The operation method is simple, fast and practical. I hope this article "how to create mysql stored procedures" can help you solve the problem.

In mysql, you can use the "CREATE PROCEDURE" statement to create a stored procedure with the syntax of "CREATE PROCEDURE procedure name, procedure parameter body, procedure parameter format [IN | OUT | INOUT] parameter name type."

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

What is the syntax of mysql stored procedure

SQL statements need to be compiled and then executed, and a stored procedure (Stored Procedure) is a set of SQL statements that are compiled and stored in the database. The user invokes and executes the stored procedure by specifying the name of the stored procedure and giving parameters (if the stored procedure has parameters).

Stored procedures are programmable functions that are created and saved in the database and can be composed of SQL statements and control structures. Stored procedures are useful when you want to execute the same function on different applications or platforms, or to encapsulate specific functions. The stored procedure in the database can be seen as a simulation of the object-oriented method in programming, which allows you to control how the data is accessed.

Advantages of stored procedures:

(1)。 Enhance the function and flexibility of SQL language: stored procedures can be written with control statements, have strong flexibility, and can complete complex judgments and more complex operations.

(2)。 Standard component programming: after a stored procedure is created, it can be called many times in the program without having to rewrite the SQL statement of the stored procedure. And database professionals can modify the stored procedure at any time, which has no effect on the application source code.

(3)。 Faster execution: if an operation contains a large amount of Transaction-SQL code or is executed multiple times separately, the stored procedure executes much faster than batch processing. Because the stored procedure is precompiled. When a stored procedure is run for the first time, the optimizer analyzes and optimizes it, and gives the execution plan that is eventually stored in the system table. Batch Transaction-SQL statements are compiled and optimized each time they are run, which is relatively slow.

(4)。 Reduce network traffic: an operation (such as query, modification) against the same database object, if the Transaction-SQL statement involved in this operation is organized into a stored procedure, then when the stored procedure is called on the client computer, only the calling statement is transmitted in the network, thus greatly reducing the network traffic and reducing the network load.

(5)。 As a security mechanism to make full use of: by restricting the authority to execute a stored procedure, we can achieve the restrictions on the access to the corresponding data, avoid unauthorized users' access to the data, and ensure the security of the data.

Stored procedures for MySQL

Stored procedure is an important function of database. MySQL 5.0did not support stored procedure before, which greatly reduced the application of MySQL. Fortunately, MySQL 5.0 begins to support stored procedures, which can not only greatly improve the processing speed of the database, but also improve the flexibility of database programming.

The creation of MySQL stored procedure

Grammar

CREATE PROCEDURE procedure name ([[IN | OUT | INOUT] parameter name data type [, [IN | OUT | INOUT] parameter name data type …]]) [features.] Process body DELIMITER / / CREATE PROCEDURE myproc (OUT s int) BEGIN SELECT COUNT (*) INTO s FROM students; END / / DELIMITER

Separator

MySQL defaults to ";". If the delimiter is not declared, the compiler will treat the stored procedure as a SQL statement, so the compilation process will report an error, so declare the current segment delimiter with "DELIMITER / /" in advance, so that the compiler will not execute the code between the two "/ /" as the code of the stored procedure; "DELIMITER;" means to restore the delimiter.

Parameters.

Stored procedures may have input, output, input and output parameters as needed, and if there are multiple parameters, they may be separated by ",". The parameters of MySQL stored procedures are used in the definition of stored procedures. There are three parameter types, IN,OUT,INOUT:

The value of the IN parameter must be specified when calling the stored procedure. The value that modifies the parameter in the stored procedure cannot be returned and is the default value.

OUT: this value can be changed inside the stored procedure and can be returned

INOUT: specified when called, and can be changed and returned

Process body

The beginning and end of the process body are identified by BEGIN and END.

That's all for "how to create mysql stored procedures". Thank you for reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

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.