（MySQL）存储过程 - 循环结果

We're migrating and application from PostgreSQL to MySQL.
Basically, I would like to loop through a result in MySQL.

我们正在从PostgreSQL迁移和应用到MySQL。基本上，我想在MySQL中循环一个结果。

SELECT col1 FROM table1; <--- (1) Get the result from this query.

SELECT col1 FROM table1; <---（1）从此查询中获取结果。

LOOP THROUGH col1Result

SELECT myCol FROM table2 WHERE thisCol = col1Result <--- Equal to every single results from the previous query.

SELECT myCol FROM table2 WHERE thisCol = col1Result <---等于上一个查询的每个结果。

END LOOP;

I also found this reference
http://dev.mysql.com/doc/refman/5.0/en/cursors.html
However, I'm stuck with this line.

我也找到了这个参考http://dev.mysql.com/doc/refman/5.0/en/cursors.html但是，我坚持这一行。

FETCH cur1 INTO a, b;

Doesn't that get every single result of cur1 into variables a and b? How would I make sure that I'm currently on the first index of variable a?

Here's an example on how the result will be used on my end (Written in PostgreSQL).

这不是将cur1的每一个结果都变成变量a和b吗？我如何确保我目前在变量a的第一个索引？这是一个关于如何在我的结尾使用结果的例子（写在PostgreSQL中）。

FOR my_record IN

   SELECT DISTINCT col1
   FROM            table1
   WHERE           col2 = param1;

LOOP

   SELECT DISTINCT col4
   FROM            table2
   WHERE           col3 = my_record.col1;

   IF true THEN
      RAISE EXCEPTION '%', 'ERROR MESSAGE' || my_record.col1;
   END IF

   SELECT DISTINCT col5
   FROM            table3
   WHERE           col6 = my_record.col1;

   IF true THEN
      RAISE EXCEPTION '%', ERROR MESSAGE' || my_record.col1;
   END IF;

END LOOP;

2 个解决方案

#1

cur1 would read the results of the each row. Each time it loops it gets the next row. So the first time it loops it will get the first row. This will go on until the loop breaks or no more rows are available (in which case a No Data condition occurs.

cur1会读取每行的结果。每次循环都会得到下一行。所以它第一次循环就会得到第一行。这将一直持续到循环中断或没有更多行可用（在这种情况下会出现无数据条件）。

See this for more. Hope it helps.

有关更多信息，请参阅希望能帮助到你。

#2

Why would you use a loop when you can do the processing with a join?

为什么在使用连接进行处理时会使用循环？

select mycol
from table2 t2 join
     table1 t1
     on t1.col1 = t2.thiscol;

This works in both Postgres and MySQL.

这适用于Postgres和MySQL。

#1