MySQL存储过程

概念

存储过程是一组为了完成特定功能的SQL语句级集合，也就是说存储过程中可以有多条SQL语句。

存储过程经过编译后存储在数据库中，用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。

说白了，就类似于php中的函数。

优点

1. 存储过程增强了SQL语言的功能和灵活性。存储过程可以用if/while/case等控制语句编写，可以完成复杂的判断和运算

2. 存储过程类似于变成语言的函数，可以在程序中多次调用。

3. 存储过程能实现较快的执行速度。因为存储过程编写完成，编译一次后，之后调用，不再编译。

语法

create procedure 名字(参数1[,参数2])
begin
    sql语句
end

存储过程参加完整步骤

1. 选中某个数据库

   mysql> use demo;

2. 修改mysql默认结束符号

   mysql> \d $

   或 delimiter $

3. 创建存储过程

   mysql> create procedure hello()

   mysql> begin

   mysql> show tables;

   mysql> end

   mysql> $

4. 将结束符号改为;

   mysql> \d ;

5. 调用存储过程

   mysql> call hello();

存储过程中的局部变量定义

局部变量只会在存储过程中生效，其他地方调用不了

• 局部变量定义

declare 变量名 数据类型 default 默认值

• 局部变量赋值

set 变量名 = 值 [, 变量名1 = 值]

• 存储过程的三种参数

  存储过程的参数有三种，分别是输入输出类型(in)，输入类型(inout)，输出类型(out)。

  参数形式：

  [in | out | inout] 参数名 type

  其中，in表示输入参数；out表示输出参数； inout表示既可以是输入，也可以是输出；type参数指定存储过程的参数类型，该类型可以是MySQL数据库的任意数据类型

  in 类型的参数： 表示该参数的值必须在调用存储过程之前指定，在存储过程中修改的值不能被返回。

  out类型的参数： out的值可以在存储过程内部改变，并可以返回

  inout类型的参数： inout的值可以在调用时指定，并可以在存储过程中修改和返回

• 局部变量定义例子

  mysql> \d $
  mysql> create procedure test_var()
  mysql>     begin
  mysql>          declare name varchar(20) default 'jack';// 定义一个局部变量
  mysql>          select name;//输出局部变量
  mysql>     end
  mysql>   $
  mysql>  \d ;
  mysql> call test_var();//调用存储过程
  

• 局部变量赋值例子

  mysql> \d $
  mysql> create procedure test_var_val()
  mysql>  begin
  mysql>      declare num int;
  mysql>        set num = 10;//给局部变量num赋值
  mysql>        select num;
  mysql>    end
  mysql> $
  mysql> \d ;
  

• 存储过程的三种参数in类型的例子

  mysql> \d $
  mysql> create procedure test_three(in age int)
  mysql> begin
  mysql> select age;
  mysql> set age = age + 1;//在存储过程中改变值
  mysql> select age;
  mysql> end
  mysql> $
  mysql> \d ;
  mysql> set @i=3;//声明一个变量
  mysql> call test_three(@i);//调用存储过程，并且将@i这个变量传递给存储过程的age局部变量
  mysql> select @i;//查询@i的值
  

  结论： @i的值不变，还是3，说明in类型的参数是不会影响传递@i的，也就是类似于php中的值传递。

• 存储过程的三种参数out类型的例子

  mysql> \d $
  mysql> create procedure test_out(out age int)
  mysql> begin
  mysql> select age;
  mysql> set age = 22;//在存储过程中改变值
  mysql> select age;
  mysql> end
  mysql> $
  mysql> \d ;
  mysql> set @out=0;//声明一个变量
  mysql> call test_three(@out);//调用存储过程，并且将@i这个变量传递给存储过程的age局部变量
  mysql> select @out;//查询@i的值
  

总结：@out的值也变成了22,说明改变了age这个局部变量的值，也会改变@out的值

所以使用out类型参数来接受到存储过程中的需要返回的结果。

总结in、out区别：

