存储过程报 没有足够的值
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;
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_usernoall@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_usernoall@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_usernoall@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_usernoall@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;
end if;
end jsl;
[解决办法]
这么大的过程咋看啊。
首先看看你使用的工具在报“没有足够的值”的时候，能不能具体定位到某一行；
如果不能的话，就需要你检查一下代码中SELECT INTO的时候，字段是不是都能匹配上啊。
这个错误，其实就是哪里字段不够造成的。
[解决办法]
把报错的信息发出来看看！！
[解决办法]
最好把格式先调整下 看着头昏昏的 然后报错信息贴出来