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

This article mainly explains "what are the differences between mysql storage procedures and storage functions". The explanation in this article is simple and clear, easy to learn and understand. Please follow the ideas of Xiaobian and go deep into it slowly to study and learn "what are the differences between mysql storage procedures and storage functions" together!

Differences: 1, the storage function has more restrictions, and the storage procedure has fewer restrictions;2, the implementation function of the stored procedure is more complex, and the implementation function of the stored function is more targeted;3, the stored function must have a return value, and the stored procedure may not have a return value;4, the call is different;5, the parameters are different.

Operating environment of this tutorial: Windows 7 system, MySQL8 version, Dell G3 computer.

Stored procedures and stored functions are similar to methods in object-oriented programming languages, simplifying code and improving code reusability. This article mainly introduces how to create stored procedures and stored functions, as well as the use, modification, deletion and other operations of stored procedures and functions.

Stored Procedures and Stored Functions

MySQL provides stored procedures and stored functions mechanism, we will store procedures and stored functions collectively referred to as stored procedures. Unlike SQL statements that need to be compiled and executed immediately, a stored program is a set of SQL statements that are compiled to perform a specific function, stored in a database, and executed when invoked by the user by specifying the name of the stored program and giving parameters (if the stored program has parameters).

A stored program is a collection of one or more SQL statements and control statements. We can think of it as a batch file for MySQL, but its role is not limited to batch processing. Storing programs is useful when you want to perform the same function on different applications or platforms, a piece of program, or encapsulate specific functionality. Stored programs in databases can be thought of as object-oriented approaches to programming that allow control over how data is accessed.

Stored functions differ from stored procedures in the following ways:

(1) There are many restrictions on storage functions, for example, temporary tables cannot be used, only table variables can be used, and storage procedures have fewer restrictions; the implementation function of storage procedures is more complex, and the implementation function of functions is more targeted.

(2) The return value is different. Stored functions must have a return value and return only one result value; stored procedures can have no return value but return a result set (out, in out).

(3) The difference when calling. Stored functions are embedded in SQL and can store function names (variable values) in select; stored procedures call stored procedure names via call statements.

(4) Different parameters. The parameter type of the stored function is similar to the IN parameter, and there are no parameters like OUT and INOUT. There are three parameter types for stored procedures, IN, OUT, and INOUT:

a. in: data is only passed from outside to inside (value transfer), it can be a value or a variable

b. out: only allowed for internal use (no external data), for external use (reference transfer: external data will be cleared before entering the internal), can only be variables

c. Inout: Externals can be used internally, internally modified can also be used externally, typical reference passing, can only pass variables.

Thank you for reading, the above is "what is the difference between mysql storage procedures and storage functions" content, after the study of this article, I believe that we have a deeper understanding of the difference between mysql storage procedures and storage functions, the specific use of the situation also needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

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.