楼主程序可读性差,有时间要尽理减少代码重复性m_sql:='insert into m_table_pre values('||m_c1.servicekey||',
'||m_c1.chargemode||',
'||m_c1.chargeclass||',
'||m_c1.callcost||',
'||m_c1.caller||',
'||m_c1.callee||',
'||m_c1.called||',
'||m_c1.startdateandtime||',
'||m_c1.enddateandtime||',
'||m_c1.duration||',
'||m_c1.p_duration||',
'||m_caller_subsidiaryid||',
'||m_caller_type||',
'||m_callee_subsidiaryid||',
'||m_callee_company||')';
execute immediate m_sql;把所有ddl语句,修改为以上形式
'||m_c1.chargemode||',
'||m_c1.chargeclass||',
'||m_c1.callcost||',
'||m_c1.caller||',
'||m_c1.callee||',
'||m_c1.called||',
'||m_c1.startdateandtime||',
'||m_c1.enddateandtime||',
'||m_c1.duration||',
'||m_c1.p_duration||',
'||m_caller_subsidiaryid||',
'||m_caller_type||',
'||m_callee_subsidiaryid||',
'||m_callee_company||')';
execute immediate m_sql;把所有ddl语句,修改为以上形式
我自己看了发现 ,m_sql的生成可以这样:
msql1:='insert into m_table_pre values('||m_c1.servicekey||',
'||m_c1.chargemode||',
'||m_c1.chargeclass||',
'||m_c1.callcost||',
'||m_c1.caller||',
'||m_c1.callee||',
'||m_c1.called||';
m_sql2:='||m_c1.startdateandtime||',
'||m_c1.enddateandtime||',
'||m_c1.duration||',
'||m_c1.p_duration||',
'||m_caller_subsidiaryid||',
'||m_caller_type||',
'||m_callee_subsidiaryid||',
'||m_callee_company||')';
然后m_sql:=m_sql1||m_sql2;
在insert into m_table_201 的时候m_sql:=m_sql1||m_c1.cardid||m_sql2;还有哪些呢,请大家帮助指正,谢谢!
chargemode varchar2(20),
chargeclass varchar2(20),
callcost varchar2(20),
caller varchar2(20),
callee varchar2(20),
called varchar2(20),
cardid varchar2(20),
startdateandtime varchar2(14),
enddateandtime varchar2(14),
duration varchar(8));create table companies_codes(name varchar2(32),subsidiaryid number(9),netnumber varchar2(8) primary key,codetype varchar2(32));m_table_intellective 、m_table_pre、m_table_201等所指向的表就是在buffer的基础上增加
(
p_duration number(8),callee_subsidiaryid number(9),caller_subsidiaryid number(9),caller_type varchar2(32),callee_company varchar2(32)这些字段。
create or replace procedure init_process
(
i_month in number,
i_errors in number,
o_res out number
)
is
m_table_pre varchar2(32);
m_table_201 varchar2(32);
m_table_intellective varchar2(32);
m_type number(4);
m_cnt number:=1;
m_errors number:=0;
m_caller_subsidiaryid number(9):=null;
m_caller_type varchar2(16):=null;
m_callee_subsidiaryid number(9):=null;
m_callee_company varchar2(16):=null;
m_sql varchar2(2048);
v_error_msg varchar2(300);
cursor c1 is select servicekey ,
chargemode ,
chargeclass,
callcost ,
caller ,
callee ,
called ,
SPECIFICCHARGEDPAR cardid ,
startdateandtime ,
enddateandtime ,
duration ,
to_number(substr(duration,2,1))*86400+
(to_number(substr(duration,1,1))*10+to_number(substr(duration,4,1)))*3600+
(to_number(substr(duration,3,1))*10+to_number(substr(duration,6,1)))*60+
(to_number(substr(duration,5,1))*10+to_number(substr(duration,8,1)))+
(to_number(substr(duration,7,1))*0.1) p_duration,rowid
from buffer where flag is null;
cursor c2 is select * from companies_codes order by length(netnumber) desc;
begin
insert into errors_log values(null,sysdate,'start job',null);
select 'CALL_PRE_PAY_'||to_char(i_month) into m_table_pre from dual;
select 'CALL_201_'||to_char(i_month) into m_table_201 from dual;
select 'CALL_INTELLECTIVE_'||to_char(i_month) into m_table_intellective from dual;
o_res:=0;
for m_c1 in c1 loop
begin
m_cnt:=m_cnt+1;
if m_c1.servicekey=5 and m_c1.chargemode=1 then
begin
for m_c2 in c2 loop --程序要求在维护companies_codes表的时候,表中不能存在一项可被另一项包含的情况
if substr(m_c1.caller,4,length(m_c2.netnumber))=m_c2.netnumber then
m_caller_subsidiaryid:=m_c2.subsidiaryid;
m_caller_type:=m_c2.codetype;
end if;
if substr(m_c1.callee,1,length(m_c2.netnumber))=m_c2.netnumber then
m_callee_subsidiaryid:=m_c2.subsidiaryid;
m_callee_company:=m_c2.name;
end if;
exit when m_caller_subsidiaryid is not null and m_callee_subsidiaryid is not null;--此语句是否有助于减少运行时间,有待验证
end loop;
m_sql:='insert into m_table_pre '||
'values(m_c1.servicekey, '||
'm_c1.chargemode , '||
'm_c1.chargeclass, '||
'm_c1.callcost , '||
'm_c1.caller , '||
'm_c1.callee , '||
'm_c1.called , '||
'm_c1.startdateandtime , '||
'm_c1.enddateandtime , '||
'm_c1.duration , '||
'm_c1.p_duration, '||
'm_caller_subsidiaryid, '||
'm_caller_type, '||
'm_callee_subsidiaryid, '||
'm_callee_company)';
execute immediate m_sql;
exception
when others then
m_errors:=m_errors+1;
update buffer set flag=m_errors where rowid=m_c1.rowid;
v_error_msg:= substr(sqlerrm,1,256);
insert into errors_log(errorid,errortime,jobname,sys_err_info)
values(m_errors,sysdate,'insert into table '||m_table_pre||' error',v_error_msg);
if m_errors=i_errors then
return ;
end if;
end;
end if;
if m_c1.servicekey=6 and m_c1.chargemode=1 then
begin
for m_c2 in c2 loop --程序要求在维护companies_codes表的时候,表中不能存在一项可被另一项包含的情况
if substr(m_c1.caller,4,length(m_c2.netnumber))=m_c2.netnumber then
m_caller_subsidiaryid:=m_c2.subsidiaryid;
m_caller_type:=m_c2.codetype;
end if;
if substr(m_c1.callee,1,length(m_c2.netnumber))=m_c2.netnumber then
m_callee_subsidiaryid:=m_c2.subsidiaryid;
m_callee_company:=m_c2.name;
end if;
exit when m_caller_subsidiaryid is not null and m_callee_subsidiaryid is not null;--此语句是否有助于减少运行时间,有待验证
end loop;
m_sql:='insert into m_table_201 '||
'values(m_c1.servicekey, '||
'm_c1.chargemode , '||
'm_c1.chargeclass, '||
'm_c1.callcost , '||
'm_c1.caller , '||
'm_c1.callee , '||
'm_c1.called , '||
'm_c1.startdateandtime , '||
'm_c1.enddateandtime , '||
'm_c1.duration , '||
'm_c1.p_duration, '||
'm_caller_subsidiaryid, '||
'm_caller_type, '||
'm_callee_subsidiaryid, '||
'm_callee_company)';
execute immediate m_sql;
exception
when others then
m_errors:=m_errors+1;
update buffer set flag=m_errors where rowid=m_c1.rowid;
v_error_msg := substr(sqlerrm,1,256);
insert into errors_log values(m_errors,sysdate,'insert into table '||m_table_pre||' error',v_error_msg);
if m_errors=i_errors then
return ;
end if;
end;
end if;
if m_c1.servicekey=7 and m_c1.chargemode=1 then
begin
for m_c2 in c2 loop --程序要求在维护companies_codes表的时候,表中不能存在一项可被另一项包含的情况
if substr(m_c1.caller,4,length(m_c2.netnumber))=m_c2.netnumber then
m_caller_subsidiaryid:=m_c2.subsidiaryid;
m_caller_type:=m_c2.codetype;
end if;
if substr(m_c1.callee,1,length(m_c2.netnumber))=m_c2.netnumber then
m_callee_subsidiaryid:=m_c2.subsidiaryid;
m_callee_company:=m_c2.name;
end if;
exit when m_caller_subsidiaryid is not null and m_callee_subsidiaryid is not null;--此语句是否有助于减少运行时间,有待验证
end loop;
m_sql:='insert into m_table_intellective '||
'values(m_c1.servicekey, '||
'm_c1.chargemode , '||
'm_c1.chargeclass, '||
'm_c1.callcost , '||
'm_c1.caller , '||
'm_c1.callee , '||
'm_c1.called , '||
'm_c1.startdateandtime , '||
'm_c1.enddateandtime , '||
'm_c1.duration , '||
'm_c1.p_duration, '||
'm_caller_subsidiaryid, '||
'm_caller_type, '||
'm_callee_subsidiaryid, '||
'm_callee_company)';
execute immediate m_sql;
exception
when others then
m_errors:=m_errors+1;
v_error_msg := substr(sqlerrm,1,256);
update buffer set flag=m_errors where rowid=m_c1.rowid;
insert into errors_log values(m_errors,sysdate,'insert into table '||m_table_pre||' error',v_error_msg);
if m_errors=i_errors then
return ;
end if;
end;
end if;
if mod(m_cnt,100000)=0 then
commit;
end if;
m_caller_subsidiaryid:=null;
m_caller_type:=null;
m_callee_subsidiaryid:=null;
m_callee_company:=null;
end;
end loop;
insert into errors_log values(null,sysdate,'job completed',null);
commit;
return ;
end;
还是一样的错误,但是改用dbms_sql包就可以编译通过,应该是8.0.5的版本不支持
这样的写法。
另外,buffer表中存的记录达千万条,用以上的过程的话,1G内存,双CPU的机器处理
速度是150条/秒,这样的话的处理完毕要15小时以上。现在数据库是在win2000 advance
sever上,给SGA分了1/3的物理内存大小,请问怎么样调整SGA中各个缓冲区的大小有利
于提高运行速度?另外,程序还有什么地方可以优化的?
请关注,分不够可以再加,谢谢。