プロシジャの出力パラメータの値を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