MySQL存储过程

使用MySQL存储过程能够减少数据库连接， 加速数据库操作。

基础

变量

包括变量的声明和类型, 变量类型就是MySQL的几种数据类型， char， varchar， int， tinyint…..

declare a int default 1;  -- 声明变量a， 类型为int， 默认值是1
set a = 2;  -- 给变量赋值
select 2 into a; -- 和上面的效果相同

分支语句

• IF

  if a > b then
    set result='a>b';
  else if a < b  then
    set result='a<b';
  else
    set result='a=b';
  end if;
  
  

• CASE ..WHEN

  语句必须使用end结束， into是为了完成重新赋值

  SELECT CASE b
  WHEN 2 THEN 3
  WHEN 3 THEN 4
  ELSE 5
  END INTO b;
  
  -- 如果b=3 ，那么b就被赋值4， 否则赋值5
  set b = if(b = 3, 4, 5);
  

循环

• loop

  DROP PROCEDURE IF EXISTS pro;
  
  CREATE PROCEDURE pro()
  BEGIN
  DECLARE a INT DEFAULT 0;
  
  loop_a:  -- 标签声明， 使用leave可以脱离当前循环
  LOOP
  set a = a + 1;
  
  IF a >= 100 THEN
  LEAVE loop_a;
  END IF;
  
  END LOOP;   -- LOOP 声明结束
  
  SELECT a;  -- 输出 a  100
  END;
  
  call pro();  -- 调用
  

  ​

• while

  DROP PROCEDURE IF EXISTS pro;
  
  CREATE PROCEDURE pro()
  BEGIN
  DECLARE a INT DEFAULT 0;
  
  while_a:
  WHILE a < 100 DO
  set a = a + 1;
  
  IF (a % 10 = 0) THEN LEAVE while_a; END IF;  -- a = 10 的时候会退出
  END WHILE;
  
  SELECT a;
  END;
  

• repeat

  DROP PROCEDURE IF EXISTS pro;
  
  CREATE PROCEDURE pro()
  BEGIN
  DECLARE a INT DEFAULT 0;
  
  repeat_a:
  REPEAT
  
  SET a = a + 1;
  
  -- IF a >= 100 THEN LEAVE repeat_a;  -- 和下面的作用是一致的
  -- END IF;
  
  UNTIL a >= 100 END REPEAT;  -- 重复操作， 直到a>=100， 结束循环
  
  SELECT a;
  END;
  

使用游标遍历结果集

使用游标的步骤：

1. 声明： declare cursor_name for select t_name, t_age from people ;
2. 打开： open cursor_name;
3. 使用： fetch cursor_name into var_a, var_b;
4. 关闭：close cursor_name;

DROP PROCEDURE
IF EXISTS pro;

CREATE PROCEDURE pro ()
BEGIN
DECLARE var_a varchar(50);
DECLARE var_b varchar(50);
DECLARE cur_done INT DEFAULT FALSE;
-- 1 声明游标
DECLARE c_people CURSOR FOR SELECT t_name, t_age
FROM people;

-- 当游标遍历完的时候，修改cur_done的值
DECLARE CONTINUE HANDLER FOR NOT found
SET cur_done = TRUE;

-- 2 打开游标
OPEN c_people;

-- 3 遍历游标
loop_a :
LOOP

IF done THEN LEAVE loop1;
END IF;

FETCH c_people INTO var_a, var_b;

END
LOOP loop_a;

-- 关闭游标
CLOSE c_people;

END

过程

mysql 默认标识一个语句的结束符是; , 这与存储结构的相冲突， 所以我们需要改变默认分隔符, 别忘了改回来。

基础结构

• 声明

  DROP PROCEDURE IF EXISTS pro;
  delimiter //
  -- IN 类型的参数可以是标量， 但是 OUT 和 INOUT 必须是变量， 因为要存储改变后的值
  CREATE PROCEDURE pro(IN a INT, OUT b INT, INOUT c VARCHAR(40))
  BEGIN
  set b = 2;  -- 和下面等价
  set c = 3;
  -- SELECT 2 INTO b;
  -- SELECT 3 INTO c;
  END
  
  delimiter ;
  

• 调用

  set @b = 1;
  set @c = 'a';
  CALL pro(1, @b, @c);
  -- 查询改变后的值
  SELECT @b, @c;
  

  ​

函数

• 声明

  DROP FUNCTION IF EXISTS test;
  
  CREATE FUNCTION test() RETURNS INT
  
  BEGIN
  DECLARE a INT DEFAULT 2;
  
  RETURN a;
  END ;
  
  

  ​

• 调用

  SELECT test()
  

  ​