open gd_fy for select qijian,kmbh,gsdm,sum(jine) from sap_zfi001 where qijian =qihao group by qijian,kmbh,gsdm
union all select qijian,kmbh,gsdm,sum(jine) from sap_zfi001 where qijian =syqh group by qijian,kmbh,gsdm;
可以合并成为一个,为什么分两个。
create or replace procedure proc1_1_1(qihao in out varchar2)
is
gs_id varchar2(50); --公司编号
km_id varchar2(50); --科目编号
qhyf number;--时间变量1
qhyf1 varchar2(50);--时间变量2
qijian1 varchar2(50);--期号
syqh varchar2(50);--上月份期号
je_id number; --累计费用
col_id varchar2(100);--主键
kcount number;
kcount1 number;
type cur is ref cursor ;--定一个游标
gd_fy cur ;
begin
--求上月份期号如果这个月是1月份,就把上月累计设置为0
if( substr(qihao,length(qihao)-1,length(qihao))='01') then
syqh := '';
else
qhyf := to_number(substr(qihao,length(qihao)-1,length(qihao)));
qhyf := qhyf-1;
if(length(qhyf)=1) then
qhyf1 := '0'||to_char(qhyf) ;
else
qhyf1 := to_char(qhyf);
end if;
syqh := substr(qihao,1,length(qihao)-2)||qhyf1 ;
end if;
dbms_output.enable(9999999999999);
dbms_output.put_line('souser'||syqh);
open gd_fy for select qijian,kmbh,gsdm,sum(jine) from sap_zfi001 where qijian =qihao group by qijian,kmbh,gsdm
union all select qijian,kmbh,gsdm,sum(jine) from sap_zfi001 where qijian =syqh group by qijian,kmbh,gsdm;
loop
fetch gd_fy into qijian1,km_id,gs_id ,je_id;
select count(*) into kcount from table1 where col12_3 = km_id and col12_6 =gs_id ;
if(kcount =1) then
select col12_1 into col_id from table1 where col12_3 = km_id and col_1_1_12_6 =gs_id ;
select count(*) into kcount1 from table2 where col12_1 =col_id and it_state = 4||qijian1;
if(qijian1=qihao) then
if(kcount1 =0) then
execute immediate 'insert into table2 (col12_1,it_state,col12_14) values(:1,4||:2,:3)'using col_id,qijian1,je_id;
commit;
dbms_output.put_line('本月累计insert--'||je_id);
else
execute immediate 'update 科目动态表2 set col12_14=:1 where col12_1=:2 and it_state=4||:3 'using je_id,col_id,qijian1;
commit;
dbms_output.put_line('本月累计update--'||je_id);
end if;
end if;
if(qijian1=syqh) then
if(kcount1 =0) then
execute immediate 'insert into table2 (col12_1,it_state,col12_15) values(:1,4||:2,:3)'using col_id,qijian1,je_id;
commit;
dbms_output.put_line('上月累计insert--'||je_id);
else
execute immediate 'update 科目动态表2 set col12_15=:1 where col12_1=:2 and it_state=4||:3'using je_id,col_id,qijian1;
commit;
dbms_output.put_line('上月累计update--'||je_id);
end if;
end if;
end if;
end loop;
close gd_fy;