存储过程（Procedure）

一、创建

create procedure 存储过程名(参数模式 参数名 参数类型)
begin
		存储过程体
end

注意：
1.参数模式：in、out、inout，其中in可以省略

  • in：该参数可以作为输入，也就是该参数需要调用方传入值
  • out：该参数可以作为输出，也就是该参数可以作为返回值
  • inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

2.存储过程体的每一条sql语句都需要用分号结尾
3.如果存储过程体仅仅只有一句话，begin end可以省略
4.存储过程的结尾可以使用 delimiter 重新设置

二、调用

call 存储过程名(实参列表)

举例：
调用in模式的参数：call sp1（‘值’）;
调用out模式的参数：set @name; call sp1(@name);select @name;
调用inout模式的参数：set @name=值; call sp1(@name); select @name;
三、查看

show create procedure 存储过程名;
SHOW CREATE PROCEDURE  myp2;

四、删除

drop procedure 存储过程名;
DROP PROCEDURE p1;

五、举例
例：插入到admin表中五条记录

#空参情况
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
	INSERT INTO admin(username,`password`) 
	VALUES('john1','0000'),
		  ('lily','0000'),
		  ('rose','0000'),
		  ('jack','0000'),
		  ('tom','0000');
END $

#调用
CALL myp1()$

例：创建存储过程实现 根据女神名，查询对应的男神信息

#带in模式参数
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
	SELECT bo.*
	FROM boys bo
	RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.name=beautyName;
	

END $

#调用
CALL myp2('柳岩')$

例：根据输入的女神名，返回对应的男神名

#带out模式参数
CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
	SELECT bo.boyname INTO boyname
	FROM boys bo
	RIGHT JOIN
	beauty b ON b.boyfriend_id = bo.id
	WHERE b.name=beautyName ;
	
END $

例：传入a和b两个值，最终a和b都翻倍并返回

#带inout模式参数
CREATE PROCEDURE myp8(INOUT a INT ,INOUT b INT)
BEGIN
	SET a=a*2;
	SET b=b*2;
END $

#调用
SET @m=10$
SET @n=20$
CALL myp8(@m,@n)$
SELECT @m,@n$

函数（Function）

  • 函数体中仅有一句话，则可以省略begin end
  • 使用 delimiter语句设置结束标记

一、创建

create function 函数名(参数名 参数类型) returns  返回类型
begin
	函数体
end

注意：函数体中肯定需要有return语句
二、调用

select 函数名(实参列表);

三、查看

show create function 函数名;
SHOW CREATE FUNCTION myf3;

四、删除

drop function 函数名；
DROP FUNCTION myf3;

五、举例
1.无参有返回
例：返回公司的员工个数

CREATE FUNCTION myf1() RETURNS INT
BEGIN

	DECLARE c INT DEFAULT 0;#定义局部变量
	SELECT COUNT(*) INTO c#赋值
	FROM employees;
	RETURN c;
	
END $

SELECT myf1()$

2.有参有返回
例：根据员工名，返回它的工资

CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	SET @sal=0;#定义用户变量 
	SELECT salary INTO @sal   #赋值
	FROM employees
	WHERE last_name = empName;
	
	RETURN @sal;
END $

SELECT myf2('k_ing') $

例：根据部门名，返回该部门的平均工资

CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	DECLARE sal DOUBLE ;
	SELECT AVG(salary) INTO sal
	FROM employees e
	JOIN departments d ON e.department_id = d.department_id
	WHERE d.department_name=deptName;
	RETURN sal;
END $

SELECT myf3('IT')$

例：创建函数，实现传入两个float，返回二者之和

CREATE FUNCTION test_fun1(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
	DECLARE SUM FLOAT DEFAULT 0;
	SET SUM=num1+num2;
	RETURN SUM;
END $

SELECT test_fun1(1,2)$

函数和存储过程的区别

关键字

调用语法

返回值

应用场景

函数

FUNCTION

SELECT 函数()

只能是一个

一般用于查询结果为一个值并返回时，当有返回值而且仅仅一个

存储过程

PROCEDURE

CALL 存储过程()

可以有0个或多个

一般用于更新