小弟不会存储过程，模仿改的，不知道为什么错在那里，还请高手帮忙
create or replace procedure p_clear_settlement_summary_rpt
( --清算服务费汇总单
V_BIZ_PKG in INT, --业务线
V_STTLMONTH in varchar2, --账期
V_CURRENCY_TYPE in varchar2, --币种
V_STATUS in int,
V_DOCTYPE in int,
r_dataset out pubdba.Pub_Pack.t_retdataset) as
begin
--返回结果集
if( V_CURRENCY_TYPE = 'USD' OR V_CURRENCY_TYPE = 'EUR')
begin
open r_dataset for
select row_number() OVER (PARTITION BY currency_type ORDER BY ClientCode) N,ClientCode,Prtiod,ServiceType,DocType,DocNumber,SettlementAmount from
(select ClientCode,Prtiod,ServiceType,DocType,DocNumber,SettlementAmount,currency_type from
(select a.cust ClientCode,
a.sttl_month Prtiod,
decode(a.biz_pkg, 1, 'GSM', 2, 'SIMM', 3, 'S2T', '') ServiceType,
'INV' DocType,
a.bill_num DocNumber,
a.chg SettlementAmount,
a.currency_type currency_type,
a.status status,
a.biz_pkg biz_pkg
from incdba.fn_clr_serv_invoice a
where V_DOCTYPE in (0,-99)
union all
select b.cust_carrier_cd ClientCode,
b.sttl_month Prtiod,
decode(b.biz_pkg, 1, 'GSM', 2, 'SIMM', 3, 'S2T', '') ServiceType,
decode(b.bill_type,2,'CN',3,'DN') DocType,
b.bill_num DocNumber,
decode(b.bill_type,2,decode(b.bill_side,0,-b.chg_svr,b.chg_svr),3,decode(b.bill_side,1,-b.chg_svr,b.chg_svr),0) SettlementAmount,
b.svr_currency_type currency_type,
b.status status,
b.biz_pkg biz_pkg
from incdba.fn_clr_serv_cn_dn b
where (b.bill_type=V_DOCTYPE or V_DOCTYPE=-99)
and b.bill_type in (2,3) and b.is_contained = 1)
where (biz_pkg=V_BIZ_PKG or V_BIZ_PKG='-99')
and Prtiod=V_STTLMONTH
and currency_type IN ('USD','EUR')
-- and (DocType= V_DOCTYPE or V_DOCTYPE = 'ALL')
and (status=V_STATUS or V_STATUS=-99)) ;
return;
end
else
begin
open r_dataset for
select ClientCode,Prtiod,ServiceType,DocType,DocNumber,SettlementAmount from
(select a.cust ClientCode,
a.sttl_month Prtiod,
decode(a.biz_pkg, 1, 'GSM', 2, 'SIMM', 3, 'S2T', '') ServiceType,
'INV' DocType,
a.bill_num DocNumber,
a.chg SettlementAmount,
a.currency_type currency_type,
a.status status,
a.biz_pkg biz_pkg
from incdba.fn_clr_serv_invoice a
where V_DOCTYPE in (0,-99)
union all
select b.cust_carrier_cd ClientCode,
b.sttl_month Prtiod,
decode(b.biz_pkg, 1, 'GSM', 2, 'SIMM', 3, 'S2T', '') ServiceType,
decode(b.bill_type,2,'CN',3,'DN') DocType,
b.bill_num DocNumber,
decode(b.bill_type,2,decode(b.bill_side,0,-b.chg_svr,b.chg_svr),3,decode(b.bill_side,1,-b.chg_svr,b.chg_svr),0) SettlementAmount,
b.svr_currency_type currency_type,
b.status status,
b.biz_pkg biz_pkg
from incdba.fn_clr_serv_cn_dn b
where (b.bill_type=V_DOCTYPE or V_DOCTYPE=-99)
and b.bill_type in (2,3) and b.is_contained = 1)
where (biz_pkg=V_BIZ_PKG or V_BIZ_PKG='-99')
and Prtiod=V_STTLMONTH
and currency_type=V_CURRENCY_TYPE
-- and (DocType= V_DOCTYPE or V_DOCTYPE = 'ALL')
and (status=V_STATUS or V_STATUS=-99) order by ClientCode;
return;
end
exception
--其他标准异常
when others then
return;
end p_clear_settlement_summary_rpt;
------解决思路----------------------
3、缺少end if
if 条件1 then 处理1; elsif 条件2 then 处理2; else 处理3; end if;