MySQL 存储过程中的游标使用

DROP PROCEDURE IF EXISTS Proc_Cursor;
DELIMITER ;;

CREATE PROCEDURE Proc_Cursor( IN iPhone VARCHAR(11) )
BEGIN
  DECLARE Done INT DEFAULT FALSE; -- 遍历数据结束标志定义
  DECLARE strName VARCHAR(128) CHARACTER SET utf8;
  DECLARE strAddress VARCHAR(128) CHARACTER SET utf8;
  DECLARE rs CURSOR FOR SELECT username, address FROM dbname.tablename WHERE mobile = iPhone; -- 定义游标

  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = TRUE; -- 游标结束/异常处理

  OPEN rs; -- 结束游标
  FETCH NEXT FROM rs INTO strName, strAddress; -- 遍历游标取值

  REPEAT
    IF NOT Done THEN
      -- FETCH NEXT FROM rs INTO strName, strAddress; -- 遍历游标取值放在此处则后面不需要再放
      START TRANSACTION; -- 开始事务
      INSERT INTO dbname.tablename2( username, phone, address ) VALUES( strName, iPhone, strAddress );

      SELECT * FROM dbname.tablename2;

      COMMIT; -- 提交事务
    END IF;

  FETCH NEXT FROM rs INTO strName, strAddress; -- 循环遍历处理
  UNTIL Done END REPEAT; -- 结束遍历
  CLOSE rs; -- 关闭游标

	END;;
DELIMITER ;