详解Mysql 游标的用法及其作用

[mysql游标的用法及作用]

例子：

当前有三张表A、B、C其中A和B是一对多关系，B和C是一对多关系，现在需要将B中A表的主键存到C中；
常规思路就是将B中查询出来然后通过一个update语句来更新C表就可以了，但是B表中有2000多条数据，
难道要执行2000多次？显然是不现实的；最终找到写一个存储过程然后通过循环来更新C表，
然而存储过程中的写法用的就是游标的形式。

【简介】

游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。

游标充当指针的作用。

尽管游标能遍历结果中的所有行，但他一次只指向一行。

游标的作用就是用于对查询数据库所返回的记录进行遍历，以便进行相应的操作。

【用法】

一、声明一个游标: declare 游标名称 CURSOR for table;(这里的table可以是你查询出来的任意集合)
二、打开定义的游标:open 游标名称;
三、获得下一行数据:FETCH 游标名称 into testrangeid,versionid;
四、需要执行的语句(增删改查):这里视具体情况而定
五、释放游标:CLOSE 游标名称;

注:mysql存储过程每一句后面必须用;结尾，使用的临时字段需要在定义游标之前进行声明。

【实例】

- BEGIN  --定义变量 declare testrangeid BIGINT; declare versionid BIGINT; declare done int; --创建游标，并存储数据 declare cur_test CURSOR for  select id as testrangeid,version_id as versionid from tp_testrange; --游标中的内容执行完后将done设置为1  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; --打开游标 open cur_test; --执行循环  posLoop:LOOP --判断是否结束循环   IF done=1 THEN   LEAVE posLoop;  END IF; --取游标中的值  FETCH cur_test into testrangeid,versionid; --执行更新操作  update tp_data_execute set version_id=versionid where testrange_id = testrangeid;  END LOOP posLoop; --释放游标 CLOSE cur_test;  END -

我们现在要用存储过程做一个功能，统计iphone的总库存是多少，并把总数输出到控制台。

--在windows系统中写存储过程时，如果需要使用declare声明变量，需要添加这个关键字，否则会报错。 delimiter // drop procedure if exists StatisticStore; CREATE PROCEDURE StatisticStore() BEGIN  --创建接收游标数据的变量  declare c int;  declare n varchar(20);  --创建总数变量  declare total int default 0;  --创建结束标志变量  declare done int default false;  --创建游标  declare cur cursor for select name,count from store where name = 'iphone';  --指定游标循环结束时的返回值  declare continue HANDLER for not found set done = true;  --设置初始值  set total = 0;  --打开游标  open cur;  --开始循环游标里的数据  read_loop:loop  --根据游标当前指向的一条数据  fetch cur into n,c;  --判断游标的循环是否结束  if done then   leave read_loop; --跳出游标循环  end if;  --获取一条数据时，将count值进行累加操作，这里可以做任意你想做的操作，  set total = total + c;  --结束游标循环  end loop;  --关闭游标  close cur;   --输出结果  select total; END; --调用存储过程 call StatisticStore();

read_loop:loop fetch cur into n,c; set total = total+c; end loop;

declare continue HANDLER for not found set done = true;

--判断游标的循环是否结束 if done then  leave read_loop; --跳出游标循环 end if;

使用方式

游标有三种使用方式：
第一种就是上面的实现，使用loop循环；
第二种方式如下，使用while循环：

drop procedure if exists StatisticStore1; CREATE PROCEDURE StatisticStore1() BEGIN  declare c int;  declare n varchar(20);  declare total int default 0;  declare done int default false;  declare cur cursor for select name,count from store where name = 'iphone';  declare continue HANDLER for not found set done = true;  set total = 0;  open cur;  fetch cur into n,c;  while(not done) do   set total = total + c;   fetch cur into n,c;  end while;    close cur;  select total; END;  call StatisticStore1();

drop procedure if exists StatisticStore2; CREATE PROCEDURE StatisticStore2() BEGIN  declare c int;  declare n varchar(20);  declare total int default 0;  declare done int default false;  declare cur cursor for select name,count from store where name = 'iphone';  declare continue HANDLER for not found set done = true;  set total = 0;  open cur;  repeat  fetch cur into n,c;  if not done then   set total = total + c;  end if;  until done end repeat;  close cur;  select total; END;  call StatisticStore2();

在mysql中，每个begin end 块都是一个独立的scope区域，由于MySql中同一个error的事件只能定义一次，如果多定义的话在编译时会提示Duplicate handler declared in the same block。

drop procedure if exists StatisticStore3; CREATE PROCEDURE StatisticStore3() BEGIN  declare _n varchar(20);  declare done int default false;  declare cur cursor for select name from store group by name;  declare continue HANDLER for not found set done = true;  open cur;  read_loop:loop  fetch cur into _n;  if done then   leave read_loop;  end if;  begin   declare c int;   declare n varchar(20);   declare total int default 0;   declare done int default false;   declare cur cursor for select name,count from store where name = 'iphone';   declare continue HANDLER for not found set done = true;   set total = 0;   open cur;   iphone_loop:loop   fetch cur into n,c;   if done then    leave iphone_loop;   end if;   set total = total + c;   end loop;   close cur;   select _n,n,total;  end;  begin    declare c int;    declare n varchar(20);    declare total int default 0;    declare done int default false;    declare cur cursor for select name,count from store where name = 'android';    declare continue HANDLER for not found set done = true;    set total = 0;    open cur;    android_loop:loop    fetch cur into n,c;    if done then     leave android_loop;    end if;    set total = total + c;    end loop;    close cur;   select _n,n,total;  end;  begin    end;  end loop;  close cur; END;  call StatisticStore3();

动态SQL

Mysql 支持动态SQL的功能

set @sqlStr='select * from table where condition1 = ?'; prepare s1 for @sqlStr; --如果有多个参数用逗号分隔 execute s1 using @condition1; --手工释放，或者是 connection 关闭时， server 自动回收 deallocate prepare s1;

更多相关文章

随机推荐