在PL/SQL中如何避免使用嵌套游标查询

2009-12-14 19:07| 发布者: admin| 查看: 69| 评论: 0|原作者: 飞燕

△考虑下面的PL/SQL代码，这段代码生成......

考虑下面的 PL/SQL 代码，这段代码生成一个 XML 格式的矩阵样式的报表：
declare
l_count integer;
begin
dbms_output.put_line('');
-- generate matrix of parts by country
for part in (select id,description from parts order by description) loop
dbms_output.put_line('');
dbms_output.put_line(''||part.description||'');
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(''||nvl(l_count,0)||'');
end loop;
dbms_output.put_line('');
end loop;
dbms_output.put_line('');
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('');
-- generate matrix of parts by country
i := cell_tbl.first;
for row in part_tbl.first .. part_tbl.last loop
dbms_output.put_line('');
dbms_output.put_line(''||part_tbl(row).description||'');
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_tbl(i).cnt||'');
i := i + 1;
else
dbms_output.put_line('0');
end if;
end loop;
dbms_output.put_line('');
end loop;
dbms_output.put_line('');
end;

游标的 FOR 循环现在是独立运行的，并且特定记录、特定字段、特定单元格的数据被拷贝到三个 PL/SQL 表中。然后利用记录和字段具有特定顺序这一事实，将结果构建到一个 PL/SQL 表的矩阵中。由于 GROUP BY 的隐式 SORT/MERGE 操作，单元格具有同样的顺序。单元格查询已经被减少到一个查询，替代了原来的矩阵每个单元格使用一个查询。
如果字段的数目相当小，那么我们可以使用 BULK COLLECT 构建表。BULK COLLECT 不允许表记录的填充，所以我们就需要为用于这个操作的每一列数据创建一个独立的表。前面的例子可以采用 BULK COLLECT 重写为另外一种形式。
declare
type part_id_tbl_type is table of parts.id%type;
type part_desc_tbl_type is table of parts.description%type;
part_id_tbl part_id_tbl_type;
part_desc_tbl part_desc_tbl_type;
--
type country_code_tbl_type is table of countries.code%type;
country_code_tbl country_code_tbl_type;
--
type cell_cnt_tbl_type is table of orders.cnt%type;
cell_part_id_tbl part_id_tbl_type;
cell_country_tbl country_code_tbl_type;
cell_cnt_tbl cell_cnt_tbl_type;
--
i pls_integer;
begin
-- gather rows
select id,description
bulk collect into part_id_tbl,part_desc_tbl
from parts
order by description;
-- gather columns
select code
bulk collect into country_code_tbl
from countries
order by name;
-- gather cells
select part_id,cc,sum(cnt)
bulk collect into cell_part_id_tbl,cell_country_tbl,cell_cnt_tbl
from orders
group by part_id,cc;
dbms_output.put_line('');
-- generate matrix of parts by country
i := cell_cnt_tbl.first;
for row in part_id_tbl.first .. part_id_tbl.last loop
dbms_output.put_line('');
dbms_output.put_line(''||part_desc_tbl(row)||'');
for col in country_code_tbl.first .. country_code_tbl.last loop
if cell_part_id_tbl(i) = part_id_tbl(row)
and cell_country_tbl(i) = country_code_tbl(col)
then
dbms_output.put_line(''||cell_cnt_tbl(i)||'');
i := i + 1;
else
dbms_output.put_line('0');
end if;
end loop;
dbms_output.put_line('');
end loop;
dbms_output.put_line('');
end;

收藏分享邀请

		自动登录	找回密码
密码			注册

在PL/SQL中如何避免使用嵌套游标查询

最新评论

相关分类

下级分类