こっぱみぢんこ

PL/SQLのリファクタリングの4

昨日の続き。実際にコピーする処理の構造が同じなので、変化する要素であるSQLを切り離すことを考える。

すぐに思いつくのは、SQLを文字列として定義しておき、それを execute immediate で実行することだが、これだとストアドにしているメリットが大幅に減るんだよな。

実行するまでSQLが正しいかどうかが分からない。
実行都度SQLの解析が必要。
シングルクォーテーションやパラメーターの記述が面倒。

と、デメリットばかり浮かんでくる。やっぱりSQLはリテラルで記述しておきたい。

ということで、デザインパターンのコマンド相当で実装してみた。コマンドのインターフェースを決めて、個々のSQLはそのインターフェースの実装オブジェクトに持たせる。コマンドを実行する側はコマンドの中身には依存しないので、一つだけ作っておけばよい。そんな感じで。

まずは単純に置き換えてみた。

コマンド側。


create or replace type Base_Copy as object (
   m_nm varchar2(30)
   --
 , member function copy(
      p_bgn in date
    , p_end in date
   ) return number
) not final ;
/

create or replace type 納品_Copy under Base_Copy (
   constructor function 納品_Copy(
      self in out nocopy 納品_Copy
   ) return self as result
 , overriding member function copy(
      p_bgn in date
    , p_end in date
   ) return number
) ;
/

create or replace type body 納品_Copy
is
  constructor function 納品_Copy(
     self in out nocopy 納品_Copy
  ) return self as result
  is
  begin
    m_nm := '納品_Copy' ;
    return ;
  end ;
  --
  overriding member function copy(
     p_bgn in date
   , p_end in date
  ) return number
  is
  begin
    insert into 月次納品 (
       ISBN
     , 納品先ID
     , 工場出荷日
     , 納品予定日
     , 納品実績日
     , 価格
    )
    select
       ISBN
     , 納品先ID
     , 工場出荷日
     , 納品予定日
     , 納品実績日
     , 価格
    from
       日次納品
    where
       納品実績日 between p_bgn and p_end
    ;
    return sql%rowcount ;
  exception
    when others then
      raise ;
  end ;
end ;
/

create or replace type 返品_Copy under Base_Copy (
   constructor function 返品_Copy(
      self in out nocopy 返品_Copy
   ) return self as result
 , overriding member function copy(
      p_bgn in date
    , p_end in date
   ) return number
) ;
/

create or replace type body 返品_Copy
is
  constructor function 返品_Copy(
     self in out nocopy 返品_Copy
  ) return self as result
  is
  begin
    m_nm := '返品_Copy' ;
    return ;
  end ;
  --
  overriding member function copy(
     p_bgn in date
   , p_end in date
  ) return number
  is
  begin
    insert into 月次返品 (
       ISBN
     , 納品先ID
     , 返品予定日
     , 返品実績日
     , 払戻価格
     , 負担割合
     , 返品理由
    )
    select
       ISBN
     , 納品先ID
     , 返品予定日
     , 返品実績日
     , 払戻価格
     , 負担割合
     , 返品理由
    from
       日次返品
    where
       返品実績日 between p_bgn and p_end
    ;
    return sql%rowcount ;
  exception
    when others then
      raise ;
  end ;
end ;
/

コマンドを使う側。


create or replace package 月次処理
is
  PKG_NM constant varchar2(30) := '月次処理' ;
  logger bat_logger ;
  --
  function 当月分コピー return number ;
  --
  function コピー(
     p_bgn in date
   , p_end in date
   , p_obj in Base_Copy
  ) return number ;
end ;
/

create or replace package body 月次処理
is
  function 当月分コピー return number
  is
    FNC_NM constant varchar2(30) := '当月分コピー' ;
    FIN_DT constant number       := 15 ;
    v_bgn  date ;
    v_end  date ;
    v_cnt  number := 0 ;
  begin
    v_bgn := add_months( trunc( sysdate, 'MONTH' ), -1 ) + FIN_DT ; -- 先月16日00:00:00
    v_end := add_months( v_bgn, 1 ) - 1 / ( 24 * 60 * 60 ) ;        -- 当月15日23:59:59
    --
    logger.bgn_log( FNC_NM ) ;
    --
    v_cnt := v_cnt + コピー( v_bgn, v_end, new 納品_Copy ) ;
    v_cnt := v_cnt + コピー( v_bgn, v_end, new 返品_Copy ) ;
    commit ;
    --
    logger.end_log( FNC_NM, v_cnt ) ;
    return 0 ;
  exception
    when others then
      rollback ;
      dbms_output.put_line( dbms_utility.format_error_stack() );
      dbms_output.put_line( dbms_utility.format_error_backtrace() );
      logger.err_log( FNC_NM, sqlerrm ) ;
      return 1 ;
  end ;
  --
  function コピー(
     p_bgn in date
   , p_end in date
   , p_obj in Base_Copy
  ) return number
  is
    FNC_NM constant varchar2(30) := 'コピー' ;
    v_nm   varchar2(30) ;
    v_cnt  number := 0 ;
  begin
    v_nm := FNC_NM || '->' || p_obj.m_nm ;
    logger.bgn_log( v_nm ) ;
    --
    v_cnt := p_obj.copy( p_bgn, p_end ) ;
    --
    logger.end_log( v_nm, v_cnt ) ;
    return v_cnt ;
  exception
    when others then
      raise ;
  end ;
