光标中的非结束循环。 [英] Non Ending Loop in Cursor.
问题描述
请找到以下代码。它运行非停止，没有操作或输出。
Please find the below code. It runs non stop with no operations or output.
我做错了。
declare
cid number;
cadd number;
ctras number;
cr varchar(2);
cad number;
cursor c1 IS
select c_tras, c_id, c_add from customer_master;
cursor c2 IS
select c_address, cr from customer_address where c_id = cid;
begin
open c1;
open c2;
LOOP
fetch c1 into ctras, cid, cadd;
fetch c2 into cad, cr;
if cr='N'
THEN
update customer_master set c_address = (select c_address from customer_address where cr = 'Y' and c_id = cid) where c_tras = ctras;
END IF;
END LOOP;
END;
/`
推荐答案
缺少处理游标的某些部分。
As @pcej answered you are missing some parts of handling cursors.
一般来说，通常只有少数特定情况下，在自己的代码中显式处理游标是个好主意。在几乎所有情况下，最好是在一个单独的SQL语句中做事情，如果可能，或者如果实际需要循环，则使用隐式游标FOR循环，其中PL / SQL引擎执行所有的游标处理
In general, it is normally only some few specific cases where it is a good idea to handle cursors explicitly in your own code. In almost all cases it is a better idea to preferably do things in a single SQL statement if at all possible, or if looping is actually needed then you use implicit cursor FOR loops, where the PL/SQL engine does all the cursor-handling for you.
让我们逐步简化您的代码，以显示您可以做什么...
Let's simplify your code step by step to show you what can be done...
看看如何避免声明游标，获取变量和处理EXIT。这可以通过使用FOR循环：
First see how you can avoid declaring cursors, fetching into variables, and handling EXITs. This you can do by using the FOR loop:
begin
for c1 in (
select cm.c_tras, cm.c_id, cm.c_add
from customer_master cm
) loop
for c2 in (
select ca.c_address, ca.cr
from customer_address ca
where ca.c_id = c1.c_id
) loop
if c2.cr = 'N' then
update customer_master cm
set cm.c_address = (
select ca.c_address
from customer_address ca
where ca.cr = 'Y'
and ca.c_id = c1.c_id
)
where cm.c_tras = c1.c_tras
end if;
end loop;
end loop;
end;
/
使用FOR循环允许PL / SQL引擎处理游标 -
Using the FOR loop allows the PL/SQL engine to handle the cursors for you - makes it much easier.
但是上面的内容还是很慢，因为循环中有循环，所有行都会读取数据，即使不需要它们也是如此。
But the above is still very slow, as there are loops within loops and data is fetched for all rows, even if they are not needed.
更好地避免循环中的循环与JOIN，而不是IF语句使用WHERE只获取您实际需要的行：
Much better to avoid the loops within loops with a JOIN, and instead of IF statement use WHERE to only fetch the rows you actually need:
begin
for c1 in (
select cm.c_tras, cm.c_id, cm.c_add
, ca.c_address, ca.cr
from customer_master cm
join customer_address ca
on ca.c_id = cm.c_id
where ca.cr = 'N'
) loop
update customer_master cm
set cm.c_address = (
select ca.c_address
from customer_address ca
where ca.cr = 'Y'
and ca.c_id = cm.c_id
)
where cm.c_tras = c1.c_tras
end loop;
end;
/
但是，这还不是最好的办法，@Alex Poole指出。最好不要做任何循环，而是做一个单一的UPDATE语句更新所有需要的行。
But that is still not the best way, as @Alex Poole points out. It is even better not to do any looping at all, but instead do a single UPDATE statement that updates all the rows needed.
这可能是这样的：
update customer_master cm
set cm.c_address = (
select ca.c_address
from customer_address ca
where ca.cr = 'Y'
and ca.c_id = cm.c_id
)
where cm.c_tras in (
select cm1.c_tras
from customer_master cm1
join customer_address ca
on ca.c_id = cm1.c_id
where ca.cr = 'N'
)
/
或者如果数据模型和主键是这样的，你可以做一个密钥保存连接，也许可以做一个更新加入。 （但我不知道在你的情况下是否可能 - 我不知道数据模型; - ）
Or if the datamodel and primary keys are such that you can do a key preserved join, it perhaps could be possible to do an update on a join. (But I cannot tell if that is possible in your case - I do not know the data model ;-)
还要注意，在所有情况下我的重写），如果在 customer_address 中有多个行有 cr ='Y' c $ c> c_id 。您可能希望查看您的数据模型，并确定在出现此类情况时您将要执行的操作。
Also note, that in all cases (both your code and my rewrites) you have problems if there are multiple rows in customer_address having cr = 'Y' for the same c_id. You may wish to review your datamodel and determine what you are going to if such cases arise.
这篇关于光标中的非结束循环。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
