我們可以使用游標(biāo)來(lái)處理存儲(chǔ)過(guò)程中的結(jié)果集。基本上，游標(biāo)允許我們迭代查詢(xún)返回的一組行并相應(yīng)地處理每一行。
為了演示 CURSOR 在 MySQL 存儲(chǔ)過(guò)程中的使用，我們正在創(chuàng)建以下存儲(chǔ)過(guò)程，該過(guò)程基于名為“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)
登錄后復(fù)制
以下查詢(xún)將創(chuàng)建一個(gè)名為“l(fā)ist_address”的過(guò)程，該過(guò)程返回表中存儲(chǔ)的所有地址的列表 –
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)
登錄后復(fù)制
現(xiàn)在，當(dāng)我們調(diào)用這個(gè)過(guò)程時(shí)，我們可以看到下面的結(jié)果 –
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)
登錄后復(fù)制
以上就是我們?nèi)绾卧?MySQL 存儲(chǔ)過(guò)程中處理結(jié)果集？的詳細(xì)內(nèi)容，更多請(qǐng)關(guān)注www.92cms.cn其它相關(guān)文章！
