mysql存储过程和函数常用语法命令

1.概念

• 存储过程和函数是: 事先经过编译并存储在数据库中的一段SQL语句的集合

2.好处

• 可以重复使用,减轻开发人员的工作量

• 减少数据在数据库和应用服务器之间的传输，可以提高数据处理的效率

• 将一些业务逻辑在数据库层面来实现，可以减少代码层面的业务处理

3.存储过程和函数的区别

• 函数必须有返回值

• 存储过程没有返回值

4.创建存储过程

-- *创建空参存储过程
DELIMITER 分割符
	CREATE PROCEDURE 存储过程名称()
	BEGIN
		sql语句;
	-- 两处分隔符要保持一致
	END 分割符
DELIMITER;

5.调用存储过程

-- *调用存储过程
CALL 存储过程名称();

6.查看存储过程

-- *查看存储过程
SELECT * FROM mysql.proc WHERE db='数据库名称';
-- proc 指 procedure 的缩写
-- 相当于存储过程是存储在mysql.proc这个表中，这个表中有一个字段db，是当前存储过程所属的数据库

7.删除存储过程

-- *删除存储过程
DROP PROCEDURE [IF EXISTS] 存储过程名称;
-- [IF EXISTS]或写或不写

8. 存储过程语法

8.1变量的使用

-- *定义变量
DECLARE 变量名称 数据类型 [DEFAULT 默认值]
-- DECLARE定义的是局部变量 只能用在BEGIN END 范围之内

-- *变量赋值
SET 变量名称 = 值;

-- *将查询结果赋值给变量
DELIMITER $

CREATE PROCEDURE pro_test3()
BEGIN
	DECLARE men,women INT;  -- 定义变量
	SELECT SUM(score) INTO men FROM student WHERE gender='男';    -- 计算男同学总分数赋值给men
	SELECT SUM(score) INTO women FROM student WHERE gender='女';  -- 计算女同学总分数赋值给women
	SELECT men,women;           -- 查询变量
END$

DELIMITER ;

8.2 if语句的使用

-- *if语句
IF 判断条件1 THEN 执行SQL语句1;
[ELSEIF 判断条件2 THEN 执行SQL语句2;]
...
[ELSE 执行的sql语句n;]
END IF;

8.3 参数的传递

• 参数传递的语法

DELIMITER $

-- 标准语法
CREATE PROCEDURE 存储过程名称([IN|OUT|INOUT] 参数名 数据类型)
BEGIN
	执行的sql语句;
END$
/*
	IN:代表输入参数，需要由调用者传递实际数据。默认的
	OUT:代表输出参数，该参数可以作为返回值
	INOUT:代表既可以作为输入参数，也可以作为输出参数
*/
DELIMITER ;

• 输入参数

  • 标准语法

  DELIMITER $
  
  -- 标准语法
  CREATE PROCEDURE 存储过程名称(IN 参数名 数据类型)
  BEGIN
  	执行的sql语句;
  END$
  
  DELIMITER ;
  

• 输出参数

  • 标准语法

  DELIMITER $
  
  -- 标准语法
  CREATE PROCEDURE 存储过程名称(OUT 参数名 数据类型)
  BEGIN
  	执行的sql语句;
  END$
  
  DELIMITER ;
  

  • 小知识

  @变量名:  这种变量要在变量名称前面加上“@”符号，叫做用户会话变量，代表整个会话过程他都是有作用的，这个类似于全局变量一样。
  
  @@变量名: 这种在变量前加上 "@@" 符号, 叫做系统变量 
  

8.4 case语句的使用

• 标准语法1

-- 标准语法
CASE 表达式
WHEN 值1 THEN 执行sql语句1;
[WHEN 值2 THEN 执行sql语句2;]
...
[ELSE 执行sql语句n;]
END CASE;

• 标准语法2

-- 标准语法
CASE
WHEN 判断条件1 THEN 执行sql语句1;
[WHEN 判断条件2 THEN 执行sql语句2;]
...
[ELSE 执行sql语句n;]
END CASE;

8.5 while循环

• 标准语法

-- 标准语法
初始化语句;
WHILE 条件判断语句 DO
	循环体语句;
	条件控制语句;
END WHILE;

8.6 repeat循环

• 标准语法

-- 标准语法
初始化语句;
REPEAT
	循环体语句;
	条件控制语句;
	UNTIL 条件判断语句
END REPEAT;

-- 注意：repeat循环是条件满足则停止。while循环是条件满足则执行

8.7 loop循环

• 标准语法

-- 标准语法
初始化语句;
[循环名称:] LOOP
	条件判断语句
		[LEAVE 循环名称;]
	循环体语句;
	条件控制语句;
END LOOP 循环名称;

-- 注意：loop可以实现简单的循环，但是退出循环需要使用其他的语句来定义。我们可以使用leave语句完成！
--      如果不加退出循环的语句，那么就变成了死循环。

8.8 游标

• 游标的概念

  • 游标可以遍历返回的多行结果，每次拿到一整行数据
  • 在存储过程和函数中可以使用游标对结果集进行循环的处理
  • 简单来说游标就类似于集合的迭代器遍历
  • MySQL中的游标只能用在存储过程和函数中

• 游标的语法

  • 创建游标

  -- 标准语法
  DECLARE 游标名称 CURSOR FOR 查询sql语句;
  

  • 打开游标

  -- 标准语法
  OPEN 游标名称;
  

  • 使用游标获取数据

  -- 标准语法
  FETCH 游标名称 INTO 变量名1,变量名2,...;
  

  • 关闭游标

  -- 标准语法
  CLOSE 游标名称;
  

• 游标的优化使用(配合循环使用)

/*
	当游标结束后，会触发游标结束事件。我们可以通过这一特性来完成循环操作
	加标记思想：
		1.定义一个变量，默认值为0(意味着有数据)
		2.当游标结束后，将变量值改为1(意味着没有数据了)
*/
-- 1.定义一个变量，默认值为0(意味着有数据)
DECLARE flag INT DEFAULT 0;
-- 2.当游标结束后，将变量值改为1(意味着没有数据了)
DECLARE EXIT HANDLER FOR NOT FOUND SET flag = 1;

9. 存储函数

• 存储函数和存储过程是非常相似的。存储函数可以做的事情，存储过程也可以做到！

• 存储函数有返回值，存储过程没有返回值(参数的out其实也相当于是返回数据了)

• 标准语法

  • 创建存储函数

  DELIMITER $
  
  -- 标准语法
  CREATE FUNCTION 函数名称([参数 数据类型])
  RETURNS 返回值类型
  BEGIN
  	执行的sql语句;
  	RETURN 结果;
  END$
  
  DELIMITER ;
  

  • 调用存储函数

  -- 标准语法
  SELECT 函数名称(实际参数);  -- 调用存储函数，使用的是select，不是call
  

  • 删除存储函数

  -- 标准语法
  DROP FUNCTION 函数名称;