What is the difference between mysql stored procedures and stored functions?

Difference: 1. Storage functions have more restrictions, while stored procedures have fewer restrictions; 2. The implementation functions of stored procedures are more complex, while the implementation functions of storage functions are more targeted; 3. Storage The function must have a return value, but the stored procedure can have no return value; 4. Differences in calling; 5. Differences in parameters.

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

Stored procedures and stored functions are similar to methods in object-oriented programming languages, which can simplify code and improve 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 function mechanisms. For now, we will use stored procedures and stored functions. Together they are called stored procedures. Unlike general SQL statements that need to be compiled first and then executed immediately, a stored program is a set of SQL statements designed to complete a specific function. It is compiled and stored in the database. When the user specifies the name of the stored program and gives the parameters (if the The stored procedure will be executed only when called with parameters).

A stored program is a collection of one or more SQL statements and control statements. We can think of it as a MySQL batch file. Of course, its role is not limited to batch processing. Stored procedures are useful when you want a program to perform the same function across different applications or platforms, or to encapsulate specific functionality. Stored procedures in a database can be viewed as an object-oriented approach to object-oriented programming, which allows control over how data is accessed.

The differences between stored functions and stored procedures are as follows:

(1) Storage functions have many restrictions. For example, temporary tables cannot be used, only tables can be used. Variables, while stored procedures have fewer restrictions; the implementation functions of stored procedures are more complex, while the implementation functions of functions are more targeted.

(2) The return values ​​are different. Stored functions must have a return value, and only return one result value; stored procedures can have no return value, but can return result sets (out, inout).

(3) Differences in calling. Stored functions are embedded in SQL and can store function names (variable values) in select; stored procedures are called through the call statement and call stored procedure names.

(4) Difference in parameters. The parameter types of stored functions are similar to IN parameters, and there are no parameters similar to OUT and INOUT. There are three parameter types of stored procedures, IN, out and INOUT:

a. in: Data is only passed from the outside for internal use (value transfer), it can be a numerical value or a variable

b. out: only allowed for internal use of the process (no external data is used), for external use (passing by reference: external data will be cleared first before entering the inside), it can only be the variable

　c. inout : The external can be used internally, and the internally modified can also be used externally. Typical reference transfer can only transfer variables.

[Related recommendations: mysql video tutorial]

The above is the detailed content of What is the difference between mysql stored procedures and stored functions?. For more information, please follow other related articles on the PHP Chinese website!