ストアドプロシジャとトリガー言語内の文字列関数


本ページは、Ivan Prenosil による Ivan Prenosil's InterBase site 内の記事 String functions in SP/Trigger language. の日本語訳です。 本ページについては、 元ページの原著者であるIvan Prenosil氏から、元ページの情報を日本語で提供することのみを 目的として、電子メールによる許諾を受けています。必要な方は本ページまたは、元ページへの リンクを作成してください。それ以上の公的な利用については、原著者であるIvan Prenosil氏の 許諾が必要です。


このドキュメントのうちのいくつかはUDFを使って簡単に実装することができます。しかしながら、状況によってはUDFを使えない場合や、使うことが出来ない場合があります。UDFをさける理由には例えば次のようなものがあります。


ストアドプロシジャ版TrimRight

InterBaseは(文字列に)後続するスペースをトリミングする組み込み関数を持っていません。 そのような関数は、外部ファイルからデータをインポートする際に、例えばCHARからVARCHARに 変換するのに便利です。rtrim関数は例えば標準のUDBライブラリib_udf.dllの一部ですが、 しかしながら時々UDFを使うことをさけたいと思うときには、それは使えません。 あなたが日所に長い文字列(例えば、CHAR(30000))を使う必要がない限りは、 トリミング機能をストアドプロシジャとして実装することは可能です。

文字列をより短いものにキャストするとき、削除されるキャラクタがスペースの場合は、 実行分は成功しますが、非空白文字のキャラクタを削除しようとすると、失敗します。(例外が起きます) そう、文字列を短くキャストしようと試みて、文字列を短くして、エラーをトラッピングすることが、 プロシジャでTrimRightを行うというトリックを行います。それはストアドプロシジャとして呼び出すことも できますし(EXECUTE PROCEDURE TrimRight 'abc')セレクトプロシジャ(SELECT ... FROM TrimRight('abc') )として呼び出すことの、どちらでも可能です。

CREATE PROCEDURE TrimRight (str VARCHAR(10))
  RETURNS (ret VARCHAR(10)) AS
BEGIN
  ret = str;
  IF (str IS NULL) THEN BEGIN SUSPEND; EXIT; END
  IF (str = '')    THEN BEGIN ret = ''; SUSPEND; EXIT; END
  BEGIN
    ret = CAST (str AS char(9));
    ret = CAST (str AS char(8));
    ret = CAST (str AS char(7));
    ret = CAST (str AS char(6));
    ret = CAST (str AS char(5));
    ret = CAST (str AS char(4));
    ret = CAST (str AS char(3));
    ret = CAST (str AS char(2));
    ret = CAST (str AS char(1));
    SUSPEND;
    WHEN ANY DO SUSPEND;
  END
END
例:
  SELECT '>' || ret || '<'
    FROM TrimRight (null)
  ============
  <null>


  SELECT '>' || ret || '<'
    FROM TrimRight (' 1234      ')
  ============
  > 1234<


  EXECUTE PROCEDURE TrimRight '1234      '
  ==========
  1234 
他のストアドプロシジャからの呼び出し例:
  EXECUTE PROCEDURE TrimRight str_in
          RETURNING_VALUES    str_out;
注:

このコードを簡単にするためにWHILE (...) DO loopを使うのは不可能です。なぜなら CAST は文字列長のところに変数を含むことはできないからです。 (すなわち CAST(str AS CHAR(:len))のように記述することが不可能です).

IB 5.1/5.6にはバグがあります(IB 6では修正済みです) もし内部のBEGIN/ENDの括弧を除いて、ELECT ... FROM TrimRight(null); そしてSELECT ... FROM TrimRight('');を行うと、1行の代わりに2つの行を返します。


ストアドプロシジャ版Truncate

トリミングとは違って、Truncate関数は、文字列の内容には関係なく文字列を短くします。 すなわち、非ブランク文字でさせも削除します。InterBaseにはこのような組み込み関数はありません。 (外部のUDFを除いて) 長い文字列を短くキャストする時には、InterBaseは"... string truncation"例外を起こします。しかし、長い文字列を短い変数に直接アサインした場合、InterBaseはこれにも例外を起こしますが、短くされた値はとにかくアサインされてしまいます。我々が行うべきことは、この例外をWHEN ANY DOステートメントでトラップすることだけです。文字列を5文字に短くするプロシジャの例を次に記述します。
CREATE PROCEDURE Trunc10To5 (a varchar(10))
  RETURNS (ret varchar(5)) AS
