DB2 9.5 で SQLプロシージャ(3)

hoge3 を呼び出すプロシージャ hoge2 を作成してみます。

hoge2

hoge2は、カーソルを使ってループし、そのループの中でhoge3を呼び出します。
細々とした事は、ソースの下に書きます。

CREATE PROCEDURE hoge2
(
   IN  ic_USER_ID               VARCHAR(10)
   IN  ic_CODE_1                CHAR(2)
  ,OUT oi_SQLCODE               INT
  ,OUT oc_SQLSTATE              CHAR(5)
  ,OUT ov_MESSAGE_TEXT          VARCHAR(70)
)
  SPECIFIC hoge2
  LANGUAGE SQL
  DETERMINISTIC
  NO EXTERNAL ACTION
  MODIFIES SQL DATA
BEGIN
  /*--------------------------------------------------------*/
  /*  変数の宣言                                            */
  /*--------------------------------------------------------*/
  DECLARE vtemp           CHAR(10);
  DECLARE SQLCODE         INT         DEFAULT 0;            --SQLCODE (0:データが戻された,100:データが戻されない)
  DECLARE SQLSTATE        CHAR(5)     DEFAULT '00000';      --SQLSTATE
  DECLARE vi_SQLCODE      INT         DEFAULT 0;            --SQLCODE (0:データが戻された,100:データが戻されない)
  DECLARE vc_SQLSTATE     CHAR(5)     DEFAULT '00000';      --SQLSTATE
  DECLARE vv_MESSAGE_TEXT VARCHAR(70) DEFAULT 'NORMAL END'; --MESSAGE_TEXT

  DECLARE vc_col1         CHAR(10)   DEFAULT ' ';

  -- 条件ハンドラー用
  DECLARE vi_NOT_FOUND INT DEFAULT 0;
  /*--------------------------------------------------------*/
  /*  カーソルの宣言                                        */
  /*--------------------------------------------------------*/
  DECLARE cur_ForTable1 CURSOR WITH HOLD FOR
    SELECT
       col1
      ,col2
      ,col3
    FROM
       Table1
    WHERE CODE_1 = ic_CODE_1
    ORDER BY
       col1
      ,col2
  ;

  /*--------------------------------------------------------*/
  /*  条件ハンドラーの宣言                                  */
  /*--------------------------------------------------------*/
  DECLARE CONTINUE HANDLER FOR NOT FOUND --SQLSTATE='02000'アンサーセット無し
  BEGIN
    SET vi_NOT_FOUND = 1;
  END;
  
  DECLARE CONTINUE HANDLER FOR SQLWARNING --SQLSTATE='01***'
  BEGIN
  END;
  
  DECLARE EXIT HANDLER FOR SQLEXCEPTION --SQLSTATE='01***','02***'以外全部
  BEGIN
    SET vtemp       = SQLSTATE || CAST(SQLCODE AS CHAR(5));
    SET oc_SQLSTATE = SUBSTR(vtemp,1,5);
    SET oi_SQLCODE  = INT(SUBSTR(vtemp,6,5));
    
    CASE vc_SQLSTATE
        WHEN '21000' THEN  --スカラー全選択、SELECT INTOの結果が複数行になった
          SET vv_MESSAGE_TEXT = 'hoge2 : TOO MANY ROWS' || ' : ' || vc_DOCUMENT_ID;
          
        WHEN '42704' THEN  --未定義の名前
          SET vv_MESSAGE_TEXT = 'hoge2 : UNDEFINED NAME' || ' : ' || vc_DOCUMENT_ID;
          
        WHEN '22012' THEN  --ゼロ除算
          SET vv_MESSAGE_TEXT = 'hoge2 : DIVISION BY ZERO' || ' : ' || vc_DOCUMENT_ID;
          
        WHEN '23505' THEN  --ユニーク制約違反
          SET vv_MESSAGE_TEXT = 'hoge3 : VIOLATION FOR UNIQUE CONSTRAINT' || ' : ' || vc_DOCUMENT_ID;
          
        WHEN '23502' THEN  --NULL制約違反
          SET vv_MESSAGE_TEXT = 'hoge2 : VIOLATION FOR NOT NULL CONSTRAINT' || ' : ' || vc_DOCUMENT_ID;
          
        ELSE
          SET vv_MESSAGE_TEXT = 'hoge2 : OTHERS EXCEPTION' || ' : ' || vc_DOCUMENT_ID;
        
    END CASE;
    
    -- ログ出力
    CALL LOG_hoge(
            'hoge2'
           ,oi_SQLCODE
           ,oc_SQLSTATE
           ,vv_MESSAGE_TEXT
         );
    
  END;
  
  /*--------------------------------------------------------*/
  /*  ステートメント                                        */
  /*--------------------------------------------------------*/
  SET vv_MESSAGE_TEXT = 'cur_ForTable1';
  SET vi_NOT_FOUND         = 0;
  OPEN cur_ForTable1;
  ForTable1:
  LOOP
    FETCH FROM cur_ForTable1
      INTO
         vc_col1
      ;

    IF vi_NOT_FOUND = 1 THEN
      LEAVE ForTable1;
    ELSE
      CALL hoge3(
              vc_DOCUMENT_ID
             ,vc_col1
             ,vi_SQLCODE
             ,vc_SQLSTATE
           );
    END IF;
  END LOOP ForTable1;
  CLOSE cur_ForTable1;
  
  SET oc_SQLSTATE     = vc_SQLSTATE;
  SET oi_SQLCODE      = vi_SQLCODE;
  SET ov_MESSAGE_TEXT = vv_MESSAGE_TEXT;
  COMMIT;
  
