java - 我使用了cursor_loop : loop in sql stored procedure its not fetching all 15 rows it fetch one row only

Question

这是我的代码，用于在游标中获取 15 行。但是它获取一行而不是获取 15 行此代码中的任何错误。任何帮助将不胜感激。提前致谢

delimiter $$

drop procedure if exists retrieve_relstr_roc_price;

create procedure retrieve_relstr_roc_price(
   in  p_begin_date      datetime
)
begin

  declare no_more_rows     boolean;
  declare l_symbol         varchar(10);
  declare l_commit_rows    int default 60;
 declare l_row_num        int default 0;

 declare roc_value_cursor cursor for
       select * from(select distinct(symbol) as symbol from 
eqt_price_ta_relstr where category_name in ((select * from (select 
sn.category_name  from eqt_price_ta_relstr ept 
inner join sectornames sn on sn.symbol = ept.symbol where ept.category_name 
= "SPsector" and ept.close_date >= p_begin_date group by sn.category_name  order by ept.rank5 limit 0,1 )  t4  )) and close_date >= p_begin_date group by symbol  order by rank5 limit 15) as t1;     
   declare continue handler for not found set no_more_rows = true;


   open roc_value_cursor;
  cursor_loop: loop
  fetch roc_value_cursor
  into  l_symbol;
  if no_more_rows
  then
     close roc_value_cursor;
     leave cursor_loop;
  end if;

/*next part code used the cursor value*/

Answer 1

我无法重现该问题:

mysql> DROP TABLE IF EXISTS `t1`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `t1` (
    ->   `symbol` VARCHAR(10) 
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `t1`
    -> VALUES ('symbol 1'), ('symbol 2'),
    ->        ('symbol 3'), ('symbol 4'),
    ->        ('symbol 5'), ('symbol 6'),
    ->        ('symbol 7'), ('symbol 8'),
    ->        ('symbol 9'), ('symbol 10'),
    ->        ('symbol 11'), ('symbol 12'),
    ->        ('symbol 13'), ('symbol 14'),
    ->        ('symbol 15');
Query OK, 15 rows affected (0.00 sec)
Records: 15  Duplicates: 0  Warnings: 0

mysql> DROP PROCEDURE IF EXISTS `retrieve_relstr_roc_price`;
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER //

mysql> CREATE PROCEDURE `retrieve_relstr_roc_price`()
    -> BEGIN
    ->   DECLARE `no_more_rows` BOOL DEFAULT FALSE;
    ->   DECLARE `l_symbol` VARCHAR(10);
    ->   DECLARE `l_row_num` INT UNSIGNED DEFAULT 0;
    ->   DECLARE `roc_value_cursor` CURSOR FOR
    ->   SELECT * -- It's recommended to identify the columns: SELECT `symbol` ...
    ->   FROM (
    ->         SELECT `symbol`
    ->         FROM `t1`
    ->        ) `der`;
    ->   DECLARE CONTINUE HANDLER FOR NOT FOUND SET `no_more_rows` := TRUE;
    ->   OPEN `roc_value_cursor`;
    ->   `cursor_loop`: LOOP
    ->     FETCH `roc_value_cursor` INTO `l_symbol`;
    ->     IF `no_more_rows` THEN
    ->       CLOSE `roc_value_cursor`;
    ->       LEAVE `cursor_loop`;
    ->     END IF;
    ->     SET `l_row_num` := `l_row_num` + 1;
    ->   END LOOP `cursor_loop`;
    ->   SELECT `l_row_num`;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> CALL `retrieve_relstr_roc_price`;
+-------------+
| `l_row_num` |
+-------------+
|          15 |
+-------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

参见db-fiddle .