各位兄弟姐妹，乡亲父老。小弟的存储过程无奈嵌套了3层的游标，应该如何优化呢？存储过程很长，就分两段来写吧，希望各位指点，谢谢。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,re,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;
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,re,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;
V_SQL2:='select nvl(sum(DR_AMT),0) TOTAL_DR_AMT,nvl(sum(CR_AMT),0) 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.B_ACC_CODE LIKE '''||y_b_acc_code||'%''
and B.ACC_CODE ='''||l_acc_code||'''';
execute immediate V_SQL2 into L_JFJE,L_DFJE;
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;
----------如果是补助支出，将其的会计科目值变大'8888'
IF y_b_acc_code LIKE '230%' THEN
INSERT INTO DB_NS_ZJDZD(co_coa,re,JFJE,DFJE,JORD,YUEE,xuhao,gnkm,b_acc_code,co_code)
values ('8888',' ' ||y_b_acc_name,L_JFJE,L_DFJE,L_Jd,l_ye,2,y_b_acc_code,y_b_acc_code,1);
ELSE
INSERT INTO DB_NS_ZJDZD(co_coa,re,JFJE,DFJE,JORD,YUEE,xuhao,gnkm,b_acc_code,co_code)
values (l_acc_code,' ' ||y_b_acc_name,L_JFJE,L_DFJE,L_Jd,l_ye,2,y_b_acc_code,y_b_acc_code,1);
END IF;
IF y_IS_LOWEST='Y' THEN -------- 如果是末级，开启单位查询
OPEN authors_cursor2;
loop
FETCH authors_cursor2
into y_code,y_name,l_rownum2;
exit when authors_cursor2%notfound;
v_sql5:=' select count(*)
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.B_ACC_CODE LIKE '''||y_b_acc_code||'%''
and B.ACC_CODE ='''||l_acc_code||'''
and B.B_CO_CODE='''||y_code||'''';
execute immediate v_sql5 into I;
IF I>0 THEN
V_SQL3:='select 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.B_ACC_CODE LIKE '''||y_b_acc_code||'%''
and B.ACC_CODE ='''||l_acc_code||'''
and B.B_CO_CODE='''||y_code||'''
group by B.B_CO_CODE
order by B.B_CO_CODE';
execute immediate V_SQL3 into L_JFJE,L_DFJE;
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;
--------如果是补助支出-----------