如何把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');