why not use the following way to generate the CSV file?

最新推荐文章于 2025-08-23 23:16:00 发布

原创最新推荐文章于 2025-08-23 23:16:00 发布 · 462 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#csv #oracle #manager #header #file #database

本文介绍如何使用 Oracle 的 PL/SQL 语言创建存储过程来批量从数据库中提取数据，并将数据写入到一个位于指定目录下的 Excel 文件中。此过程包括创建目录、授予文件操作权限、定义存储过程及执行存储过程等步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL> create directory reports as '/export/home/oracle'
2 ;

Directory created.

SQL> grant read,write on directory reports to scott;

Grant succeeded.
SQL> create or replace procedure print_reports_simple is
2   cursor c_direct_reports is
3      select
4        empno,
5        ename,
6        job,
7        hiredate,
8        sal
9      from emp;
10    wfile_handle utl_file.file_type;
11    v_wstring varchar2 (100);
12    v_header varchar2(100);
13    v_file varchar2(100);
14    v_date varchar2(20);
15 begin
16    v_header :='empno'||chr(9)||'ename'||chr(9)||'job'||chr(9)||'hiredate'||chr(9)||'sal';
17    v_file := 'onzin.xls';
18    wfile_handle := utl_file.fopen ('REPORTS',v_file, 'W');
19    utl_file.put_line(wfile_handle,v_header);
20    for r in c_direct_reports loop
21      v_wstring := r.empno||chr(9)||r.ename||chr(9)||r.job||chr(9)||to_char(r.hiredate,'dd/mm/yyyy')||chr(9)||r.sal;
22      utl_file.put_line(wfile_handle,v_wstring);
23    end loop;
24    utl_file.fclose (wfile_handle);
25 end print_reports_simple;
26 /

Procedure created.

SQL> exec print_reports_simple

PL/SQL procedure successfully completed.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
$ ls -lrt
total 2309458
-rw-r--r--   1 oracle   dba          136 Jan 12 16:12 local.cshrc
-rw-r--r--   1 oracle   dba          157 Jan 12 16:12 local.login
-rw-r--r--   1 oracle   dba          174 Jan 12 16:12 local.profile
drwxr-xr-x   2 oracle   dba          512 Jan 12 16:16 Documents
drwxr-xr-x   2 oracle   dba          512 Jan 12 16:16 Desktop
drwx------   3 oracle   dba          512 Jan 12 16:28 config
-rw-------   1 oracle   dba      1000000000 Jan 13 12:52 swapfile
-rw-------   1 oracle   dba      179640416 Jan 13 16:35 core
-rw-r--r--   1 oracle   dba      2104784 Jan 13 17:33 WF.log
drwxrwx---   3 oracle   dba          512 Jan 13 18:10 OracleHomes
drwxrwx---   7 oracle   dba          512 Jan 13 18:15 oraInventory
-rw-r--r--   1 oracle   dba        52330 Jan 19 09:43 datacollect.sql
-rw-r--r--   1 oracle   dba        17547 Jan 19 09:44 owbcollect.txt
-rw-r--r--   1 oracle   dba         1379 Jan 23 12:25 sqlnet.log
-rw-r--r--   1 oracle   dba          515 Mar 16 17:20 onzin.xls
$ more onzin.xls
empno   ename   job     hiredate        sal
7369    SMITH   CLERK   17/12/1980      800
7499    ALLEN   SALESMAN        20/02/1981      1600
7521    WARD    SALESMAN        22/02/1981      1250
7566    JONES   MANAGER 02/04/1981      2975
7654    MARTIN SALESMAN        28/09/1981      1250
7698    BLAKE   MANAGER 01/05/1981      2850
7782    CLARK   MANAGER 09/06/1981      2450
7788    SCOTT   ANALYST 19/04/1987      3000
7839    KING    PRESIDENT       17/11/1981      5000
7844    TURNER SALESMAN        08/09/1981      1500
7876    ADAMS   CLERK   23/05/1987      1100
7900    JAMES   CLERK   03/12/1981      950
7902    FORD    ANALYST 03/12/1981      3000
7934    MILLER CLERK   23/01/1982      1300