当前位置: 代码迷 >> Oracle开发 >> 存储过程报 没有足够的值,该怎么解决
  详细解决方案

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

热度:64   发布时间:2016-04-24 07:34:22.0
存储过程报 没有足够的值
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<preYear 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 lnys
  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<preYear and substr(f.charge_date,0,6)=nowMonth then f.fact_charge/100 else 0 end) into lnss
  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;
  相关解决方案