Oracle存储过程分页

最新推荐文章于 2021-04-09 00:21:23 发布

a5575vhj

最新推荐文章于 2021-04-09 00:21:23 发布

阅读量105

点赞数

分类专栏： oracle

本文链接：https://blog.csdn.net/a5575vhj/article/details/101517016

版权

oracle 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

create or replace procedure gws_sdata(
i_begintime in varchar2, --时间
i_endtime in varchar2, --时间
i_scan in varchar2, --渠道即场景scane
i_cityid in varchar2, --地市cityid
i_querytype in varchar2,
i_startcount in integer,
i_endcount in integer,
o_result out sys_refcursor
)

is
v_sql varchar2(5000);
v_sql1 varchar2(5000);
v_sql2 varchar2(5000);
v_sql3 varchar2(5000);
v_sql4 varchar2(5000) :='';
v_sql5 varchar2(5000) :='';
v_where varchar2(5000);
v_p varchar2(64);
v_p1 varchar2(64);
v_begintime varchar2(64);
v_endtime varchar2(64);
v_querytype varchar2(64);
v_startcount number;
v_endcount number;
v_sqlhead varchar2(128);
v_sqltail varchar2(128);
v_sqlscane varchar2(32);
v_sqlscane1 varchar2(32);
v_error varchar2(5000);
v_minpartname varchar(64);
begin
v_querytype := nvl(i_querytype, '2');
v_begintime := i_begintime;
v_endtime := i_endtime;

if i_scan is null then
v_sqlscane := '1=1';
v_sqlscane1 := '1=1';
else
v_sqlscane := 't.scane='||'''' || i_scan || '''';
v_sqlscane1 := 't1.scane='||'''' || i_scan || '''';
end if;

if to_char(to_date(i_begintime,'yyyy/MM/dd HH24:Mi:ss'),'yyyyMM') = to_char(to_date(i_endtime,'yyyy/MM/dd HH24:Mi:ss'),'yyyyMM') then
--v_p := to_char(add_months(to_date(i_begintime,'yyyy/MM/dd HH24:Mi:ss'),1),'yyyyMM');
v_p := to_char(to_date(i_begintime,'yyyy/MM/dd HH24:Mi:ss'),'yyyyMM');
if i_cityid is null or i_cityid = '000' then
v_p := 'partition (P_' || v_p;
else
v_p := 'subpartition (P_' || v_p || '_P_' || i_cityid;
end if;
--v_sql := 'select * from t_pub_scanandattentionlog subpartition( P_' || v_p || 'P_' || i_cityid || ') where ';
v_sql :='select cityid,scane,increNum,a canlNum,increNum-a incre,c bindNum,d canlbindNum, c-d calbind from
(
select cityid,scane,sum(ddd.increNum) increNum,sum(ddd.a) a,sum(ddd.b) b,sum(ddd.c) c,sum(ddd.d) d from
(
(
select cityid,scane,count(*) increNum,0 a,0 b,0 c,0 d from t_pub_scanandattentionlog ' || v_p || ') t
where '|| v_sqlscane ||'
and t.scan_flag = ''0''
and t.create_time <= ' || v_endtime || '
and t.create_time >= ' || v_begintime || '
group by cityid,scane)';

v_sql1 := 'union all
( select cityid,scane,0 increNum,count(*) a,0 b,0 c,0 d from t_pub_scanandattentionlog ' || v_p || ') t
where '|| v_sqlscane ||'
and t.scan_flag = ''2''
and t.create_time <= ' || v_endtime || '
and t.create_time >= ' || v_begintime || '
group by cityid,scane)';

v_sql2 := 'union all

(select cityid,scane,0 increNum,0 a,0 b,count(scane) c,0 d from
t_pub_scanandattentionlog ' || v_p || ') t left join icdsocial_wxkf.T_WX_BINDINGINFO p
on t.open_id = p.OPENID
where p.ISVALIDATE = ''1''
and '|| v_sqlscane ||'
and t.scan_flag = ''0''
and t.create_time <= ' || v_endtime || '
and t.create_time >= ' || v_begintime || '
and t.create_time =(select MAX(bb.create_time) from
t_pub_scanandattentionlog ' || v_p || ') bb where t.open_id =bb.open_id)
group by cityid,scane)';

v_sql3 := 'union all

