mysql 存储过程 游标套用_mysql 存储过程 游标嵌套

CREATE TABLE `org_grade` (

`grade_id` int(11) NOT NULL,

PRIMARY KEY (`grade_id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `org_class` (

`class_id` int(11) NOT NULL,

`grade_id` int(11) DEFAULT NULL,

PRIMARY KEY (`class_id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert into org_grade values(1),(2),(3);

insert into org_class  values (1,1),(2,1),(3,1),(4,2),(5,2),(6,3),(7,3),(8,3);

delimiter //

drop procedure if exists good_nested_cursors1

//

CREATE   PROCEDURE good_nested_cursors1()

BEGIN

DECLARE l_grade_id INT;

DECLARE l_class_id   INT;

DECLARE l_class_cnt     INT DEFAULT 0 ;

DECLARE l_done          INT DEFAULT  0;

DECLARE grade_csr cursor  FOR    SELECT grade_id FROM org_grade;

DECLARE class_csr cursor  FOR     SELECT class_id FROM org_class  WHERE grade_id=l_grade_id;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_done=1;

OPEN grade_csr;

grade_loop: LOOP   -- Loop through org_grade

FETCH grade_csr into l_grade_id;

IF l_done=1 THEN

LEAVE grade_loop;

END IF;

select concat('grade:', l_grade_id);

OPEN class_csr;

SET l_class_cnt=0;

class_loop: LOOP      -- Loop through class in grade.

FETCH class_csr INTO l_class_id;

IF l_done=1 THEN

LEAVE class_loop;

END IF;

SET l_class_cnt=l_class_cnt+1;

select  concat(concat('grade:',l_grade_id),concat(' class:', l_class_id));

END LOOP;

CLOSE class_csr;

SET l_done=0;

END LOOP grade_loop;

CLOSE grade_csr;

END;

//

delimiter ;

注意：

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; (游标取不到下一条记录时 done=1)

DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_done=1; (查询不到结果时 l_done=1)

这两条语句的作用域分别是声明处的 block

中

(一对begin，end算一个block) 。 如果block中的语句触发了这个handler ，记得重置该值(上例)。或者如下例，采用独立的BLOCK 声明独立的 handler；

--------------------------------------------------------------------

delimiter //

drop procedure if exists test1//

create procedure test1()

begin

declare done int default 0;

declare v_order_id int(11);

declare v_tagr_id int(11);

block1:begin

declare cur1 cursor for select order_id from tbl_order where date_add(add_time,interval 2 day)

declare cur2 cursor for select tag_id from tbl_tags_record limit 2;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

open cur1;

loop1:loop

fetch cur1 into v_order_id;

if done then

close cur1;

leave loop1;

end if;

select concat('out1:',v_order_id);

block2:begin

DECLARE done2 int default 0;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done2 = 1;

open cur2;

loop2:loop

fetch cur2 into v_tagr_id;

if done2 then

close cur2;

leave loop2;

end if;

select concat('in1:',v_tagr_id);

end loop loop2;

end block2;

end loop loop1;

end block1;

end

//

delimiter ;

参考资料：http://bbs.chinaunix.net/viewthread.php?tid=772236

http://school.cnd8.com/mysql/jiaocheng/34969.htm

http://hideto.iteye.com/blog/195275

http://xp19830105.iteye.com/blog/667974

http://godzwj.iteye.com/blog/398707

分享到：

2010-11-05 15:33

浏览 1629

分类:数据库

评论