通过拆分为多个游标来重构大型游标查询

Question

又一个 PL/SQL 重构问题！

我有几个一般简化形式的游标：

cursor_1 is
  with X as (select col1, col2 from TAB where col1 = '1'),
       Y as (select col1, col2 from TAB where col2 = '3'),
  /*main select*/
  select count(X.col1), ...
  from X inner join Y on...
  group by rollup (X.col1, ...

cursor_2 is
  with X as (select col1, col2 from TAB where col1 = '7' and col2 = '9' and col3 = 'TEST'),
       Y as (select col1, col2 from TAB where col3 = '6'),
  /*main select*/
  select count(X.col1), ...
  from X inner join Y on...
  group by rollup (X.col1, ...


cursor_2 is
  with X as (select col1, col2 from TAB where col1 IS NULL ),
       Y as (select col1, col2 from TAB where col2 IS NOT NULL ),
  /*main select*/
  select count(X.col1), ...
  from X inner join Y on...
  group by rollup (X.col1, ...


...
begin
    for r in cursor_1 loop
       print_report_results(r);
    end loop;

    for r in cursor_2 loop
       print_report_results(r);
    end loop;
    ...
end;

基本上，所有这些游标（超过 3 个）都是相同的摘要/报告查询。区别在于因子子查询。始终有 2 个因子子查询“X”和“Y”，并且它们始终选择相同的列来馈送到主报告查询中。

问题是主要报告查询非常大，大约 70 行。这本身并没有那么糟糕，但它是为所有报告查询复制粘贴的（我认为有十多个）。

由于唯一的区别在于分解的子查询（它们都返回相同的列，这实际上只是它们选择的表及其条件的区别），我希望找到一种方法来重构所有这些，以便有一个查询对于大型报告和各种因子子查询的较小报告，这样当对报告的完成方式进行更改时，我只需在一个地方进行，而不是十几个。更不用说更容易浏览（和读取）文件了！

我只是不知道如何正确重构这样的东西。我在想管道功能？我不确定它们是否适合这个，或者是否有更简单的方法......

另一方面，我也想知道通过拆分报告查询性能是否会明显变差。性能（速度）是该系统的一个问题。如果它会显着增加执行时间，我宁愿不为了开发人员的方便而引入更改。

---

我想我最终想要的是看起来像这样的东西（我只是不确定如何做到这一点以便它能够实际编译）：（

cursor main_report_cursor (in_X, in_Y) is
    with X as (select * from in_X),
         Y as (select * from in_Y)
  /*main select*/
  select count(X.col1), ...
  from X inner join Y on...
  group by rollup (X.col1, ...

cursor x_1 is
     select col1, col2 from TAB where col1 = '1';
cursor y_1 is
     select col1, col2 from TAB where col2 = '3'

...
begin
for r in main_report_cursor(x_1,y_1) loop
   print_report_results(r);
end loop;

for r in main_report_cursor(x_2,y_2) loop
   print_report_results(r);
end loop;
...

使用 Oracle 10g）

Answer 1

为主查询创建视图怎么样？这可以美化您的代码并集中启动主要查询。

Answer 2

使用管道函数。例如：

drop table my_tab;
create table my_tab
(
col1 number,
col2 varchar2(10),
col3 char(1)
);
insert into my_tab values (1, 'One', 'X');
insert into my_tab values (1, 'One', 'Y');
insert into my_tab values (2, 'Two', 'X');
insert into my_tab values (2, 'Two', 'Y');
insert into my_tab values (3, 'Three', 'X');
insert into my_tab values (4, 'Four', 'Y');
commit;

-- define types
create or replace package refcur_pkg is
    --type people_tab is table of people%rowtype;
    type my_subquery_tab is table of my_tab%rowtype;
end refcur_pkg;

创建函数流水线

-- create pipelined function
create or replace function get_tab_data(p_cur_num in number, p_cur_type in char)
return REFCUR_PKG.my_subquery_tab pipelined
IS
    v_ret  REFCUR_PKG.my_subquery_tab;
begin
    if (p_cur_num = 1) then
        if (upper(p_cur_type) = 'X') then
            for rec in (select * from my_tab where col1=1 and col3='X')
            loop
                pipe row(rec);
            end loop;
        elsif (upper(p_cur_type) = 'Y') then
            for rec in (select * from my_tab where col1=1 and col3='Y')
            loop
                pipe row(rec);
            end loop;
        else
            return;
        end if;
    elsif (p_cur_num = 2) then
        if (upper(p_cur_type) = 'X') then
            for rec in (select * from my_tab where col1=2 and col3='X')
            loop
                pipe row(rec);
            end loop;
        elsif (upper(p_cur_type) = 'Y') then
            for rec in (select * from my_tab where col1=2 and col3='Y')
            loop
                pipe row(rec);
            end loop;
        else
            return;
        end if;
    end if;
    return;
end;

MAIN 过程示例

-- main procedure/usage
declare

  cursor sel_cur1 is
    with X as (select * from table(get_tab_data(1, 'x'))),
           Y as (select * from table(get_tab_data(1, 'y')))
    select X.col1, Y.col2 from X,Y where X.col1 = Y.col1;

begin
    for rec in sel_cur1
    loop
        dbms_output.put_line(rec.col1 || ',' ||  rec.col2);
    end loop;
end;

所有各种子查询都简化为对单个流水线函数的调用，该函数确定要返回的行。

编辑：

要将所有需要的类型和函数合并到 1 个过程中，并使用变量作为子查询函数参数，我添加以下示例：

create or replace procedure my_pipe
IS
    -- define types
    type my_subquery_tab is table of my_tab%rowtype;
    type ref_cur_t is ref cursor;
    v_ref_cur ref_cur_t; 

    -- define vars
    v_with_sql varchar2(4000);
    v_main_sql varchar2(32767);
    v_x1 number;
    v_x2 char;
    v_y1 number;
    v_y2 char;
    v_col1 my_tab.col1%type;
    v_col2 my_tab.col2%type;

    -- define local functions/procs
    function get_tab_data(p_cur_num in number, p_cur_type in char)
    return my_subquery_tab pipelined
    IS
        v_ret  my_subquery_tab;
    begin
        if (p_cur_num = 1) then
            if (upper(p_cur_type) = 'X') then
                for rec in (select * from my_tab where col1=1 and col3='X')
                loop
                    pipe row(rec);
                end loop;
            elsif (upper(p_cur_type) = 'Y') then
                for rec in (select * from my_tab where col1=1 and col3='Y')
                loop
                    pipe row(rec);
                end loop;
            else
                return;
            end if;
        elsif (p_cur_num = 2) then
            if (upper(p_cur_type) = 'X') then
                for rec in (select * from my_tab where col1=2 and col3='X')
                loop
                    pipe row(rec);
                end loop;
            elsif (upper(p_cur_type) = 'Y') then
                for rec in (select * from my_tab where col1=2 and col3='Y')
                loop
                    pipe row(rec);
                end loop;
            else
                return;
            end if;
        end if;
        return;
    end;

BEGIN
    ---------------------------------
    -- Setup SQL for cursors
    ---------------------------------
    -- this will have different parameter values for subqueries
    v_with_sql := q'{
    with X as (select * from table(get_tab_data(:x1, :x2))),
           Y as (select * from table(get_tab_data(:y1, :y2)))
    }';
    -- this will stay the same for all cursors
    v_main_sql := q'{
    select X.col1, Y.col2 from X,Y where X.col1 = Y.col1
    }';

    ---------------------------------
    -- set initial subquery parameters
    ---------------------------------
    v_x1 := 1;
    v_x2 := 'x';
    v_y1 := 1;
    v_y2 := 'y';
    open v_ref_cur for v_with_sql || v_main_sql using v_x1, v_x2, v_y1, v_y2;
    loop
        fetch v_ref_cur into v_col1, v_col2;
        exit when v_ref_cur%notfound;
        dbms_output.put_line(v_col1 || ',' ||  v_col2);
    end loop;
    close v_ref_cur;
    ---------------------------------
    -- change subquery parameters
    ---------------------------------
    v_x1 := 2;
    v_x2 := 'x';
    v_y1 := 2;
    v_y2 := 'y';
    open v_ref_cur for v_with_sql || v_main_sql using v_x1, v_x2, v_y1, v_y2;
    loop
        fetch v_ref_cur into v_col1, v_col2;
        exit when v_ref_cur%notfound;
        dbms_output.put_line(v_col1 || ',' ||  v_col2);
    end loop;
    close v_ref_cur;
end;

请注意，现在的好处是，即使您有许多不同的游标，您也只需定义主查询和子查询SQL各一次。之后，您只需更改变量即可。

干杯

Answer 3

--Create views that will be replaced by common table expressions later.
--The column names have to be the same, the actual content doesn't matter.
create or replace view x as select 'wrong' col1, 'wrong' col2 from dual;
create or replace view y as select 'wrong' col1, 'wrong' col2 from dual;

--Put the repetitive logic in one view
create or replace view main_select as
select count(x.col1) total, x.col2
from X inner join Y on x.col1 = y.col1
group by rollup (x.col1);

--Just querying the view produces the wrong results
select * from main_select;

--But when you add the common table expressions X and Y they override
--the dummy views and produce the real results.
declare
    cursor cursor_1 is
    with X as (select 'right' col1, 'right' col2 from dual),
         Y as (select 'right' col1, 'right' col2 from dual)
    select total, col2 from main_select;
    --... repeat for each cursor, just replace X and Y as necessary
begin
    for r in cursor_1 loop
        dbms_output.put_line(r.col2);
    end loop;
    null;
end;
/

这个解决方案比管道方法有点奇怪，并且需要 3 个新的视图对象，但它可能会运行得更快
因为 SQL 和 PL/SQL 之间的上下文切换较少。

Answer 4

您可以考虑的一种可能性是为 X 和 Y 使用 2 个全局临时表 (GTT)。那么您只需要一个游标，但您必须多次清除并重新填充 2 个 GTT - 如果数据量很大，您可能需要每次也可以获得 GTT 的优化器统计数据。

这就是我的意思：

cursor_gtt is
  select count(X.col1), ...
  from GTT_X inner join GTT_Y on...
  group by rollup (X.col1, ...

begin
    insert into gtt_x select col1, col2 from TAB where col1 = '1';
    insert into gtt_y select col1, col2 from TAB where col2 = '3';
    -- maybe get stats for gtt_x and gtt_y here
    for r in cursor_gtt loop
       print_report_results(r);
    end loop;

    delete gtt_x;
    delete gtt_y;
    insert into gtt_x select col1, col2 from TAB where col1 = '7' and col2 = '9' and col3 = 'TEST';
    insert into gtt_y select col1, col2 from TAB where col3 = '6'
    -- maybe get stats for gtt_x and gtt_y here
    for r in cursor_gtt loop
       print_report_results(r);
    end loop;
    ...
end;

因此，相同的 2 个 GTT 会被重新填充，并且每次都会使用相同的游标。