Oracle PL/SQLでの文字列操作まわり CSV出力とか変数置換などのサンプルメモ

CSV_BUFFER.pls

CREATE OR REPLACE package csv_buffer as

  /**
   * CSVハンドル
   */
  type csv_handle is record (

    /**
     * 出力されるCSVのバイナリバッファ.
     * UTF8エンコードされたCSVの内容が、ここに書き込まれています.
     * 必要に応じてアクセスできます.
     */
    buf blob,
    
    /**
     * 名前.
     * CSVハンドルを識別するために使える名前です.
     */
    csv_name varchar2(256),
    
    /**
     * 改行済みか?
     * この値が1であれば行頭なのでカラムの出力時にカンマはつきません.
     * その値が0であればカンマを出力し、前のカラムと区切り文字とします.
     */
    newline number(1)
    );

  /**
   * CSVバッファを構築します.
   * 一時BLOBが作成されます.
   * 使い終わったら、destroy_csvで破棄する必要があります.
   * @param p_csv_name 名前
   * @return CSVバッファハンドル
   */
  function create_csv(p_csv_name in varchar2 := null) return csv_handle;
  
  /**
   * CSVバッファにBLOBをアタッチします.
   * @param p_buf BLOBバッファ
   * @param p_csv_name 名前
   * @return CSVバッファハンドル
   */
  function attach_csv(p_buf in out blob, p_csv_name in varchar2 := null) return csv_handle;

  /**
   * CSVバッファを破棄します.
   * @param p_handle CSVバッファ
   */
  procedure destroy_csv(p_handle in out nocopy csv_handle);
  
  /**
   * 数値カラムを書き込みます.
   * @param p_handle CSVバッファ
   * @param val 数値
   */
  procedure write_number(p_handle in out nocopy csv_handle, val number);

  /** 
   * 文字カラムを書き込みます.
   * 前後にダブルクォートがつけられ、ダブルクォートを含むものはエスケープされます.
   * 文字列内部にカンマがあっても許容されます.
   * @param p_handle CSVバッファ
   * @param val 文字列
   */
  procedure write_nvarchar(p_handle in out nocopy csv_handle, val nvarchar2);

  /** 
   * 文字カラムを書き込みます.
   * 前後にダブルクォートがつけられ、ダブルクォートを含むものはエスケープされます.
   * 文字列内部にカンマがあっても許容されます.
   * @param p_handle CSVバッファ
   * @param val 文字列
   */
  procedure write_varchar(p_handle in out nocopy csv_handle, val varchar2);

  /**
   * 改行します.
   * @param p_handle CSVバッファ
   */
  procedure line_feed(p_handle in out nocopy csv_handle);

  /**
   * バッファの内容をファイルに書き出します.
   * 出力先ディレクトリのオブジェクト名はあらかじめCREATE DIRECTORYで宣言し、
   * READ/WRITEの権限を割り当ててある必要があります.
   * @param p_handle CSVバッファ
   * @param p_dir 出力先ディレクトリオブジェクト名
   * @param p_filename 出力ファイル名
   */
  procedure save_to_file(p_handle in out nocopy csv_handle, p_dir in varchar2, p_filename varchar2);
  
end csv_buffer;
/


