mysql´æ´¢¹ý³Ì Àý×Ó Óαêcursor Ñ­»·Ç¶Ì× ÊÂÎï

×òÌìÒªmysqlÖÐд´æ´¢¹ý³Ì·¢ÏÖÍøÉÏÓйØÓÚcursorÑ­»·ÇÒÌ×µÄ×ÊÁϺÜÉÙ,ÏÖ·¢³ö±¾ÈËдµÄÀý×Ó,Ï£ÍûÄܶԴó¼ÒÓаïÖú.
¿´ÕÐ:
DELIMITER $$
USE `ad_db`$$
DROP PROCEDURE IF EXISTS `test`$$
CREATE DEFINER=`root`@`%` PROCEDURE `test`()
BEGIN
     /** CPAЧ¹ûÊý */
DECLARE _tmpId1 INT; 
DECLARE _tmpId2 VARCHAR(5000);
DECLARE not_found INT DEFAULT 0 ;-- ÊÇ·ñδÕÒµ½Êý¾Ý ±ê¼Ç
DECLARE  cursor1 CURSOR FOR  SELECT id from fhtest2  ; -- ²âÊÔÓÃÎÞÒµÎñ¹Øϵ
DECLARE  cursor2  CURSOR FOR SELECT test from  fhtest2 ;-- ²âÊÔÓÃÎÞÒµÎñ¹Øϵ
DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found = 1; -- Èç¹ûδÕÒµ½Êý¾Ý ½«±ê¼Ç¸ÄΪ²»¿ÉÓÃ
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;  -- Òì³£Ìø³ö
START TRANSACTION;    -- Æô¶¯ÊÂÎï
INSERT INTO fhtest2 (test)  VALUES (' ¿ªÊ¼1 ');
INSERT INTO fhtest2 (test)  VALUES (' ¿ªÊ¼2 ');
OPEN cursor1; -- Æô¶¯Óαê1
WHILE not_found = 0 DO 
  FETCH cursor1 INTO _tmpId1;-- »ñÈ¡Êý¾Ý
  IF NOT not_found THEN
OPEN cursor2; -- Æô¶¯Óαê2
WHILE not_found = 0 DO
  FETCH cursor2 INTO _tmpId2;-- »ñÈ¡Êý¾Ý
  IF NOT not_found THEN
INSERT INTO fhtest2 (test)  VALUES (CONCAT(' id + test=   ',_tmpId1,_tmpId2));
  END IF ;
END WHILE ;
CLOSE cursor2;
SET not_found = 0 ; -- ½« ±ê¼Ç¸ÄΪ0¼´¿ÉÓà ·ñÔòÖ»Ö´ÐÐÒ»´Î,ÒÔΪÄÚ²¿Ñ­»·Ö´ÐÐÍê³Éºó not_found = 1,ËùÓÐÒª½«ÆäÖÃΪ0
  END IF;          
END WHILE;
CLOSE cursor1;
COMMIT;
    END$$
DELIMITER ;