HSQLDB 　 HSQLDBのストアドプロシージャ
　 (not ever)


HSQLDBのストアドプロシージャ

In-ProcessモードでHSQLDBを使っていると、CONFLICTとかDEADLOCKとかよくわからんエラーに悩まされます。 これやったら、Java VM上で動くよく似たH2 Databaseの方を使えばええやんけと思ってしまいますが、 HSQLDBの方が一日の長があるのがストアドファンクション/ストアドプロシージャのサポートです。 CREATE ALIASという、Javaコードの別名に当たる物だけが作れるH2に対して、 HSQLDBではOracleのPL/SQLと同等でかつ、SQL/PSM標準への準拠を目指した構造化SQLでストアドファンクションやストアドプロシージャを定義でき、 JDBCクライアントから呼び出して実行できます。
なお、HSQLDBよりも新しいプロダクトであるはずのH2 Databaseがストアドプロシージャをサポートしないのは、 両者の目指す所の違い、設計思想やそれに基づく実装優先度の違いが大きいようです。 1999年に生まれたHSQLDBは、当時の多くのRDBMSと同様に完全なSQLのサポート(SQL/PSMを含む) を主目的として発展し、そのために企業情報システムが必要とする機能を幅広くサポートする結果となりました。 一方でH2は特に組み込み用途に特化し、Javaアプリケーションの内部にロジックを統合・集約する方向に機能を集中させたため、 ストアドプロシージャは対象から外れたものと思われます。

●OUT引数を持たないストアドプロシージャのサンプル
それでは早速、ストアドプロシージャのサンプルを一つテキストファイルで書いてみます。 

DROP PROCEDURE UPDATE_NOTES IF EXISTS;

CREATE PROCEDURE UPDATE_NOTES(
  IN ani_item_id INT,
  IN avi_notes VARCHAR(255),
  IN ani_display_order INT
)
MODIFIES SQL DATA
BEGIN ATOMIC
  UPDATE ITEMS
     SET NOTES = avi_notes,
         DISPLAY_ORDER = ani_display_order
   WHERE ITEM_ID = ani_item_id;
END;
.;
これを一例で「procedure.sql」というファイルにして、SqlToolを起動し、 
\i procedure.sql
で作成できます。

引数で渡した値でNOTESとDISPLAY_ORDERというそれぞれVARCHAR、INT型の列を更新するだけのサンプルですが、 これだけでも幾つか注意点があります。

  1. Oracleと違って「CREATE OR REPLACE」とは書けないので、既存のプロシージャを置き換える場合は事前にDROPする必要があります。 そんとき、IF EXISTSを付けると存在しなくてもエラーになりません。これはHSQLDBではプロシージャに限らず表やビューなども同様です。
  2. 引数のINやOUTの位置はOracleのPL/SQLとは違います。PostgreSQLなどと同じですね。
  3. 引数のデータ型にVARCHARを指定するとき、最大サイズを省略できません(Oracleは省略可能)
  4. MODIFIESの部分は、テーブルを更新しない(SELECTのみ)の場合は「READS」とします。
  5. SqlToolで実行するときは、末尾に「.;」という謎な物を書く必要があります。 (参考サイト)。 これを付けないと「unterminated input」というよく分からないエラーに悩まされることになります。

作ったらさっそく実行してみましょう。SqlToolからはこのように実行できます。 

CALL UPDATE_NOTES(10, 'テストの備考', 398);
SELECT * FROM ITEMS WHERE ITEM_ID = 10;
COMMIT;
このプロシージャはOUT引数がないので、何も応答はありませんが、別途SELECT文を発行するとデータが変わっているのが分かると思います。
ちなみに以前にも触れましたが、 SqlToolはデフォルトでオートコミットではないので、実行後commit文の発行が必要です。
さらに起動時のURLに;shutdown=true;を付けていない場合はshutdownも実行が必要なので注意してくださいね。

●OUT引数を持つストアドプロシージャのサンプル
先のサンプルでは、OUT引数を持たないので処理結果を返すことができませんでした (別途処理結果ログテーブルなどを作って書き込み、後続処理で参照するなどで対応できますが)。 というわけで、次にOUT引数を持つ版を作ってみます。 

DROP PROCEDURE UPDATE_NOTES IF EXISTS;

