oracle中没有足够的值,存储过程报 没有足够的值,该怎么解决

当前位置:我的异常网» Oracle开发 » 存储过程报 没有足够的值,该怎么解决

存储过程报 没有足够的值,该怎么解决

www.myexceptions.net  网友分享于：2013-05-24  浏览：80次

存储过程报 没有足够的值

create or replace procedure jsl

(isNowMonth in varchar2,

place_no in varchar2,

nowMonth in varchar2,

preYear in varchar2,

thisYear in varchar2,

copyerName in varchar2,

bigMonth in varchar2,

dyys out varchar2,

dyss out varchar2,

blys out varchar2,

blss out varchar2,

lnys out varchar2,

lnss out varchar2)

is

begin

if(isNowMonth=1)then

select zc.copyer,sum(case when l.gather_month=nowMonth then l.fact_charge/100 else 0 end) into dyys

from zj_copyer_userno@szlink zc,lack_charge l where zc.place_no=place_no and zc.gather_month=nowMonth and zc.copyer=copyerName and l.user_no = zc.user_no group by zc.copyer order by zc.copyer;

select zc.copyer,sum(case when f.gather_month=nowMonth then f.fact_charge/100 else 0 end) into dyss

from zj_copyer_userno@szlink zc,fact_charge f where zc.place_no=place_no and zc.gather_month=nowMonth and zc.copyer=copyerName and f.user_no = zc.user_no group by zc.copyer order by zc.copyer;

if(bigMonth=1)then

select zc.copyer,sum(case when f.gather_month between preYear and thisYear and substr(f.charge_date,0,6)=nowMonth then f.fact_charge/100 else 0 end)+ sum(case when l.gather_month between preYear and thisYear and l.CANCEL_FLAG='0' then l.fact_charge/100 else 0 end) into blys

from zj_copyer_userno@szlink zc,fact_charge f,lack_charge l where zc.place_no=place_no and zc.gather_month=nowMonth and zc.copyer=copyerName and f.user_no = zc.user_no and l.user_no=zc.user_no group by zc.copyer order by zc.copyer;

else

blys:=0;

end if;

if(bigMonth=1)then

select zc.copyer,sum(case when f.gather_month between preYear and thisYear and substr(f.charge_date,0,6)=nowMonth then f.fact_charge/100 else 0 end) into blss

from zj_copyer_userno@szlink zc,fact_charge f,lack_charge l where zc.place_no=place_no and zc.gather_month=nowMonth and zc.copyer=copyerName and f.user_no = zc.user_no and l.user_no=zc.user_no group by zc.copyer order by zc.copyer;

else

blss:=0;

end if;

select zc.copyer,sum(case when f.gather_month

from zj_copyer_userno@szlink zc,fact_charge f,lack_charge l where zc.place_no=place_no and zc.gather_month=nowMonth and zc.copyer=copyerName and f.user_no = zc.user_no and l.user_no=zc.user_no group by zc.copyer order by zc.copyer;

select zc.copyer,sum(case when f.gather_month

from zj_copyer_userno@szlink zc,fact_charge f where zc.place_no=place_no and zc.gather_month=nowMonth and zc.copyer=copyerName and f.user_no = zc.user_no group by zc.copyer order by zc.copyer;

else

select zc.copyer,sum(case when l.gather_month=nowMonth then l.fact_charge/100 else 0 end) into dyys

from zj_copyer_usernoall@szlink zc,lack_charge l where zc.place_no=place_no and zc.gather_month=nowMonth and zc.copyer=copyerName and l.user_no = zc.user_no group by zc.copyer order by zc.copyer;

select zc.copyer,sum(case when f.gather_month=nowMonth then f.fact_charge/100 else 0 end) into dyss

from zj_copyer_userno@szlink zc,fact_charge f where zc.place_no=place_no and zc.gather_month=nowMonth and zc.copyer=copyerName and f.user_no = zc.user_no group by zc.copyer order by zc.copyer;

文章评论