END
@

カーソル使うので、行を取得できなかった時の処理も入れてます。
こういう時、PL/SQLならば、%NOTFOUNDを使って、FETCHの後にEXIT WHEN カーソル%NOTFOUND が使えるのですが、DB2 9.5 SQLプロシージャはそんなうまいものがなさそうでした。実際どのような流れになるか、大まかに書いてみましょう。

まず、「OPEN cur_ForTable1」でカーソルをオープンし、ループ開始後、「FETCH FROM cur_ForTable1 INTO vc_col1」でカーソルから取り出した行(今回は値１つ)を 「vc_col1」にセットします。ここで行取得が出来ない場合は、「SQLSTATE='02000'アンサーセット無し」が発生します。

すると制御は、条件ハンドラー部の「HANDLER FOR NOT FOUND」に遷移します。
カーソルから取得できないだけでは、エラーとしたくなかったので、「CONTINUE HANDLER FOR NOT FOUND」にして、条件ハンドラー部を処理した後は、処理が継続するようにしました。また、カーソルをループを抜けてくれるよう、フラグ(vi_NOT_FOUND)に　1 をセットしています。

この、条件ハンドラー部を処理した後は、「IF vi_NOT_FOUND = 1 THEN」に処理が遷移してきます。そして、「vi_NOT_FOUND = 1」なので、「LEAVE ForTable1」してループを抜けます。

hoge3

次にhoge3を作成します。hoge3は、hoge2から受け取ったDOC_IDを元に、table2の内容を更新します。ただし今回はUPDATEではなく、DELETE-INSERT方式としました。DOC_IDのレコードが存在しない場合は、UPDATEではなくINSERTになってしまうので。

注意：上記がベストであるとは思っていません。他にも検討の余地はありますが今回はこれで。例えば、「とりあえずINSERTしてデュプってたらUPDATE」や「MERGE」や「前もってCOUNT(DOC_ID)してゼロか否か」など色々です。

CREATE PROCEDURE hoge3
(
   IN  ic_DOC_ID                VARCHAR(16)
  ,IN  ic_col1                  CHAR(2)
  ,OUT oi_SQLCODE               INT
  ,OUT oc_SQLSTATE              CHAR(5)
)
  SPECIFIC hoge3
  LANGUAGE SQL
  DETERMINISTIC
  NO EXTERNAL ACTION
  MODIFIES SQL DATA
