[SQL] 避免在 PL/SQL 中使用嵌套游标查询

考虑下面的 PL/sql 代码，这段代码生成一个 XML 格式的矩阵样式的报表：

declare
     l_count     integer;
begin
     dbms_output.put_line('<matrix>');
     -- generate matrix of parts by country
     for part in (select id,description from parts order by description) loop
          dbms_output.put_line('<row>');
          dbms_output.put_line('<cell>'||part.description||'</cell>');
          for country in (select code from countries order by name) loop
               select sum(cnt) into l_count from orders
               where part_id = part.id and cc = country.code;
               dbms_output.put_line('<cell>'||nvl(l_count,0)||'</cell>');
          end loop;
          dbms_output.put_line('</row>');
     end loop;
     dbms_output.put_line('</matrix>');
end;

如果在这个例子中 parts 和 countries 有很多行数据，那么性能就会趋于下降。这是因为，在 PL/sql 中，每次遇到一个游标 FOR 循环，在重新查询并获得数据时，都会有一个切换到 sql 的上下文切换。

以一些服务器端内存为代价，提高这种构造的速度是有可能做到的——如果动态构建 PL/sql 数据表和矩阵单元格条目就可以提高速度。例如：

declare
     type part_tbl_type is table of parts%rowtype index by binary_integer;
     part_tbl     part_tbl_type;
     --
     type country_tbl_type is table of countries%rowtype index by binary_integer;
     country_tbl     country_tbl_type;
     --
     type cell_rec is record
     (
          part_id          orders.part_id%type,
          cc               orders.cc%type,
          cnt               orders.cnt%type
     );
     type cell_tbl_type is table of cell_rec index by binary_integer;
     cell_tbl cell_tbl_type;
     --
     i pls_integer;
begin
     -- build rows
     for row in (select * from parts order by description) loop
          part_tbl(part_tbl.count+1) := row;
     end loop;
     -- build columns
     for col in (select * from countries order by name) loop
          country_tbl(country_tbl.count+1) := col;
     end loop;
     -- build cells
     for cell in (select part_id,cc,sum(cnt) from orders group by part_id,cc) loop
          cell_tbl(cell_tbl.count+1) := cell;
     end loop;
     dbms_output.put_line('<matrix>');
     -- generate matrix of parts by country
     i := cell_tbl.first;
     for row in part_tbl.first .. part_tbl.last loop
          dbms_output.put_line('<row>');
          dbms_output.put_line('<cell>'||part_tbl(row).description||'</cell>');
          for col in country_tbl.first .. country_tbl.last loop
               if cell_tbl(i).part_id = part_tbl(row).id
                    and cell_tbl(i).cc = country_tbl(col).code
               then
                    dbms_output.put_line('<cell>'||cell_tbl(i).cnt||'</cell>');
                    i := i + 1;
               else
                    dbms_output.put_line('<cell>0</cell>');
               end if;
          end loop;
          dbms_output.put_line('</row>');
     end loop;
     dbms_output.put_line('</matrix>');
end;