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

In this article Xiaobian for you to introduce in detail the "mysql stored procedures and stored functions what are the differences", detailed content, clear steps, details handled properly, I hope that this "mysql stored procedures and stored functions what are the differences" article can help you solve doubts, following the editor's ideas slowly in depth, together to learn new knowledge.

Differences: 1, the restrictions of the stored function are more, while the restrictions of the stored procedure are relatively less; 2, the stored function will return one and only one result value to the caller, and the stored procedure will return one or more result sets; 3, there are three parameter types of stored procedures and only one parameter type of stored functions.

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

What is a stored procedure?

A stored procedure is a set of SQL statements that are stored in the database in order to complete a specific function in a large database system. It is called again after the first compilation and does not need to be compiled again. The user executes it by specifying the name and parameters of the stored procedure (if the stored procedure has parameters). Stored procedure is an important object in database, and any well-designed database application should use stored procedure.

Advantages:

(1) reuse: stored procedures can be reused to reduce the workload of developers.

(2) improve efficiency: stored procedures are compiled when they are used for the first time, and do not need to be compiled again after one compilation, which improves the efficiency.

(3) reduce network traffic: the stored procedure is located on the server, and only the name and parameters of the stored procedure need to be passed when called, thus reducing the amount of data transmitted through the network.

(4) Security: parameterized stored procedures can prevent SQL injection, and Grant, Deny, and Revoke permissions can be applied to stored procedures.

Disadvantages:

(1) debugging trouble

(2) poor portability

(3) poor maintainability

Code:

CREATE DEFINER= `root` @ `% `PROCEDURE `getName` (IN `uid` int,OUT my_uname VARCHAR (255)) BEGIN # Routine body goes here...select admin_name into my_uname from admin_user where id=uid; select my_uname;END

What is a storage function?

The stored function is a set of stored procedures with function names, parameters, and returns a result set. Stored functions and stored procedures have a similar structure, but there must be a return clause to return the result.

Code:

CREATE DEFINER= `root` @ `% `queryChildrenAreaInfo` (areaId INT) RETURNS varchar (4000) CHARSET utf8BEGIN DECLARE sTemp VARCHAR (4000); DECLARE sTempChd VARCHAR (4000); SET sTemp='$'; SET sTempChd = CAST (areaId AS CHAR); WHILE sTempChd IS NOT NULL DO SET sTemp= CONCAT (sTemp,',',sTempChd); SELECT GROUP_CONCAT (id) INTO sTempChd FROM personal_dept WHERE FIND_IN_SET (dept_parentId,sTempChd) > 0 END WHILE; RETURN sTemp;END

The difference between stored procedures and stored functions

1. Summary

Stored procedures and stored functions are collectively called stored routines. The grammar of the two is very similar, but it is different in content.

There are many restrictions on storage functions, such as not using temporary tables, but only table variables. There are some functions that are not available, and so on.

On the other hand, the restrictions of stored procedures are relatively less, and the functions to be implemented are more complex.

2. The difference in the return value

The stored function returns one and only one result value to the caller.

The stored procedure will return one or more result sets (which the function cannot do), or simply to achieve an effect or action without returning a result.

3. Different ways of calling

Stored functions that are embedded in sql can be called in select, just like built-in functions, such as cos () and sin ().

4. Different parameters

The parameter type of the storage function is similar to the IN parameter

There are three parameter types of stored procedures: IN parameter, OUT parameter, and INOUT parameter.

In: data is only passed in from outside for internal use (value passing), which can be numeric or variable

Out: only allow internal use of the procedure (no external data), for external use (reference passing: external data will be emptied before entering the internal), can only be variables

Inout: external can be used internally, internal modifications can also be used externally, typical references are passed, only variables are passed.

A stored procedure is a collection of user-defined sql statements that design a specific table or other object, and the user can call the stored procedure.

A function, on the other hand, is usually a method defined by the database, which takes parameters and returns a certain type of value and does not involve a specific user table.

After reading this, the article "what is the difference between mysql stored procedures and stored functions" has been introduced. If you want to master the knowledge points of this article, you still need to practice and use it yourself. If you want to know more about related articles, welcome to follow the industry information channel.

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.