MySQL存储过程

参考：链接

1.定义

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

2.创建一个简单的存储过程

存储过程语法

create procedure 名称()
begin
.........
end

-- example
create procedure test()
begin
    select * from students;
    select * from teachers;
end;

-- 调用
call test();

3.存储过程的变量

表的结构(users)

id name

1 zsdf
2 skt
3 ig

3.1变量的声明和赋值

create procedure testIName()
begin
  -- 使用 declare语句声明一个变量
  declare username varchar(32) default '';
  -- 使用set语句给变量赋值
  set username='xxx';
  -- 将users表中id=1的名称赋值给username
  select name into username from users where id=1;
  -- 返回变量
  select username;
end;

3.2变量总结

变量的声明使用declare,一句declare只声明一个变量；

变量具有数据类型和长度，与mysql的SQL数据类型保持一致；

变量可以通过set来赋值，也可以通过select into的方式赋值(结合表中的数据一起使用)；

变量返回使用select语句，例如：select 变量名。

4.变量的作用域

变量作用域说明（这个和全局变量局部变量是一致的）

存储过程中变量是有作用域的，作用范围在begin和end块之间，end结束变量的作用范围即结束。

需要多个块之间传值，可以使用全局变量，即放在所有代码块之前

5.存储过程参数

5.1基本语法(IN|OUT|INOUT)

create procedure 名称([IN|OUT|INOUT] 参数名 参数数据类型 )
begin
.........
end

5.2存储过程的传出参数IN

传入参数：类型为in,表示该参数的值必须在调用存储过程事指定，如果不显示指定为in,那么默认就是in类型。
IN类型参数一般只用于传入，在调用过程中一般不作为修改和返回

create procedure testIN(userId int)
    begin
            declare username varchar(32) default '';
            select name into username from users where id=userId;
            select username;
    end;
  
-- 调用
call testIN(2);

5.3存储过程的传出参数OUT

传出参数：在调用存储过程中，可以改变其值，并可返回；
out是传出参数，不能用于传入参数值；
调用存储过程时，out参数也需要指定，但必须是变量，不能是常量；

-- 效果和上例相同
create procedure testOUT(in userId int,out username varchar(32))
       begin
           select name into username from users where id=userId;
       end;
       
-- 调用方式
set @name='';
set @id=2;
CALL testOUT(@id,@name);
select @name ;

5.4存储过程的可变参数INOUT

可变变量INOUT:调用时可传入值，在调用过程中，可修改其值，同时也可返回值

create procedure testINOUT(inout userId int,inout username varchar(32))
begin
    set userId=2;
    set username='';
    select id,name into userId,username from users where id=userId;
end;

-- 调用
set @name='';
set @id=2;
CALL testINOUT(@id,@name);
select @name,@id ;

6.存储过程条件语句

6.1条件语句基本结构

-- 单条件
if() then... else... end if;

-- 多重条件
if() then...
elseif() then...
else ...
end if;

6.2示例

Question：用户Id是偶数则返回name,否则返回Id

create procedure testSif(in usersId int)
begin
   declare usersname varchar(32) default '';
   if(userId%2=0)
   then 
      select name into usersname from users where id=usersId;
      select usersname;
   else
      select usersId;
      end if;
end;

-- 调用
call testSif(1);
call testSif(2);

7.存储过程循环语句(while/repeat)

7.1while语句

Question：插入1-10号

-- 基本结构
while(表达式) do 
    ......  
end while;

-- example(插入1-10号)
create procedure test()
begin
  declare i int default 0;
  while(i<10) do 
    begin 
        select i;
        set i=i+1;
        insert into users(id) values(i);
     end;
  end while;
end;

7.2repeat语句

Question：插入1-10号

-- 基本结构
repeat...until...end repeat;

-- example(插入1-10号)
create procedure test()
begin
    declare i int default 0;
    repeat 
    begin 
        select i;
        set i=i+1;
        insert into users(id) values(i);
    end;
    until i>=10 -- 如果i>=10,则跳出循环
    end repeat;
end;

注意：until后面没有 “;”

8.流程控制(case)

Question：将users中的名字改变

-- 基本语法
case ...
when ... then....
when.... then....
else ... 
end case;

-- 例子
create procedure testcase(IN usersId int)
    begin
        case 
            when usersId=1 then update users set name='bob' where id=usersId;
            when usersId=2 then update users set name='num' where id=usersId;
            else update users set name='sos' where id=usersId;
        end case;
    end;

-- 调用
call testcase(1);
call testcase(2);
call testcase(3);