CREATE PROCEDURE UPDATE_NOTES(
  IN ani_item_id INT,
  IN avi_notes VARCHAR(255),
  IN ani_display_order INT,
  OUT ano_ora INT,
  OUT avo_message VARCHAR(255)
)
MODIFIES SQL DATA
BEGIN ATOMIC
  DECLARE ln_affected_rows INT; 

  SET ano_ora = 0;
  SET avo_message = '(OK)';

  UPDATE ITEMS
     SET NOTES = avi_notes,
         DISPLAY_ORDER = ani_display_order
   WHERE ITEM_ID = ani_item_id;

  GET DIAGNOSTICS ln_affected_rows = ROW_COUNT;
  if ln_affected_rows = 0 then
    SET ano_ora = 123; /* 適当 */
    SET avo_message = '対象レコードが見つかりません。';
  end if;
END;
.;
OUT引数が増えただけ…ではないので、追加でさらに色々な注意事項があります。 順番に見ていくと、
  1. ローカル変数をDECLAREで宣言するのはOracleのPL/SQLと共通ですが、 よく見ると宣言する位置がBEGINの後ろ、つまり処理本体の中に書く点が異なります（宣言部というブロックがない）。
  2. 代入は「:=」ではなくSETを使います。
  3. IF文の文法はOracleやPostgreSQLと同じです。つまりEND IF;で終わります。
  4. 直前に実行したDML文の処理行数はPL/SQLでは「SQL%ROWCOUNT」で取得できますが、 HSQLDBの場合は例のようにGET DIAGNOSTICSを使います (参考サイト)。
  5. コメントや文字列に日本語を使う場合、Windows上であればSJISでこのソースコードを書けばOKです。 UTF-8にする必要はなく、Java上で正常に日本語の文字列が受け取れるようです。
で、作成方法は先ほどと同じです。いいですね。では早速実行を…という所で、最後に最も重大な注意事項があります。
なんと、SqlToolではOUT引数をコマンドラインで定義する方法がないため、 OUT引数を持つストアドプロシージャを実行できません。
なんちゅう〜！！といってもできないものは仕方ありません。そう、JDBCで接続するJavaコードを書いて実行する必要があります。

●JDBCでストアドプロシージャを実行するサンプル
では、そのJDBCで接続してストアドプロシージャを実行するサンプルです。
こちらはJDBC共通のCallableStatementを使っているため、Oracle等と同じで特に問題になる点はないと思います。 

package test.jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class HsqldbCallableExample {
    public static void main(String args[]) {
        Connection conn = null;
        CallableStatement stmt = null;

        try {
            // JDBCドライバをロードし、データベースに接続
            //Class.forName("org.hsqldb.jdbcDriver");
            //final String url = "jdbc:hsqldb:file:C:/usr/dbms/hsqldb-2.7/database/nkdb;shutdown=true;ifexists=true";
            final String url = "jdbc:hsqldb:hsql://localhost:9001/nkx;ifexists=true";
            conn = DriverManager.getConnection(url, "sa", "");
            System.out.println("connected.");

            // SQL文(DML文)作成とパラメータ設定
            final String sql = "{call UPDATE_NOTES(?, ?, ?, ?, ?)}";
            final int itemId = 10;
            final String notes = "テスト by JDBC";
            final int displayOrder = 398;
            stmt = conn.prepareCall(sql);
            stmt.setInt(1, itemId);
            stmt.setString(2, notes);
            stmt.setInt(3, displayOrder);
            stmt.registerOutParameter(4, java.sql.Types.INTEGER);
            stmt.registerOutParameter(5, java.sql.Types.VARCHAR);

            // ストアドプロシージャの実行とOUTパラメータの値の取得
            stmt.execute();
            final int status = stmt.getInt(4);
            final String message = stmt.getString(5);

            System.out.println("Calling stored procedure ended successfully.");
            System.out.println(" with status=[" + status + "] message=[" + message + "]");
        } catch(Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                if(stmt != null) {
                    stmt.close();
                }
                if(conn != null) {
                    conn.close();
                }
            } catch(SQLException ex) {
                ex.printStackTrace();
            }
        }
    }
}

　 もくじ
HSQLDB 　 (C) 2002-2025 MISUMI URANO (2025/03/23 - (not ever))