About Oracle Batch Export

Oracle Batch Export:

创建数据批导出路径

--sysdba:
SQL> CREATE DIRECTORY KYLINDIR AS '\\10.80.36.17\D:\ORCL_DATA_EXPORT';
SQL> GRANT READ,WRITE ON DIRECTORY KYLINDIR TO EDBADM;

---

创建存储过程

CREATE OR REPLACE PROCEDURE PRO_EXPORT_TO_CSV(P_DIR IN VARCHAR2)
IS
--显示游标，一次性将数据全部查询完
CURSOR MYCUR IS
SELECT DAY_TIMEKEY,
       EQP_ID,
       STEP_ID,
       PRODUCT_ID,
       GLS_TYPE,
       GLS_ID,
       TACT_TIME
FROM DWT_ARRAY_TT_2019;
--行记录
MYRECORD DWT_ARRAY_TT_2019%ROWTYPE;
CSV_EXPORT UTL_FILE.FILE_TYPE;
MAX_LINE NUMBER := 10000;
OUT_FILE_NAME VARCHAR2(20);
BEGIN_TIME NUMBER;
END_TIME NUMBER;
COUNT_NUM NUMBER;
BEGIN
        BEGIN_TIME := DBMS_UTILITY.GET_TIME;
        --显式打开游标
        OPEN MYCUR;
        FOR I IN 1..999 LOOP
                --拼接文件名
                OUT_FILE_NAME := 'ArrayTT' || I ||'.csv';
                COUNT_NUM := 0;
                --打开文件
                CSV_EXPORT := UTL_FILE.FOPEN(P_DIR, OUT_FILE_NAME, 'W', MAX_LINE);
                --每10000条写一个文件
                WHILE COUNT_NUM < 10000 LOOP
                        --逐条叫游标记录放入记录中
                        FETCH MYCUR INTO MYRECORD;
                        --写数据
                        UTL_FILE.PUT_LINE(CSV_EXPORT,MYRECORD.DAY_TIMEKEY || ',' ||
                                                     MYRECORD.EQP_ID || ',' ||
                                                     MYRECORD.STEP_ID || ',' ||
                                                     MYRECORD.PRODUCT_ID || ',' ||
                                                     MYRECORD.GLS_TYPE || ',' ||
                                                     MYRECORD.GLS_ID || ',' ||
                                                     MYRECORD.TACT_TIME );
                COUNT_NUM := COUNT_NUM+1;
                --取游标中下一条记录
                FETCH MYCUR INTO MYRECORD;
                END LOOP;
                UTL_FILE.FCLOSE(CSV_EXPORT);
        END LOOP;
        --关闭游标
        CLOSE MYCUR;
        END_TIME := DBMS_UTILITY.GET_TIME;
        DBMS_OUTPUT.PUT_LINE('TotalTime=' || (END_TIME-BEGIN_TIME)*10 || 'ms.');
END PRO_EXPORT_TO_CSV;

---

调用存储过程

BEGIN
PRO_EXPORT_TO_CSV('KYLINDIR'); # 方式1
END;

CALL PRO_EXPORT_TO_CSV('KYLINDIR'); # 方式2