对于一个业务使用游标和使用临时表的两种实现

viwo

浏览: 221641 次
性别:
来自: 大连

最近访客更多访客>>

hdhuql

yinter

MIss_LI

fire-boy

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

数据库

SQL UP

无疑在大数据量的情况下使用临时表版本的效率会远远高于使用游标版本。

使用游标版本：

sql 代码

cursor cur_swcode is
select code as swcode, name as swname from swcode where flag = 3;
begin
for rec_swcode in cur_swcode loop
--DBMS_OUTPUT.put_line(rec_swcode.swcode||rec_swcode.swname);
--本期累计批准的核实调查条数
select count(*)
into vn_bqljpzdchsts
from sh_unpass_sub
left outer join sh_unpass on sh_unpass.MS_ID = sh_unpass_sub.MS_ID
left outer join cpcode on cpcode.CODE = sh_unpass.CPCODE
where pz_date >= to_date(vs_start_day, 'yyyymmdd')
and pz_date <= to_date(vs_end_day, 'yyyymmdd')
and pz_flag = 'R'
and cpcode.swcode = rec_swcode.swcode;
--上期批准上期未办结的调查核实条数
select count(*)
into vn_sqpzsqwbjdchsts
from sh_unpass_sub
left outer join sh_unpass on sh_unpass.MS_ID = sh_unpass_sub.MS_ID
left outer join cpcode on cpcode.CODE = sh_unpass.CPCODE
where pz_date >= to_date(vs_ln_start_day, 'yyyymmdd')
and pz_date <= to_date(vs_ln_end_day, 'yyyymmdd')
and pz_flag = 'R'
and jzhs_flag <> 'R'
and cpcode.swcode = rec_swcode.swcode;
--期末未到期的调查核实数
select count(*) into vn_qmwdqdchsts
from sh_unpass_sub
left outer join sh_unpass on sh_unpass.ms_id = sh_unpass_sub.ms_id
left outer join cpcode on cpcode.CODE = sh_unpass.CPCODE
where pz_date >= to_date(vs_start_day, 'yyyymmdd')
and pz_date <= to_date(vs_end_day, 'yyyymmdd')
and pz_flag = 'R'
and cpcode.swcode = rec_swcode.swcode
and FLOOR(to_date(vs_end_day, 'yyyymmdd')-pz_date)
<
case sh_unpass_sub.hsjg
when '3' then 69
else
case sh_unpass_sub.hsfs
when '1' then 7
when '2' then 36
when '3' then 72
end
end;
--到期已审核无信息的调查核实条数
select count(*) into vn_dqyshwxxdchsts
from sh_unpass_sub
left outer join sh_unpass on sh_unpass.ms_id = sh_unpass_sub.ms_id
left outer join cpcode on cpcode.CODE = sh_unpass.CPCODE
where pz_date>=to_date(vs_start_day,'yyyymmdd')
and pz_date<=to_date(vs_end_day,'yyyymmdd')
and up_reason = '3'
and pz_flag = 'R'
and cpcode.swcode = rec_swcode.swcode ;
--到期未核实完结条数
select count(*) into vn_dqwwjhsts
from sh_unpass_sub
left outer join sh_unpass on sh_unpass.ms_id = sh_unpass_sub.ms_id
left outer join cpcode on cpcode.CODE = sh_unpass.CPCODE
where pz_date>=to_date(vs_start_day,'yyyymmdd')
and pz_date<=to_date(vs_end_day,'yyyymmdd')
and pz_flag = 'R'
and jzhs_flag<>'R'
and cpcode.swcode = rec_swcode.swcode
and FLOOR(to_date(vs_end_day, 'yyyymmdd')-pz_date)
>
case sh_unpass_sub.hsjg
when '3' then 69
else
case sh_unpass_sub.hsfs
when '1' then 7
when '2' then 36
when '3' then 72
else 7
end
end;
--到期超时核实完结条数
select
sum(
floor
(
case sh_unpass_sub.hsjg
when '3' then
case
when jzhs_date-pz_date>69
then (jzhs_date-pz_date-69)/10+1
else 0
end
else
case sh_unpass_sub.hsfs
when '1' then
case
when jzhs_date-pz_date>7
then (jzhs_date-pz_date-7)/7+1
else 0
end
when '2' then
case
when jzhs_date-pz_date>36
then (jzhs_date-pz_date-36)/10+1
else 0
end
when '3' then
case
when jzhs_date-pz_date>72
then (jzhs_date-pz_date-72)/10+1
else 0
end
else 0
end
end
)
) into vn_dqcshswjts
from sh_unpass_sub
left outer join sh_unpass on sh_unpass.ms_id = sh_unpass_sub.ms_id
left outer join cpcode on cpcode.CODE = sh_unpass.CPCODE
where pz_date>=to_date(vs_start_day,'yyyymmdd')
and pz_date<=to_date(vs_end_day,'yyyymmdd')
and pz_flag = 'R'
and jzhs_flag = 'R'
and cpcode.swcode = rec_swcode.swcode;
end loop;

