打开游标open out_return for这句报“缺少表达式”,这是怎么回事?怎么解决了?拜托
D_A_O 2017-11-30 05:31:02 create or replace package body bd_gysjylForms is
PROCEDURE gysjylForms(p_startTime in varchar2,p_endTime in varchar2,p_avgStartTime in varchar2,p_avgEndTime in varchar2,
p_year in varchar2,p_selectorg in varchar2,p_supplier in varchar2,p_wasteDown in varchar2,p_wasteUp in varchar2,
p_wasteRateDown in varchar2,p_wasteRateUp in varchar2,p_pageIndex in number,p_pageSize in number,out_return out cur) is
sqlstr VARCHAR2 (5000);
begin
sqlstr := 'select * from (select a.*,rownum rn from ( ';
sqlstr := sqlstr ||
'select s.c_id supplierid,
s.c_strcorpname,
round(nvl(a1.amountsum, 0), 4) amountsum,
round(nvl(a2.bdj, 0), 4) bdj,
round(nvl(a2.bdj - a1.amountsum, 0), 4) bdjwaste,
to_char(case
when a2.bdj = 0 or a2.bdj is null or
(a2.bdj - a1.amountsum) is null then
0
else
(a2.bdj - a1.amountsum) * 100 / a2.bdj
end,
"99999999990.99") || "%" bdjrate,
round(nvl(a3.firstamount - a1.amountsum, 0), 4) firstwaste,
to_char(case
when a3.firstamount = 0 or a3.firstamount is null or
(a3.firstamount - a1.amountsum) is null then
0
else
(a3.firstamount - a1.amountsum) * 100 / a3.firstamount
end,
"99999999990.99") || "%" firstwasterate,
round(nvl(a5.avgamount - a1.amountsum, 0), 4) avgwaste,
to_char(case
when a5.avgamount = 0 or a5.avgamount is null or
(a5.avgamount - a1.amountsum) is null then
0
else
(a5.avgamount - a1.amountsum) * 100 / a5.avgamount
end,
"99999999990.99") || "%" avgwasterate,
round(nvl(a6.baseamount - a1.amountsum, 0), 4) shoudongwaste,
to_char(case
when a6.baseamount = 0 or a6.baseamount is null or
(a6.baseamount - a1.amountsum) is null then
0
else
(a6.baseamount - a1.amountsum) * 100 / a6.baseamount
end,
"99999999990.99") || "%" shoudongwasterate
from t_supplier s
inner join (select sa.c_supplier, sum(sa.c_price * sa.c_qty) amountsum
from t_stockaffirm s
inner join t_stockaffirmpart sa
on s.c_id = sa.c_stockaffirm
where s.c_type != 3' ;
if p_startTime is not null then
sqlstr := sqlstr || ' and s.c_createdate >= to_date( ' || p_startTime || ',yyyy-mm-dd hh24:mi:ss)';
end if;
if p_endTime is not null then
sqlstr := sqlstr || ' and s.c_createdate <= to_date( ' || p_endTime || ',yyyy-mm-dd hh24:mi:ss)';
end if;
if p_selectorg is not null then
sqlstr := sqlstr || ' and s.c_createcomp = ' || p_selectorg;
end if;
sqlstr := sqlstr || '
group by sa.c_supplier) a1
on a1.c_supplier = s.c_id
left join (select sa2.c_supplier, sum(bp.c_bidprice * so.c_number) bdj
from t_bidprice bp
inner join t_stockorder so
on so.c_id = bp.c_stockorder
inner join t_stockaffirmpart sa2
on sa2.c_stockorder = so.c_id
inner join t_stockaffirm s2
on s2.c_id = sa2.c_stockaffirm where 1 = 1 ';
if p_startTime is not null then
sqlstr := sqlstr || ' and s2.c_createdate >= to_date( ' || p_startTime || ',yyyy-mm-dd hh24:mi:ss)';
end if;
if p_endTime is not null then
sqlstr := sqlstr || ' and s2.c_createdate <= to_date( ' || p_endTime || ',yyyy-mm-dd hh24:mi:ss)';
end if;
if p_selectorg is not null then
sqlstr := sqlstr || ' and s2.c_createcomp = ' || p_selectorg;
end if;
sqlstr := sqlstr || '
group by sa2.c_supplier) a2
on a2.c_supplier = s.c_id
left join (select bf.supplierid, sum(bf.firstprice) firstamount
from v_baobiao_firstquoteprice_supp bf
group by bf.supplierid) a3
on a3.supplierid = s.c_id
left join (select sa.c_supplier, sum(a51.avgprice * sa.c_qty) avgamount
from t_stockaffirm s
inner join t_stockaffirmpart sa
on sa.c_stockaffirm = s.c_id
inner join (select c.c_suppcode,
cp.c_prodcode,
avg(cp.c_price) avgprice
from t_contract c --平均价
inner join t_contractpart cp
on cp.c_contract = c.c_id
where c.c_status = 10002773 ';
if p_avgStartTime is not null then
sqlstr := sqlstr || ' and c.c_lastupdate >= to_date( ' || p_avgStartTime|| ',yyyy-mm-dd hh24:mi:ss)';
end if;
if p_avgEndTime is not null then
sqlstr := sqlstr || ' and c.c_lastupdate <= to_date( ' || p_avgEndTime|| ',yyyy-mm-dd hh24:mi:ss)';
end if;
sqlstr := sqlstr || '
group by c.c_suppcode, cp.c_prodcode) a51
on a51.c_suppcode = sa.c_supplier
and a51.c_prodcode = sa.c_prodcode
where s.c_type != 3 ';
if p_selectorg is not null then
sqlstr := sqlstr || ' and s.c_createcomp = ' || p_selectorg;
end if;
sqlstr := sqlstr || '
group by sa.c_supplier) a5
on a5.c_supplier = s.c_id
left join (select sa.c_supplier,
avg(a61.baseprice) * sum(sa.c_qty) baseamount
from t_stockaffirm s
inner join t_stockaffirmpart sa
on sa.c_stockaffirm = s.c_id
inner join (select m.c_prodid, avg(bp.c_baseprice) baseprice
from t_baseprice bp
inner join t_prod_mapper m
on m.c_company = bp.c_company_id
and m.c_comprodcode = bp.c_comprodcode ';
if p_year is not null then
sqlstr := sqlstr || ' where bp.c_year = ' || p_year;
end if;
sqlstr := sqlstr || '
group by m.c_prodid) a61
on a61.c_prodid = sa.c_prod
where s.c_type != 3 ';
sqlstr := sqlstr || ' and s.c_createcomp = ' || p_selectorg;
sqlstr := sqlstr || '
group by sa.c_supplier) a6
on a6.c_supplier = s.c_id
where exists (select 1
from t_contract c
where c.c_suppcode = s.c_id
and c.c_status = 10002773) ';
if p_supplier is not null then
sqlstr := sqlstr || ' and s.c_id in ( ' || p_supplier || ' ) ';
end if;
if p_wasteDown is not null then
sqlstr := sqlstr || ' and a5.avgamount - a1.amountsum >= to_number(' || p_wasteDown || ')';
end if;
if p_wasteUp is not null then
sqlstr := sqlstr || ' and a5.avgamount - a1.amountsum <= to_number(' || p_wasteUp || ')';
end if;
if p_wasteRateDown is not null then
sqlstr := sqlstr || ' (a5.avgamount - a1.amountsum) * 100 / a5.avgamount >= to_number(' || p_wasteRateDown || ')';
end if;
if p_wasteRateUp is not null then
sqlstr := sqlstr || ' and (a5.avgamount - a1.amountsum) * 100 / a5.avgamount <= to_number(' || p_wasteRateUp || ')';
end if;
sqlstr := sqlstr || ' order by s.c_id ';
sqlstr := sqlstr || ') a ) where rn < ' || ((p_pageIndex+1) * p_pageSize + 1) || ' and rn > ' || (p_pageIndex * p_pageSize);
sqlstr := sqlstr || '';
open out_return for
to_char(sqlstr);
end gysjylForms;
end bd_gysjylForms;