MySql存储过程详解

存储过程

在一些语言中，比如pascal，有 “过程” 和 “函数” 的概念。

• 过程：封装了若干条语句，调用时，这个封装体集中执行

• 函数：是一个有返回值的过程

• 过程：是一个没有返回值的函数

我们将若干条数据封装起来 — 过程

再将过程存储到数据库中 — 存储过程

mysql没有匿名过程（使用后直接丢掉）

创建存储过程：

create procedure procedureName()
begin
  sql 语句集
end//

查看已有的存储过程：

show procedure status;

调用存储过程：

call procedureName();

存储过程是可以变成的，意味着存储结果可以使用变量、表达式、控制结构，那么存储过程可以完成复杂的功能。

引入变量：

定义变量的格式： declare 变量名 变量类型 [default 默认值]

drop procedure if exists p_test1//
create procedure p_test1()
begin
    declare age int default 18;
    declare height int default 180;

    select concat('年龄是', age, ' 身高是', height) from dual;
end //

传参：

/*
打印矩形的类型  瘦、胖、方
*/
delimiter //
drop procedure if exists p_test2//
create procedure p_test2(width int, height int)
  begin
    /*存储过程不能有返回值*/
    if width > height then
      select '你很胖' from dual;
    elseif width < height then
      select '你很瘦' from dual;
    else
      select '你很方' from dual;
    end if;
  end//
delimiter ;
call p_test2(100, 100);

while循环:

/*
计算1到100的和
*/
delimiter //
drop procedure if exists p_test3;
create procedure p_test3()
  begin
    declare total int default 0;
    declare num int default 1;

    while num <= 100 do
      -- mysql中等于也是判断，所以推荐:=区分
      set total := num + total;
      set num := num + 1;
    end while ;

    select total from dual;
  end //
delimiter ;

call p_test3();

in, out, inout:

---------------- 使用 in和out参数改造
delimiter //
drop procedure if exists p_test4;
# in型参数可以接受参数
# out类型可以输出参数
create procedure p_test4(in n int, out total int)
  begin
    declare num int default 1;
    set total := 0;

    while num <= n do
      set total := num + total;
      set num := num + 1;
    end while ;

    select total from dual;
  end //
delimiter ;

# @开头是输出变量
call p_test4(100, @total);
select @total;

---------------- inout参数
/*
给年龄涨10岁
*/
delimiter //
drop procedure if exists p_test5;
create procedure p_test5(inout age int)
  begin
    set age := age + 10;
  end //
delimiter ;
set @currentAge := 18;
call p_test5(@currentAge);
select @currentAge from dual; # 28

case结构：

/*
case
*/
delimiter //
drop procedure if exists p_test6;
create procedure p_test6(in i int)
  begin
    case i
      when 1 then select 'one';
      when 2 then select 'two';
    else select 'zero';
    end case;
  end //
delimiter ;
call p_test6(1); # one

repeat结构：

/*
计算1到100的和，使用repeat
 */
delimiter //
drop procedure if exists p_test7;
create procedure p_test7()
  begin
    declare total int default 0;
    declare num int default 1;
    repeat
      set total := total + num;
      set num := num +1.;
    until num > 100
    end repeat;
    select total;
  end //
delimiter ;
call p_test7();

游标

/*
游标 cursor
1条sql查询语句，往往会查出多条记录。取出资源的句柄就是游标
游标每次可以取出一条记录，通过循环可以依次将资源取出，并做出处理

创建游标：
declare 游标名 cursor for select statement;
打开游标：
open 游标名;
取出一个资源，并将游标后移一位：
fetch 游标名 into var1, var2, var3;
关闭游标：
close 游标名称;
*/

