在linux中建立mysql存储过程

---

之前在项目上遇到一个问题，实施人员在数据库中建了许多临时的测试数据，在正式客户环境中是要删掉的，但是产品页面上没有删除选项，只能手动在数据库中删除。不仅数据多，而且表之间关系复杂，一条一条删除估计客户的黄花菜都凉了。所以就有了这篇文章，记录存储过程的一些语法，也便于大家参考。

什么是存储过程？

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。

说的直白一点就是，存储过程是一组特定功能sql的集合，第一次编译后无需再编译，后续需要时直接调用即可。

所以这个时候存储过程就派上了用场。

在linux怎么创建存储过程

先来看一个最简单的存储过程示例

create procedure demo()
begin
    select id, name from user;
    update user set age = 20 where name = 'zhangsan';
end

这个存储过程的名称就是demo，功能就是查出user表中所有的id和name，并把张三的年龄改为20，存储过程中的sql集合就写在begin...end之间

第一次在linux命令行中执行上述操作，可能都会报如下的错

告诉你第三行有语法错误，那是因为mysql认为你的语句到第一个 ; 就结束了，所以存储过程创建失败。对上述语句做如下改动

delimiter //
create procedure demo()
begin
    select id, name from user;
    update user set age = 20 where name = 'zhangsan';
end
//

第一行的作用就是把mysql默认分隔符改为//（当然你可以改为其他符号比如@，$等，但是不要改成 * 等中间语句会用到的），读到 // 时mysql才会认为一个完整语句结束

最后要记得创建完存储过程后，要执行 delimiter ;

存储过程常用语法

代码清单1

delimiter //
create procedure demo1(
    in input int,
    out output int,
    inout param int
)
begin
    declare num int default 0;
    set output = 0;
    select age into @myage from user where id = input;

    if @myage = 20 then
        set output = 200;
        set num = 2;
    elseif @myage = 21 then
        set output = 300;
        set num = 3;
    else
        set output = 400;
        set num = 4;
    end if;
    
    while param < num do
        set param = param + 10;
    end while;
end//
delimiter ;

# 给inout类型变量赋初值
set @param = 1;
# 调用存储过程
call demo1(3, @myout, @param);

# 查看变量值和结果
select @myage;
select @myout;
select @param;

• 参数

  存储过程的参数有3种：in，out，inout
  • in 是入参标识，input是变量名称，int代表类型
    输入参数传对应类型的值即可（当然也可以传递一个赋值后的变量）
  • out 代表输出参数，你可以理解为方法的返回值
    输出参数需要传变量，且要加上@
  • inout 代表输入输出变量，即你把一个变量输入，处理后再输出
    inout类型和out一样，也是传变量，但是需要先对变量赋值

三种参数调用方式

• 变量声明和赋值

  声明

  • declare
    用declare声明变量，需要指明变量类型，后续可以直接使用变量名，详见代码清单1
  • @
    变量名前面加@表示声明为变量，而后续使用该变量也需要加上@符号，详见代码清单1

  赋值

  set @myage = 20;
  # 直接set一个值
  
  select age into @myage from user where ...;
  # select ... into @...  给变量赋值
  
  set @myage = (select age from user where ...);
  # set @... = (select ...) 给变量赋值
  

  如果select多个字段，给多个变量赋值，就必须用into

  select id, age into @myid, @myage from user where ...;
  

• 循环控制

  • while

  while 条件 do            # 满足条件进入循环
      do something;
  end while;
  

  • loop

  my_loop: LOOP                      # 定义循环 (my_loop是自定义循环名称)
      do something;                  # 循环内执行操作
      if 条件 then                    # 跳出循环判断
          leave my_loop;             # 跳出循环
      end if;
  end LOOP;                          # 循环结束
  

  • repeat （注意：until跳出repeat 语句后面不能加分号）

  repeat
      do something;
      until 条件                  #满足条件结束repeat
  end repeat;
  

• 条件判断

  if ... then
      do something;
  elseif
      do something;
  end if;
  

• 游标操作

  游标（Cursor）是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次一行或者多行前进或向后浏览数据的能力。可以把游标当作一个指针，它可以指定结果中的任何位置，然后允许用户对指定位置的数据进行处理

  在存储过程中，是不能够将多结果集赋值给一个变量的，所以这个时候就需要用到游标了

  # 声明接收游标遍历结果的变量
  declare msg varchar(20);
  
  # 声明控制遍历游标的变量
  declare done int default false;
  
  # 声明游标，for后面的select语句查询结果为多结果
  declare cur cursor for select content from user_msg where ...;
  
  # 设定游标结束标志，含义是游标在结果集取不到数据时
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  
  # 打开游标
  open cur;
  
  # 游标遍历结果
  my_loop: loop
      fetch next from cur into msg;      # 从游标拿结果到msg变量
      if done then                       # 游标结果遍历完时，跳出循环
            leave my_loop;
      end if;
      do something;                      # 一些要执行的sql语句
  end loop;
  
  # 关闭游标
  close cur;
  
  # 可以声明多个游标，声明方式一样
  # 两个游标情况下，注意在遍历第二个之前把done标志设为false
  set done = false;