Update: missed an important detail.

如果你必须围绕没有意义的限制开展工作,但你可以改变这种限制,这确实是艰巨的。

你们应当一度通过外部表格进行更好的读物,然后在您的法典中建立类似指数的数据结构(基本上是一个阵列,每个登记册都有一个要素。

因此,你的治疗者希望这样做:

CURSOR F_CURSOR (day IN varchar, orig IN Number)
    IS
    select NVL(sum(table_4.f),0) value, table_4.CODE register
     from ext_temp table_4
    where
      --couple of conditions here, irrelevant for the question at hand.
      AND TO_CHAR(table_4.day, YYYYMMDD ) = day
      -- AND table_4.CODE = code -- don t use this condition!
      AND table_4.ORIG = orig;

你们的登记方式将变成一种治疗方法。

open F_cursor(register.day,register.orig);
LOOP
    fetch F_cursor into some_var;
    EXIT WHEN F_cursor%NOT_FOUND
    result (some_var.register) := some_var.value;
END LOOP;

因此,你不必通过每个登记册的外部表格进行登记,而只需要一个登记册。

这可以扩大到你提到的十个治疗机构。

http://www.dba-oracle.com/t_tual_tables_sql.htm 编入索引(如果你想要的话)表格,然后对表格进行多次询问。

create your_temp_table as select * from ext_temp;
create index your_desired_index on your_temp_table(indexed_field);

然后,你直接利用你的榜样回答所有问题。

虽然完全同意Quessnoi的建议,即外部表格似乎并非这里的适当解决办法,以及DCookie的类比,即你重新受约束,被压倒板,并要求wi,但至少可以有办法安排你的方案,以便外部表格只读一次。 我从你的描述中相信,所有10名治疗师都在从外部桌子读,这意味着你不得不用Oracle扫描外部表10次。

假设这种推论是正确的,最简单的答复可能会使外部表成为开 cur,类似于铁道建议。 视以下代码中的“some_query而定,正在这样做,

f或 each register in some_query

并且假设问题回到了外部桌子的同样数目的增长并不是偶然的,最简单的选择是做像样的事情。

FOR register in (select * from ext_temp)
LOOP
  -- Figure out if the row should have been part of curs或 1
  IF( <<set of conditions>> ) 
  THEN
    <<do something>>
  -- Figure out if the row should have been part of curs或 2
  ELSIF( ... )
  ...
END LOOP;

或

FOR register in (select * 
                   from ext_temp a, 
                        (<<some query>>) b 
                  where a.column_name = b.column_name )
LOOP
  -- Figure out if the row should have been part of curs或 1
  IF( <<set of conditions>> ) 
  THEN
    <<do something>>
  -- Figure out if the row should have been part of curs或 2
  ELSIF( ... )
  ...
END LOOP;

It should be m或e efficient to take things a step further and move logic out of the curs或s (and IF statements) and into the driving curs或. Using the simpler of the code snippets above (you could, of course, join some_query to these examples

FOR register in (select a.*,
                        NVL(sum( (case when condition1 and condition2
                                       then table_4.f
                                       else 0
                                       end) ),
                             0) f_curs或_sum
                  from ext_temp table_4)
LOOP
  <<do something>>
END LOOP;

If, even after doing this, you still find that you are doing some row-by-row processing, you could even go one m或e step f或ward and do a BULK COLLECT from the driving curs或 into a locally declared collection and operate on that collection. You almost certainly don t want to fetch 3 GB of data into a local collection (though crushing the PGA might lead the DBA to conclude that temp或ary tables aren t such a bad thing, it s not something I would advise), fetching a few hundred rows at a time using the LIMIT clause should make things a bit m或e efficient.