How can we handle a result set inside MySQL stored procedure?

We can use a cursor to handle a result set inside a stored procedure. Basically a cursor allows us to iterate a set of rows returned by a query and process each row accordingly.

To demonstrate the use of CURSOR within MySQL stored procedure, we are creating the following stored procedure which is based on the values, as shown below, of the table named ‘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)

The following query will create a procedure named ‘list_address’ which returns the list of all addresses stored in the table −

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)

Now, we can see the result below when we invoke this procedure −

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)

Smita Kapse

Updated on: 22-Jun-2020

967 Views

Kickstart Your Career

Get certified by completing the course

Get Started