begin
  logger := new Bat_Logger( PKG_NM ) ;
end ;
/

うーん…。

オブジェクト指向言語で言う所のインターフェースと実装オブジェクトは、いずれもtypeによるもの。基底となる Base_Copy を継承してコピーの実装オブジェクトを作り、メソッド copy をオーバーライドして個別のSQLを記述した。

typeベースのオブジェクトは、typeがコレクション上がりだからか、フィールドを持たないメソッドだけのオブジェクトが作れない。ダミーのフィールドを作ってもよかったが、使わないものを持たせるのもどうかと思い直して、自分の名前をフィールドにしてみた。そしてこの値を、実装オブジェクトのコンストラクターで設定するようにした。

その結果、コードが結構増えてしまった。しかも値が違うだけの同じ構造の処理という、解消したいと思っていたそもそものパターンで増えるというどうしようもない結果に…。

一方コマンドを使う側は、昨日版ではそれぞれのコピー処理内に記述されていた処理

開始ログを出力する。
コピーするためのSQLを実行する。
正常終了ログを出力する。
処理件数を返す。
例外発生時は例外を再スローする。

が「コピー」というfunctionに一本化。こいつは、制御の流れの中で、引数として渡されたBase_Copyの実装コマンドのcopyメソッドを実行するが、copyメソッドの中で何が行われているかは一切関知しない。処理結果として更新件数を受け取るか例外を捕捉するだけ。

と、割と綺麗に制御とSQLが分離できているのだが、開始日と終了日を引数でずっと引き継いでいるのが邪魔臭い。

ということで、ちょっと変更。せっかくユーザー定義コンストラクターを作ったのだし、開始日と終了日はコンストラクターで指定するようにしてみた。

コマンド側。


create or replace type Base_Copy as object (
   m_nm  varchar2(30)
 , m_bgn date
 , m_end date
   --
 , member function copy return number
) not final ;
/

create or replace type 納品_Copy under Base_Copy (
   constructor function 納品_Copy(
      self  in out nocopy 納品_Copy
    , p_bgn in date
    , p_end in date
   ) return self as result
 , overriding member function copy return number
) ;
/

create or replace type body 納品_Copy
is
  constructor function 納品_Copy(
     self  in out nocopy 納品_Copy
   , p_bgn in date
   , p_end in date
  ) return self as result
  is
  begin
    m_nm  := '納品_Copy' ;
    m_bgn := p_bgn ;
    m_end := p_end ;
    return ;
  end ;
  --
  overriding member function copy return number
  is
  begin
    insert into 月次納品 (
       ISBN
     , 納品先ID
     , 工場出荷日
     , 納品予定日
     , 納品実績日
     , 価格
    )
    select
       ISBN
     , 納品先ID
     , 工場出荷日
     , 納品予定日
     , 納品実績日
     , 価格
    from
       日次納品
    where
       納品実績日 between m_bgn and m_end
    ;
    return sql%rowcount ;
  exception
    when others then
      raise ;
  end ;
end ;
/

create or replace type 返品_Copy under Base_Copy (
   constructor function 返品_Copy(
      self  in out nocopy 返品_Copy
    , p_bgn in date
    , p_end in date
   ) return self as result
 , overriding member function copy return number
) ;
/

create or replace type body 返品_Copy
is
  constructor function 返品_Copy(
     self  in out nocopy 返品_Copy
   , p_bgn in date
   , p_end in date
  ) return self as result
  is
  begin
    m_nm  := '返品_Copy' ;
    m_bgn := p_bgn ;
    m_end := p_end ;
    return ;
  end ;
  --
  overriding member function copy return number
  is
  begin
    insert into 月次返品 (
       ISBN
     , 納品先ID
     , 返品予定日
     , 返品実績日
     , 払戻価格
     , 負担割合
     , 返品理由
    )
    select
       ISBN
     , 納品先ID
     , 返品予定日
     , 返品実績日
     , 払戻価格
     , 負担割合
     , 返品理由
    from
       日次返品
    where
       返品実績日 between m_bgn and m_end
    ;
    return sql%rowcount ;
  exception
    when others then
      raise ;
  end ;
