MySQL cursor fetch NULL

问题:

Why both my variables output NULL? SELECT part of the cursor is working properly.

CREATE PROCEDURE p2()
  BEGIN
    # Account table
    DECLARE accountid INT;
    DECLARE accountname VARCHAR(1000);

    # 1. cursor finished/done variable comes first
    DECLARE done INT DEFAULT 0;
    # 2. the curser declaration and select
    DECLARE c_account_id_name CURSOR FOR SELECT
                                           accountid,
                                           accountname
                                         FROM temp.test;
    # 3. the continue handler is defined last
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = TRUE;

    OPEN c_account_id_name;
    SET accountid = 0;
    SET accountname = '';

    read_loop: LOOP
      FETCH c_account_id_name
      INTO accountid, accountname;
      IF done
      THEN
        LEAVE read_loop;
      END IF;
      SELECT accountname;
    END LOOP;
  END;

回答1:

Variable and select attribute in cursor can't be the same...it's a MySQL bug. This will work

DROP PROCEDURE IF EXISTS p2;
DELIMITER $$
CREATE PROCEDURE p2()
  BEGIN
    # Account table
    DECLARE v_accountidsome INT;  #pay attention
    DECLARE v_accountnameelst VARCHAR(1000); #pay attention

    # 1. cursor finished/done variable comes first
    DECLARE v_done INT DEFAULT FALSE;
    # 2. the cursor declaration and select
    DECLARE c_account_id_name CURSOR FOR SELECT
                                           accountid, #pay attention
                                           accountname #pay attention
                                         FROM temp.test;
    # 3. the continue handler is defined last
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET v_done = TRUE;

    OPEN c_account_id_name;

    read_loop: LOOP
      FETCH c_account_id_name
      INTO v_accountidsome, v_accountnameelst;
      IF v_done
      THEN
        LEAVE read_loop;
      END IF;
      SELECT v_accountidsome;
      SELECT v_accountnameelst;
    END LOOP;
    CLOSE c_account_id_name;
  END $$
DELIMITER ;

CALL p2();

Find more here