create or replace procedure GetNewJb1( startrq in date,endrq in date, jxlb in out varchar2) as cursor Cur_NewJb1 is select * from NewJb1; tt number;begin if jxlb is null then jxlb := ''; end if ; delete from newjb1; insert into NewJb1(rq,jxlb,lbmc,trans_head,col01,col02,col03, col04,col05,col06,col07,col08,col09,col10, col11,col12,col13,col14,col15,col16,col17, col18,col19,col20_H,col20,col21_H,col21, col22_H,col22,col23_H,col23,col24_H,col24, col25_H,col25) select tjrq,jxlb,lbmc,trans_head,sum(col01),sum(col02),sum(col03), sum(col04),sum(col05),sum(col06),sum(col07),sum(col08),sum(col09),sum(col10), sum(col11),sum(col12),sum(col13),sum(col14),sum(col15),sum(col16),sum(col17), sum(col18),sum(col19),sum(col20_H),sum(col20),sum(col21_H),sum(col21), sum(col22_H),sum(col22),sum(col23_H),sum(col23),sum(col24_H),sum(col24), sum(col25_H),sum(col25) from NewJb1Rj where jxlb=jxlb and tjrq>=startrq and tjrq<=endrq group by tjrq,jxlb,lbmc,trans_head; update NewJb1 set col19=col20+col21+col22+col23+col24+col25; update NewJb1 set col07=col08+col09+col10+col11+col12+col13+col14+col15; update NewJb1 set col06=col07+col17+col18+col19 ; update NewJb1 set col01=col02+col03+col05+col06-col04 ; update NewJb1 set col19_h=decode(col06,null,0,0,0,round((col19/col06)*100,1)); update NewJb1 set col20_h=decode(col06,null,0,0,0,round((col20/col06)*100,1)); update NewJb1 set col21_h=decode(col06,null,0,0,0,round((col21/col06)*100,1)); update NewJb1 set col22_h=decode(col06,null,0,0,0,round((col22/col06)*100,1)); update NewJb1 set col23_h=decode(col06,null,0,0,0,round((col23/col06)*100,1)); update NewJb1 set col24_h=decode(col06,null,0,0,0,round((col24/col06)*100,1)); update NewJb1 set col25_h=decode(col06,null,0,0,0,round((col25/col06)*100,1)); commit; 我想在这顺便问一西如果我没有这个COMMIT的动作 在程序的执行的过程当中有什么影响? end;
真的太奇怪了啊
我直接用ODAC的那些控件来连ORACLE的话就没有什么问题
但就是速度比较慢
没有谁遇到过吗?
干脆那位大哥
把你用ADO连ORACLE的ADOCONNECTION1的代码全COPY一份给我看看
以前连SQL 是没有一点问题的,
真晕
startrq in date,endrq in date,
jxlb in out varchar2)
as
cursor Cur_NewJb1 is select * from NewJb1;
tt number;begin
if jxlb is null then
jxlb := '';
end if ;
delete from newjb1;
insert into NewJb1(rq,jxlb,lbmc,trans_head,col01,col02,col03,
col04,col05,col06,col07,col08,col09,col10,
col11,col12,col13,col14,col15,col16,col17,
col18,col19,col20_H,col20,col21_H,col21,
col22_H,col22,col23_H,col23,col24_H,col24,
col25_H,col25)
select
tjrq,jxlb,lbmc,trans_head,sum(col01),sum(col02),sum(col03),
sum(col04),sum(col05),sum(col06),sum(col07),sum(col08),sum(col09),sum(col10),
sum(col11),sum(col12),sum(col13),sum(col14),sum(col15),sum(col16),sum(col17),
sum(col18),sum(col19),sum(col20_H),sum(col20),sum(col21_H),sum(col21),
sum(col22_H),sum(col22),sum(col23_H),sum(col23),sum(col24_H),sum(col24),
sum(col25_H),sum(col25)
from NewJb1Rj
where jxlb=jxlb
and tjrq>=startrq
and tjrq<=endrq
group by tjrq,jxlb,lbmc,trans_head; update NewJb1 set col19=col20+col21+col22+col23+col24+col25;
update NewJb1 set col07=col08+col09+col10+col11+col12+col13+col14+col15;
update NewJb1 set col06=col07+col17+col18+col19 ;
update NewJb1 set col01=col02+col03+col05+col06-col04 ;
update NewJb1 set col19_h=decode(col06,null,0,0,0,round((col19/col06)*100,1));
update NewJb1 set col20_h=decode(col06,null,0,0,0,round((col20/col06)*100,1));
update NewJb1 set col21_h=decode(col06,null,0,0,0,round((col21/col06)*100,1));
update NewJb1 set col22_h=decode(col06,null,0,0,0,round((col22/col06)*100,1));
update NewJb1 set col23_h=decode(col06,null,0,0,0,round((col23/col06)*100,1));
update NewJb1 set col24_h=decode(col06,null,0,0,0,round((col24/col06)*100,1));
update NewJb1 set col25_h=decode(col06,null,0,0,0,round((col25/col06)*100,1));
commit;
我想在这顺便问一西如果我没有这个COMMIT的动作
在程序的执行的过程当中有什么影响?
end;
而我用TOAD 编译是通过了的啊