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

fengfeng86 2012-12-26 10:46:28
各位兄弟姐妹,乡亲父老。小弟的存储过程无奈嵌套了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,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;
...全文
502 3 打赏 收藏 转发到动态 举报
写回复
用AI写文章
3 条回复
切换为时间正序
请发表友善的回复…
发表回复
fengfeng86 2013-04-06
  • 打赏
  • 举报
回复
引用 2 楼 jdsnhan 的回复:
太长了,能不能把游标的内容和里面的内容组合成一个sql
已经实现了,给分给你吧。谢谢。
jdsnhan 2012-12-27
  • 打赏
  • 举报
回复
太长了,能不能把游标的内容和里面的内容组合成一个sql
fengfeng86 2012-12-26
  • 打赏
  • 举报
回复
                     IF I > 0 THEN       --如果有发生数
                        
                              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,remark,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,remark,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;
                                          --------如果是补助支出-----------

17,078

社区成员

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