Related explanations of Mysql functions-Mysql Tutorial-php.cn

Related explanations of Mysql functions

jacklove

Release： 2018-06-14 16:37:24

Original

1784 people have browsed it

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 ;

Copy after login

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 函数名(参数列表)

Copy after login

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 ;

Copy after login

After defining it, you need to execute the following to compile

1.2 Call the function

SELECT  myFun1();

Copy after login

----------

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 ;

Copy after login

2.2 Call

SELECT  myFun2(&#39;盖伦&#39;);

Copy after login

==========

3. View function

SHOW CREATE FUNCTION myFun1;

Copy after login

4. Delete functions

DROP FUNCTION myFun2;

Copy after login

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!