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;
解决方案 »
- 关于job的操作
- 特殊字符问题创建了一个用户,密码含有&是登录不上,请问如何解决
- 这个select 语句哪儿错了?
- oracle服务器出现乱码
- 我现在想查看一台远程主机(unix系统)的配置文件有个命令是什么记不清了 请高手帮忙
- 请问为什么sqlplus登录成功后,(9i)oracle management server报错,请问如何解决呢?
- 单独执行正确的 SELECT 语句放在存储过程的游标中,出现编译错误,在存储过程中要如何写? 各位高手,帮帮忙!感激不尽!
- 请教:在8.1.7的OEM中,我的数据库不能STARTUP,IMPORT之前必须STARTUP吗?
- Developer中的全局变量设立和menu与form的关系!
- 启动数据库时报错 Error 57 initializing SQL*Plus Error loading message shared library
- 一条简单的sql语句 case when 提示错误 or不存在关键字
- 使用游标逐行更新数据
首先看看你使用的工具在报“没有足够的值”的时候,能不能具体定位到某一行;
如果不能的话,就需要你检查一下代码中SELECT INTO的时候,字段是不是都能匹配上啊。
这个错误,其实就是哪里字段不够造成的。
B:from 语句
a:报 没有足够的值
b:报 plsql:statement ignoreselect aa,sum(..)into 变量
之后我把aa删除就好了 不过现在多了一个问题 我查出的数据为空 也就是没有数据 把变量就是空 我给了变量默认值的
输出参数:=变量
页面调用输出参数 报空指针错误
null==输出参数?“0”:输出参数可我的jsp还是报空指针
我新手
这个怎么解决啊