Mysql创建存储过程,使用游标Cursor循环更新
使用游标(cursor)
1.声明游标
DECLARE cursor_name CURSOR FOR select_statement
这个语句声明一个游标。也可以在子程序中定义多个游标,但是一个块中的每一个游标必须有唯一的名字。声明游标后也是单条操作的,但是不能用SELECT语句不能有INTO子句。
2. 游标OPEN语句
OPEN cursor_name
这个语句打开先前声明的游标。
3. 游标FETCH语句
FETCH cursor_name INTO var_name [, var_name] ...
这个语句用指定的打开游标读取下一行(如果有下一行的话),并且前进游标指针。
4. 游标CLOSE语句
CLOSE cursor_name
这个语句关闭先前打开的游标。
写之前查了好多资料但是总是执行不了,后来自琢磨了一下,写了个例子,经过测试完全正确。
下面是创建存储过程,使用游标循环更新操作的例子:
下面是创建存储过程,使用游标循环更新操作的例子:
-- Procedure "useCursor" DDL
CREATE PROCEDURE `useCursor`()
BEGIN
/*局部变量的定义 declare*/
declare aid bigint default 0 ;
declare mdsl bigint default 0;
declare stop int default 0;
declare cur CURSOR FOR (select count(area_info.id) as mdsl, area_info.id as aid from area_info right join subbranch_info on subbranch_info.i_subbran_area=area_info.id
where area_info.i_record_status=0 and subbranch_info.i_record_status=0 group by area_info.id);
/* mysql 不知道为什么用异常加入判断 ?
* 这把 游标 异常后 捕捉
* 并设置 循环使用 变量 stop 为 null 跳出循环。
*/
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop = null;
/*开游标*/
OPEN cur;
/*游标向下走一步,将查询出来的两个值付给定义的两个变量*/
FETCH cur INTO mdsl,aid;
/* 循环体 这很明显 把游标查询出的 name 都加起并用 ; 号隔开 */
WHILE ( stop is not null) DO
update zlgsydrsp.area_info set i_subbran_count=mdsl where id = aid ;
/*游标向下走一步*/
FETCH cur INTO mdsl,aid;
END WHILE;
CLOSE cur;
END;
drop procedure if exists pr_product_fix;
-- ---------------------------------------------------------------------------
-- author : pcwl
-- create date : 2010-08-21
-- description : fix product detail
-- ---------------------------------------------------------------------------
create procedure pr_product_fix
(
i_product_id int
,out o_ret int -- 0:ok, -1:system error
)
labproc:begin
declare v_fetch_status int default 1;
declare v_product_id int;
declare v_ret int;
-- ------------------------------------------------------------------------
-- cursor for select products
-- ------------------------------------------------------------------------
declare cur_sel cursor for
select id
from product
where id = i_product_id or i_product_id is null
order by id;
declare continue handler for NOT FOUND
begin
set v_fetch_status = 0;
end;
declare exit handler for sqlexception
begin
rollback;
end;
set o_ret = -1;
-- ------------------------------------------------------------------------
-- fix data
-- ------------------------------------------------------------------------
open cur_sel;
lab1:loop
fetch cur_sel into v_product_id;
-- fix data ...
if v_fetch_status = 0 then leave lab1;
end if;
end loop lab1;
close cur_sel;
set o_ret = 0;
end;
下面是去掉干扰部分,只突出显示游标cursor的又一个过程。
drop procedure if exists pr_product_fix;
create procedure pr_product_fix
(
i_product_id int
)
begin
declare v_fetch_status int default 1;
declare v_product_id int;
-- ------------------------------------------------------------------------
-- cursor for select products
-- ------------------------------------------------------------------------
declare cur_sel cursor for
select id
from product
where id = i_product_id or i_product_id is null
order by id;
declare continue handler for NOT FOUND
begin
set v_fetch_status = 0;
end;
-- ------------------------------------------------------------------------
-- open cursor and loop cursor
-- ------------------------------------------------------------------------
open cur_sel;
lab1:loop
fetch cur_sel into v_product_id;
-- fix data ...
-- update product set detail = '' where id = v_product_id;
if v_fetch_status = 0 then
leave lab1;
end if;
end loop lab1;
close cur_sel;
end;