Related explanations of Mysql functions

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

1. Function syntax

1 . Create

DELIMITER $
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
	函数体
END$
DELIMITER ;

Note:

• The parameter list contains two parts: Parameter name Parameter type

• ## The return value type must be declared at the beginning of #.

• There must be a

  return statement in the function body, otherwise an error will be reported

• Use the delimiter statement to set the end mark

----------

2. Call the

SELECT 函数名(参数列表)

function. Since the function has a return value, you can use select. Print out this value.

==========

2. Case demonstration

1. Return function without parameters

Case: return student table The total number

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. There are parameters and returns

Case: Return scores based on student name

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('盖伦');

==========

3. View function

SHOW CREATE FUNCTION myFun1;

4. Delete functions

DROP FUNCTION myFun2;

This article explains the relevant content of Mysql functions. For more related recommendations, please pay attention to the PHP Chinese website.

Related recommendations:

SQLLite related content

##Spark SQL implements log offline batch processing

sql Compare the time difference between two adjacent records

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!