How do we process result sets in MySQL stored procedures?-Mysql Tutorial-php.cn

How do we process result sets in MySQL stored procedures?

WBOY

Release： 2023-09-10 23:53:09

forward

1439 people have browsed it

我们如何在 MySQL 存储过程中处理结果集？

We can use cursors to process the result set in the stored procedure. Basically, cursors allow us to iterate over a set of rows returned by a query and process each row accordingly.

To demonstrate the use of CURSOR in MySQL stored procedures, we are creating the following stored procedure, which is based on the values of the table named "student_info" as shown below-

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)

Copy after login

The following query will create a procedure called "list_address" which returns a 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)

Copy after login

Now when we call this procedure we can see below Result-

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)

Copy after login

The above is the detailed content of How do we process result sets in MySQL stored procedures?. For more information, please follow other related articles on the PHP Chinese website!