CREATE OR REPLACE package body csv_buffer as
  
  -- 改行コード
  crlf constant varchar2(2) := chr(13) || chr(10);

  -- バッファ
  rd raw(4096);

  /**
   * CSVバッファにUTF-8のBOMを書き込みます.
   * @return CSVバッファハンドル
   */
  procedure write_bom(p_handle in out nocopy csv_handle) as
    bom raw(3) := hextoraw('EFBBBF');
  begin
    dbms_lob.writeappend(p_handle.buf, utl_raw.length(bom), bom);
  end;

  function create_csv(p_csv_name in varchar2) return csv_handle as
    v_handle csv_handle;
  begin
    v_handle.csv_name := p_csv_name;
    v_handle.newline := 1;
    dbms_lob.createtemporary(v_handle.buf, true, dbms_lob.call);
    write_bom(v_handle);
    return v_handle;
  end;
  
  function attach_csv(p_buf in out nocopy blob, p_csv_name varchar2) return csv_handle as
    v_handle csv_handle;
  begin
    v_handle.csv_name := p_csv_name;
    v_handle.newline := 1;
    v_handle.buf := p_buf;
    write_bom(v_handle);
    return v_handle;
  end;
  
  procedure destroy_csv(p_handle in out nocopy csv_handle) as
  begin
    if p_handle.buf is not null then
      dbms_lob.freetemporary(p_handle.buf);
      p_handle.buf := null;
    end if;
  end;

  /**
   * 行頭でなければカンマを出力します.
   */
  procedure out_separator_if_need(p_handle in out nocopy csv_handle) as
  begin
    if p_handle.newline = 0 then
      rd := utl_i18n.string_to_raw(',', 'UTF8');
      dbms_lob.writeappend(p_handle.buf, utl_raw.length(rd), rd);
    end if;
  end;
  
  procedure WRITE_NUMBER(P_HANDLE in OUT NOCOPY CSV_HANDLE, VAL in number) as
    rdlen pls_integer;
  begin
    out_separator_if_need(p_handle);
    RD := UTL_I18N.STRING_TO_RAW(TO_CHAR(VAL), 'UTF8');
    RDLEN := UTL_RAW.length(RD);
    if RDLEN > 0 then
      DBMS_LOB.WRITEAPPEND(P_HANDLE.BUF, RDLEN, RD);
    end if;
    p_handle.newline := 0;
  end;
  
  procedure write_nvarchar(p_handle in out nocopy csv_handle, val nvarchar2) as
  begin
    out_separator_if_need(p_handle);
    -- 前後にダブルクォートをつけ、内容文にダブルクォートがあれば、それをエスケープする.
    rd := utl_i18n.string_to_raw('"' || replace(val, '"', '""') || '"', 'UTF8');
    dbms_lob.writeappend(p_handle.buf, utl_raw.length(rd), rd);
    p_handle.newline := 0;
  end;
  
  procedure write_varchar(p_handle in out nocopy csv_handle, val varchar2) as
  begin
    out_separator_if_need(p_handle);
    -- 前後にダブルクォートをつけ、内容文にダブルクォートがあれば、それをエスケープする.
    rd := utl_i18n.string_to_raw('"' || replace(val, '"', '""') || '"', 'UTF8');
    dbms_lob.writeappend(p_handle.buf, utl_raw.length(rd), rd);
    p_handle.newline := 0;
  end;

  procedure line_feed(p_handle in out nocopy csv_handle) as
  begin
    rd := utl_i18n.string_to_raw(crlf, 'UTF8');
    dbms_lob.writeappend(p_handle.buf, utl_raw.length(rd), rd);
    p_handle.newline := 1;
  end;
  
  procedure save_to_file(p_handle in out nocopy csv_handle, p_dir in varchar2, p_filename varchar2) as
    v_file utl_file.file_type;
  begin
    v_file := utl_file.fopen(
      p_dir, -- 予めCREATE DIRECTORYした名前。R/W権限も付与済みのこと。
      p_filename, -- 作成するファイル名、フォルダは含んではならない
      'wb' -- バイナリモードでオープン。(改行コードが自動でつかないようにする)
      );
    declare
      abount binary_integer  := 4096;
      pos pls_integer := 1;
      blob_len pls_integer := dbms_lob.getlength(p_handle.buf);
    begin
      while pos < blob_len
      loop
        dbms_lob.read(p_handle.buf, abount, pos, rd);
        utl_file.put_raw(
          v_file,
          rd,
          true
          );
        rd := null;
        pos := pos + abount;
      end loop;  
    exception when others then
      utl_file.fclose(v_file);
      raise;
    end;
    utl_file.fclose(v_file);
  end;

end csv_buffer;
/

CSV_BUFFER_TEST.sql

declare
  csv csv_buffer.csv_handle;
begin
  csv := csv_buffer.create_csv();
  for idx in 1 .. 30
  loop
    csv_buffer.write_number(csv, idx);
    csv_buffer.write_varchar(csv, '施設' || idx);
    csv_buffer.write_nvarchar(csv, N'施設V' || idX);
    csv_buffer.line_feed(csv);
  end loop;
  csv_buffer.save_to_file(csv, 'TEMP_DIR', 'TEST.CSV');
  csv_buffer.destroy_csv(csv);
end;

VAREXPANDER.pls

CREATE OR REPLACE package VAREXPANDER as
  
  /**
   * 変数名と、その値を格納する辞書オブジェクト.
   * キーは変数名、値は変数の値とする.
   */
  type DICT is table of varchar2(256) index by varchar2(256);
  
  /**
   * テンプレート文字列中に含まれる${FOO}のような置換変数を読み取り、
   * 辞書の値で置き換える.
   * ${FOO}で示す「FOO」という変数名が辞書にない場合は実行時例外となる.
   * @param templ 置換変数を含む文字列を与え、その置換結果が返るCLOB
   * @param dict 変数名と、その値を格納する辞書オブジェクト
   */
  procedure expand_variables(templ in out nocopy clob, args in out nocopy dict);
  
  /**
   * varchar2からCLOBを生成して返します.
   * @param templ varchar2型による文字列
   * @return 生成された一時CLOB
   */
  function make_clob(templ in varchar2) return clob;
  
  /**
   * 一時CLOBを解放します.
   */
  procedure free_clob(templ in out nocopy clob);

