重庆思庄Oracle、Redhat认证学习论坛

标题: 如何把sql语句结果输出到excel [打印本页]

作者: 郑全 时间: 2019-11-15 16:02
标题: 如何把sql语句结果输出到excel
如果SQL语句的结果太大，通过plsql developer无法显示所有的结果，这个时候，我们可以通过一段代码来完成，下面是一个例子：

把
select last_name, salary, department_id
   from employees
   order by department_id
的结果显示到excel

下面是具体例子：

create or replace procedure out_excel(dir    in varchar2,
                                    filename in varchar2) is
  file utl_file.file_type;
  cursor empc is
select last_name, salary, department_id
   from employees
   order by department_id;
begin
  file := utl_file.fopen(dir, filename, 'w');
  utl_file.put_line(file, 'report: generated on ' || sysdate);
  utl_file.new_line(file);
  utl_file.put_line(file,
                  'department_id' || chr(9) || 'name' || chr(9) ||
                  'salary');
  FOR emp_rec IN empc LOOP
UTL_FILE.PUT_LINE(file,
                  nvl(emp_rec.department_id, '') || chr(9) ||
                  emp_rec.last_name || chr(9) || emp_rec.salary);
  END LOOP;
  UTL_FILE.PUT_LINE(file, '*** END OF REPORT ***');
  UTL_FILE.FCLOSE(file);
EXCEPTION
  WHEN UTL_FILE.INVALID_FILEHANDLE THEN
RAISE_APPLICATION_ERROR(-20001, 'Invalid File.');
  WHEN UTL_FILE.WRITE_ERROR THEN
RAISE_APPLICATION_ERROR(-20002, 'Unable to write to file');
  when utl_file.invalid_operation then
RAISE_APPLICATION_ERROR(-20003, 'file operate invalid');
END out_excel;

execute  sal_status(dir =>'DIR_FILE',filename => 'outexcel.xls');

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)