Meaning: A set of pre-compiled SQL statements, which can be understood as batch statements
Function:
Improve code reusability
Simplify operations
Reduce the number of compilations and connections to the database server, improving efficiency
The difference between stored procedures:
Stored procedures: There can be 0 returns or multiple returns, suitable for Batch insert, batch update
Function: There is only one return, suitable for processing data and returning a result
DELIMITER $ CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型 BEGIN 函数体 END$ DELIMITER ;
Note:
The parameter list contains two parts: Parameter name Parameter type
return statement in the function body, otherwise an error will be reported
SELECT 函数名(参数列表)
1.1 Create the function
DELIMITER $ CREATE FUNCTION myFun1() RETURNS INT BEGIN DECLARE num INT DEFAULT 0; #定义一个变量 SELECT COUNT(*) INTO num #赋值 FROM student; RETURN num; #返回值 END $ DELIMITER ;After defining it, you need to execute the following to compile
1.2 Call the function
SELECT myFun1();
2.1 Create function
DELIMITER $ CREATE FUNCTION myFun2(stuName VARCHAR(20)) RETURNS INT BEGIN DECLARE grade INT DEFAULT 0; #定义变量 SELECT s.grade INTO grade #赋值 FROM student s WHERE s.name = stuName; RETURN grade; #返回 END $ DELIMITER ;
2.2 Call
SELECT myFun2('盖伦');
SHOW CREATE FUNCTION myFun1;
DROP FUNCTION myFun2;
The above is the detailed content of Related explanations of Mysql functions. For more information, please follow other related articles on the PHP Chinese website!