[Oracle] 動的に編集したカラム数が不定なSELECT文をカーソル処理してみる。

カーソル変数を使うと、動的に編集したSELECT文をカーソル処理することができます。

DECLARE
  type cur_type IS REF CURSOR;
  cur cur_type;
      .
      .
      .
BEGIN
  OPEN cur FOR 'SELECT ... FROM ...';
  LOOP
    FETCH cur INTO ...;
    EXIT WHEN cur%NOTFOUND;
          .
          .
          .
  END LOOP;
  CLOSE cur;
END;
/

条件に応じてWHERE句やORDER BY句などを変えたい場合などに便利な機能です。

しかし、カーソル変数を使っても、FETCH文のINTO句を動的に編集することはできないため、取得するカラム数が不定のSELECT文には対応できません。


そもそも、取得するカラム数が不定のSELECT文を使用すること自体極めて稀かとは思いますが、どうしてもそういうことをしたいという場合はDBMS_SQLパッケージを使えば実現可能なようです。

試しに、指定したテーブルのデータをCSVファイルに出力するというプロシージャを作成してみました。

CREATE OR REPLACE PROCEDURE OUTPUT_TABLE_AS_CSV
( 
  p_table_name   IN   VARCHAR2,
  p_directory    IN  VARCHAR2
)
IS

  TYPE table_type IS TABLE OF VARCHAR2(32767)
    INDEX BY BINARY_INTEGER;

  wk_column_name        table_type;
  wk_column_value        table_type;
  wk_column_count        NUMBER;
  wk_sql            VARCHAR2(2000);

  CURSOR column_cur IS
    SELECT COLUMN_NAME
      FROM USER_TAB_COLUMNS
      WHERE TABLE_NAME = p_table_name
      ORDER BY COLUMN_ID;

  table_cur          INTEGER;

  wk_file            UTL_FILE.FILE_TYPE;

BEGIN

  --USER_TAB_COLUMNS から指定したテーブルのカラム名を取得し、SELECT文を編集する。
  wk_column_count := 0;
  OPEN column_cur;
  LOOP
    FETCH column_cur INTO wk_column_name( wk_column_count + 1 );
    EXIT WHEN column_cur%NOTFOUND;
    wk_column_count := wk_column_count + 1;
    IF wk_sql IS NULL THEN
      wk_sql := 'SELECT ' || wk_column_name( wk_column_count );
    ELSE
      wk_sql := wk_sql || ', ' || wk_column_name( wk_column_count );
    END IF;
    wk_column_value( wk_column_count ) := null;
  END LOOP;
  CLOSE column_cur;

  --カラムが１つでも存在する場合
  IF wk_column_count > 0 THEN

    --カーソルをオープンする。
    table_cur := DBMS_SQL.OPEN_CURSOR;

    --SQL文を解析する。
    wk_sql := wk_sql || ' FROM ' || p_table_name;
    DBMS_SQL.PARSE( table_cur, wk_sql, DBMS_SQL.NATIVE );

    --カラムを定義する。
    FOR i IN 1..wk_column_count LOOP
      DBMS_SQL.DEFINE_COLUMN( table_cur, i, wk_column_value( i ), 4000 );
    END LOOP;

    --SELECT文を実行する。
    IF ( DBMS_SQL.EXECUTE( table_cur ) = 0 ) THEN

      --CSVファイルをオープンする。
      wk_file := UTL_FILE.FOPEN( p_directory, p_table_name || '.csv', 'w', 32767 );

      --カラムヘッダーを出力する。
      FOR i IN 1..wk_column_count LOOP
        --CSVファイルに出力する。
        IF i = 1 THEN
          UTL_FILE.PUT( wk_file, wk_column_name( i ) );
        ELSE
          UTL_FILE.PUT( wk_file, ',' || wk_column_name( i ) );
        END IF;
      END LOOP;
      UTL_FILE.NEW_LINE( wk_file );

      --データ行を出力する。
      WHILE DBMS_SQL.FETCH_ROWS( table_cur ) > 0 LOOP

        FOR i IN 1..wk_column_count LOOP
          --カラム値を取得する。
          DBMS_SQL.COLUMN_VALUE( table_cur, i, wk_column_value( i ) );
          --CSVファイルに出力する。
          IF i = 1 THEN
            UTL_FILE.PUT( wk_file, wk_column_value( i ) );
          ELSE
            UTL_FILE.PUT( wk_file, ',' || wk_column_value( i ) );
          END IF;
        END LOOP;
        UTL_FILE.NEW_LINE( wk_file );

      END LOOP;

      --CSVファイルをクローズする。
      UTL_FILE.FCLOSE( wk_file );

    END IF;

    --カーソルをクローズする。
    DBMS_SQL.CLOSE_CURSOR(table_cur);

  END IF;

END;
/

カーソルをオープン(OPEN_CURSOR)して、解析(PARSE)して、カラムを定義(DEFINE_COLUMN)して、実行(EXECUTE)して、フェッチ(FETCH_ROWS)して、値を取得(COLUMN_VALUE)して、クローズ(CLOSE)する．．．と手順は煩雑ですが、このようにカラムの値を受け取る変数にPL/SQL表などを使えばカラム数が不定でも対応することができます。

ただし、上記の例ではすべてのカラムを文字列型として処理していますが、データ型を区別して値を取得したいという場合にはもう一工夫必要です。

さすがにそこまでやってみる気はありませんが．．．（笑）

【関連エントリ】
[Oracle] 動的に編集したカラム数が不定なSELECT文をカーソル処理してみる。 2005/09/21
[Oracle] DBMS_SQLパッケージでORA-04030エラーが発生 2006/09/29