OracleDBの場合、SQL*Plusから
SQL> SPOOL xxx.csv
SQL> SELECT * FROM TAB;
SQL> SPOOL OFF- 項目名を論理名にしたい
- 不要な空白はなるべく少なくしたい
- レコードを固定長ではなく可変長で出力したい
などなど、自分が使うだけなら別にいいのですが、クライアントやユーザーからの依頼であれば多少は見やすく／使いやすくしておいてあげたいという思いやりもあったりします。たいした作業ではないものの、都度々々手作業で時間を取られるのもよろしくありません。
というわけで、任意のテーブルの全レコードを主キーでソートし、タブ区切り形式で出力するツールを作りました。タブ区切りなので、正確にはCSVではなくTSVです。試していませんがビューやマテリアライズドビューでも大丈夫なはずです。
特にパフォーマンスチューニングはしていないため、単純なSPOOLよりは遅いと思います。数十万〜数百万レコードの抽出には向いていませんのでご注意ください（そもそもそんなユースケースはまずないと思いますが）。
なおソースコード一式は本エントリ末尾に添付してあります。
ソースコード一式をダウンロード（zip形式／3.61KB）
ご利用は各自のご責任でお願いいたします。改変などはご自由に。
特にパフォーマンスチューニングはしていないため、単純なSPOOLよりは遅いと思います。数十万〜数百万レコードの抽出には向いていませんのでご注意ください（そもそもそんなユースケースはまずないと思いますが）。
なおソースコード一式は本エントリ末尾に添付してあります。
［ツール］
仕様
- 任意のテーブル名を物理名で指定する。
- 指定されたテーブルの項目論理名（USER_COL_COMMENTSに定義されているもの）をヘッダーとして出力する。項目論理名が定義されていない項目については、項目物理名をヘッダーとして出力する。
- 指定されたテーブルの主キーで結果セットを昇順ソートする。主キーが設定されていない場合、ROWIDで昇順ソートする。
- 指定されたテーブルの各レコードの項目値をタブ区切りで連結し、上記のソート順に従って出力する。
- <テーブル物理名>_<システム日付（YYYYMMDD）>.csvというファイル名でカレントディレクトリへファイル出力する。
構成
- SPOOL_ALL_RECORDS.sql … 呼び出しスクリプト
- PKG_UTIL.sql … PL/SQLパッケージ（仕様部）
- PKG_UTIL_BODY.sql … PL/SQLパッケージ（本体部）
動作確認環境
Oracle Database 11g Express Edition Release 11.2.0.2.0呼び出しスクリプト
赤文字の部分にテーブル物理名を入力してください。--------------------------------------------------------------------------------
-- SPOOL_ALL_RECORDS.sql
--------------------------------------------------------------------------------
-- 出力設定
SET ECHO OFF
SET FEEDBACK OFF
SET LINESIZE 32767
SET SERVEROUTPUT ON
SET SQLBLANKLINES OFF
SET TERMOUT OFF
SET TIMING OFF
SET TRIMSPOOL ON
-- セッションパラメータ設定
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY/MM/DD HH24:MI:SS.FF3';
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY/MM/DD HH24:MI:SS.FF3';
-- カラム設定
COL FILE_NAME NEW_VALUE FILE_NAME
-- バインド変数宣言
VAR TABLE_NAME VARCHAR2(30)
VAR OUTPUT_DATE CHAR(8)
-- バインド変数初期化
EXEC :TABLE_NAME := 'SAMPLE_TABLE_01';
EXEC :OUTPUT_DATE := TO_CHAR(SYSDATE, 'YYYYMMDD');
-- ログファイル名編集
SELECT :TABLE_NAME || '_' || :OUTPUT_DATE || '.csv' AS FILE_NAME FROM DUAL;
-- ログファイル出力開始
SPOOL &FILE_NAME
-- 任意のテーブルのレコードをタブ区切り形式で全件出力
EXEC PKG_UTIL.PROC_PUT_TSV(:TABLE_NAME);
-- ログファイル出力終了
SPOOL OFF
PL/SQLパッケージ（仕様部）
CREATE OR REPLACE PACKAGE PKG_UTIL IS
/**
* レコードタブ区切り出力プロシージャ
*
* @param i_table_name テーブル名
*/
PROCEDURE PROC_PUT_TSV (
i_table_name IN VARCHAR2
);
END PKG_UTIL;
/
PL/SQLパッケージ（本体部）
CREATE OR REPLACE PACKAGE BODY PKG_UTIL IS
/**
* レコードタブ区切り出力プロシージャ
*
* @param i_table_name テーブル名
*/
PROCEDURE PROC_PUT_TSV (
i_table_name IN VARCHAR2
)
IS
-- 定数定義
C_TAB_CODE CONSTANT CHAR(1) := CHR(9);
-- レコード型定義
TYPE t_rec IS RECORD (
REC NVARCHAR2(32767)
);
-- レコード定義
rec_col_value t_rec;
-- 参照カーソル型定義
TYPE t_cur IS REF CURSOR;
-- 参照カーソル定義
cur_col_value t_cur;
-- カーソル定義：項目名取得カーソル
CURSOR cur_col_header IS
SELECT
ucc.COLUMN_NAME AS COLUMN_NAME
,ucc.COMMENTS AS COMMENTS
FROM
USER_TAB_COLUMNS utc
INNER JOIN
USER_COL_COMMENTS ucc
ON
utc.TABLE_NAME = ucc.TABLE_NAME
AND utc.COLUMN_NAME = ucc.COLUMN_NAME
WHERE
utc.TABLE_NAME = i_table_name
ORDER BY
utc.COLUMN_ID
;
-- カーソル定義：主キー項目取得カーソル
CURSOR cur_pk_col IS
SELECT
ucc.COLUMN_NAME AS COLUMN_NAME
FROM
USER_CONS_COLUMNS ucc
WHERE
ucc.TABLE_NAME = i_table_name
AND ucc.CONSTRAINT_NAME IN (
SELECT
ucn.CONSTRAINT_NAME AS CONSTRAINT_NAME
FROM
USER_CONSTRAINTS ucn
WHERE
ucn.TABLE_NAME = i_table_name
AND ucn.CONSTRAINT_TYPE = 'P'
)
ORDER BY
ucc.POSITION
;
-- 変数定義
v_col_header NVARCHAR2(32767) := NULL;
v_sql VARCHAR2(32767) := NULL;
v_pk_col_count PLS_INTEGER := 0;
BEGIN
-- 動的SQL文を編集する：SELECT句の追加
v_sql := 'SELECT ';
-- 項目名を取得する／動的SQL文を編集する：SELECT項目の追加
FOR rec IN cur_col_header LOOP
v_col_header := v_col_header
|| NVL(rec.COMMENTS, rec.COLUMN_NAME)
|| C_TAB_CODE;
v_sql := v_sql
|| rec.COLUMN_NAME
|| ' || '''
|| C_TAB_CODE
|| ''' || ';
END LOOP;
-- 動的SQL文を編集する：FROM句とORDER BY句の追加
v_sql := SUBSTR(v_sql, 1, LENGTH(v_sql) - 10)
|| 'FROM '
|| i_table_name
|| ' ORDER BY ';
-- 動的SQL文を編集する：ORDER BY項目の追加
FOR rec IN cur_pk_col LOOP
v_sql := v_sql || rec.COLUMN_NAME || ',';
v_pk_col_count := v_pk_col_count + 1;
END LOOP;
-- 動的SQL文を編集する：末尾の整形
IF v_pk_col_count > 0 THEN
v_sql := SUBSTR(v_sql, 1, LENGTH(v_sql) - 1);
ELSE
v_sql := v_sql || 'ROWID';
END IF;
-- 項目名を出力する
DBMS_OUTPUT.PUT_LINE(SUBSTR(v_col_header, 1, LENGTH(v_col_header) - 1));
-- カーソルを開く
OPEN cur_col_value FOR v_sql;
-- 項目値を出力する
LOOP
FETCH cur_col_value INTO rec_col_value;
EXIT WHEN cur_col_value%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(rec_col_value.REC);
END LOOP;
-- カーソルを閉じる
CLOSE cur_col_value;
EXCEPTION
WHEN OTHERS THEN
-- カーソルが開いていたら閉じる
IF cur_col_value%ISOPEN THEN
CLOSE cur_col_value;
END IF;
-- 例外を伝播
RAISE;
END PROC_PUT_TSV;
END PKG_UTIL;
/
［検証］
サンプル01：主キーが単項目のテーブル
-- テーブル作成
CREATE TABLE SAMPLE_TABLE_01 (
COL_01 NUMBER(8)
,COL_02 VARCHAR2(4)
);
-- 主キー付与
ALTER TABLE SAMPLE_TABLE_01 ADD CONSTRAINTS PK_SAMPLE_TABLE_01 PRIMARY KEY (
COL_01
);
-- コメント付与
COMMENT ON TABLE SAMPLE_TABLE_01 IS 'サンプルテーブル０１';
COMMENT ON COLUMN SAMPLE_TABLE_01.COL_01 IS '項目０１';
COMMENT ON COLUMN SAMPLE_TABLE_01.COL_02 IS '項目０２';
-- サンプルデータ挿入
INSERT INTO SAMPLE_TABLE_01 VALUES (1, 'AAAA');
INSERT INTO SAMPLE_TABLE_01 VALUES (2, 'BBBB');
INSERT INTO SAMPLE_TABLE_01 VALUES (4, 'CCCC');
INSERT INTO SAMPLE_TABLE_01 VALUES (3, 'DDDD');
COMMIT;SQL> -- SAMPLE_TABLE_01を指定
SQL> @SPOOL_ALL_RECORDS.sql項目０１ 項目０２
1 AAAA
2 BBBB
3 DDDD
4 CCCC
サンプル02：主キーが複数項目のテーブル
-- テーブル作成
CREATE TABLE SAMPLE_TABLE_02 (
COL_01 NUMBER(8)
,COL_02 CHAR(2)
,COL_03 VARCHAR2(4)
);
-- 主キー付与
ALTER TABLE SAMPLE_TABLE_02 ADD CONSTRAINTS PK_SAMPLE_TABLE_02 PRIMARY KEY (
COL_01
,COL_02
);
-- コメント付与
COMMENT ON TABLE SAMPLE_TABLE_02 IS 'サンプルテーブル０２';
COMMENT ON COLUMN SAMPLE_TABLE_02.COL_01 IS '項目０１';
COMMENT ON COLUMN SAMPLE_TABLE_02.COL_02 IS '項目０２';
COMMENT ON COLUMN SAMPLE_TABLE_02.COL_03 IS '項目０３';
-- サンプルデータ挿入
INSERT INTO SAMPLE_TABLE_02 VALUES (1, 'X1', 'AAAA');
INSERT INTO SAMPLE_TABLE_02 VALUES (1, 'X3', 'BBBB');
INSERT INTO SAMPLE_TABLE_02 VALUES (1, 'X2', 'CCCC');
INSERT INTO SAMPLE_TABLE_02 VALUES (2, 'X1', 'DDDD');
COMMIT;SQL> -- SAMPLE_TABLE_02を指定
SQL> @SPOOL_ALL_RECORDS.sql項目０１ 項目０２ 項目０３
1 X1 AAAA
1 X2 CCCC
1 X3 BBBB
2 X1 DDDDサンプル03：主キーがないテーブル／項目論理名がない項目
-- テーブル作成
CREATE TABLE SAMPLE_TABLE_03 (
COL_01 NUMBER(8)
,COL_02 VARCHAR2(4)
);
-- 主キー付与
--ALTER TABLE SAMPLE_TABLE_03 ADD CONSTRAINTS PK_SAMPLE_TABLE_03 PRIMARY KEY (
-- COL_01
--);
-- コメント付与
COMMENT ON TABLE SAMPLE_TABLE_03 IS 'サンプルテーブル０３';
COMMENT ON COLUMN SAMPLE_TABLE_03.COL_01 IS '項目０１';
--COMMENT ON COLUMN SAMPLE_TABLE_03.COL_02 IS '項目０２';
-- サンプルデータ挿入
INSERT INTO SAMPLE_TABLE_03 VALUES (1, 'AAAA');
INSERT INTO SAMPLE_TABLE_03 VALUES (2, 'BBBB');
INSERT INTO SAMPLE_TABLE_03 VALUES (4, 'CCCC');
INSERT INTO SAMPLE_TABLE_03 VALUES (3, 'DDDD');
COMMIT;
SQL> -- SAMPLE_TABLE_03を指定
SQL> @SPOOL_ALL_RECORDS.sql項目０１ COL_02
1 AAAA
2 BBBB
4 CCCC
3 DDDDサンプル04：存在しないテーブル
SQL> -- SAMPLE_TABLE_04を指定
SQL> @SPOOL_ALL_RECORDS.sqlBEGIN PKG_UTIL.PROC_PUT_TSV(:TABLE_NAME); END;
*
行1でエラーが発生しました。:
ORA-00900: SQL文が無効です。
ORA-06512: "SAMPLE_SCHEMA.PKG_UTIL", 行127
ORA-06512: 行1［ソースコード一式］
ご利用は各自のご責任でお願いいたします。改変などはご自由に。
0 件のコメント:
コメントを投稿