MySQL foreach 替代程序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1775521/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL foreach alternative for procedure
提问by Tomá? Fejfar
My problem is fairly simple. I have table setsthat store product sets (more products looking like one on the outside - computer, mouse and keyboard for ex.) it's connected M:N using sets_productstable to productstable. Each product can have parameters (connected again M:N).
我的问题很简单。我有一张sets存储产品集的桌子（更多的产品看起来像外面的一个 - 例如电脑、鼠标和键盘）它使用sets_products桌子到products桌子连接 M:N 。每个产品都可以有参数（再次连接 M:N）。
I have a procedure, that generates all parameters as string (for search cache - like 'hdd:120GB, LCD:1440:900, ..'), but now I need to loop through the set's products and call the procedure for each of them. I CAN'T DO IT IN PHP, because this is used in trigger.
我有一个程序，它将所有参数生成为字符串（用于搜索缓存 - 如'hdd:120GB, LCD:1440:900, ..'），但现在我需要遍历集合的产品并为每个产品调用该程序他们。我不能用 PHP 来做，因为这是在触发器中使用的。
I'd like to use something like this (pseudo SQL)
我想使用这样的东西（伪 SQL）
FOREACH(SELECT products_id FROM sets_products WHERE set_id = 1)
generate_parameter_list(product_id,@result)
@param = CONCAT(@param,",",@result);
END FOREACH;
Can this be done in MySQL or not?
这可以在 MySQL 中完成吗？
回答by Rory
Here's the mysql reference for cursors. So I'm guessing it's something like this:
DECLARE done INT DEFAULT 0;
DECLARE products_id INT;
DECLARE result varchar(4000);
DECLARE cur1 CURSOR FOR SELECT products_id FROM sets_products WHERE set_id = 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO products_id;
IF NOT done THEN
CALL generate_parameter_list(@product_id, @result);
SET param = param + "," + result; -- not sure on this syntax
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
-- now trim off the trailing , if desired
回答by Max Toro
This can be done with MySQL, although it's highly unintuitive:
这可以用 MySQL 来完成，尽管它非常不直观：
CREATE PROCEDURE p25 (OUT return_val INT)
BEGIN
DECLARE a,b INT;
DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET b = 1;
OPEN cur_1;
REPEAT
FETCH cur_1 INTO a;
UNTIL b = 1
END REPEAT;
CLOSE cur_1;
SET return_val = a;
END;//
Check out this guide: mysql-storedprocedures.pdf