BEGIN
  ret = '';
  ret = a;
  WHEN ANY DO EXIT;
END
以下のコマンドは'12345'を戻します。
  EXECUTE PROCEDURE Trunc10To5 '1234567890'

注: キャスト(CAST)を使ってはいけません。そしてアサインする変数は<null>を含んではいけません。そのため次の2つのプロシジャはうまく動作しません

  CREATE PROCEDURE test1 (a varchar(10))
    RETURNS (ret varchar(5)) AS
  BEGIN
    ret = null;
    ret = a;
    WHEN ANY DO EXIT;
  END

  CREATE PROCEDURE test2 (a varchar(10))
    RETURNS (ret varchar(5)) AS
  BEGIN
    ret = CAST(a AS VARCHAR(5));
    WHEN ANY DO EXIT;
  END
別の注: 例外が起こったのにもかかわらず値がアサインされるのはおそらくバグではないでしょうか。しかしながら、これがIB4, IB5そしてIB6の動き方です。

ストアドプロシジャ版Len(gth)

Len(gth)関数は入力文字を変更しない(trimmingやtruncatingのようには変更しない)ので、 WHILEループを使い、LIKEによるテストをおこなっている実装は率直なものです。
CREATE PROCEDURE Len (str VARCHAR(100))
  RETURNS (len INTEGER) AS
DECLARE VARIABLE pat VARCHAR(100);
BEGIN
  len = null;
  IF (str IS NULL) THEN EXIT;

  pat = '';
  len = 0;
  WHILE (NOT str LIKE pat) DO BEGIN
    pat = pat || '_';
    len = len + 1;
  END
END
 "len = null;"は省くことができます。なぜなら変数は自動的にnullに初期化されるからです。長さは文字列の後に続くスペースもカウントします。
  EXECUTE PROCEDURE Len null
      LEN 
  ======= 
   <null>

  EXECUTE PROCEDURE Len ''6
      LEN 
  ======= 
        0 

  EXECUTE PROCEDURE Len 'abc'
      LEN 
  ======= 
        3 

  EXECUTE PROCEDURE Len 'xyz   '
      LEN 
  ======= 
        6 

ストアドプロシジャ版Substr位置取得

この関数は与えられた文字列(Str)内で、指定した部分文字列(SubStrパラメタ)の 最初の文字のインデックスを戻します。
CREATE PROCEDURE Pos (SubStr VARCHAR(100), Str VARCHAR(100))
  RETURNS (Pos INTEGER) AS
DECLARE VARIABLE SubStr2 VARCHAR(201); /* 1 + SubStr-lenght + Str-length */
DECLARE VARIABLE Tmp VARCHAR(100);
BEGIN
  IF (SubStr IS NULL OR Str IS NULL)
  THEN BEGIN Pos = NULL; EXIT; END

  SubStr2 = SubStr || '%';
  Tmp = '';
  Pos = 1;
  WHILE (Str NOT LIKE SubStr2 AND Str NOT LIKE Tmp) DO BEGIN
    SubStr2 = '_' || SubStr2;
    Tmp = Tmp || '_';
    Pos = Pos + 1;
  END

  IF (Str LIKE Tmp) THEN Pos = 0;
END
tmp変数は、繰り返し(iteration)の回数がstrの長さと同じになったときに、 ループを終了させるために使われています。SubStrはLIKE演算子の右辺として 使われていますので、SQLのワイルドカード(例えば'_'や'%'は(ESCAPE句を使わない限り) SubStrに含むことはできません。もし部分文字列が見つからない場合は、戻り値は0です。
  EXECUTE PROCEDURE Pos 'ab', 'abcdefghij'
      POS 
  ======= 
        1 

  EXECUTE PROCEDURE Pos 'cd', 'abcdefghij'
      POS 
  ======= 
        3 

  EXECUTE PROCEDURE Pos 'x', 'abcdefghij'
      POS 
  ======= 
        0 

ストアドプロシジャ版Substr

これは親愛なる読者のための宿題として残しておきます。I
Copyright © 2001 Ivan Prenosil
初公開日:2001年08月14日, 元ページ取得日:2001年07月20日, 本ページ最終更新日:2003年09月07日
日本語訳: 木村明治(KIMURA, Meiji)