不使用游标版本：

sql 代码

insert into dchsjsltjb_tmp(swcode,swname,num1,num2,num3,num4,num5,num6)
select code,name,v1,v2,v3,v4,v5,v6 from swcode
left outer join
--本期累计批准的核实调查条数
--insert into dchsjsltjb_tmp(swcode,num1)
(
select cpcode.swcode as swcode ,count(*) as v1
--into vn_bqljpzdchsts
from sh_unpass_sub
left outer join sh_unpass on sh_unpass.MS_ID = sh_unpass_sub.MS_ID
left outer join cpcode on cpcode.CODE = sh_unpass.CPCODE
--left outer join swcode on swcode.code = cpcode.swcode
where pz_date >= to_date(vs_start_day, 'yyyymmdd')
and pz_date <= to_date(vs_end_day, 'yyyymmdd')
and pz_flag = 'R'
group by swcode
--and cpcode.swcode = rec_swcode.swcode;
) sel1 on sel1.swcode = swcode.code
left outer join
(
--上期批准上期未办结的调查核实条数
--insert into dchsjsltjb_tmp(swcode,num2)
select cpcode.swcode as swcode ,count(*) as v2
--into vn_sqpzsqwbjdchsts
from sh_unpass_sub
left outer join sh_unpass on sh_unpass.MS_ID = sh_unpass_sub.MS_ID
left outer join cpcode on cpcode.CODE = sh_unpass.CPCODE
--left outer join swcode on swcode.code = cpcode.swcode
where pz_date >= to_date(vs_ln_start_day, 'yyyymmdd')
and pz_date <= to_date(vs_ln_end_day, 'yyyymmdd')
and pz_flag = 'R'
and jzhs_flag <> 'R'
group by swcode
--and cpcode.swcode = rec_swcode.swcode;
) sel2 on sel2.swcode = swcode.code
--期末未到期的调查核实数
left outer join
(
--insert into dchsjsltjb_tmp(swcode,num3)
select cpcode.swcode as swcode ,count(*) as v3
--into vn_qmwdqdchsts
from sh_unpass_sub
left outer join sh_unpass on sh_unpass.ms_id = sh_unpass_sub.ms_id
left outer join cpcode on cpcode.CODE = sh_unpass.CPCODE
--left outer join swcode on swcode.code = cpcode.swcode
where pz_date >= to_date(vs_start_day, 'yyyymmdd')
and pz_date <= to_date(vs_end_day, 'yyyymmdd')
and pz_flag = 'R'
--and cpcode.swcode = rec_swcode.swcode
and FLOOR(to_date(vs_end_day, 'yyyymmdd')-pz_date)
<
case sh_unpass_sub.hsjg
when '3' then 69
else
case sh_unpass_sub.hsfs
when '1' then 7
when '2' then 36
when '3' then 72
else 7
end
end
group by swcode
) sel3 on sel3.swcode = swcode.code
--到期已审核无信息的调查核实条数
left outer join
(
--insert into dchsjsltjb_tmp(swcode,num4)
select cpcode.swcode as swcode ,count(*) as v4
--into vn_dqyshwxxdchsts
from sh_unpass_sub
left outer join sh_unpass on sh_unpass.ms_id = sh_unpass_sub.ms_id
left outer join cpcode on cpcode.CODE = sh_unpass.CPCODE
--left outer join swcode on swcode.code = cpcode.swcode
where pz_date>=to_date(vs_start_day,'yyyymmdd')
and pz_date<=to_date(vs_end_day,'yyyymmdd')
and up_reason = '3'
and pz_flag = 'R'
--and cpcode.swcode = rec_swcode.swcode ;
group by swcode
) sel4 on sel4.swcode = swcode.code
--到期未核实完结条数
left outer join
(
--insert into dchsjsltjb_tmp(swcode,num5)
select cpcode.swcode as swcode ,count(*) as v5
--into vn_dqwwjhsts
from sh_unpass_sub
left outer join sh_unpass on sh_unpass.ms_id = sh_unpass_sub.ms_id
left outer join cpcode on cpcode.CODE = sh_unpass.CPCODE
--left outer join swcode on swcode.code = cpcode.swcode
where pz_date>=to_date(vs_start_day,'yyyymmdd')
and pz_date<=to_date(vs_end_day,'yyyymmdd')
and pz_flag = 'R'
and jzhs_flag<>'R'
--and cpcode.swcode = rec_swcode.swcode
and FLOOR(to_date(vs_end_day, 'yyyymmdd')-pz_date)
>
case sh_unpass_sub.hsjg
when '3' then 69
else
case sh_unpass_sub.hsfs
when '1' then 7
when '2' then 36
when '3' then 72
else 7
end
end
group by swcode
) sel5 on sel5.swcode = swcode.code
--到期超时核实完结条数
left outer join
(
--insert into dchsjsltjb_tmp(swcode,num6)
select cpcode.swcode as swcode ,
sum(
floor
(
case sh_unpass_sub.hsjg
when '3' then
case
when jzhs_date-pz_date>69
then (jzhs_date-pz_date-69)/10+1
else 0
end
else
case sh_unpass_sub.hsfs
when '1' then
case
when jzhs_date-pz_date>7
then (jzhs_date-pz_date-7)/7+1
else 0
end
when '2' then
case
when jzhs_date-pz_date>36
then (jzhs_date-pz_date-36)/10+1
else 0
end
when '3' then
case
when jzhs_date-pz_date>72
then (jzhs_date-pz_date-72)/10+1
else 0
end
else 0
end
end
)
) as v6
--into vn_dqcshswjts
from sh_unpass_sub
left outer join sh_unpass on sh_unpass.ms_id = sh_unpass_sub.ms_id
left outer join cpcode on cpcode.CODE = sh_unpass.CPCODE
--left outer join swcode on swcode.code = cpcode.swcode
where pz_date>=to_date(vs_start_day,'yyyymmdd')
and pz_date<=to_date(vs_end_day,'yyyymmdd')
and pz_flag = 'R'
and jzhs_flag = 'R'
--and cpcode.swcode = rec_swcode.swcode;
group by swcode
) sel6 on sel6.swcode = swcode.code
where swcode.flag='3'
;

分享到：

身体是革命的本钱 | 道可道，非常道；名可名，非常名

2007-03-14 20:37
浏览 1447
评论(0)
查看更多

发表评论

您还没有登录,请您登录后再发表评论

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

对于一个业务使用游标和使用临时表的两种实现

评论

发表评论

相关推荐

最近访客 更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

对于一个业务使用游标和使用临时表的两种实现

评论

发表评论

相关推荐

通过一个实际的例子学习SQLServer存储过程

SQLServer你都用哪些函数

Oracle 临时表的用法

主流数据库分页的SQL语句

通过一个实际的例子学习Oracle存储过程

SQLServer和Oracle常用函数对比

DTS增量导出方案汇总

最近访客更多访客>>