我们可以使用游标来处理存储过程中的结果集。基本上，游标允许我们迭代查询返回的一组行并相应地处理每一行。
为了演示 CURSOR 在 MySQL 存储过程中的使用，我们正在创建以下存储过程，该过程基于名为“student_info”的表的值，如下所示 -
mysql> Select * from student_info; +-----+---------+----------+------------+ | id | Name | Address | Subject | +-----+---------+----------+------------+ | 101 | YashPal | Amritsar | History | | 105 | Gaurav | JAIpur | Literature | | 125 | Raman | Shimla | Computers | +-----+---------+----------+------------+ 3 rows in set (0.00 sec)
以下查询将创建一个名为“list_address”的过程，该过程返回表中存储的所有地址的列表 -
mysql> Delimiter //
mysql> CREATE PROCEDURE list_address (INOUT address_list varchar(255))
-> BEGIN
-> DECLARE value_finished INTEGER DEFAULT 0;
-> DECLARE value_address varchar(100) DEFAULT "";
-> DEClARE address_cursor CURSOR FOR
-> SELECT address FROM student_info;
-> DECLARE CONTINUE HANDLER
-> FOR NOT FOUND SET value_finished = 1;
-> OPEN address_cursor;
-> get_address: LOOP
-> FETCH address_cursor INTO value_address;
-> IF value_finished = 1 THEN
-> LEAVE get_address;
-> END IF;
-> SET address_list = CONCAT(value_address,";",address_list);
-> END LOOP get_address;
-> CLOSE address_cursor;
-> END //
Query OK, 0 rows affected (0.00 sec)
现在，当我们调用这个过程时，我们可以看到下面的结果 -
mysql> DELIMITER ; mysql> Set @address_list = ""; Query OK, 0 rows affected (0.00 sec) mysql> CALL list_address(@address_list); Query OK, 0 rows affected (0.00 sec) mysql> Select @address_list; +-------------------------+ | @address_list | +-------------------------+ | Shimla;Jaipur;Amritsar; | +-------------------------+ 1 row in set (0.00 sec)
以上就是我们如何在 MySQL 存储过程中处理结果集？的详细内容。
