oracle存储过程
各位高手看看我写的存储过程有没有问题 如果有 问题在那里 以及 怎么调用这个存储过程或者说怎么测试这个存储过程

create or replace package pkg_gfwf_fwlx is

  TYPE Result_Cur is REF CURSOR;
  PROCEDURE SP_Rpt_Leave(yyyy number,fwlx varchar2,dw varchar2,qy VARCHAR2,hy varchar2,p_Result out Result_Cur);

end;

create or replace package body pkg_gfwf_fwlx IS
  PROCEDURE SP_Rpt_Leave(yyyy number,fwlx varchar2,dw varchar2,qy VARCHAR2,hy varchar2,p_Result out Result_Cur) IS
  v_SQL VARCHAR2(4000);
  BEGIN
  v_SQL :='select rownum,a.entname,b.name qyname, d.name hyname ,f.zlmc zlmc , nvl(g.this_year,0) this,nvl(e.spsj,0)sbl,nvl(h.last_year,0)last_year
from gfwf_gfcsdw a 
left outer join code_region b on substr(a.regioncode,1,6)=b.code 
left outer join gfwf_sbdj_qyjbxx c on c.wrybh=a.WRYBH and to_char(c.apply_date,''yyyy'') ='||yyyy||'
left outer join gfwf_sbhylb d on nvl(c.hylb,8)=d.code 
left outer join gfwf_sbdj_fwcsqxqk e on c.gfsbdjbh=e.gfsbdjbh and e.fwlb=2 and e.fwlx ='||fwlx||'
left join gfwf_fwzl f on f.zlbh='||fwlx||'
left outer join (

  select  
  d.entname dwmc,to_char(apply_date,''yyyy'')sj ,fwlx ,sum(SL) this_year
  from gfwf_gfcsdw d 
  left outer join 
  ( select * from GFWF_ZYLD a inner join GFWF_ZYLD_WXFW b on a.zyldh=b.zyldh and fwlx='||fwlx||' ) a 
  on d.WRYBH=a.csdwbh and to_char(apply_date,''yyyy'')='||yyyy||'
  group by d.entname, to_char(apply_date,''yyyy'') ,fwlx 
)g on g.dwmc=a.entname and e.fwlx=g.fwlx and to_char(c.apply_date,''yyyy'')=g.sj
left outer join (

  select  
  d.entname dwmc,to_char(apply_date,''yyyy'')sj ,fwlx,sum(SL) last_year
  from gfwf_gfcsdw d 
  left outer join 
  ( select * from GFWF_ZYLD a inner join GFWF_ZYLD_WXFW b on a.zyldh=b.zyldh and fwlx='||fwlx||') a 
  on d.WRYBH=a.csdwbh and to_char(apply_date,''yyyy'')='||yyyy||'-1
  group by d.entname, to_char(apply_date,''yyyy'') ,fwlx 
)h on h.dwmc=a.entname and e.fwlx=h.fwlx and to_char(c.apply_date,''yyyy'')=h.sj

where a.ENTNAME like ''%'||dw||'%'' 
';
  IF qy IS NOT NULL THEN
  v_SQL := v_SQL || ' AND b.code = '||qy;
  END IF;
  IF hy IS NOT NULL THEN
  v_SQL := v_SQL || ' AND d.code = '||hy;
  END IF;

  v_SQL := v_SQL||' order by rownum';

  open p_Result for v_SQL ;
  END SP_Rpt_Leave;
end; --end package


------解决方案--------------------

SQL code

declare
    r_cursor     pkg_gfwf_fwlx.Result_Cur;
begin
    pkg_gfwf_fwlx.SP_Rpt_Leave(2011,'a','b','c','d', r_cursor);
end;
/