v_exec_sql:='
select
decode(num1,null,0,num1) ,
decode(num1,null,0,0,0,round(num3/num1*100,2)) ,
decode(num1,null,0,0,0,round(num2/num1*100,2))
from (
select
count(*) as num1,
sum( case when ( to_date (to_char(i.creation_date ,''yyyy-mm-dd''),''yyyy-mm-dd'' ) - jxepc.STRINGTODATE(I.APPLY_DATE) ) <5 then 1 else 0 end ) as num2,
sum( case when pub_flag = 2 then 1 else 0 end ) as num3
from jxepc.t_pm_plan_equiprepair_release i
where plan_flag =3 and to_char(i.creation_date,''yyyy-mm-dd'') >= :begindate and to_char(i.creation_date,''yyyy-mm-dd'') <= :enddate
) ';
EXECUTE IMMEDIATE v_exec_sql into v_num1,v_num2,v_num3 Using begindate ,enddate;
卡死? 没反应?
--------------------------------------------------------------------------------------------------------------
为什么?
EXECUTE IMMEDIATE v_exec_sql Using begindate ,enddate;
这样就好快?
select
decode(num1,null,0,num1) ,
decode(num1,null,0,0,0,round(num3/num1*100,2)) ,
decode(num1,null,0,0,0,round(num2/num1*100,2))
from (
select
count(*) as num1,
sum( case when ( to_date (to_char(i.creation_date ,''yyyy-mm-dd''),''yyyy-mm-dd'' ) - jxepc.STRINGTODATE(I.APPLY_DATE) ) <5 then 1 else 0 end ) as num2,
sum( case when pub_flag = 2 then 1 else 0 end ) as num3
from jxepc.t_pm_plan_equiprepair_release i
where plan_flag =3 and to_char(i.creation_date,''yyyy-mm-dd'') >= :begindate and to_char(i.creation_date,''yyyy-mm-dd'') <= :enddate
) ';
EXECUTE IMMEDIATE v_exec_sql into v_num1,v_num2,v_num3 Using begindate ,enddate;
卡死? 没反应?
--------------------------------------------------------------------------------------------------------------
为什么?
EXECUTE IMMEDIATE v_exec_sql Using begindate ,enddate;
这样就好快?
if organid is null THEN
select
decode(num1,null,0,num1) ,
decode(num1,null,0,0,0,round(num3/num1*100,2)) 数据完成率,
decode(num1,null,0,0,0,round(num2/num1*100,2)) 数据及时率
into v_num1,v_num2 ,v_num3
from (
select
count(*) as num1,
sum( case when ( to_date (to_char(i.creation_date ,'yyyy-mm-dd'),'yyyy-mm-dd' ) - jxepc.STRINGTODATE(I.APPLY_DATE) ) <5 then 1 else 0 end ) as num2,
sum( case when pub_flag = 2 then 1 else 0 end ) as num3
from jxepc.t_pm_plan_equiprepair_release i
where plan_flag =3 and to_char(i.creation_date,'yyyy-mm-dd') >= begindate and to_char(i.creation_date,'yyyy-mm-dd') <= enddate
);
-- EXECUTE IMMEDIATE v_exec_sql Using begindate ,enddate;
else
....................................................................................
或者: v_exec_sql:='
select
decode(num1,null,0,num1) 停电检修管理数,
decode(num1,null,0,0,0,round(num3/num1*100,2)) 数据完成率,
decode(num1,null,0,0,0,round(num2/num1*100,2)) 数据及时率
into v_num1,v_num2 ,v_num3
from (
select
count(*) as num1,
sum( case when ( to_date (to_char(i.creation_date ,''yyyy-mm-dd''),''yyyy-mm-dd'' ) - jxepc.STRINGTODATE(I.APPLY_DATE) ) <5 then 1 else 0 end ) as num2,
sum( case when pub_flag = 2 then 1 else 0 end ) as num3
from jxepc.t_pm_plan_equiprepair_release i
where plan_flag =3 and to_char(i.creation_date,''yyyy-mm-dd'') >= :begindate and to_char(i.creation_date,''yyyy-mm-dd'') <= :enddate
) ';
EXECUTE IMMEDIATE v_exec_sql Using begindate ,enddate;
------------------------------------------------------------------------------
去掉INTO 很快!单独执行语句也很快,数据也能出来,就是加入INTO 赋值变量就卡死,数据也出不来?
如果去掉函数STRINGTODATE(),则没有问题,数据能出来,速度也很快。
函数:STRINGTODATE()的功能很简单,判断str能否to_date(str,'yyyy-mm-dd') 如果catch Exception
则返回空。
另外v_num1,v_num2 ,v_num3 长度不是问题。
原本v_num1,v_num2 ,v_num3 都是number 类型,我干脆用 varchar2 。
--------------------------------------------------------------------我猜测:SQL语句加入了INTO v_num1,v_num2 ,v_num3 与 函数 STRINGTODATE(str) 有冲突 !但是我不知道为什么? 另外数据量不大,只有几万条记录。
select
decode(num1,null,0,num1) ,
decode(num1,null,0,0,0,round(num3/num1*100,2)) 数据完成率,
decode(num1,null,0,0,0,round(num2/num1*100,2)) 数据及时率
from (
select
count(*) as num1,
sum( case when ( to_date (to_char(i.creation_date ,'yyyy-mm-dd'),'yyyy-mm-dd' ) - jxepc.STRINGTODATE(I.APPLY_DATE) ) <5 then 1 else 0 end ) as num2,
sum( case when pub_flag = 2 then 1 else 0 end ) as num3
from jxepc.t_pm_plan_equiprepair_release i
where plan_flag =3 and to_char(i.creation_date,'yyyy-mm-dd') >= '2000-01-01' and to_char(i.creation_date,'yyyy-mm-dd') <= '20011-01-01'
);
-------------------------------------------------------------------------------
速度很快,而且结果也能出来。
卡死?没反应?
估计是你定义的into变量的类型有问题。
与查询的3个值:
decode(num1,null,0,num1) ,
decode(num1,null,0,0,0,round(num3/num1*100,2)) ,
decode(num1,null,0,0,0,round(num2/num1*100,2))
有的类型不匹配,导致SQL运行不下去,就僵死在那了。
注释掉:
select into v_num1,v_num2,v_num3
就能很快出来数据。
所以类型不匹配的问题,好像也不存在。
再说varchar2不存在类型不匹配的问题。
把你的这个函数贴出来看看?
还有你的里面的汇总要是没结果匹配 也是0
你外面还要这么多的处理干什么 搞不懂你
能用静态的 尽量静态