end ;
/

コマンドを使う側。


create or replace package 月次処理
is
  PKG_NM constant varchar2(30) := '月次処理' ;
  logger bat_logger ;
  --
  function 当月分コピー return number ;
  --
  function コピー(
     p_obj in Base_Copy
  ) return number ;
end ;
/

create or replace package body 月次処理
is
  function 当月分コピー return number
  is
    FNC_NM constant varchar2(30) := '当月分コピー' ;
    FIN_DT constant number       := 15 ;
    v_bgn  date ;
    v_end  date ;
    v_cnt  number := 0 ;
  begin
    v_bgn := add_months( trunc( sysdate, 'MONTH' ), -1 ) + FIN_DT ; -- 先月16日00:00:00
    v_end := add_months( v_bgn, 1 ) - 1 / ( 24 * 60 * 60 ) ;        -- 当月15日23:59:59
    --
    logger.bgn_log( FNC_NM ) ;
    --
    v_cnt := v_cnt + コピー( new 納品_Copy( v_bgn, v_end ) ) ;
    v_cnt := v_cnt + コピー( new 返品_Copy( v_bgn, v_end ) ) ;
    commit ;
    --
    logger.end_log( FNC_NM, v_cnt ) ;
    return 0 ;
  exception
    when others then
      rollback ;
      dbms_output.put_line( dbms_utility.format_error_stack() );
      dbms_output.put_line( dbms_utility.format_error_backtrace() );
      logger.err_log( FNC_NM, sqlerrm ) ;
      return 1 ;
  end ;
  --
  function コピー(
     p_obj in Base_Copy
  ) return number
  is
    FNC_NM constant varchar2(30) := 'コピー' ;
    v_nm   varchar2(30) ;
    v_cnt  number := 0 ;
  begin
    v_nm := FNC_NM || '->' || p_obj.m_nm ;
    logger.bgn_log( v_nm ) ;
    --
    v_cnt := p_obj.copy ;
    --
    logger.end_log( v_nm, v_cnt ) ;
    return v_cnt ;
  exception
    when others then
      raise ;
  end ;
begin
  logger := new Bat_Logger( PKG_NM ) ;
end ;
/

コマンドを使う側は若干すっきりしたが、コマンドは更に肥大化。

まあインターフェースの定義は必須だし、コンストラクターでやっていることもフィールドの初期化だけだし、必要最低限ではある。でもやっぱり、これを繰り返すことを考えると、面倒としか思えないんだよなぁ…。

ちょっと開き直ってみよう。

コマンドのコードがちょっとデブに感じるのは、値が違うだけで同じ構造のコードを繰り返しているから。それぞれのオブジェクトに固有の名前は内部で設定し、都度変わる開始日と終了日は外部から与えるようにと、フィールドの性質によって切り分けた結果そうなった。

でも考えてみれば、typeベースのオブジェクトってカプセル化できないんだよな。内側で設定したところで、外から丸見えだし変えられもする。だったら内部とか外部とか区別する意味が無いじゃないか。だったら全部コンストラクターで設定してもいいんじゃないか。

ということで更に変更。

コマンド側。


create or replace type Base_Copy as object (
   m_nm  varchar2(30)
 , m_bgn date
 , m_end date
   --
 , member function copy return number
) not final ;
/

create or replace type 納品_Copy under Base_Copy (
  overriding member function copy return number
) ;
/

create or replace type body 納品_Copy
is
  overriding member function copy return number
  is
  begin
    insert into 月次納品 (
       ISBN
     , 納品先ID
     , 工場出荷日
     , 納品予定日
     , 納品実績日
     , 価格
    )
    select
       ISBN
     , 納品先ID
     , 工場出荷日
     , 納品予定日
     , 納品実績日
     , 価格
    from
       日次納品
    where
       納品実績日 between m_bgn and m_end
    ;
    return sql%rowcount ;
  exception
    when others then
      raise ;
  end ;
end ;
/

create or replace type 返品_Copy under Base_Copy (
  overriding member function copy return number
) ;
/

create or replace type body 返品_Copy
is
  overriding member function copy return number
  is
  begin
    insert into 月次返品 (
       ISBN
     , 納品先ID
     , 返品予定日
     , 返品実績日
     , 払戻価格
     , 負担割合
     , 返品理由
    )
    select
       ISBN
     , 納品先ID
     , 返品予定日
     , 返品実績日
     , 払戻価格
     , 負担割合
     , 返品理由
    from
       日次返品
    where
       返品実績日 between m_bgn and m_end
    ;
    return sql%rowcount ;
  exception
    when others then
      raise ;
  end ;
