plsql命令窗口導入sql文件：plsql導入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’);