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

MySQL stored function (custom function). Functions are generally used to calculate and return a value. Frequently used calculations or functions can be written into a function.

Stored functions, like stored procedures, are a collection of SQL statements defined in the database.

The difference between stored functions and stored procedures

1. A stored function has and has only one return value, while a stored procedure can have multiple return values ​​or no return value.

2.Stored functions can only have input parameters, and cannot take in, while stored procedures can have multiple in, out, and inout parameters.

3. Statements in stored procedures are more powerful. Stored procedures can implement very complex business logic, while functions have many restrictions. For example, insert, update, delete, create, etc. cannot be used in functions. Statement;

4. The stored function only completes the query work, accepts input parameters and returns a result, which means that the functions implemented by the function are relatively targeted.

5. Stored procedures can call stored functions, but functions cannot call stored procedures.

6. Stored procedures are generally executed as an independent part (call). The function can be called as part of the query statement.

create function func_name ([param_name type[,...]])
returns type
[characteristic ...] 
begin
    routine_body
end;

Parameter description:
(1) func_name: The name of the stored function.
(2)param_name type: optional, specifies the parameters of the storage function. The type parameter is used to specify the parameter type of the stored function, which can be all supported types in the MySQL database.
(3) RETURNS type: Specify the type of return value.
(4) characteristic: optional, specifies the characteristics of the storage function.
(5) routine_body: SQL code content.

create database mydb9_function;
-- 导入测试数据
use mydb9_function;
set global log_bin_trust_function_creators=TRUE; -- 信任子程序的创建者
 
-- 创建存储函数-没有输输入参数
drop function if exists myfunc1_emp;
 
delimiter $$
create function myfunc1_emp() returns int
begin
  declare cnt int default 0;
    select count(*) into  cnt from emp;
  return cnt;
end $$
delimiter ;
-- 调用存储函数
select myfunc1_emp();

rrree

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