Shulou(Shulou.com)06/01 Report--

Most people do not understand the knowledge points of this article, "what is the difference between MySQL stored functions and stored procedures?", so the editor summarizes the following, detailed contents, clear steps, and a certain reference value. I hope you can get something after reading this article. Let's take a look at this "what is the difference between MySQL stored functions and stored procedures".

MySQL stores functions (custom functions), which are generally used to calculate and return a value, and can write frequently used calculations or functions as a function.

Stored functions, like stored procedures, are collections of SQL statements that are defined in the database.

The difference between stored functions and stored procedures

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

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

3. Statements in stored procedures are more powerful, stored procedures can implement very complex business logic, and functions have many limitations, such as not using statements such as insert,update,delete,create in functions.

4. The storage function only completes the query and can accept the input parameters and return a result, that is, the function is more targeted.

5. A stored procedure can call a stored function, but a function cannot call a stored procedure.

6. Stored procedures are typically executed as a separate part (call calls). The function can be called as part of the query statement.

Create function func_name ([param_name type [,...]]) returns type [characteristic...] Begin routine_bodyend

Parameter description:

(1) func_name: the name of the storage function.

(2) param_name type: optionally, specify 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: specifies the type of return value.

(4) characteristic: optionally, specify the characteristics of the storage function.

(5) the content of routine_body:SQL code.

Create database mydb9_function;-- imports test data use mydb9_function;set global log_bin_trust_function_creators=TRUE;-the creator of the trust subroutine-creates the storage function-does not enter the input parameter drop function if exists myfunc1_emp; delimiter $$create function myfunc1_emp () returns intbegin declare cnt int default 0; select count (*) into cnt from emp; return cnt;end $$delimiter;-- calls the storage function select myfunc1_emp () -- create a stored procedure with input parameters drop function if exists myfunc2_emp;delimiter $$create function myfunc2_emp (in_empno int) returns varchar (50) begin declare out_name varchar (50); select ename into out_name from emp where empno = in_empno; return out_name;end $$delimiter; select myfunc2_emp (1008) The above is the content of this article on "what is the difference between MySQL stored functions and stored procedures". I believe we all have a certain understanding. I hope the content shared by the editor will be helpful to you. If you want to know more related knowledge, please 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.