Oracle 通過儲存過程來生成CSV檔案

feelpurple發表於2016-09-20
Oracle儲存過程
建立directory
create directory REPORT_DIR as '/u01/app/oracle/report';
grant all on directory REPORT_DIR to TEST;

建立儲存過程
CREATE OR REPLACE PROCEDURE SQL_TO_CSV(
                                       P_DIR      IN VARCHAR2, -- output directory
                                       P_FILENAME IN VARCHAR2 -- csv file name
                                       ) IS
  L_OUTPUT       UTL_FILE.FILE_TYPE;
  L_SEPARATOR    VARCHAR2(1);
  L_DESCTBL      DBMS_SQL.DESC_TAB;
  P_MAX_LINESIZE NUMBER := 32000;
  --DEFINE A CURSOR
  CURSOR c1 IS SELECT D_START_DATE FROM OSMSR_VIEW_OPERATION_HISTORY WHERE ROWNUM <= 10;
  v_start_date  OSMSR_VIEW_OPERATION_HISTORY.D_START_DATE%TYPE;
BEGIN
  --OPEN FILE
  L_OUTPUT := UTL_FILE.FOPEN(P_DIR, P_FILENAME, 'W', P_MAX_LINESIZE);
  --DEFINE DATE FORMAT
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD HH24:MI:SS''';
  --OPEN CURSOR
    OPEN c1;
  LOOP  -- Fetches columns into variables
    FETCH c1 INTO v_start_date;
    EXIT WHEN c1%NOTFOUND;
  --DEFINE THE SEPARATOR ,
    L_SEPARATOR := '';
    UTL_FILE.PUT(L_OUTPUT, L_SEPARATOR || v_start_date);
    L_SEPARATOR := ',';
   --CHANGE THE LINE
    UTL_FILE.NEW_LINE(L_OUTPUT);
  END LOOP;
  --CLOSE CURSOR
  CLOSE c1;
  --CLOSE FILE
  UTL_FILE.FCLOSE(L_OUTPUT);
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END;

呼叫儲存過程，REPORT_DIR是directory的名稱，02.csv是生成的CSV檔名
BEGIN
  SQL_TO_CSV('REPORT_DIR', '02.csv');
END;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26506993/viewspace-2125200/，如需轉載，請註明出處，否則將追究法律責任。

相關文章