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

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

使用流水线函数。例如:

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。之后，您只是在更改变量。

干杯