BEGIN
  /*--------------------------------------------------------*/
  /*  変数の宣言                                            */
  /*--------------------------------------------------------*/
  DECLARE vtemp           CHAR(10);
  DECLARE SQLCODE         INT         DEFAULT 0;            --SQLCODE (0:データが戻された,100:データが戻されない)
  DECLARE SQLSTATE        CHAR(5)     DEFAULT '00000';      --SQLSTATE
  DECLARE vi_SQLCODE      INT         DEFAULT 0;            --SQLCODE (0:データが戻された,100:データが戻されない)
  DECLARE vc_SQLSTATE     CHAR(5)     DEFAULT '00000';      --SQLSTATE
  DECLARE vv_MESSAGE_TEXT VARCHAR(70) DEFAULT 'NORMAL END'; --MESSAGE_TEXT
  
  DECLARE oc_DOC_ID        CHAR(16)  DEFAULT ' ';
  DECLARE oc_col33         CHAR(10)  DEFAULT ' ';
  
  /*--------------------------------------------------------*/
  /*  カーソルの宣言                                        */
  /*--------------------------------------------------------*/
  /*--------------------------------------------------------*/
  /*  条件ハンドラーの宣言                                  */
  /*--------------------------------------------------------*/
  DECLARE CONTINUE HANDLER FOR NOT FOUND --SQLSTATE='02000'アンサーセット無し
  BEGIN
    SET vtemp       = SQLSTATE || CAST(SQLCODE AS CHAR(5));
    SET oc_SQLSTATE = SUBSTR(vtemp,1,5);
    SET oi_SQLCODE  = INT(SUBSTR(vtemp,6,5));
  END;
  
  DECLARE CONTINUE HANDLER FOR SQLWARNING --SQLSTATE='01***'
  BEGIN
  END;
  
  DECLARE EXIT HANDLER FOR SQLEXCEPTION --SQLSTATE='01***','02***'以外全部
  BEGIN
    SET vtemp       = SQLSTATE || CAST(SQLCODE AS CHAR(5));
    SET oc_SQLSTATE = SUBSTR(vtemp,1,5);
    SET oi_SQLCODE  = INT(SUBSTR(vtemp,6,5));
    GET DIAGNOSTICS EXCEPTION 1 vv_MESSAGE_TEXT = MESSAGE_TEXT;
    
    IF vv_MESSAGE_TEXT IS NULL
    THEN
      CASE vc_SQLSTATE
        WHEN '21000' THEN  --スカラー全選択、SELECT INTOの結果が複数行になった
          SET vv_MESSAGE_TEXT = 'hoge3 : TOO MANY ROWS' || ' : ' || vc_DOCUMENT_ID;
          
        WHEN '42704' THEN  --未定義の名前
          SET vv_MESSAGE_TEXT = 'hoge3 : UNDEFINED NAME' || ' : ' || vc_DOCUMENT_ID;
          
        WHEN '22012' THEN  --ゼロ除算
          SET vv_MESSAGE_TEXT = 'hoge3 : DIVISION BY ZERO' || ' : ' || vc_DOCUMENT_ID;
          
        WHEN '23505' THEN  --ユニーク制約違反
          SET vv_MESSAGE_TEXT = 'hoge3 : VIOLATION FOR UNIQUE CONSTRAINT' || ' : ' || vc_DOCUMENT_ID;
          
        WHEN '23502' THEN  --NULL制約違反
          SET vv_MESSAGE_TEXT = 'hoge3 : VIOLATION FOR NOT NULL CONSTRAINT' || ' : ' || vc_DOCUMENT_ID;
          
        ELSE
          SET vv_MESSAGE_TEXT = 'hoge3 : OTHERS EXCEPTION' || ' : ' || vc_DOCUMENT_ID;
          
      END CASE;
    ELSE
      SET vv_MESSAGE_TEXT = vv_MESSAGE_TEXT || ' : ' || vc_DOCUMENT_ID;
    END IF;
  
    -- ログ出力
    CALL LOG_hoge(
            'hoge3'
           ,oi_SQLCODE
           ,oc_SQLSTATE
           ,vv_MESSAGE_TEXT
         );
  END;
  
  /*--------------------------------------------------------*/
  /*  ステートメント                                        */
  /*--------------------------------------------------------*/
  SET oc_DOC_ID = ic_DOC_ID;
  
  DELETE FROM table2
   WHERE DOC_ID = ic_DOC_ID
  ;
  
  SELECT
     col33
  INTO
     oc_col33
  FROM
  WHERE col1 = ic_col1
  ;
  
  INSERT INTO
     table2
  VALUES (
     oc_DOC_ID
    ,oc_col33
  )
  ;
  
  COMMIT;
  
END
@

ポイントは、DELETE-INSERT方式。DELETEする時に、存在しない場合、「SQLSTATE='02000'アンサーセット無し」が発生するので、hoge2同様、条件ハンドラー部で「CONTINUE HANDLER FOR NOT FOUND」としました。これが後々不幸な結果をもたらす事になろうとは、考えもしませんでした。
時間も遅いので今日はここまで。