导航
  • 主页
  • Oracle 基础和管理
  • Oracle 高级技术
  • Oracle 认证与考试
  • 职位交流
  • 问答

存储过程报 没有足够的值

accp1002 2012-03-15 04:02:42
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;
...全文
156 点赞 收藏 5
写回复
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
accp1002 2012-03-15
A:select 语句
B:from 语句
a:报 没有足够的值
b:报 plsql:statement ignore

select aa,sum(..)into 变量


之后我把aa删除就好了

不过现在多了一个问题 我查出的数据为空 也就是没有数据 把变量就是空 我给了变量默认值的
输出参数:=变量

页面调用输出参数 报空指针错误
null==输出参数?“0”:输出参数

可我的jsp还是报空指针
我新手
这个怎么解决啊
回复
最好把格式先调整下 看着头昏昏的 然后报错信息贴出来
回复
ICE-word 2012-03-15
把报错的信息发出来看看!!
回复
yixilan 2012-03-15
这么大的过程咋看啊。
首先看看你使用的工具在报“没有足够的值”的时候,能不能具体定位到某一行;
如果不能的话,就需要你检查一下代码中SELECT INTO的时候,字段是不是都能匹配上啊。
这个错误,其实就是哪里字段不够造成的。
回复
accp1002 2012-03-15
我把zc.copyer 都删除就好了。。新手的悲剧
回复
发动态
发帖子
Oracle
创建于2007-09-28

1.6w+

社区成员

Oracle开发相关技术讨论
申请成为版主
社区公告
暂无公告