用spool导出数据每导出一行,就会flush一下,所以比较慢。
我把数据全部放在内存中,一次导出,这样就快的多。
缺点是 如果数据量大了的话,也许会死机哦。
创建访问路径:
create or replace directory EXP_DIR as 'D:\filedir';
赋予权限:
grant read, write on directory exp_dir to zlb
存储过程:
create or replace procedure proc_exp
(
v_filename in varchar2
)
as
outputfile utl_file.file_type;
v_usernumber varchar2(20);
v_errorcode number;
v_errortext varchar2(200);
begin
outputfile := utl_file.fopen('EXP_DIR',v_filename,'w');
declare cursor cur_sms is
select usernumber FROM u_userservice;
begin
open cur_sms;
loop
fetch cur_sms into v_usernumber;
exit when cur_sms%notfound;
utl_file.put_line(outputfile , v_usernumber);
end loop;
close cur_sms;
utl_file.fflush(outputfile);
utl_file.fclose(outputfile);
end;
EXCEPTION
WHEN OTHERS THEN
begin
if utl_file.is_open(outputfile) then
utl_file.fclose(outputfile);
end if;
v_errorcode:=sqlcode;
v_errortext:=substr(sqlerrm,1,200);
insert into s_errlog(id,procname,errorcode,v_errortext,errtime)
values(seq_s_errlog_id.nextval,'proc_exp',v_errorcode,v_errortext,SYSDATE);
commit;
end;
end proc_exp;
最后修改于 2006-05-11 23:21
阅读(?)评论(0)