in:表示输入一个值，你需要一个值，我给你一个值 out:你往外输出一个值，你输出的那个值我就拿一个变量来接收你给我输出的那个值

• 存储过程的三种参数inout类型的例子

  mysql> \d $
  mysql> create procedure test_five(inout num int)
  mysql> begin
  mysql> select num;
  mysql> set num = 33;//在存储过程中改变值
  mysql> select num;
  mysql> end
  mysql> $
  mysql> \d ;
  mysql> set @i=3;//声明一个变量
  mysql> call test_five(@i);//调用存储过程，并且将@i这个变量传递给存储过程的age局部变量
  mysql> select @i;//查询@i的值
  

inout类型参数： @i可以传递进去给num,并且num的值也返回给@i

存储过程之选择语句(if)

语法：

 if  条件1  then   if内语句

 [elseif 条件2 then 语句2]

 [else 语句3]

 end if;

例子1：

//如果age> 20 就输出'成年了'，小于20就输出未成年

mysql>  \d $
mysql> create procedure test_if(in age int)
mysql>  begin
mysql>      if age > 20 then
mysql>          select '成年了';
mysql>        else
mysql>          select '未成年';
mysql>        end if;
mysql>    end
mysql>  $

例子2：

//如果60>age> 20 就输出'成年了'，小于20就输出未成年,大于60小于200就是老年人

mysql>  \d $
mysql> create procedure test_ifelse(in age int)
mysql>  begin
mysql>      if age > 20 && age < 60 then
mysql>          select '成年人';
mysql>        elseif age <= 20  then
mysql>          select '未成年';
mysql>        elseif age > 60 && age < 200 then
mysql>            select '老年人';
mysql>        else
mysql>            select '人妖';
mysql>        end if;
mysql>    end
mysql>  $

存储过程之选择语句(case)

case语句用来进行条件判断，类似php中的switch case

语法：

   case case_value

   when when_value  then 语句

   [when when_value then 语句]

   [else 语句]

   end case;

例子：如果用户=2，就将工资加1000，如果用户id=3，工资加2000

create table saraly(

    id int not null primary key auto_increment,

    uid int unsigned not null ,

    saraly decimal(18,2) not null

)engine=innodb  default charset=utf8;

//插入测试数据

insert into saraly(uid, saraly) values(2,200000);

insert into saraly(uid, saraly) values(3,400000);

insert into saraly(uid, saraly) values(3,500000);

//存储过程命令

\d $
create procedure test_case(in userid int)
begin
    case userid 
        when 1 then 
            update saraly set saraly=saraly+1000 where `uid` =1;
        when 2  then
            update saraly  set saraly=saraly+2000 where `uid`=2;
        else
            select '工资没有涨';
    end case;
end
$
\d ;

• case 另外常用例子

//将用户详情表中的性别为1，变成男，0=>女

select id,uid,city,(case sex when 1 then '男' when 0 then '女' else '人妖' end) as sex from user_detail;

存储过程之循环语句(while)

语法：

   while 条件 do
        语句

   end while;

 例子： 利用while循环计算1+2+3+..+100的值

 \d $
mysql> create procedure addsum(out sum int)
    -> begin
    -> declare i int default 1;
    -> declare result int default 0;
    -> while i<=100 do
    ->   set result = i + result;
    ->   set i = i + 1;
    -> end while;
    -> set sum = result;
    -> end
    -> $

mysql>\d ;

//定义一个变量接受addsum返回的值
mysql> set @res=0;
mysql> call addsum(@res);
mysql> select @res;
+--------+
| @resul |
+--------+
|   5050 |
+--------+

存储过程管理

• 查看某个数据库下的所有存储过程

  show procedure status where db ='数据库名';

• 查看当前数据库下面的存储过程列表

  use demo;//先要进入到某个数据库 select specific_name from mysql.proc;

• 查看存储过程的内容

  use demo;//先要进入到某个数据库 show create procedure 存储过程名字；

• 删除存储过程

  drop procedure 存储过程名