end VAREXPANDER;
/


CREATE OR REPLACE package body varexpander as

  /**
   * テンプレート文字列中に含まれる${FOO}のような置換変数を読み取り、
   * 辞書の値で置き換える.
   * ${FOO}で示す「FOO」という変数名が辞書にない場合は実行時例外となる.
   * @param templ 置換変数を含む文字列を与え、その置換結果が返るCLOB
   * @param dict 変数名と、その値を格納する辞書オブジェクト
   */
  procedure expand_variables(
      templ in out nocopy clob,
      args  in out nocopy dict)
  as
    buf clob; -- 展開後文字列バッファ
    mxlen pls_integer := dbms_lob.getlength(templ); -- 元のCLOBの長さ
  begin
    -- 展開後文字列バッファを一時CLOBとして作成
    dbms_lob.createtemporary(buf, true, dbms_lob.call);

    -- オープン
    dbms_lob.open(templ, dbms_lob.lob_readwrite);
    dbms_lob.open(buf, dbms_lob.lob_readwrite);

    declare
      lastpos pls_integer := 0;
      pos pls_integer;  -- 置換変数の頭
      pos2 pls_integer; -- 置換変数のしっぽ
      len pls_integer;     -- 長さ計算用
      destpos pls_integer; -- 書き込み先位置計算用
    begin
      loop
        -- 変数定義を探す
        pos := dbms_lob.instr(templ, '${', lastpos + 1);

        if pos <= 0 then
          -- 変数が末尾までなければ前回位置から末尾まで単純転記
          len := mxlen - lastpos;
        else
          -- 変数があれば、前回位置から変数名の手前まで転記
          len := pos - lastpos - 1;
        end if;

        -- 転記処理
        if len > 0 then
          destpos := dbms_lob.getlength(buf);
          dbms_lob.copy(buf, templ, len, destpos + 1, lastpos + 1);
        end if;

        -- これ以上、変数がなければで終了
        exit when pos <= 0;

        -- 変数名の区切り文字を探す
        pos2 := dbms_lob.instr(templ, '}', pos + 2);
        if pos2 <= 0 then
          raise_application_error(-20100, '変数名が閉じられていません pos=' || pos);
        end if;
  
        -- 変数名の取り出し
        len := pos2 - pos - 2;
        if len  > 0 then
          declare
            varname varchar2(256);
            varval  varchar2(256);
          begin
            varname := dbms_lob.substr(templ, len, pos + 2);
            begin
              -- 変数の値をバッファに書き込み
              varval := args(varname);
              dbms_lob.writeappend(buf, length(varval), varval);
            exception when others then
              raise_application_error(-20100, '変数がみつかりません: ' || varname, true);
            end;
          end;
        end if;
  
        lastpos := pos2;
      end loop;
  
      -- 完成したバッファCLOBを、もとのCLOBに書き戻す
      len := dbms_lob.getlength(buf);
      dbms_lob.copy(templ, buf, len, 1, 1);
      if len < mxlen then
        -- 元の長さよりも小さくなる場合は切り詰める
        dbms_lob.trim(templ, len);
      end if;
  
    exception when others then
      dbms_lob.close(templ);
      dbms_lob.close(buf);
      dbms_lob.freetemporary(buf);
      raise;
    end;
  
    dbms_lob.close(templ);
    dbms_lob.close(buf);
    dbms_lob.freetemporary(buf);
  end;

  /**
   * varchar2からCLOBを生成して返します.
   * @param templ varchar2型による文字列
   * @return 生成された一時CLOB
   */
  function make_clob(templ in varchar2) return clob as
    buf clob;
  begin
    dbms_lob.createtemporary(buf, true, dbms_lob.call);
    dbms_lob.writeappend(buf, length(templ), templ);
    return buf;
  end;
  
  /**
   * 一時CLOBを解放します.
   */
  procedure free_clob(templ in out nocopy clob) as
  begin
    dbms_lob.freetemporary(templ);
  end;

end varexpander;
/

VAREXPANDER_TEST.sql

declare
  dict varexpander.dict;
  msg clob;
begin
  dict('NAME') := '田中太郎';
  dict('TELEPHONE') := '123-4567-8901';
  dict('RESULT') := '適正';
  
  msg := varexpander.make_clob(
    N'${NAME}様、厳正な審査の結果、あなたは${RESULT}となりました。' ||
    N'この決定について不明な場合は${TELEPHONE}まで、お問い合わせください。');

  dbms_output.put_line('input=' || msg);
  varexpander.expand_variables(msg, dict);
  dbms_output.put_line('expanded=' || msg);
  
  varexpander.free_clob(msg);
end;