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

前回は例外処理がよくわからんというところまででしたが、例外処理、一応入れてみました。これでたぶん大丈夫そう。大丈夫だと思う。おそらく。。うん。

hoge1

CREATE PROCEDURE hoge01
(
   IN  ic_DOC_ID                VARCHAR(16)
  ,OUT oi_SQLCODE               INT
  ,OUT oc_SQLSTATE              CHAR(5)
  ,OUT ov_MESSAGE_TEXT          VARCHAR(70)
)
  SPECIFIC hoge01
  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 vi_NOT_FOUND INT DEFAULT 0;
  /*--------------------------------------------------------*/
  /*  カーソルの宣言                                        */
  /*--------------------------------------------------------*/
  /*--------------------------------------------------------*/
  /*  条件ハンドラーの宣言                                  */
  /*--------------------------------------------------------*/
  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));
    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 ov_MESSAGE_TEXT = 'hoge01:TOO MANY ROWS' || ' : ' || vc_DOCUMENT_ID;
          
        WHEN '42704' THEN  --未定義の名前
          SET ov_MESSAGE_TEXT = 'hoge01:UNDEFINED NAME' || ' : ' || vc_DOCUMENT_ID;
          
        WHEN '22012' THEN  --ゼロ除算
          SET ov_MESSAGE_TEXT = 'hoge01:DIVISION BY ZERO' || ' : ' || vc_DOCUMENT_ID;
          
        WHEN '23505' THEN  --ユニーク制約違反
          SET ov_MESSAGE_TEXT = 'hoge01:VIOLATION FOR UNIQUE CONSTRAINT' || ' : ' || vc_DOCUMENT_ID;
          
        WHEN '23502' THEN  --NULL制約違反
          SET ov_MESSAGE_TEXT = 'hoge01:VIOLATION FOR NOT NULL CONSTRAINT' || ' : ' || vc_DOCUMENT_ID;
          
        ELSE
          SET ov_MESSAGE_TEXT = 'hoge01:OTHERS EXCEPTION' || ' : ' || vc_DOCUMENT_ID;
          
      END CASE;
    ELSE
      SET ov_MESSAGE_TEXT = vv_MESSAGE_TEXT || ' : ' || vc_DOCUMENT_ID;
    END IF;
  
    -- ログ出力
    CALL LOG_hoge(
            'hoge1'
           ,oi_SQLCODE
           ,oc_SQLSTATE
           ,ov_MESSAGE_TEXT
         );
    
  END;
  
  /*--------------------------------------------------------*/
  /*  ステートメント                                        */
  /*--------------------------------------------------------*/
  SET oc_DOC_ID = ic_DOC_ID;
  
  DELETE FROM XXX
   WHERE DOC_ID = ic_DOC_ID
  ;
  
  INSERT INTO
     XXX
  VALUES (
     oc_DOC_ID, ..
  )
  ;
  
  COMMIT;
  
END
@

上の中で LOG_hoge を CALL しているが、なぜかというと、どうも、DB2 SQLプロシージャは、ファイルシステムにファイル出力が出来ないみたいだから。
Oracleならば、UTL_FILE使えるのに。。。かなりショック！9.7からは使えるのかもしれないが、それまでの人たちはどうしたらいいのか？ログ出力のために、CやJavaでストアドプロシージャを書けというのか？用意されていない事がお粗末にしか思えなくなってきた。

さて、LOG_hogeの方はというと、まず、テーブルとシーケンスを準備しておく。

シーケンスの作成

CREATE SEQUENCE SCHEMA_NAME.LOG_hoge_SEQ
 AS INTEGER
 START WITH 1
 INCREMENT BY 1
 CYCLE;

ログテーブルの作成

CREATE TABLE SCHEMA_NAME.LOG_hoge_TABLE (
  LOG_NO               INT       NOT NULL ,
  CRT_TIME_STAMP       TIMESTAMP NOT NULL ,
  PGID                 CHAR(15)  NOT NULL ,
  SQLCODE              INT,
  SQLSTATE             CHAR(5),
  MESSAGE_TEXT         VARCHAR(70)
)
 IN "USERSPACE1" ;

LOG_hoge

CREATE PROCEDURE LOG_hoge
(
   IN  ic_PGID                  VARCHAR(15)
  ,IN  ii_SQLCODE               INT
  ,IN  ic_SQLSTATE              CHAR(5)
  ,IN  iv_MESSAGE_TEXT          VARCHAR(70)
)
  SPECIFIC LOG_hoge
  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 CONTINUE HANDLER FOR NOT FOUND --SQLSTATE='02000'アンサーセット無し
  BEGIN
  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 vc_SQLSTATE = SUBSTR(vtemp,1,5);
    SET vi_SQLCODE  = INT(SUBSTR(vtemp,6,5));
    GET DIAGNOSTICS EXCEPTION 1 vv_MESSAGE_TEXT = MESSAGE_TEXT;
  END;
  
  /*--------------------------------------------------------*/
  /*  ステートメント                                        */
  /*--------------------------------------------------------*/
  --■ ログテーブルに情報を書き出す
  INSERT INTO LOG_hoge_TABLE
    VALUES (
       NEXT VALUE FOR LOG_hoge_SEQ --ログNO
      ,CURRENT_TIMESTAMP           --作成タイムスタンプ
      ,ic_PGID                     --PGID
      ,ii_SQLCODE                  --SQLCODE
      ,ic_SQLSTATE                 --SQLSTATE
      ,iv_MESSAGE_TEXT             --MESSAGE_TEXT
    );
  
  --■ コミットする。
  COMMIT;
  
END
@

今日はここまで。