orcle数据库如何通过指令形式直接生成EXCEL下发到本地硬盘

数据库有一个文件：yonghuzl,字段如下：
用户名   号码         所属中心
张三     5200000          海滨中心
张一     5200001          三角中心
李三     5200000          泗水中心
李一     5200001          宾阳中心
赵三     5200000          阳海中心
毛一     5200001          宾阳中心
钱三     5200000          贵港中心
.......
有好几十万条记录，还有三十个不同中心，我想每个中心生成一个EXCEL文件到本地硬盘，请问如何写指令？指令可以在客户端中一条接一条执行下去最好，如果写成存储过程也行。
目前用的是PL/SQL和青蛙8.0客户端。
多谢。

8 个解决方案

#1

楼主可以考虑使用  sqlplus 这个工具， spool 写本地文件。

#2

网上搜下 sqlludr 导出用的  速度很可观

#3

sqlplus 这个工具试过，不够直观，循环也搞不了。
不知道 sqlludr 这个是什么？

#4

CREATE OR REPLACE FUNCTION dump_csv_to_file
(
    p_query     IN VARCHAR2,
    p_separator IN VARCHAR2 DEFAULT ';',
    p_dir       IN VARCHAR2,
    p_filename  IN VARCHAR2,
    p_header    IN VARCHAR2 DEFAULT 'Y',
    p_limit     IN INTEGER DEFAULT 100
) RETURN NUMBER IS
    TYPE csv_cur_type IS REF CURSOR;
    csv_cur csv_cur_type;
    TYPE myarray IS TABLE OF VARCHAR2(4000);
    l_data          myarray;
    l_query         VARCHAR2(4000);
    l_column_string VARCHAR2(2000);
    l_thecursor     INTEGER DEFAULT dbms_sql.open_cursor;
    l_columnvalue   VARCHAR2(2000);
    l_status        INTEGER;
    l_colcnt        NUMBER DEFAULT 0;
    l_separator_csv VARCHAR2(10);
    l_cnt           NUMBER DEFAULT 0;
    rec_tab         dbms_sql.desc_tab;
    l_output        utl_file.file_type;
    l_buffer        VARCHAR2(32767) := NULL;
    l_new_line      VARCHAR2(10) := chr(10);
BEGIN
    l_output := utl_file.fopen(p_dir, p_filename, 'w');
    dbms_sql.parse(l_thecursor, p_query, dbms_sql.native);
    FOR i IN 1 .. 255 LOOP
        BEGIN
            dbms_sql.define_column(l_thecursor, i, l_columnvalue, 2000);
            l_colcnt := i;
        EXCEPTION
            WHEN OTHERS THEN
                IF (SQLCODE = -1007) THEN
                    EXIT;
                ELSE
                    RAISE;
                END IF;
        END;
    END LOOP;
    dbms_sql.define_column(l_thecursor, 1, l_columnvalue, 2000);
    l_status := dbms_sql.execute(l_thecursor);
    dbms_sql.describe_columns(l_thecursor, l_colcnt, rec_tab);
    l_separator_csv := '';
    FOR x IN 1 .. l_colcnt LOOP
        l_column_string := l_column_string || l_separator_csv || rec_tab(x)
                          .col_name;
        l_separator_csv := '||''' || p_separator || '''||';
    END LOOP;
    IF nvl(p_header, 'Y') != 'N' THEN
        utl_file.put_line(l_output, l_column_string);
        l_cnt := l_cnt + 1;
    END IF;
    l_query := regexp_replace(upper(p_query),
                              '^SELECT (.*?) FROM',
                              'SELECT ' || l_column_string || ' FROM');
    OPEN csv_cur FOR l_query;
    LOOP
        FETCH csv_cur BULK COLLECT
            INTO l_data LIMIT p_limit;
        FOR j IN 1 .. l_data.count LOOP
            IF length(l_buffer || l_data(j)) >= 30000 THEN
                utl_file.put_line(l_output, l_buffer);
                l_buffer := l_data(j);
            ELSE
                IF l_buffer IS NULL THEN
                    l_buffer := l_data(j);
                ELSE
                    l_buffer := l_buffer || l_new_line || l_data(j);
                END IF;
            END IF;
            l_cnt := l_cnt + 1;
        END LOOP;
        EXIT WHEN csv_cur%NOTFOUND;
    END LOOP;
    CLOSE csv_cur;
    utl_file.put_line(l_output, l_buffer);
    utl_file.fclose(l_output);
    RETURN l_cnt;
EXCEPTION
    WHEN OTHERS THEN
        IF csv_cur%ISOPEN THEN
            CLOSE csv_cur;
            RAISE;
        END IF;
END dump_csv_to_file;



--先创建 directories
DECLARE
    l_count PLS_INTEGER;
BEGIN
    l_count := dump_csv_to_file(p_query     => 'select * from TABLE',
                                p_separator => ';',
                                p_dir       => 'DIRECTORIES',
                                p_filename  => 'dump_csv_to_file_new.csv',
                                p_header    => 'N',
                                p_limit     => 100);
    DBMS_OUTPUT.PUT_LINE(l_result);
END;

#5

引用 3 楼 dbcz444的回复:

sqlplus 这个工具试过，不够直观，循环也搞不了。
不知道 sqlludr 这个是什么？

是一sqlldr的反向操作  是别人写的EXE

#6

引用 5 楼 baidu_36457652 的回复:

Quote: 引用 3 楼 dbcz444的回复:

sqlplus 这个工具试过，不够直观，循环也搞不了。
不知道 sqlludr 这个是什么？

是一sqlldr的反向操作  是别人写的EXE

这个工具还有吗，很难找到了，而且，我印象中到oracle11貌似就不支持了

#7

多谢3楼，对于只会用SELECT 的小白来说，如果有点注释就好了。

#8

引用 6 楼 jdsnhan的回复:

Quote: 引用 5 楼 baidu_36457652 的回复:

Quote: 引用 3 楼 dbcz444的回复:

sqlplus 这个工具试过，不够直观，循环也搞不了。
不知道 sqlludr 这个是什么？

是一sqlldr的反向操作  是别人写的EXE

这个工具还有吗，很难找到了，而且，我印象中到oracle11貌似就不支持了

有 我12c r2都可以的