PL/SQL プログラミング入門(2) > 汎用 CSV 出力プロシージャ

任意のテーブル、ビュー、SQL の結果を CSV で出力する

Table to CSV 、 View to CSV、 Select query to CSV を行うための PL/SQL パッケージ

CSV を出力する UNLOAD パッケージモジュール

Oracle においてテーブルから CSV を生成するための UNLOAD 機能は用意されていないため CSV 生成を行なうためのライブラリパッケージを作成。

以下のような要件のもと作成

  • 任意のテーブル、ビュー、任意の SELECT 文、関数が使用可能
  • DATE 型、TIMESTAMP 型の書式を指定可能
    デフォルト書式は SESSION の初期化パラメータ から自動設定(ALTER SESSION SET NLS_* 〜 に依存)
  • フィールド間のセパレータを指定可能
  • 文字列のクォート文字を指定可能
  • 文字列データ内のクォート文字を別文字列に変換可能
  • CHAR 型の文字データの空白埋め部分の TRIM 処理の有無を指定可能
  • 数値型のクォートのありなしを指定可能
  • CLOB データを抽出可能 / CLOB データを抽出するかしないかを選択可能
  • 実行環境:Oracle 9i 以上
  • ファイル出力はアプリケーション側で行なう (SQL*Plusなど)

CSV を生成するプログラム

対象ファイル名漢字コード
パッケージファイルfileunload.sql.5Shift JIS

USER_TABLES を ソートして CSV 化する例 その1

組み込んである GET_CSV 表関数 を使用して、SELECT 文だけで 1カラムの CSV を取り出す。(1レコード 4000 バイト以下)

set pagesize 0
set linesize 300
select * from table(unload.get_csv(q'{select * from user_tables order by table_name}'));

