ORACLE 存储过程 游标嵌套优化，请帮忙指点!()

create or replace function FN_NS_ZJDZD(

                                    kjqj   in Varchar2,   --会计期间

                                    co_code   Varchar2,   --预算单位

                                    ysnd      Varchar2,   --预算年度

                                    ztdm      varchar2    -- 账套代码

                                    )

  return types.cursortypes is

  Result types.cursortypes;





  v_sql1 varchar2(32767);

  v_sql2 varchar2(32767);

  v_sql3 varchar2(32767);

  v_sql4 varchar2(32767);

  v_sql5 varchar2(32767);

  v_sql10 varchar2(32767);

  I       INT;

  I_INT   INT;

  L_JFJE number(20,6);

  L_DFJE number(20,6);

  L_NAME VARCHAR2(500);

  L_JD   VARCHAR2(2);

  L_YE   NUMBER(20,6);

  L_TIRE  INT;

  L_TIRE1 INT;

  l_acc_code varchar2(100);

  l_acc_name1  varchar2(500);

  y_b_acc_code varchar2(100);

  y_b_acc_name varchar2(200);

  y_IS_LOWEST  varchar2(2);

  y_tier       varchar2(5);

  y_code       varchar2(100);

  y_name       varchar2(200);

  l_dwbl       varchar2(100);

  l_dwb2       varchar2(100);

  l_rownum     int;

  l_rownum1     int;

  l_rownum2     int;    

  l_bzzc       varchar2(100); --补助支出

  AAA           INT;

    

Begin

  DELETE FROM DB_NS_ZJDZD;     -------清空表

  I:=0;                     -------附初值

  I_INT:=0;                 -------附初值

  L_NAME:='';

  L_JFJE:=0;

  L_DFJE:=0;

  L_JD:='';

  l_ye:=0;

  L_TIRE:=0;

  L_TIRE1:=0;

  

  CASE 

    when co_code='1014600' THEN l_dwbl:='511010101';l_dwb2:='511010201';  

    when co_code='1014700' THEN l_dwbl:='511010102';l_dwb2:='511010202';  

    when co_code='1014800' THEN l_dwbl:='511010103';l_dwb2:='511010203';   

    when co_code='1014900' THEN l_dwbl:='511010104';l_dwb2:='511010204';  

  END CASE;     

  

  

  

  

  ------------------------------------------游标处理 (处理支出)

  declare



          name1 varchar2(100);

          l_low1    varchar2(1);

                   

  CURSOR authors_cursor IS

            select B.ACC_CODE,sum(DR_AMT) TOTAL_DR_AMT,sum(CR_AMT) TOTAL_CR_AMT

            from V_GL_ALL_BAL B

            where FIS_PERD >= 1

            AND FIS_PERD <= to_number(kjqj)

            AND B.CO_CODE = '1'

            AND B.ACCOUNT_ID = ztdm

            AND B.FISCAL = ysnd

/*            and (B.ACC_CODE in (l_dwbl)

            OR B.ACC_CODE in (l_dwb2))*/  --by feng20121226

              and (B.ACC_CODE in (l_dwbl,l_dwb2)

            group by B.ACC_CODE

            order by B.ACC_CODE;



  CURSOR authors_cursor1 IS

          SELECT b_acc_code,b_acc_name,IS_LOWEST,c.tier,rownum FROM  gl_bacc  c

          WHERE c.co_code=1

          and c.account_id=ztdm

          and b_acc_code like '2%' 

          and c.fiscal=ysnd        

          order by b_acc_code;

          

  CURSOR authors_cursor2 IS

          SELECT co_code,co_name,rownum  from as_company n

          where n.IS_LOWEST='Y'

          and n.nd=ysnd

          ORDER BY co_code;    





    BEGIN

         OPEN authors_cursor;

         loop

         FETCH authors_cursor

          into l_acc_code,L_JFJE,L_DFJE;

          exit when authors_cursor%notfound;

                       

                     V_SQL1:='select acc_name1,rownum 

                              from gl_coa_acc B

                              where b.fiscal='''||ysnd||'''

                              and co_code=''1''

                              and account_id= '''||ztdm||'''

                              and acc_code ='''||l_acc_code||'''';

                     execute immediate V_SQL1 into l_acc_name1,l_rownum;

                     CASE WHEN (L_JFJE-L_DFJE)=0 THEN L_Jd:='平';l_ye:=0;

                          WHEN (L_JFJE-L_DFJE)>0 THEN L_Jd:='借';l_ye:=L_JFJE-L_DFJE;

                          WHEN (L_JFJE-L_DFJE)<0 THEN L_Jd:='贷';l_ye:=L_DFJE-L_JFJE;

                     END CASE;

                     INSERT INTO DB_NS_ZJDZD(co_coa,remark,JFJE,DFJE,JORD,YUEE,xuhao,b_acc_code,co_code) values (l_acc_code,l_acc_name1,L_JFJE,L_DFJE,L_Jd,l_ye,1,1+(0.01*l_rownum),'1');

                     ----------------1. 插入会计科目

                       OPEN authors_cursor1;

                       loop

                       FETCH authors_cursor1

                        into y_b_acc_code,y_b_acc_name,y_IS_LOWEST,y_tier,l_rownum1;

                        exit when authors_cursor1%notfound;

                        IF y_b_acc_code = '2130211' then 

                           aaa:=1 ;

                        end if;

                        

                          --判断科目是否有发生

                      /* V_SQL4:=' SELECT count(*)  FROM GL_VOU_DETAIL a, GL_VOU_DETAIL_ass b

                                    WHERE a.fiscal='''||ysnd||'''

                                    AND a.acc_code='''||l_acc_code||'''

                                    AND a.account_id='''||ztdm||'''

                                    and substr(a.vou_no,4,2)>=''01''

                                    and substr(a.vou_no,4,2)<='''||kjqj||'''

                                    AND b.b_acc_code like '''||y_b_acc_code||'%''

                                    and a.co_code=b.co_code

                                    and a.fiscal=b.fiscal

                                    and a.account_id=b.account_id

                                    and a.vou_no=b.vou_no

                                    and a.vou_seq=b.vou_seq ';*/

                         V_SQL4:='SELECT COUNT(*) FROM V_GL_ALL_BAL A

                                   WHERE A.FISCAL='''||ysnd||'''

                                     AND A.acc_code='''||l_acc_code||'''

                                     AND a.account_id='''||ztdm||'''

                                     AND FIS_PERD BETWEEN (''01'') AND ('''||kjqj||''')   

                                     AND B_ACC_CODE LIKE '''||y_b_acc_code||'%''';        

                       execute immediate v_sql4 into I;