在游标中嵌套游标 [英] Nested cursor in a cursor

问题描述

我有一个光标

CURSOR B_CUR IS select DISTINCT big_id from TEMP_TABLE;

这将返回多个值。之前它被用作

This would return multiple values. Earlier it was being used as

FOR b_id IN B_CUR LOOP
    select s.col1, s.col2 INTO var1, var2 from sometable s where s.col3 = b_id.col1;
END LOOP;

早期确定内部选择查询将总是返回1行。现在这个查询可以返回多行。我如何改变这个逻辑？

Earlier it was certain that the inner select query would always return 1 row. Now this query can return multiple rows. How can I change this logic?

我想创建一个嵌套的游标，它将被提取到一个记录类型的数组（我将声明），但我不知道嵌套游标在这里工作。

I was thinking to create a nested cursor which will fetch into an array of record type (which i will declare) but I have no idea how nested cursor would work here.

我最关心的是效率。因为它将在每次执行上处理数百万条记录。你能建议什么是这里最好的方法吗？

My main concern is efficiency. Since it would be working on millions of records per execution. Could you guys suggest what would be the best approach here?

推荐答案

通常，您只需要加入两个表。

Normally, you would just join the two tables.

FOR some_cursor IN (SELECT s.col1,
                           s.col2
                      FROM sometable s
                           JOIN temp_table t ON (s.col3 = t.col1))
LOOP
  <<do something>>
END LOOP

由于您关注效率，

• 是 TEMP_TABLE 真的是临时表吗？如果是，为什么？非常罕见的是，Oracle实际上需要使用临时表，这使我怀疑你可能做的事情效率不高，以填充临时表在第一。


• 为什么有一个光标 FOR 循环处理来自 TEMP_TABLE 的数据？逐行处理是在PL / SQL中执行任何操作最慢的方法，因此如果您关注效率，通常会避免这种情况。从性能的角度来看，您希望最大化SQL，这样就不用做一系列单行 INSERT 或 UPDATE 操作，您可以单独修改一整行的 INSERT 或 UPDATE 。如果你真的需要以块为单位处理数据，这就是PL / SQL集合和批量处理的地方，但这不会像直SQL一样有效。


• 在对 TEMP_TABLE 的查询中的 DISTINCT ？你真的期望有重复的 big_id 值是不是错误的？大多数时候，人们使用 DISTINCT 不正确，以掩盖数据已加入不正确的问题，或者您强迫Oracle进行昂贵的排序，以防数据不正确

• Is TEMP_TABLE really a temporary table? If so, why? It is exceedingly rare that Oracle actually needs to use temporary tables so that leads me to suspect that you're probably doing something inefficient to populate the temporary table in the first place.
• Why do you have a cursor FOR loop to process the data from TEMP_TABLE? Row-by-row processing is the slowest way to do anything in PL/SQL so it would generally be avoided if you're concerned about efficiency. From a performance standpoint, you want to maximize SQL so that rather than doing a loop that did a series of single-row INSERT or UPDATE operations, you'd do a single INSERT or UPDATE that modified an entire set of rows. If you really need to process data in chunks, that's where PL/SQL collections and bulk processing would come in to play but that will not be as efficient as straight SQL.
• Why do you have the DISTINCT in your query against TEMP_TABLE? Do you really expect that there will be duplicate big_id values that are not erroneous? Most of the time, people use DISTINCT incorrectly either to cover up problems where data has been joined incorrectly or where you're forcing Oracle to do an expensive sort just in case incorrect data gets created in the future when a constraint would be the more appropriate way to protect yourself.

这篇关于在游标中嵌套游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！