Introduction to MySQL Stored Procedures

最新推荐文章于 2022-02-15 19:42:34 发布

kezhen

最新推荐文章于 2022-02-15 19:42:34 发布

阅读量1.4k

点赞数

分类专栏： MySql相关

MySql相关专栏收录该内容

14 篇文章 0 订阅

订阅专栏

http://www.mysqltutorial.org/introduction-to-sql-stored-procedures.aspx

Definition of stored procedures

A stored procedure is a segment of declarative SQL statements stored inside the database catalog. A stored procedure can be invoked by triggers, other stored procedures or applications such as Java, C#, PHP, etc.

存储过程是存储在数据库目录中的SQL声明片段。存储过程可以被触发器触发，其他存储过程或者应用程序，如Java、C#、PHP等等

A stored procedure that calls itself is known as a recursive stored procedure. Most database management system supports recursive stored procedures. However MySQL does not support it verywell. You should check your version of MySQL database before implementing recursive stored procedures in MySQL.

Stored Procedures in MySQL

MySQL is known as the most popular open source RDBMS which is widely used by both community and enterprise. However, during the first decade of its existence, it did not support stored procedures, stored functions, triggers and events. Since MySQL version 5.0, those features were added to MySQL database engine to make it more flexible and powerful.

MySQL stored procedures advantages

Typically stored procedures help increase the performance of the applications. Once created, stored procedures are compiled and stored in the database. However MySQL implements the stored procedures slightly different. MySQL stored procedures are compiled on demand. After compiling a stored procedure, MySQL puts it to a cache. And MySQL maintains its own stored procedure cache for every single connection. If an application uses a stored procedure multiple times in a single connection, the compiled version is used, otherwise the stored procedure works like a query.
通常存储过程可以提升应用程序的性能。存储过程一旦创建，就会编译并存储在数据库中。但是Mysql实现的存储过程略有不同。Mysql的存储过程是按需编译的。存储过程编译完后，Mysql将其放在缓存中。Mysql对每个连接维持自己的存储过程缓存。如果应用程序在一次连接中多次使用一个存储过程，那么编译的版本会被使用，否则存储过程跟查询的效果一样。
Stored procedures helps reduce the traffic between application and database server because instead of sending multiple lengthy SQL statements, the application has to send only name and parameters of the stored procedure.
存储过程有助于减少应用程序和数据库之间的拥塞，因为应用程序仅发送存储过程的名字和参数，而不再是冗长的SQL语句。
Stored procedures are reusable and transparent to any applications. Stored procedures expose the database interface to all applications so that developers don’t have to develop functions that are already supported in stored procedures.
存储过程对任何应用程序都是可复用和透明的。存储过程将数据库接口暴露给所有的应用程序，所以开发者不需要再次开发在存储过程中已支持的函数。
Stored procedures are secure. Database administrator can grant appropriate permissions to applications that access stored procedures in the database without giving any permission on the underlying database tables.
存储过程较为安全。数据库管理员可以授予应用程序合适的权限访问数据库中的存储过程，而不用给数据库表访问的权限。

Besides those advantages, stored procedures have their own disadvantages, which you should be aware of before using the store procedures.

MySQL stored procedures disadvantages

If you use a lot of stored procedures, the memory usage of every connection that is using those stored procedures will increase substantially. In addition, if you overuse a large number of logical operations inside store procedures, the CPU usage will also increase because database server is not well-designed for logical operations.
如果使用大量的存储过程，每次使用这些存储过程连接的内存使用量会增加。另外，如果在存储过程中大量使用逻辑操作符，CPU使用量会增加，因为数据库对逻辑操作的支持较差。
A constructs of stored procedures make it more difficult to develop stored procedures that have complicated business logic.
存储过程的构造使其对复杂的业务逻辑较难开发出合适的存储过程。
It is difficult to debug stored procedures. Only few database management systems allow you to debug stored procedures. Unfortunately, MySQL does not provide facilities for debugging stored procedures.
不能调试存储过程。只有少数的数据库管理系统可以调试存储过程。而Mysql不提供调试存储过程的功能。
It is not easy to develop and maintain stored procedures. Developing and maintaining stored procedures are often required specialized skill set that not all application developers possess. This may lead to problems in both application development and maintenance phases.
不易开发和维护存储过程。开发和维护存储过程需要特殊技巧，并不是所有的开发都掌握的。这可能在程序开发和维护阶段导致问题。

MySQL stored procedures have their own advantages and disadvantages. When you develop applications, you should decide whether you should or should not use stored procedure based on thebusiness requirements.

In the following tutorials, we will show you how to leverage MySQL stored procedures in your database programming tasks with many practical examples.