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设置结尾$