create or replace directory DIR_EXCEL as 'E:/test';//新建存储路径
create or replace procedure PRC_TASK_EXCEL is
out_file utl_file.file_type; --定义一个文件类型
L_FILENAME varchar2(200);
str1 varchar2(200);
str2 varchar2(200);
begin
--定义Excel文件名称
select to_char(sysdate, 'yyyymmddhh24miss') || '.xls'
into L_FILENAME
from dual;
--定义Excel文件抬头
select t1.demo1 || chr(9) into str1 from sku_demo t1;
select t2.demo2 || chr(9) into str2 from sku_demo t2;
--导出Excel文件
out_file := utl_file.fopen('OUTPUT_EXCEL', L_FILENAME, 'W');
--写入Excel抬头内容
utl_file.put(out_file, convert(str1, 'ZHS16GBK'));//根据Oracle数据库的字符编码进行转换
utl_file.put(out_file, convert(str2, 'ZHS16GBK'));
utl_file.put_line(out_file, '');
--循环写入Excel明细
for o in (select c.sku || chr(9) vin, c.DESCR_C || chr(9) autoid
from bas_sku c) loop
utl_file.put(out_file, convert(o.vin, 'ZHS16GBK'));
utl_file.put(out_file, convert(o.autoid, 'ZHS16GBK'));
utl_file.put_line(out_file, '');
end loop;
utl_file.fflush(out_file);
utl_file.fclose(out_file); --关闭文件流
--处理异常
exception
when others then
rollback;
utl_file.fclose(out_file); --关闭文件流,防止异常关闭
end PRC_TASK_EXCEL;
做定时备份的时候,只需要新建一个定时器,将该存储过程加入到定时执行的行列,设置好执行间隔时间,如下图:
begin
sys.dbms_job.submit(job => :job,
what => 'PRC_TASK_EXCEL;',
next_date => to_date('11-10-2018 02:00:00', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'TRUNC(sysdate)+1+2/(24)');
commit;
end;
/