Oracle存储过程将执行结果导出生成Excel文件

---directories新建存储路径
create or replace directory DIR_EXCEL as '/opt/leasing';
 
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 '姓名' into str1 from dual;
 
select '电话' into str2 from dual;
 
--导出Excel文件
 
out_file := utl_file.fopen('DIR_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.bakhxm || chr(9) xm, c.bakhzh || chr(9) zh
 
from bat001 c) loop
 
utl_file.put(out_file, convert(o.xm, 'ZHS16GBK'));
 
utl_file.put(out_file, convert(o.zh, '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;