本文由 发布，转载请注明出处，如有问题请联系我们！ 发布时间: 2021-10-01plsql导入excel数据（plsql命令窗口导入sql文件）
加载中如果SQL语句的结果太大，通过plsql developer无法显示所有的结果，这个时候，我们可以通过一段代码来完成，下面是一个例子：把select last_name, salary, department_idfrom employeesorder by department_id的结果显示到excel下面是具体例子：create or replace procedure out_excel(dir in varchar2,filename in varchar2) isfile utl_file.file_type;cursor empc isselect last_name, salary, department_idfrom employeesorder by department_id;beginfile := 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 LOOPUTL_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);EXCEPTIONWHENUTL_FILE.INVALID_FILEHANDLE THENRAISE_APPLICATION_ERROR(-20001, ‘Invalid File.’);WHEN UTL_FILE.WRITE_ERROR THENRAISE_APPLICATION_ERROR(-20002, ‘Unable to write to file’);whenutl_file.invalid_operation thenRAISE_APPLICATION_ERROR(-20003, ‘file operate invalid’);END out_excel;execute sal_status(dir =>’DIR_FILE’,filename => ‘outExcel.xls’);
