HHeLiBeXの日記 正道編

日々の記憶の記録とメモ‥

プロシジャの出力パラメータの値をSQLだけを使って確認する

例えば、DB2で次のようなプロシジャを作ったとする。('!'は、「db2 -td! -f hoge.sql」と実行するための区切り文字)

-- 配列型はCREATE TYPEしないと使えない
CREATE TYPE CHARACTER_VARYING_255_ARRAY AS CHARACTER VARYING(255) ARRAY[255]
!
-- テスト対象のプロシジャ1
CREATE PROCEDURE test_target_1
   (IN strs CHARACTER_VARYING_255_ARRAY,
    IN separator CHARACTER VARYING(8),
    OUT ResultValue CLOB)
   LANGUAGE SQL
   DETERMINISTIC
   MODIFIES SQL DATA
   BEGIN
      DECLARE i INTEGER;
      SET ResultValue = '';
      SET i = 1;
      WHILE i <= CARDINALITY(strs) DO
         IF i > 1 THEN
            SET ResultValue = ResultValue || separator;
         END IF;
         SET ResultValue = ResultValue || strs[i];
         SET i = i + 1;
      END WHILE;
   END
!
-- テスト対象のプロシジャ2
CREATE PROCEDURE test_target_2
   (IN strs CHARACTER_VARYING_255_ARRAY,
    OUT ResultValue CLOB)
   LANGUAGE SQL
   DETERMINISTIC
   MODIFIES SQL DATA
   BEGIN
      DECLARE i INTEGER;
      SET ResultValue = '';
      SET i = 1;
      WHILE i <= CARDINALITY(strs) DO
         SET ResultValue = ResultValue || UPPER(strs[i]);
         SET i = i + 1;
      END WHILE;
   END
!

Javaとかでプログラムを書けば出力パラメータの値は得られるのだが、テストのためだけにそんなことをするのは嫌だ、ということで、何か考えてみた。
とりあえず、上記2つのプロシジャのテストケースをそれぞれ1つずつ書いてみる。

-- テストケース1
--   OUT ResultValue: テスト対象のプロシジャからの出力を格納する変数
CREATE PROCEDURE test_case_1
   (OUT ResultValue CLOB)
   LANGUAGE SQL
   DETERMINISTIC
   MODIFIES SQL DATA
   BEGIN
      DECLARE dat CHARACTER_VARYING_255_ARRAY;
      SET dat = ARRAY['aaa', 'bbb', 'ccc', 'ddd'];
      CALL test_target_1(dat, '-', ResultValue);
   END
!
-- テストケース2
--   OUT ResultValue: テスト対象のプロシジャからの出力を格納する変数
CREATE PROCEDURE test_case_2
   (OUT ResultValue CLOB)
   LANGUAGE SQL
   DETERMINISTIC
   MODIFIES SQL DATA
   BEGIN
      DECLARE dat CHARACTER_VARYING_255_ARRAY;
      SET dat = ARRAY['aaa', 'bbb', 'ccc', 'ddd'];
      CALL test_target_2(dat, ResultValue);
   END
!

テストケースプロシジャは、出力パラメータを1つ持つ。これをどこか適当なテーブルに放り込んでやれば、出力を確認できるかな、ということで、次のようなものを作る。

-- プロシジャの単体テスト実行結果を保持するテーブル
--   ts: 実行日時
--   nm: テストケース名
--   rv: 実行結果の出力
CREATE TABLE test_log(ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, nm VARCHAR(64), rv CLOB)
!
-- テストケースを実行するプロシジャ
--   IN name: テストケース(プロシジャ)名
CREATE PROCEDURE test_call
   (IN name CLOB)
   LANGUAGE SQL
   DETERMINISTIC
   MODIFIES SQL DATA
   BEGIN
      DECLARE stmt1 CLOB;
      DECLARE stmt2 CLOB;
      DECLARE ResultValue CLOB;

      SET stmt1 = 'CALL ' || name || '(?)';
      PREPARE pstmt1 FROM stmt1;
      EXECUTE pstmt1 INTO ResultValue;

      SET stmt2 = 'INSERT INTO test_log(nm, rv) VALUES(?, ?)';
      PREPARE pstmt2 FROM stmt2;
      EXECUTE pstmt2 USING name, ResultValue;
   END
!

これを使って、次のように実行

CALL test_call('test_case_1')
!
CALL test_call('test_case_2')
!
SELECT CAST(ts AS TIMESTAMP(0)) AS ts, CAST(nm AS VARCHAR(16)) AS nm, CAST(rv AS VARCHAR(32)) AS rv FROM test_log
!

すると、次のような出力が得られる。

TS                  NM               RV
------------------- ---------------- --------------------------------
2011-03-05-01.55.08 test_case_1      aaa-bbb-ccc-ddd
2011-03-05-01.55.08 test_case_2      AAABBBCCCDDD

これなら、テキストエディタDB2コマンドウインドウ1つがあればテストができる、っと(謎)