end ;
/

コマンドを使う側。


create or replace package 月次処理
is
  PKG_NM constant varchar2(30) := '月次処理' ;
  logger bat_logger ;
  --
  function 当月分コピー return number ;
  --
  function コピー(
    p_obj in Base_Copy
  ) return number ;
end ;
/

create or replace package body 月次処理
is
  function 当月分コピー return number
  is
    FNC_NM constant varchar2(30) := '当月分コピー' ;
    FIN_DT constant number       := 15 ;
    v_bgn  date ;
    v_end  date ;
    v_cnt  number := 0 ;
  begin
    v_bgn := add_months( trunc( sysdate, 'MONTH' ), -1 ) + FIN_DT ; -- 先月16日00:00:00
    v_end := add_months( v_bgn, 1 ) - 1 / ( 24 * 60 * 60 ) ;        -- 当月15日23:59:59
    --
    logger.bgn_log( FNC_NM ) ;
    --
    v_cnt := v_cnt + コピー( new 納品_Copy( '納品_Copy', v_bgn, v_end ) ) ;
    v_cnt := v_cnt + コピー( new 返品_Copy( '返品_Copy', v_bgn, v_end ) ) ;
    commit ;
    --
    logger.end_log( FNC_NM, v_cnt ) ;
    return 0 ;
  exception
    when others then
      rollback ;
      dbms_output.put_line( dbms_utility.format_error_stack() );
      dbms_output.put_line( dbms_utility.format_error_backtrace() );
      logger.err_log( FNC_NM, sqlerrm ) ;
      return 1 ;
  end ;
  --
  function コピー(
    p_obj in Base_Copy
  ) return number
  is
    FNC_NM constant varchar2(30) := 'コピー' ;
    v_nm   varchar2(30) ;
    v_cnt  number := 0 ;
  begin
    v_nm := FNC_NM || '->' || p_obj.m_nm ;
    logger.bgn_log( v_nm ) ;
    --
    v_cnt := p_obj.copy ;
    --
    logger.end_log( v_nm, v_cnt ) ;
    return v_cnt ;
  exception
    when others then
      raise ;
  end ;
begin
  logger := new Bat_Logger( PKG_NM ) ;
end ;
/

ユーザー定義コンストラクターをやめて属性コンストラクターにすることで、コマンド側はかなりすっきりした。その結果、インスタンス生成時に自分の名前を指定するという間抜けなことになっているが、その間抜けな1行で他の17行が減らせるならいいだろう。

さて、typeを使えばオブジェクト指向っぽいことができることが判ったが、同時にいろいろ制約が多くて今一つ使い難いことも判った。

フィールドが無いメソッドだけのオブジェクトが作れない。
定数を持てない。
パッケージ内で宣言／実装できない。

これらが今回使っていて気になったこと。特にパッケージ内で宣言／実装できないってのがね。特定用途でしか使わないような汎用性の無いものでも、トップレベルで個別にtypeとtype bodyを作らなきゃいけないとか、何とかならないものか。

これ、オブジェクト型じゃないただのコレクション型としてならいけるんだよな。だからオブジェクトでもいけると思ったのだが、どう書いてもコンパイルエラーになってしまう。何か特殊な書き方が必要なのか。バージョンに依存するんじゃないか。と、改めてOracle10g〜12cのマニュアルを探した結果。

Oracle 10g

現在のところ、PL/SQLのブロック、サブプログラムまたはパッケージ内ではオブジェクト型を定義できません。

Oracle 11g

オブジェクト型がスキーマに定義およびインストールされたら、その型はすべてのPL/SQLブロック、サブプログラムまたはパッケージにおいて使用できます。

Oracle 12c

オブジェクト型がスキーマに定義およびインストールされたら、その型はすべてのPL/SQLブロック、サブプログラムまたはパッケージにおいて使用できます。

できないんだね。

まあできないならしょうがないけどさ。でも「できません」ときっぱり言い切りながらも「現在のところ」と但し書き付きをつけて将来に含みをもたせていた10gから、別建てに準備するのが当然のような11gの書き方への変更がモヤモヤする。

頑張ってみたけど駄目だった。でも駄目だったとは言いたくないので、なんとなくポジティブな表現にしてみた。

そんな感じがするよ。

まあそれはそれとして、SQLの定義と使用をもっと近付けたい。明日、もうちょっと考えてみる。

2018 03 07

PL/SQLのリファクタリングの4