MySQL的存储过程

1. 什么是存储过程

存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

说明：mysql从5.0版本之后开始支持存储过程

2. 存储过程优缺点

优点：

• 增强sql的灵活性
• 增加业务的复用性，存储过程创建成功后可以多次调用
• 执行速度快
• 距离需要处理的数据近，减少网络流量
• 减少应用程序和数据库的交互次数

缺点：

• 数据库的移植性差
• 维护性没有java等编程语言好，调试不方便

3. 语法结构

3.1 基本语法

delimiter $$create procedure 过程名称([[IN|OUT|INOUT] 参数名 数据类型,...])begin   -- sql; 语句end $$delimiter ;

示例：

delimiter $
   #指定分隔符create procedure pro_hello_word()
begin
  select 'hello word';
end;
$
delimiter ;  # 还原分隔符

调用：

存储过程的删除

drop procedure 存储过程

3.2 参数

参数类型包括：in，out，inout

• in： 输入参数，在调用过程时指定，即使在过程中被改动，也不能返回
• out：该值可在存储过程内被改变，可返回
• inout： 调用时指定，在过程中可改变可返回

IN参数示例：

mysql> delimiter $
mysql> create procedure pro_in_param(in p_in int)
    -> begin
    ->   select p_in;
    ->   set p_in=1000;
    ->   select p_in;
    -> end;
    -> $
Query OK, 0 rows affected (0.00 sec)

mysql> set @p_in=1;
    -> call pro_in_param(@p_in);
    -> $
Query OK, 0 rows affected (0.00 sec)

+------+
| p_in |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

+------+
| p_in |
+------+
| 1000 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

# 变量的之并没有变化mysql> select @p_in;
    -> $
+-------+
| @p_in |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

mysql>

OUT参数示例

mysql> create procedure pro_out_param(out p_out int)
    -> begin
    ->   select p_out;
    ->   set p_out=9999;
    ->   select p_out;
    -> end
    -> $
Query OK, 0 rows affected (0.00 sec)

mysql> set @p_out=1;
    -> call pro_out_param(@p_out);
    -> $
Query OK, 0 rows affected (0.00 sec)

+-------+
| p_out |
+-------+
|  NULL |
+-------+
1 row in set (0.00 sec)

+-------+
| p_out |
+-------+
|  9999 |
+-------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

# 变量的值已经变了mysql> select @p_out;
    -> $
+--------+
| @p_out |
+--------+
|   9999 |
+--------+
1 row in set (0.00 sec)

mysql>

3.3 变量

声明变量语法：DECLARE 变量名1[,变量名2...] 数据类型 [默认值];

变量赋值语法：SET 变量名 = 变量值 [,变量名= 变量值 ...]

注：在mysql中变量一般使用@开头

示例一：set 方式为变量赋值

delimiter $
create procedure pro_var()
begin 
  declare num int default 1;
  set num = num + 200;
  select num;
end $
delimiter ;

示例二：select ... into 变量

delimiter $
create procedure pro_var()
begin 
  declare num int default 1;
  select COUNT(*) from test into num;
  select num;
end $
delimiter ;

调用

3.4 if结构

条件判断结构

delimiter $
create procedure pro_if(in score int)
begin 
  declare ds varchar(10) default'A';
  if score > 85 then
    set ds='A';
  elseif score <= 85 and score > 75 then
    set ds='B';
  elseif score <= 75 and score > 60 then
    set ds='C';
  else
    set ds='D';
  end if;
  
  select ds;
end $
delimiter ;

3.5 case when

delimiter $
create procedure pro_casewhen(in score varchar(1))
begin 
  declare ds varchar(10);
  case score
  when 'A' then
    set ds = '大于85';
  when 'B' then 
    set ds = '大于等于75小于85';
  when 'C' then
    set ds = '大于等于60小于75';
  when 'D' then
    set ds = '不及格';
  else 
    set ds = '输入A-D';
  end case;
  
  select ds;
end $
delimiter ;

3.6 while循环

