こっぱみぢんこ

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

昨日のコマンドパターン風味は、共通処理となる制御部分と個別の処理をするSQLとを綺麗に分けることができたが、宣言／定義と使う場所が遠くなりすぎた感がある。ということで、今日は同じパッケージ内でなんとかできないかを考える。

と言うといかにもこれから考えるようだが、実は朝からずっとこればっかり考えていた。通勤のバスの中で。仕事の気分転換に。いやもうこっちがメインで、気分転換に仕事している状態。すっごい忙しいはずなのに。

で、いろいろ考えた結果、早々に捨てた execute immediate に戻ってきた。

動的SQLを避けたかった理由は昨日言った通りだが、それはSQLが大きく複雑なことが前提なんだよな。簡単なSQLなら問題無いのだ。大きく複雑になる想定の実際にデータの処理を行うSQLはモジュール内にリテラルで定義しておき、そのモジュールの実行を動的SQLにすれば、怪しい部分を極小化できるんじゃないか。

そんな方針でやった結果がこれ。


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_fnc in varchar2
  ) return number ;
  --
  function 納品_Copy(
     p_bgn in date
   , p_end in date
  ) return number ;
  --
  function 返品_Copy(
     p_bgn in date
   , p_end in date
  ) 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
    logger.bgn_log( FNC_NM ) ;
    --
    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
    --
    v_cnt := v_cnt + コピー( v_bgn, v_end, '納品_Copy' ) ;
    v_cnt := v_cnt + コピー( v_bgn, v_end, '返品_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_fnc in varchar2
  ) return number
  is
    FNC_NM constant varchar2(30) := 'コピー' ;
    v_nm   varchar2(30) ;
    v_cnt  number := 0 ;
  begin
    v_nm := FNC_NM || '.' || p_fnc ;
    logger.bgn_log( v_nm ) ;
    --
    execute immediate
      'begin :v_cnt := ' || PKG_NM || '.' || p_fnc || '( :p_bgn, :p_end ); end;'
      using out v_cnt, in p_bgn, in p_end ;
    --
    logger.end_log( v_nm, v_cnt ) ;
    return v_cnt ;
  exception
    when others then
      raise ;
  end ;
  --
  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 ;
  --
  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 ;
  --
begin
  logger := new Bat_Logger( PKG_NM ) ;
end ;
/

うーん…。

当月の範囲を引数で明示的に渡しているせいか、ちょっとくどい。同じ理由で、SQLをラップするモジュールを実行する execute immediate の部分も、ちょっと読み難い。まあここは今更なんだけどさ。

あんまりいい方向とは思えないが、月の開始日と終了日の引数渡しをやめてみた。


create or replace package 月次処理
is
  PKG_NM constant varchar2(30) := '月次処理' ;
  FIN_DT constant number := 15 ;
  logger Bat_Logger ;
  --
  function 当月分コピー return number ;
  --
  function コピー(
     p_fnc in varchar2
  ) return number ;
  --
  function 納品_Copy return number ;
  function 返品_Copy return number ;
end ;
/

create or replace package body 月次処理
is
  BGN_DT constant date := add_months( trunc( sysdate, 'MONTH' ), -1 ) + FIN_DT ;
  END_DT constant date := add_months( BGN_DT, 1 ) - 1 / ( 24 * 60 * 60 ) ;
  --
  function 当月分コピー return number
  is
    FNC_NM constant varchar2(30) := '当月分コピー' ;
    v_cnt  number := 0 ;
  begin
    logger.bgn_log( FNC_NM ) ;
    --
    v_cnt := v_cnt + コピー( '納品_Copy' ) ;
    v_cnt := v_cnt + コピー( '返品_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_fnc in varchar2
  ) return number
  is
    FNC_NM constant varchar2(30) := 'コピー' ;
    v_nm   varchar2(30) ;
    v_cnt  number := 0 ;
  begin
    v_nm := FNC_NM || '.' || p_fnc ;
    logger.bgn_log( v_nm ) ;
    --
    execute immediate
      'begin :v_cnt := ' || PKG_NM || '.' || p_fnc || '; end;'
      using out v_cnt ;
    --
    logger.end_log( v_nm, v_cnt ) ;
    return v_cnt ;
  exception
    when others then
      raise ;
  end ;
  --
  function 納品_Copy return number
  is
  begin
    insert into 月次納品 (
       ISBN
     , 納品先ID
     , 工場出荷日
     , 納品予定日
     , 納品実績日
     , 価格
    )
    select
       ISBN
     , 納品先ID
     , 工場出荷日
     , 納品予定日
     , 納品実績日
     , 価格
    from
       日次納品
    where
       納品実績日 between BGN_DT and END_DT
    ;
    return sql%rowcount ;
  exception
    when others then
      raise ;
  end ;
  --
  function 返品_Copy return number
  is
  begin
    insert into 月次返品 (
       ISBN
     , 納品先ID
     , 返品予定日
     , 返品実績日
     , 払戻価格
     , 負担割合
     , 返品理由
    )
    select
       ISBN
     , 納品先ID
     , 返品予定日
     , 返品実績日
     , 払戻価格
     , 負担割合
     , 返品理由
    from
       日次返品
    where
       返品実績日 between BGN_DT and END_DT
    ;
    return sql%rowcount ;
  exception
    when others then
      raise ;
  end ;
  --
begin
  logger := new Bat_Logger( PKG_NM ) ;
end ;
/

コードのすっきり感はこっちの方があると思うが、モジュールの独立性はちょっと落ちたような…。その辺りの怪しさを少しでも薄くするために、開始日と終了日は定数扱いにしているが。

両者共通で微妙な感じなのは、動的SQL実行の部分。

文字列のSQLを execute immediate で実行するとき、その記述している場所がどこであれ、実行時はトップレベルから見えるものしか見えなくなる。なのでコピーを実行するためのモジュールをわざわざパッケージ内に持ってきたのに、実行するときに自分のパッケージ名も記述するという間抜けなことに。

昨日もわざわざ自分の名前を書かなきゃいけないのが気になったが、結局今日も同じようなことになってしまった。と言っても方向は逆で、今日は親の名前を書かされるんだけどさ。

さて、だいたい一通りのことをやったと思うので、リファクタリングはここらで一旦終わり。

最終的に何が良いのか。

書く量は昨日のも今日のも大差無いが、コンパイルの段階でチェックが効かない部分が無いだけ、昨日の方が良いような気がする。あと、Excelか何かで自動生成するのにも、昨日の方が向いてるんじゃないだろうか。サンプルが簡単だったせいで、何の工夫もないベタ書きの方が読みやすいんじゃないか、徒らに複雑にしただけなんじゃないか、という気もするんだけどさ。

まあ折角色々やってみたのだし、いつかどこかで使ってみよう。

2018 03 08

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