關于避免在 PL/SQL 中使用嵌套游標查詢

2024-08-31 00:46:36

字體：大中小

來源：轉載

供稿：網友

考慮下面的 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;

游標

游標的 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('<matrix>');
　　-- 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('<row>');
　　dbms_output.put_line('<cell>'part_desc_tbl(row)'</cell>');
　　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>'cell_cnt_tbl(i)'</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>');