delimiter $
create procedure pro_while() 
begin
  declare s int default 0;
  declare i int default 0;
  while i<=100 do
     set s=s+i;
     set i=i+1;
  end while;
  
  select s;
 end $
delimiter ;

3.7 repeat循环

delimiter $
create procedure pro_repeat() 
begin
  declare s int default 0;
  declare i int default 0;

  repeat
    set s=s+i;
    set i=i+1;
  until i > 100 end repeat;
  
  select s;
 end $
delimiter ;

3.8 Loop

delimiter $
create procedure pro_loop() 
begin
  declare s int default 0;
  declare i int default 0;

  loop_label:loop
    set s=s+i;
    set i=i+1;
    if i>100 then
       leave loop_label;
    end if;
  end loop;
  
  select s;
 end $
delimiter ;

3.9 iterate

delimiter $
create procedure pro_loopiterate() 
begin
  declare s int default 0;
  declare i int default 0;

  loop_lable:loop
    if i <=100 then
      set s=s+i;
      set i=i+1;
    end if;
    if i>100 then
       leave loop_lable;
    end if;
  end loop;
  
  select s;
 end $
delimiter ;

4. 游标

4.1 什么是游标

一个游标是一个SQL语句执行时系统内存创建的一个临时工作区域。一个游标包含一个查询语句的信息和它操作的数据行的信息。

4.2 mysql游标的特点

• 只读： 无法通过游标更新基础表中的数据
• 不可滚动： 只能根据select中确定的顺序来获取行数据，不能跳行
• 敏感：敏感游标使用实际的数据，不敏感游标使用实际数据的副本，敏感游标速度快，但如果更新数据则会影响实际数据，mysql游标是敏感的同时是只读的

4.3 游标开发示例

准备工作：

# 班级定义表create table t_class (  
  cid int not null auto_increment,
  cname varchar(40),
  primary key (cid)
) engine=innodb charset=utf8 collate=utf8_general_ci;# 学生表create table t_student (  
  sid int not null auto_increment,
  sname varchar(40),
  score int,
  class int,
  primary key (sid)
) engine=innodb charset=utf8 collate=utf8_general_ci;# 学生等级create table t_grade (  
  gid int not null auto_increment,
  cid int not null,
  A int not null default 0,
  B int not null default 0,
  C int not null default 0,
  D int not null default 0,
  primary key (gid)
) engine=innodb charset=utf8 collate=utf8_general_ci;

功能描述： 统计每个班的学生数据，并进行成绩分类：

• 大于等于90 为A
• 大于等于75分小于90分为B
• 大于等于60分小于75分为C
• 小于60分为D

将统计的数据存放在下面的表格t_grade中，下面为测试数据

insert  into `t_student`(`sid`,`sname`,`score`,`class`) values (1,'小明',90,1),(2,'小李子',95,1),(3,'张三丰',60,1),(4,'王紫',50,1),(5,'王敏',75,1),(6,'赵晓峰',90,2),(7,'刘小锋',80,2),(8,'孙涛',75,2),(9,'朱晓',40,2),(10,'李思敏',78,2),(11,'王晓飞',95,3),(12,'吴蒙',90,3),(13,'夏非',80,3),(14,'郑宝',92,3);

存储过程如下：

drop procedure if exists pro_stat_score;
delimiter $
create procedure pro_stat_score() 
begin

  declare a int default 0;
  declare b int default 0;
  declare c int default 0;
  declare d int default 0;
  
  declare done boolean default true;
  declare cls_cid int default 0;
  declare cls_cur cursor for select cid from t_class;
  declare continue handler for not found set done=false;

  open cls_cur;
  
  while done do
      fetch cls_cur into cls_cid;      select COUNT(*) into a from t_student where class=cls_cid and score >= 90;
      select COUNT(*) into b from t_student where class=cls_cid and score >=75 and score <90;
      select COUNT(*) into c from t_student where class=cls_cid and score >=60 and score <75;
      select COUNT(*) into d from t_student where class=cls_cid and score <60;
      
      insert into t_grade(cid,A,B,C,D)values(cls_cid,a,b,c,d);  end while;
    
  close cls_cur;
  
end $
delimiter ;