(select cityid,scane,0 increNum,0 a,0 b,0 c,count(*) d from
t_pub_scanandattentionlog ' || v_p || ') t left join icdsocial_wxkf.T_WX_BINDINGINFO p
on t.open_id = p.OPENID
where p.ISVALIDATE = ''0''
and '|| v_sqlscane ||'
and t.scan_flag = ''2''
and t.create_time <= ' || v_endtime || '
and t.create_time >= ' || v_begintime || '
and t.create_time =(select MAX(bb.create_time) from
t_pub_scanandattentionlog ' || v_p || ') bb where t.open_id =bb.open_id)
group by cityid,scane))ddd group by cityid,scane)';

else
--v_p := to_char(add_months(to_date(i_begintime,'yyyy/MM/dd HH24:Mi:ss'),1),'yyyyMM');
--v_p1 := to_char(add_months(to_date(i_endtime,'yyyy/MM/dd HH24:Mi:ss'),1),'yyyyMM');
v_p := to_char(to_date(i_begintime,'yyyy/MM/dd HH24:Mi:ss'),'yyyyMM');
v_p1 := to_char(to_date(i_endtime,'yyyy/MM/dd HH24:Mi:ss'),'yyyyMM');
if i_cityid is null or i_cityid = '000' then
v_p := 'partition (P_' || v_p;
v_p1 := 'partition (P_' || v_p1;
else
v_p := 'subpartition (P_' || v_p || '_P_' || i_cityid;
v_p1 := 'subpartition (P_' || v_p1 || '_P_' || i_cityid;
end if;
--v_sql := 'select * from t_pub_scanandattentionlog subpartition( P_' || v_p || 'P_' || i_cityid || ') where ';
v_sql :='select cityid,scane,increNum,a canlNum,increNum-a incre,c bindNum,d canlbindNum, c-d calbind from
(
select cityid,scane,sum(ddd.increNum) increNum,sum(ddd.a) a,sum(ddd.c) c,sum(ddd.d) d from
(
(
select cityid,scane,count(*) increNum,0 a,0 c,0 d from (
select * from t_pub_scanandattentionlog ' || v_p || ') t
where '|| v_sqlscane ||'
and t.scan_flag = ''0''
and t.create_time <= ' || v_endtime || '
and t.create_time >= ' || v_begintime || '
union all
select * from t_pub_scanandattentionlog ' || v_p1 || ') t1
where '|| v_sqlscane1 ||'
and t1.scan_flag = ''0''
and t1.create_time <= ' || v_endtime || '
and t1.create_time >= ' || v_begintime || ') group by cityid,scane
)';

v_sql1 := 'union all
(select cityid,scane,0 increNum,count(*) a,0 c,0 d from
(select * from t_pub_scanandattentionlog ' || v_p || ') t
where '|| v_sqlscane ||'
and t.scan_flag = ''2''
and t.create_time <= ' || v_endtime || '
and t.create_time >= ' || v_begintime || '
union all
select * from t_pub_scanandattentionlog ' || v_p1 || ') t1
where '|| v_sqlscane1 ||'
and t1.scan_flag = ''2''
and t1.create_time <= ' || v_endtime || '
and t1.create_time >= ' || v_begintime || ') group by cityid,scane
)';

v_sql2 := 'union all

(select cityid,scane,0 increNum,0 a,count(*) c,0 d from (
select * from (select * from
t_pub_scanandattentionlog ' || v_p || ')
union all
select * from
t_pub_scanandattentionlog ' || v_p1 || ')) t left join icdsocial_wxkf.T_WX_BINDINGINFO p
on t.open_id = p.OPENID
where p.ISVALIDATE = ''1''
and '|| v_sqlscane ||'
and t.scan_flag = ''0''
and t.create_time <' || v_endtime || '
and t.create_time >' || v_begintime || '
and t.create_time =(select MAX(bb.create_time) from
(select * from
t_pub_scanandattentionlog ' || v_p || ')
union all
select * from
t_pub_scanandattentionlog ' || v_p1 || ')) bb where t.open_id =bb.open_id)
) group by cityid,scane
)';

