如: create or replace PROCEDURE daily_end --日终处理 (v_zoneno in varchar2, IMDays in number, --当月的实际天数 IYDays in number, --当年的实际天数 workdate in date, v_msg out number) IS quasi_rec quasi_customer%rowtype; --quasi_customer表记录 best_rec best_customer%rowtype; --best_customer表记录 v_error_code NUMBER; v_error_message VARCHAR2(255);
cursor quasi_cur is select * from quasi_customer where to_char(init_date,'DD-MON-YY') != to_char(workdate,'DD-MON-YY') order by cino,accno,currtype desc for update; cursor best_cur is select * from best_customer where to_char(init_date,'DD-MON-YY') != to_char(workdate,'DD-MON-YY') order by cino,open_branch desc for update; BEGIN daily_agent_trade_proce(v_zoneno,workdate,v_msg); if(v_msg!=0) then return; end if; open quasi_cur; fetch quasi_cur into quasi_rec; while quasi_cur%found loop --循环读取quasi_customer记录 update quasi_customer set balance=balance+current_balance,y_balance=y_balance+current_balance, average_balance=(balance+current_balance)/IMDAYS, y_average_balance=(y_balance+current_balance)/IYDAYS where current of quasi_cur; fetch quasi_cur into quasi_rec; end loop; close quasi_cur;
open best_cur; fetch best_cur into best_rec; while best_cur%found loop --循环读取best_customer记录 update best_customer set balance=balance+current_balance,y_balance=y_balance+current_balance, y_average_balance=(y_balance+current_balance)/IYDAYS, d_balance=d_balance+d_current_balance,d_y_balance=d_y_balance+d_current_balance, d_y_average_balance=(d_y_balance+d_current_balance)/IYDAYS where current of best_cur; fetch best_cur into best_rec; end loop; close best_cur; v_msg := 0; COMMIT; EXCEPTION WHEN OTHERS THEN -- COMMIT; ROLLBACK; v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_error_code)||': '|| v_error_message); v_msg := SQLCODE; --v_msg := 1; END daily_end; / --exit;顺便再提个问题,我这两张表均很大,各有几百万条记录,造成运行较慢,有办法提高效率吗?
1. 希望你贴的是 daily_agent_trade_proce(v_zoneno,workdate,v_msg);的代码,看看为什么调用出错。2. 不用cursor,直接完成批量update open quasi_cur; fetch quasi_cur into quasi_rec; while quasi_cur%found loop --循环读取quasi_customer记录 update quasi_customer set balance=balance+current_balance,y_balance=y_balance+current_balance, average_balance=(balance+current_balance)/IMDAYS, y_average_balance=(y_balance+current_balance)/IYDAYS where current of quasi_cur; fetch quasi_cur into quasi_rec; end loop; close quasi_cur; 改为 update quasi_customer set balance=balance+current_balance,y_balance=y_balance+current_balance, average_balance=(balance+current_balance)/IMDAYS, y_average_balance=(y_balance+current_balance)/IYDAYS where to_char(init_date,'DD-MON-YY') != to_char(workdate,'DD-MON-YY') ;另一个cursor也是类似修改。
函数索引与普通索引区别在于索引多加了一函数,用于查询时出现函数作条件用 例: create index name_idx on quasi_customer (to_char(init_date,'DD-MON-YY'));
你的v_msg=>:msg什么意思??? 好象不对把
函数索引与普通索引区别在于索引多加了一函数,用于查询时出现函数作条件用 例: create index name_idx on quasi_customer (to_char(init_date,'DD-MON-YY'));是用于如下查询吗?: select * from quasi_customer where to_char(init_date,'DD-MM-YY')='2003-02-26';
还要在init.ora设置一个参数为true,才能生效。具体那个参数忘记了。:)
你这样执行一下试试: set serveroutput on;declare v_msg number; begin daily_end('aaa',1,2,sysdate,v_msg); dbms_output.put_line(to_char(v_msg)); end;
create or replace PROCEDURE daily_end --日终处理
(v_zoneno in varchar2,
IMDays in number, --当月的实际天数
IYDays in number, --当年的实际天数
workdate in date,
v_msg out number)
IS
quasi_rec quasi_customer%rowtype; --quasi_customer表记录
best_rec best_customer%rowtype; --best_customer表记录
v_error_code NUMBER;
v_error_message VARCHAR2(255);
cursor quasi_cur is
select * from quasi_customer
where to_char(init_date,'DD-MON-YY') != to_char(workdate,'DD-MON-YY')
order by cino,accno,currtype desc for update;
cursor best_cur is
select * from best_customer
where to_char(init_date,'DD-MON-YY') != to_char(workdate,'DD-MON-YY')
order by cino,open_branch desc for update;
BEGIN
daily_agent_trade_proce(v_zoneno,workdate,v_msg);
if(v_msg!=0) then
return;
end if;
open quasi_cur;
fetch quasi_cur into quasi_rec;
while quasi_cur%found loop --循环读取quasi_customer记录
update quasi_customer set balance=balance+current_balance,y_balance=y_balance+current_balance,
average_balance=(balance+current_balance)/IMDAYS,
y_average_balance=(y_balance+current_balance)/IYDAYS
where current of quasi_cur;
fetch quasi_cur into quasi_rec;
end loop;
close quasi_cur;
open best_cur;
fetch best_cur into best_rec;
while best_cur%found loop --循环读取best_customer记录
update best_customer set balance=balance+current_balance,y_balance=y_balance+current_balance,
y_average_balance=(y_balance+current_balance)/IYDAYS,
d_balance=d_balance+d_current_balance,d_y_balance=d_y_balance+d_current_balance,
d_y_average_balance=(d_y_balance+d_current_balance)/IYDAYS
where current of best_cur;
fetch best_cur into best_rec;
end loop;
close best_cur;
v_msg := 0;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
-- COMMIT;
ROLLBACK;
v_error_code := SQLCODE;
v_error_message := SQLERRM;
DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_error_code)||': '|| v_error_message);
v_msg := SQLCODE;
--v_msg := 1;
END daily_end;
/
--exit;顺便再提个问题,我这两张表均很大,各有几百万条记录,造成运行较慢,有办法提高效率吗?
不慢才怪!特别是并行处理时,大量的锁争用。
你不能用批量更新的办法吗?一定要用循环?我看你的程序是可以改为批量更新的啊。
还有,每个地方有两个fetch是干什么的。
-----------------------
没有仔细看你的程序,觉得要从根本上改起。
daily_agent_trade_proce(v_zoneno,workdate,v_msg);的代码,看看为什么调用出错。2. 不用cursor,直接完成批量update
open quasi_cur;
fetch quasi_cur into quasi_rec;
while quasi_cur%found loop --循环读取quasi_customer记录
update quasi_customer set balance=balance+current_balance,y_balance=y_balance+current_balance,
average_balance=(balance+current_balance)/IMDAYS,
y_average_balance=(y_balance+current_balance)/IYDAYS
where current of quasi_cur;
fetch quasi_cur into quasi_rec;
end loop;
close quasi_cur;
改为
update quasi_customer set
balance=balance+current_balance,y_balance=y_balance+current_balance,
average_balance=(balance+current_balance)/IMDAYS,
y_average_balance=(y_balance+current_balance)/IYDAYS
where to_char(init_date,'DD-MON-YY') != to_char(workdate,'DD-MON-YY') ;另一个cursor也是类似修改。
例:
create index name_idx on quasi_customer (to_char(init_date,'DD-MON-YY'));
好象不对把
例:
create index name_idx on quasi_customer (to_char(init_date,'DD-MON-YY'));是用于如下查询吗?:
select * from quasi_customer where to_char(init_date,'DD-MM-YY')='2003-02-26';
set serveroutput on;declare
v_msg number;
begin
daily_end('aaa',1,2,sysdate,v_msg);
dbms_output.put_line(to_char(v_msg));
end;
Query_rewrite_enabled=true