Mysql has stored procedures. Stored procedures are a set of SQL statements designed to complete specific functions. MySQL version 5.0 did not support stored procedures before, but it supports stored procedures starting from version 5.0. This not only improves the processing speed of the database, but also improves the flexibility of database programming. sex. Stored procedures can be used to convert data, migrate data, and create reports. It is similar to a programming language. Once executed successfully, it can be called at any time to complete specified functional operations.
The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.
Mysql has stored procedures.
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 Converting data, migrating data, and making reports is similar to a programming language. 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#.
Writing stored procedures requires slightly higher requirements for developers, but this does not affect the common use of stored procedures, because stored procedures have the following advantages:
1) Encapsulation
Usually multiple SQL statements are needed to complete a logical function, and parameters are likely to be passed between each statement. Therefore, writing logical functions is relatively complicated, and stored procedures can Contain these SQL statements into an independent unit so that the outside world cannot see the complex SQL statements and only need simple calls to achieve the purpose. And database professionals can modify the stored procedure at any time without affecting the source code of the application that calls it.
2) Can enhance the function and flexibility of SQL statements
Stored procedures can be written using flow control statements, which have strong flexibility and can complete complex judgments and more complex operations.
3) Can reduce network traffic
Because the stored procedure is run on the server side and the execution speed is fast, when the stored procedure is called on the client computer, Only the call statement is transmitted over the network, thus reducing network load.
4) High performance
When the stored procedure is successfully compiled, it is stored in the database server, and the client can call it directly in the future, so that all SQL statements Will be executed from the server, thus improving performance. However, it should be noted that the more stored procedures, the better. Excessive use of stored procedures will actually affect system performance.
5) Improve database security and data integrity
One way to improve the security of stored procedures is to use them as intermediate components. Stored procedures can Some tables perform related operations, and then the stored procedures are provided as interfaces to external programs. In this way, external programs cannot directly operate database tables and can only operate corresponding tables through stored procedures. Therefore, security can be improved to a certain extent.
6) Make data independent
The independence of data can achieve the effect of decoupling, that is to say, the program can call stored procedures to replace the execution of multiple SQL statements. statement. In this case, the stored procedure isolates the data from the user. The advantage is that when the structure of the data table changes, there is no need to modify the program when calling the table. The database administrator only needs to rewrite the stored procedure.
MySQL Stored Procedure Example
We will show you step by step how to use CREATE PROCEDURE to develop your first MySQL stored procedure. Additionally, we will show you how to call stored procedures from SQL statements.
Writing the first MySQL stored procedure
We will develop a simple stored procedure to GetAllProducts() to help you become familiar with the syntax. GetAllProducts() stored procedure selects all products from the products table.
Start the mysql client tool and type the following command:
DELIMITER // CREATE PROCEDURE GetAllProducts() BEGIN SELECT productCode, productName FROM products; END // DELIMITER ;
Execute the command:
mysql> DELIMITER // mysql> CREATE PROCEDURE GetAllProducts() -> BEGIN -> SELECT productCode, productName FROM products; -> END // Query OK, 0 rows affected (0.02 sec) mysql> DELIMITER ;
Let us examine the stored procedure in more detail:
The first command isDELIMITER //, which has nothing to do with stored procedure syntax. The DELIMITER statement changes the standard delimiter (semicolon (;) to another semicolon). In this case, the delimiter changes from a semicolon (;) to a double slash //. Why do we need to change the delimiter? Because we want to pass the stored procedure to the server as a whole instead of having the mysql tool interpret each statement at once. After the END keyword, we use the delimiter // to indicate the end of the stored procedure. The last command (DELIMITER; ) changes the delimiter back to a semicolon (;).
We use the CREATE PROCEDURE statement to create a new stored procedure. We specify the name of the stored procedure after the CREATE PROCEDURE statement. In this case, the name of the stored procedure is GetAllProducts. We put parentheses after the name of the stored procedure.
The part between BEGIN and END is called the body of the stored procedure. You place declarative SQL statements in the body to handle business logic. In this stored procedure, we use a simple SELECT statement to query the data in the products table.
View stored procedures
We can use the following command to view which stored procedures are in the specified database:
select name from mysql.proc where db='mysqldemo';
Run Result:
Calling the stored procedure
To call the stored procedure, use the following SQL command:
CALL stored_procedure_name();
You use the CALL statement to call a stored procedure. For example, to call the GetAllProducts() stored procedure, use the following statement:
CALL GetAllProducts();
If you execute the above statement, you will get all the products in the products table.
[Related recommendations:mysql video tutorial]
The above is the detailed content of Does mysql have stored procedures?. For more information, please follow other related articles on the PHP Chinese website!