q'{〜}' Oracle 10g を使用すると SQL 文内に自然な形で単一引用符(')を使える ⇒ 代替引用符

USER_TABLES を ソートして CSV 化する例 その2

組み込んである GET_CSV_CLOB 表関数 を使用して、SELECT 文だけで 1カラムの CSV を取り出す。(CLOB として抽出)

set pagesize 0
set linesize 300
set loboffset 1
set long 32767
set longchunksize 32767
select * from table(
  unload.get_csv_clob(q'{select * from user_tables order by table_name}')
);

カスタマイズして SQL*Plus から USER_TABLES を ソートして CSV 化する例

データソース(data_source)は テーブル名のみ、ビュー名のみ、または、任意の SQL が指定可能 
DATE 型の書式(date_format)は: YYYY年MM月DD日 ⇒ date_format オプション
カンマ・セパレータではなく、タブ・セパレータファイル ⇒ set_col_sep オプション

set echo off
set feedback off
set term off
set head off
set linesize 1000
-- set linesize 2000
-- set linesize 4000
-- set linesize 32767
--  ↑↑ 1レコードの文字列の長さによって適宜調整する(レコード数増加により相当遅くなる)
set long 32767
set longchunksize 32767
set pagesize 0
set trimspool on
set serveroutput on
var cur refcursor
begin
	:cur := unload.sql_to_csv(
		data_source => 'select * from user_tables order by table_name',
		date_format => 'YYYY"年"MM"月"DD"日"',
		set_col_sep => CHR(9)
	);
exception
 when others then
	dbms_output.put_line('ERROR : ' || SQLERRM);
	raise;
end;
/
set term off
spool user_tables.csv
print cur
spool off
set term on

ポイント 高速化のポイント

SQL*Plus からレコード数が多いデータを UNLOAD するときの高速化のポイントは LINESIZE を取り出すデータに適した長さに設定すること。
不必要に SET LINESIZE のバイト数を大きくしすぎると処理時間が数倍〜十数倍にもなってしまう。
一歩踏み込んだ CSV ファイル出力の高速化手法 ⇒ CSV 出力を10倍高速にする方法

Data UNLOAD パッケージ、SQL to CSV プロシージャ

PACKAGE 定義部のみなのでコピーしても動きません

  1. CREATE OR REPLACE PACKAGE RIVUS.UNLOAD
  2. IS
  3. /*********************************************************************/
  4. -- ファンクション名 SQL_TO_CSV
  5. -- パラメータ :(I  ) data_source      テーブル、ビュー、クエリー
  6. --            :(I  ) date_format      日付書式(以下すべて任意)
  7. --            :(I  ) timestamp_format タイムスタンプ書式(9i)
  8. --            :(I  ) set_col_sep      カラム間の区切り文字(,)
  9. --            :(I  ) set_enclose      カラム引用符文字(")
  10. --            :(I  ) set_alt_quote    データ中の引用符の代替文字("")
  11. --            :(I  ) trim_char        CHAR 型のRTRIM あり(1) なし(0)
  12. --            :(I  ) enclose_num      数値型の引用符 あり(1) なし(0)
  13. --            :(I  ) ignore_lob       LOB 型はスキップする(1) しない(0)
  14. --            :(I  ) over_32k          結果文字列をCLOBに する(1) しない(0)
  15. -- 戻り値     :CSV を格納した REF カーソル
  16. -- Copyright  : rivus.jp
  17. /*********************************************************************/
  18. -- constatnt
  19. nTRUE  CONSTANT BINARY_INTEGER := 1;
  20. nFALSE  CONSTANT BINARY_INTEGER := 0;
  21. -- type
  22. SUBTYPE BOOLEAN# IS BINARY_INTEGER RANGE 0..1;  -- 0: FALSE / 1: TRUE
  23. TYPE VARCHAR2_ARRAY IS TABLE OF VARCHAR2(4000);
  24. TYPE CLOB_ARRAY IS TABLE OF CLOB;
  25. --
  26. --
  27. FUNCTION SQL_TO_CSV(
  28.   data_source       IN VARCHAR2,
  29.   date_format       IN VARCHAR2 := NULL,
  30.   timestamp_format  IN VARCHAR2 := NULL,
  31.   set_col_sep       IN VARCHAR2 := ',',
  32.   set_enclose       IN VARCHAR2 := '"',
  33.   set_alt_quote     IN VARCHAR2 := '""',
  34.   trim_char         IN BOOLEAN# := 1,
  35.   enclose_num       IN BOOLEAN# := 0,
  36.   ignore_lob        IN BOOLEAN# := 1,
  37.   over_32k          IN BOOLEAN# := 0
  38. )
  39. RETURN SYS_REFCURSOR;
  40. --
  41. FUNCTION SQL_TO_REGULAR_SQL(
  42.   data_source       IN VARCHAR2,
  43.   date_format       IN VARCHAR2 := NULL,
  44.   timestamp_format  IN VARCHAR2 := NULL,
  45.   set_col_sep       IN VARCHAR2 := ',',
  46.   set_enclose       IN VARCHAR2 := '"',
  47.   set_alt_quote     IN VARCHAR2 := '""',
  48.   trim_char         IN BOOLEAN# := 1,
  49.   enclose_num       IN BOOLEAN# := 0,
  50.   ignore_lob        IN BOOLEAN# := 1,
  51.   over_32k           IN BOOLEAN# := 0
  52. )
  53. RETURN VARCHAR2;
  54. --
  55. FUNCTION GET_CSV(
  56.   query      IN VARCHAR2,
  57.   fetch_size IN POSITIVEN := 15
  58. )
  59. RETURN VARCHAR2_ARRAY PIPELINED;
  60. --
  61. FUNCTION GET_CSV_CLOB(
  62.   query      IN VARCHAR2,
  63.   fetch_size IN POSITIVEN := 1
  64. )
  65. RETURN CLOB_ARRAY PIPELINED;
  66. --
  67. PROCEDURE EXPORT_TO_FILE(
  68.   query    IN VARCHAR2,
  69.   file_name IN VARCHAR2,
  70.   dir_name  IN VARCHAR2 := 'DATA_PUMP_DIR'
  71. );
  72. --
  73. PROCEDURE EXPORT_TO_FILE(
  74.   cDataSource  IN SYS_REFCURSOR,
  75.   file_name    IN VARCHAR2,
  76.   dir_name  IN VARCHAR2 := 'DATA_PUMP_DIR'
  77. );
  78. END;
  79. /

UNLOAD 処理速度の目安

環境: Windows XP (Corei7-920) + Oracle 11g 
SQL*Plus から SPOOL にて CSV ファイル出力

テスト用 表定義 その1(やや大きめ)

CREATE TABLE BIG01
(
    V1                             VARCHAR2(200),
    N1                             NUMBER,
    N2                             NUMBER,
    D1                             DATE,
    T1                             TIMESTAMP(6),
    C1                             CHAR(50),
    V2                             VARCHAR2(200),
    V3                             VARCHAR2(200),
    V4                             VARCHAR2(200),
    V5                             VARCHAR2(200)
);

抽出レコード数: 100万レコード / 平均レコード長: 600 バイト の場合
ファイル出力時間: 約 5分 / ファイルサイズ 590 MB

 

テスト用 表定義 その2(やや小さめ)

CREATE TABLE SMALL01
(
    V1                             VARCHAR2(10),
    V2                             VARCHAR2(10),
    V3                             VARCHAR2(10),
    D1                             DATE
)

抽出レコード数 : 10万レコード / レコード長 50 バイト の場合
ファイル出力時間: 約 15秒 / ファイルサイズ 5 MB



関連事項

日本オラクル
■ 日本オラクル 株式会社
■ オラクルマスター資格 (オラクルマスターとは
■ Oracle Web セミナー