mysql 嵌套存储过程_MySQL 嵌套存储过程 实例

数据库表准备：

DROP TABLE IF EXISTS `test`.`athlete`;

CREATE TABLE `test`.`athlete` (

`athleteId` int(11) NOT NULL AUTO_INCREMENT,

`firstname` varchar(20) COLLATE utf8_unicode_ci NOT NULL,

`lastname` varchar(20) COLLATE utf8_unicode_ci NOT NULL,

`country` varchar(50) COLLATE utf8_unicode_ci NOT NULL,

`gender` char(1) COLLATE utf8_unicode_ci NOT NULL,

`dateOfBirth` date NOT NULL,

PRIMARY KEY (`athleteId`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

数据准备：

INSERT INTO athlete

()

VALUES

(1, 'zhang', 'jiawei', 'China', 'm', '2010/9/21');

INSERT INTO athlete

()

VALUES

(2, 'zhang', 'java', 'China', 'm', '2010/9/11');

INSERT INTO athlete

()

VALUES

(3, 'li', 'wi', 'USA', 'f', '2010/9/19');

存储过程源代码：

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`sp_cursor`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `test`.`sp_cursor`()

begin

declare a int default 0;

declare b varchar(20) ;

declare cursor_flag int default 0;

declare cur_a cursor for select lastname from test.athlete;

declare continue handler for NOT FOUND set cursor_flag=1; open cur_a;

fetch cur_a into b ; while cursor_flag = 0 do

select b;

set a=a+1;

begin

declare birthday date ;

declare cur_b_flag int default 0;

declare cur_b cursor for select dateofbirth from test.athlete where lastname = b;

declare continue handler for NOT FOUND set cur_b_flag =1; open cur_b;

fetch cur_b into birthday;

select 'cur_b',birthday;

close cur_b;

end;

fetch next from cur_a into b ;

end while;

close cur_a; select a;

commit;

end $$

DELIMITER ;

测试执行结果：

mysql> call test.sp_cursor();

+--------+

| b |

+--------+

| jiawei |

+--------+

1 row in set

+-------+------------+

| cur_b | birthday |

+-------+------------+

| cur_b | 2010-09-21 |

+-------+------------+

1 row in set

+------+

| b |

+------+

| java |

+------+

1 row in set

+-------+------------+

| cur_b | birthday |

+-------+------------+

| cur_b | 2010-09-11 |

+-------+------------+

1 row in set

+----+

| b |

+----+

| wi |

+----+

1 row in set

+-------+------------+

| cur_b | birthday |

+-------+------------+

| cur_b | 2010-09-19 |

+-------+------------+

1 row in set

+---+

| a |

+---+

| 3 |

+---+

1 row in set

Query OK, 0 rows affected。

完全正确！！