MySQL中函数、存储过程的创建、删除与查看

创建函数

DELIMITER $   //设置$为结束标识
CREATE FUNCTION 方法名() 返回值类型
BEGIN
	//函数体...
END $

示例1：无参返回，返回emp表中数据的个数

DELIMITER $
CREATE FUNCTION getCount() RETURNS INT
BEGIN
	SET @a = 0;    #定义变量
	SELECT COUNT(*) INTO @a #查询结果值给到变量
	FROM emp;
	RETURN @a;   #返回变量值
END $

示例2：有参返回，输入部门编号，返回部门平均工资

DELIMITER $
CREATE FUNCTION avgSalary(id INT) RETURNS DOUBLE
BEGIN
	SET @x = 0; #定义变量
	SELECT AVG(salary) INTO @x #查询结果值给到变量
	FROM emp
	GROUP BY deparid
	HAVING deparid = id;
	RETURN @x;  #返回变量值
END $

使用函数

SELECT 函数名(参数列表);

示例

show getCount();
show avgSalary(1);

查看函数

//查看所有函数
SHOW FUNCTION STATUS; 
//查看指定
SHOW CREATE FUNCTION 函数名;

示例

show CREATE FUNCTION getCount;
show CREATE FUNCTION avgSalary;

删除函数

DROP FUNCTION 函数名;

示例

DROP FUNCTION getCount;
DROP FUNCTION avgSalary;

创建存储过程

DELIMITER $
CREATE PROCEDURE 名称(参数)
BEGIN
	//存储过程体...
END $

示例1：无参无返回值，批量插入数据

DELIMITER $
CREATE PROCEDURE insertAdmin()
BEGIN
	INSERT INTO admin VALUES
	(1,'Tom',15,160),
	(2,'Tom',15,1580),
	(3,'Tom',19,1586),
	(4,'Tom',15,15860);
END $

示例2：有参无返回值，通过女孩的名字查询id对应的男孩

DELIMITER $
CREATE PROCEDURE selectBoyGirl(IN `name` VARCHAR(8))
BEGIN
	SELECT boys.*
	FROM girls
	JOIN boys
	ON girls.`id`=boys.`id`
	WHERE `name`=girls.`name`;
END $

示例3：有参有返回值，通过女孩的名字查询id对应的男孩，并返回男孩名字。

IN：代表输入参数
OUT：代表返回值

CREATE PROCEDURE selectBoyGirl2(IN `name` VARCHAR(8),OUT boyName VARCHAR(8))
BEGIN
	SELECT boys.`name` INTO boyName
	FROM boys
	INNER JOIN girls
	ON boys.`id`=girls.`id`
	WHERE `name`=girls.`name`;
END $

调用存储过程

CALL 存储过程名称(参数);

示例

CALL insertAdmin();

CALL selectBoyGirl('苍老师');

SET @bName; //先定义变量
CALL selectBoyGirl2('苍老师',@bName);

删除存储过程

DROP PROCEDURE 名称;

示例

DROP PROCEDURE insertAdmin;
DROP PROCEDURE selectBoyGirl;
DROP PROCEDURE selectBoyGirl2;

查看存储过程

//查看所有存储过程
SHOW PROCEDURE STATUS;
//查看指定
SHOW CREATE PROCEDURE 名称;

示例

SHOW CREATE PROCEDURE insertAdmin;
SHOW CREATE PROCEDURE selectBoyGirl;
SHOW CREATE PROCEDURE selectBoyGirl2;

函数与存储过程的对比

存储过程：返回0个或多个，适合做批量插入，更新。
调用使用CALL

函数：有且仅有一个返回，适合做数据处理返回一个结果
调用使用SELECT

都使用DELIMITER设置结尾$