MySQL 存储过程游标不工作

Question

MySQL 5.5

CREATE TABLE `card` (
  `id` int,
  `cardnumber` varchar(100),
  `customer` varchar(100),
  PRIMARY KEY (`id`)
);

INSERT INTO `card` VALUES (1, '5000', 'Google');

 

DELIMITER // 
CREATE PROCEDURE `test` ()
BEGIN 

DECLARE finished INTEGER DEFAULT 0;
DECLARE cardnumber varchar(20) DEFAULT "";
DECLARE cursor1 CURSOR FOR SELECT cardnumber FROM card WHERE customer = 'Google';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

OPEN cursor1;
get_card: LOOP

  FETCH cursor1 INTO cardnumber;
  IF finished = 1 THEN 
    LEAVE get_card;
  END IF;

END LOOP get_card; 

SELECT cardnumber;

CLOSE cursor1;
END // 
DELIMITER ;

 

CALL test();

没有返回结果，我做错了什么？

Answer 1

如果定义了相同的名称，那么在识别列和局部变量时会产生歧义。在处理诸如column之类的数据库对象名称时，在存储过程、函数和触发器主体中定义不同的命名约定始终是更好的做法。

要交叉检查，请执行以下过程并查看结果。

delimiter // 
drop procedure if exists `same_name_test` //

create procedure `same_name_test`()
begin
   DECLARE cardnumber varchar(20) DEFAULT "";
   -- this statement will print empty string
   SELECT cardnumber as cn 
     FROM card 
    WHERE customer = 'Google';
end;
//
delimiter ;
call `same_name_test`;
+------+
| cn   |
+------+
|      |
+------+

修改的存储过程:

DELIMITER // 
drop procedure if exists `test` //

CREATE PROCEDURE `test`()
BEGIN 
  DECLARE finished INTEGER DEFAULT 0;
  DECLARE card_number varchar(20) DEFAULT "";
  DECLARE cursor1 CURSOR FOR 
            SELECT cardnumber 
              FROM card_so_q23811277 
             WHERE customer = 'Google';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

  OPEN cursor1;
  get_card: LOOP
    -- read cursor into new local variable
    FETCH cursor1 INTO card_number;
    IF finished = 1 THEN 
      LEAVE get_card;
    END IF;
  END LOOP get_card; 

  -- using new name of variable
  SELECT card_number;

  CLOSE cursor1;
END;

// 

DELIMITER ;

现在调用存储过程:

CALL `test`;
+-------------+
| card_number |
+-------------+
| 5000        |
+-------------+