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()