【mySQL】在存储过程中使用多层游标嵌套

本帖最后由 rockage 于 2017-1-14 12:12 编辑

[附件]

BEGIN   
DECLARE i_price INT DEFAULT 0;
DECLARE i_id INT DEFAULT 0;
DECLARE eof INT DEFAULT 0;
DECLARE result VARCHAR(35) DEFAULT "";

DEClARE cur_products CURSOR FOR Select `id` From `ld_products`;
DEClARE cur_price CURSOR FOR Select `price` From `ld_products_price` where `product_id` = i_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET eof = 1;

set eof = 0;
open cur_products;
Loop_X1:LOOP
fetch cur_products into i_id;
IF eof = 1 THEN 
leave Loop_X1; 
END IF;

                                
                                                set eof = 0;
                                                OPEN cur_price;
                                                FETCH cur_price INTO i_price;
                                                Loop_X2:LOOP                
                                                        IF eof = 1 THEN 
                                                                leave Loop_X2; 
                                                        END IF;
                                                        SET result = concat(result, "|" , cast(i_price as CHAR));
                                                        FETCH cur_price INTO i_price;

                                                End Loop;
                                          CLOSE cur_price;

Update `ld_products` set `all_price` = result where id = i_id;
SET eof = 0;
SET result = "";
End Loop;
Close cur_products;

select all_price from ld_products;
END

不多说了，看图，上面是源码。
测试环境：
mySQL 5.6
NaviCat for MySQL v11.0