另一个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'),col2 from TAB where col3 = '6'),...
cursor_2 is
with X as (select col1,col2 from TAB where col1 IS NULL ),col2 from TAB where col2 IS NOT NULL ),...
...
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),...
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)
使用流水线功能.例如：
原文链接：https://www.f2er.com/oracle/242109.htmldrop 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,'Y');
insert into my_tab values (2,'Two','X');
insert into my_tab values (2,'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 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_col2);
end loop;
close v_ref_cur;
end;
请注意,现在的好处是,即使您有许多不同的游标,也只需要定义主查询和子查询sql一次.在那之后,你只是在改变变量.
干杯
