MySQL游标

DELIMITER $$
DROP PROCEDURE IF EXISTS `cosbuyer`.`cursor_p`$$
CREATE PROCEDURE `cursor_p`()
BEGIN
DECLARE v_id VARCHAR(50);
DECLARE v_pdtname VARCHAR(200);
DECLARE v_price VARCHAR(20) DEFAULT 'abc';
DECLARE v_fig INT DEFAULT 0;
/*给查询的列取别名:MySQL版本为（5.0.22-community-nt-log）列名不能与declare中相同，别名与declare要保持相同*/
DECLARE v_pp CURSOR FOR SELECT id AS v_id,pdtname AS v_pdtname,price AS v_price FROM tb_pdt_product AS product LIMIT 0,5;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_fig = 1;
OPEN v_pp;
WHILE(v_fig<>1)DO
  FETCH v_pp INTO v_id,v_pdtname,v_price;
  SELECT v_id,v_pdtname,v_price;
END WHILE;
CLOSE v_pp;
END$$
DELIMITER;

CALL cursor_p();


MySQL中对游标的三种循环方式:

1.WHILE

OPEN v_pp;
WHILE(v_fig<>1)DO
  FETCH v_pp INTO v_id,v_pdtname,v_price;
  SELECT v_id,v_pdtname,v_price;
END WHILE;
CLOSE v_pp;

2.LOOP

OPEN v_pp;
LOOP_LABLE:LOOP
  IF v_fig=1 THEN 
    LEAVE LOOP_LABLE;
  END IF;
  FETCH v_pp INTO v_id,v_pdtname,v_price;
  SELECT v_id,v_pdtname,v_price;
END LOOP;
CLOSE v_pp;

3.REPEAT

OPEN v_pp;
REPEAT
  FETCH v_pp INTO v_id,v_pdtname,v_price;
  SELECT v_id,v_pdtname,v_price;
  UNTIL v_fig =1
END REPEAT;
CLOSE v_pp;