都是在SQL/PLUS WORKSHEET里执行，
这么写是成功的：
select distinct substr(a.temp,instr(a.temp,'/')+1,7)
from t_em_chqfee a
where a.ctnoper = 'ANL' and a.vessel = 'OOCL SYDNEY' and a.voyage = '010N' and a.ioflag = 'I'
and a.yxr = '上海新XXX公司'
and a.cqf > 0.0
and (to_char(a.jsdate,'yyyymmdd') between '20040501' and '20040531' )
and exists(select bb from (select trunc(sum(b.cqf + nvl(b.bgf,0.0) + nvl(b.czf,0.0) + nvl(b.zxf,0.0)) * 8.3,0) bb,
b.temp
from t_em_chqfee b
where b.ctnoper = 'ANL' and b.vessel = 'OOCL SYDNEY' and b.voyage = '010N' and b.ioflag = 'I'
and b.yxr = '上海新XXX公司'
and b.cqf > 0.0
and (to_char(b.jsdate,'yyyymmdd') between '20040501' and '20040531' )
group by b.temp
) where bb = 1460.0);
但如果这么写的话，则报错：
DECLARE
*
ERROR 位于第 1 行:
ORA-01403: 未找到数据
ORA-06512: 在line 15
DECLARE
l_ctnoper varchar2(30);
l_vesselvarchar2(22);
l_voyagevarchar2(6);
l_yxr
varchar2(50);
n_money
number(19,2);
ff
varchar(10);
begin
l_ctnoper := 'ANL';
l_vessel := 'OOCL SYDNEY';
l_voyage := '010N';
l_yxr := '上海新XXX公司';
n_money := 1460.0;
select distinct substr(a.temp,instr(a.temp,'/')+1,7)
into ff
from t_em_chqfee a
where a.ctnoper = l_ctnoper and a.vessel = l_vessel and a.voyage = l_voyage and a.ioflag = 'I'
and a.yxr = l_yxr
and a.cqf > 0.0
and (to_char(a.jsdate,'yyyymmdd') between '20040501' and '20040531' )
and exists(select bb from (select trunc(sum(b.cqf + nvl(b.bgf,0.0) + nvl(b.czf,0.0) + nvl(b.zxf,0.0)) * 8.3,0) bb,
b.temp
from t_em_chqfee b
where b.ctnoper = l_ctnoper and b.vessel = l_vessel and b.voyage = l_voyage and b.ioflag = 'I'
and b.yxr = l_yxr
and b.cqf > 0.0
and (to_char(b.jsdate,'yyyymmdd') between '20040501' and '20040531' )
group by b.temp
) where bb = n_money);
end;
|