/*
使用游标取出商品表的第一条记录的内容，并打印出来
*/
delimiter //
drop procedure if exists p_test8;
create procedure p_test8()
  begin
    # 定义变量，用来接收fetch收到的列值
    declare row_id int;
    declare row_name varchar(20);
    declare row_stock int;
    # 定义游标
    declare get_goods cursor for select id, name, stock from goods;
    # 开启游标，准备遍历
    open get_goods;
    # 获取一条资源
    fetch get_goods
    into row_id, row_name, row_stock;
    select concat('id:', row_id, ' name:', row_name, ' stock:', row_stock);

    # 处理结束，关闭游标
    close get_goods;
  end //
delimiter ;

call p_test8();

游标循环：

/*
上面的游标只fetch了一条记录，要想fetch所有记录，要进行游标循环
游标循环共有一下几种实现方式：
1. select count(*) 计算记录条数并赋值给变量循环
2. 使用越界标识
*/

-- 使用select count(*) 计算记录数
delimiter //
drop procedure if exists p_test9;
create procedure p_test9()
  begin
    # 定义变量，用来接收fetch收到的列值
    declare row_id int;
    declare row_name varchar(20);
    declare row_stock int;
    # 获取记录条数
    declare counts int default 0;
    # 定义游标
    declare get_goods cursor for select id, name, stock from goods;
    select count(*) into counts from goods;
    # 开启游标，准备遍历
    open get_goods;
    while counts > 0 do
      fetch get_goods into row_id, row_name, row_stock;
      set counts := counts-1;
      select concat('id:', row_id, ' name:', row_name, ' stock:', row_stock);
    end while ;
    # 处理结束，关闭游标
    close get_goods;
  end //
delimiter ;

call p_test9();

-- 使用越界标识符
delimiter //
drop procedure if exists p_test10;
create procedure p_test10()
  begin
    # 定义变量，用来接收fetch收到的列值
    declare row_id int;
    declare row_name varchar(20);
    declare row_stock int;
    # 定义越界标识符 1 代表仍然有数据， 0代表没有数据
    declare has int default 1;
    # 定义游标
    declare get_goods cursor for select id, name, stock from goods;
    # 定义游标监控，当下一条数据没有被发现的时候，将has设置为0
    declare continue handler for not found set has := 0;
    # 开启游标，准备遍历
    open get_goods;
    while has <> 0 do
      fetch get_goods into row_id, row_name, row_stock;
      select concat('id:', row_id, ' name:', row_name, ' stock:', row_stock);
    end while ;
    # 处理结束，关闭游标
    close get_goods;
  end //
delimiter ;

call p_test10();

# 注意！！！！！！！！！！
# 这里结果会多取一条，这是为什么？
# ！！！！！！！！！！！！！

delimiter //
drop procedure if exists p_test10;
create procedure p_test10()
  begin
    # 定义变量，用来接收fetch收到的列值
    declare row_id int;
    declare row_name varchar(20);
    declare row_stock int;
    # 定义越界标识符 1 代表仍然有数据， 0代表没有数据
    declare has int default 1;
    # 定义游标
    declare get_goods cursor for select id, name, stock from goods;
    # 定义游标监控，当下一条数据没有被发现的时候，将has设置为0
    declare continue handler for not found set has := 0;
    # 开启游标，准备遍历
    open get_goods;
    while has <> 0 do
      fetch get_goods into row_id, row_name, row_stock;
      select concat('id:', row_id, ' name:', row_name, ' stock:', row_stock);
    end while ;
    # 处理结束，关闭游标
    close get_goods;
  end //
delimiter ;

call p_test10();

/*
这里goods表中有两条数据，问题就出在第三次repeat上
当执行第四次repeat时，fetch->没数据->not found->set has = 0 -> continue -> 继续执行下面的sql语句：
select concat('id:', row_id, ' name:', row_name, ' stock:', row_stock);
所以最后一行被取出两次。

解决办法：
保证 not found 发生后，后面的语句不执行

声明处理的handler为 exit 即可达到目的：

declare exit handler for not found set has := 0;

exit和continue的区别：
exit触发后，后面的语句不再执行。
 */

除了 exit，continue 外，还有一种 undo handler。

undo handler 是出发后，前面的语句进行撤销，但是目前mysql并不支持。