我们可以使用游标来处理存储过程中的结果集。基本上,游标允许我们迭代查询返回的一组行并相应地处理每一行。
为了演示在MySQL存储过程中CURSOR的用法,我们基于以下名为“ student_info”的表的值创建以下存储过程-mysql>Select*fromstudent_info;
+-----+---------+----------+------------+
|id|Name|Address|Subject|
+-----+---------+----------+------------+
|101|YashPal|Amritsar|History|
|105|Gaurav|Jaipur|Literature|
|125|Raman|Shimla|Computers|
+-----+---------+----------+------------+
3rowsinset(0.00sec)
以下查询将创建一个名为“ list_address”的过程,该过程返回表中存储的所有地址的列表-mysql>Delimiter//
mysql>CREATEPROCEDURElist_address(INOUTaddress_listvarchar(255))
->BEGIN
->DECLAREvalue_finishedINTEGERDEFAULT0;
->DECLAREvalue_addressvarchar(100)DEFAULT"";
->DEClAREaddress_cursorCURSORFOR
->SELECTaddressFROMstudent_info;
->DECLARECONTINUEHANDLER
->FORNOTFOUNDSETvalue_finished=1;
->OPENaddress_cursor;
->get_address:LOOP
->FETCHaddress_cursorINTOvalue_address;
->IFvalue_finished=1THEN
->LEAVEget_address;
->ENDIF;
->SETaddress_list=CONCAT(value_address,";",address_list);
->ENDLOOPget_address;
->CLOSEaddress_cursor;
->END//
现在,当我们调用此过程时,我们可以看到以下结果:mysql>DELIMITER;
mysql>Set@address_list="";
mysql>CALLlist_address(@address_list);
mysql>Select@address_list;
+-------------------------+
|@address_list|
+-------------------------+
|Shimla;Jaipur;Amritsar;|
+-------------------------+
1rowinset(0.00sec)