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

Difference: 1. Stored functions have many restrictions, while stored procedures have relatively few restrictions; 2. Stored functions will return one and only one result value to the caller, while stored procedures will return one or Multiple result sets; 3. There are three parameter types for stored procedures, and only one parameter type for stored functions.

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

What is a stored procedure?

Stored procedure is a set of SQL statements in large database systems that are used to complete specific functions. They are stored in the database. After being compiled for the first time, they do not need to be compiled again when called again. The user executes the stored procedure by specifying its name and parameters (if the stored procedure has parameters). Stored procedures are an important object in the database, and any well-designed database application should use stored procedures.

Advantages:
(1) Reuse: Stored procedures can be reused, which can reduce the workload of developers.
(2) Improve efficiency: The stored procedure will be compiled the first time it is used. Once compiled once, it does not need to be compiled again, which improves efficiency.
(3) Reduce network traffic: The stored procedure is located on the server. When calling, you only need to pass the name and parameters of the stored procedure, thus reducing the amount of data transmitted over 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 troubles
(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 stored function?

Stored functions are a set of stored procedures, with function names, parameters, and return a result set. Stored functions are similar in structure to stored procedures, but must have a return clause to return results.

Code:

CREATE DEFINER=`root`@`%` FUNCTION `queryChildrenAreaInfo`(areaId INT) RETURNS varchar(4000) CHARSET utf8
BEGIN
	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. Overview

Stored procedures and stored functions are collectively called stored routines. The syntax of the two is very similar, but they have different contents.

There are many restrictions on storage functions. For example, temporary tables cannot be used, only table variables can be used. There are also some functions that are not available and so on.

The restrictions on stored procedures are relatively few, and the functions to be implemented are more complex.

2. Differences in return values

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

The stored procedure will return one or more result sets (functions cannot do this), or just to achieve some effect or action without returning results.

3. Different calling methods
The stored function is embedded in sql and can be called in select, just like the built-in functions, such as cos(), sin() .

4. Differences in parameters

The parameter types of stored functions are similar to IN parameters

There are three parameter types of stored procedures: IN parameters, OUT Parameters, INOUT parameters

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

out: only allowed for internal use of the process (not used) External data), for external use (reference transfer: external data will be cleared first before entering the internal), can only be the variable

inout: external can be used internally, and internally modified can also be given For external use, typical reference transfer can only pass variables.

Stored procedures are a collection of user-defined SQL statements. Users can call stored procedures to design tasks for specific tables or other objects.

The function is usually a method defined by the database, which receives parameters and returns a certain type of value and does not involve a specific user table.

[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!