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;
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; 可以合并成为一个,为什么分两个。execute immediate 。using je_id,col_id,qijian1; 直接写语句不就行了。不要使用execute immediate ,直接写insert ...values 或update
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;
每次的时间是多少?
union all select qijian,kmbh,gsdm,sum(jine) from sap_zfi001 where qijian =syqh group by qijian,kmbh,gsdm;
可以合并成为一个,为什么分两个。execute immediate 。using je_id,col_id,qijian1;
直接写语句不就行了。不要使用execute immediate ,直接写insert ...values 或update