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;
(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;
解决方案 »
- ORACLE 10G 如何创建数据库~在线等
- 在linux下安装oracle,是否要专门的用户和组
- Forms里如何读写multi-line的text item
- 截掉字符串"HelloWorldh"中首尾的"H"和"h"
- C# nhibernate config文件配置的菜鸟问题
- 求救!!!在线等待ing.....
- [求教]一个比较麻烦的sql语句
- 在线等:TNS:没有监听器?在哪启动或如何配置它?
- 初学者关于oracle汉字集设置问题
- 关于vb+oracle开发的问题,为什么我一运行我的应用程序,系统就写一个set.log文件,吧我的机器搞得很慢!为什么阿。事什么原因
- 一条简单的sql语句 case when 提示错误 or不存在关键字
- 使用游标逐行更新数据
首先看看你使用的工具在报“没有足够的值”的时候,能不能具体定位到某一行;
如果不能的话,就需要你检查一下代码中SELECT INTO的时候,字段是不是都能匹配上啊。
这个错误,其实就是哪里字段不够造成的。
B:from 语句
a:报 没有足够的值
b:报 plsql:statement ignoreselect aa,sum(..)into 变量
之后我把aa删除就好了 不过现在多了一个问题 我查出的数据为空 也就是没有数据 把变量就是空 我给了变量默认值的
输出参数:=变量
页面调用输出参数 报空指针错误
null==输出参数?“0”:输出参数可我的jsp还是报空指针
我新手
这个怎么解决啊