v_sql3 := 'union all
(select cityid,scane,0 increNum,0 a,0 c,count(*) d from (
select * from (select * from
t_pub_scanandattentionlog ' || v_p || ')
union all
select * from
t_pub_scanandattentionlog ' || v_p1 || ')) t left join icdsocial_wxkf.T_WX_BINDINGINFO p
on t.open_id = p.OPENID
where p.ISVALIDATE = ''2''
and '|| v_sqlscane ||'
and t.scan_flag = ''0''
and t.create_time <' || v_endtime || '
and t.create_time >' || v_begintime || '
and t.create_time =(select MAX(bb.create_time) from
(select * from
t_pub_scanandattentionlog ' || v_p || ')
union all
select * from
t_pub_scanandattentionlog ' || v_p1 || ')) bb where t.open_id =bb.open_id)
) group by cityid,scane
) ) ddd group by cityid,scane)';
select min(t.partition_name) min_partname into v_minpartname from user_tab_partitions t where t.table_name = 'T_PUB_SCANANDATTENTIONLOG';
if to_char(to_date(i_begintime,'yyyy/MM/dd HH24:Mi:ss'),'yyyyMM') <SUBSTR(v_minpartname,3,6) and to_char(to_date(i_endtime,'yyyy/MM/dd HH24:Mi:ss'),'yyyyMM')=SUBSTR(v_minpartname,3,6) then
v_sql :='select cityid,scane,increNum,a canlNum,increNum-a incre,c bindNum,d canlbindNum, c-d calbind from
(
select cityid,scane,sum(ddd.increNum) increNum,sum(ddd.a) a,sum(ddd.b) b,sum(ddd.c) c,sum(ddd.d) d from
(
(
select cityid,scane,count(*) increNum,0 a,0 b,0 c,0 d from t_pub_scanandattentionlog ' || v_p1 || ') t
where '|| v_sqlscane ||'
and t.scan_flag = ''0''
and t.create_time <= ' || v_endtime || '
and t.create_time >= ' || v_begintime || '
group by cityid,scane)';

v_sql1 := 'union all
( select cityid,scane,0 increNum,count(*) a,0 b,0 c,0 d from t_pub_scanandattentionlog ' || v_p1 || ') t
where '|| v_sqlscane ||'
and t.scan_flag = ''2''
and t.create_time <= ' || v_endtime || '
and t.create_time >= ' || v_begintime || '
group by cityid,scane)';

v_sql2 := 'union all

(select cityid,scane,0 increNum,0 a,0 b,count(scane) c,0 d from
t_pub_scanandattentionlog ' || v_p1 || ') t left join icdsocial_wxkf.T_WX_BINDINGINFO p
on t.open_id = p.OPENID
where p.ISVALIDATE = ''1''
and '|| v_sqlscane ||'
and t.scan_flag = ''0''
and t.create_time =<' || v_endtime || '
and t.create_time >=' || v_begintime || '
and t.create_time =(select MAX(bb.create_time) from
t_pub_scanandattentionlog ' || v_p1 || ') bb where t.open_id =bb.open_id)
group by cityid,scane)';

v_sql3 := 'union all

(select cityid,scane,0 increNum,0 a,0 b,0 c,count(*) d from
t_pub_scanandattentionlog ' || v_p1 || ') t left join icdsocial_wxkf.T_WX_BINDINGINFO p
on t.open_id = p.OPENID
where p.ISVALIDATE = ''0''
and '|| v_sqlscane ||'
and t.scan_flag = ''2''
and t.create_time <=' || v_endtime || '
and t.create_time >=' || v_begintime || '
and t.create_time =(select MAX(bb.create_time) from
t_pub_scanandattentionlog ' || v_p1 || ') bb where t.open_id =bb.open_id)
group by cityid,scane))ddd group by cityid,scane)';
end if;

end if;

--处理分页
if v_querytype ='2' then
v_startcount := nvl(i_startcount, 1);
v_endcount := nvl(i_endcount, 10);
v_sqlhead := 'select * from (select t.*, rownum rn from (' ;
v_sqltail := ') t where rownum <= ' || v_endcount || ') where rn >= ' || v_startcount;
v_sql :=v_sqlhead || v_sql || v_sql1 || v_sql4 || v_sql2 || v_sql3 || v_sql5 || v_sqltail;
--v_sql := 'select * from (select t.*, rownum rn from (' || v_sql || ') t where rownum <= ' || v_endcount || ') where rn >= ' || v_startcount;
else
if v_querytype ='3' then
v_sql :=v_sql || v_sql1 || v_sql4 || v_sql2 || v_sql3 || v_sql5;
else
v_sql :='select count(1) as totalcount from (' || v_sql || v_sql1 || v_sql4 || v_sql2 || v_sql3 || v_sql5 ||')';
end if;
--v_sql :='select count(1) as totalcount from (' || v_sql || v_sql1 || v_sql4 || v_sql2 || v_sql3 || v_sql5 ||')';
end if;
open o_result for v_sql;

exception
when others then
open o_result for select 'x' from dual where 1 = 0;
--v_error := substr(sqlerrm,0,1000);
--insert into t_c_sheetstatus values(sysdate,'','','','','','','',v_error);
end gws_sdata;
/