ユーザー定義関数 (スカラー/表)

SQL でも Java を含む外部プログラム でも処理を実装して、ユーザー定義関数として使用することができます。

返す値によって、スカラー関数(値をひとつ返すもの)と表関数(表 = テーブルを返すもの)があります。
また、SQL 自身で処理の実装を行なうものと、外部プログラムに処理の実装を行なうものがあり、UDB のマニュアルの CREATE FUNCTION のところでは両方の区別を踏まえて「ユーザー定義関数(SQLスカラー)」とか「ユーザー定義関数(外部表)」などと区別されて解説されています。

スカラー関数の例です。(SQL)

CREATE FUNCTION BONUS(salary int, bonus_percent int)
 RETURNS INT
 LANGUAGE SQL CONTAINS SQL
 RETURN
  (
  salary * bonus_percent / 100
  )
UPDATE EMPLOYEE
 SET BONUS_PAY = BONUS(EMPLOYEE.SALARY, 10)

表関数の例です。(SQL)

CREATE FUNCTION DEPTEMPLOYEES (deptno char(3))
 RETURNS TABLE (empno char(6),
             lastname varchar(15),
             firstname varchar(12))
 LANGUAGE SQL
 CONTAINS SQL
 NO EXTERNAL ACTION
 DETERMINISTIC
 RETURN
   select empno, lastname, firstname
    from employee
    where employee.workdept = deptemployees.deptno
SELECT * FROM TABLE(DEPTEMPLOYEES(123))

iSeries の場合は SQL にしろ外部にしろ、V5R1 ではスカラー関数しかサポートされていません。
表関数は V5R2 からのサポートになります。

ちなみに iSeries でのスカラー関数の作成の例です。

上のユーザー定義関数を使用した実行例です。

一応、結果の確認です。


スカラー関数と表関数のそれぞれ、Java を使用して処理を実装するやり方を簡単にまとめてみました。

スカラー関数(Java)

パラメーターの受け渡し方には、LANGUAGE JAVA を指定した場合、パラメーター・スタイルとして Java と DB2GENERAL が選択できます。

PARAMETER STYLE DB2GENERAL の場合、実装クラスは com.ibm.db2.app.UDF クラスを拡張し、set() メソッドを使用して戻り値を明示的にセットします。

PARAMETER STYLE JAVA の場合、戻り値は return value; での戻り値になります。また、FINAL CALL、SCRATCHPAD、DBINFO 文節がサポートされません。

その他、いろいろな違いがありますので詳細はUDB for iSeries のマニュアルを参照してください。

Java でのスカラー関数の例についてはこちらを参照してください。手順等を実際に行なってみて紹介しています。

表関数(Java)

表関数の場合、LANGUAGE JAVA を指定している時はパラメーター・スタイルには DB2GENERAL しか指定できません。(SQL解説書 P.684)

DB2GENERAL の場合、FINAL CALL か NO FINAL CALL か指定があるわけですが、この違いによって例えばコンストラクタの呼び出されるタイミングが異なってきます。詳細はアプリケーション開発の手引き P.439 「Java の表関数実行モデル」を参照してください。

CREATE FUNCTION properties()
 RETURNS TABLE (propertiy varchar(500), value varchar(500))
 EXTERNAL NAME 'JVMProperties.show'
 LANGUAGE JAVA
 PARAMETER STYLE DB2GENERAL
 FENCED
 NO SQL
 DISALLOW PARALLEL
 SCRATCHPAD
SELECT * FROM TABLE(PROPERTIES()) AS PROPS

処理を実装する Java のコードはこちらです。

DB2GENERAL スタイルなので、処理を行なうメソッドは public void のメソッドで、入力パラメータと返り値をすべて引数としてとります。
case SQLUDF_TF_FETCH での set で引数ごとに返り値をセットしています。引数の順で、1から始まる値をひとつめの引数に指定します。( set (1, property))
[もし public void method(String arg1, BLOB arg2, int result) で int が出力用のパラメータだったら set(3, number) といったように指定します]

import com.ibm.db2.app.*;
import java.util.*;

public class JVMProperties extends UDF {
Enumeration propertyNames;
Properties properties;

public void show (String property, String value) throws Exception
{
int callType = getCallType();
switch(callType) {

case SQLUDF_TF_FIRST:
break;

case SQLUDF_TF_OPEN:
properties = System.getProperties();
propertyNames = properties.propertyNames();
break;

case SQLUDF_TF_FETCH:
if (propertyNames.hasMoreElements()) {
property = (String) propertyNames.nextElement();
value = properties.getProperty(property);
set(1, property);
set(2, value);
} else { setSQLstate("02000"); }
break;

case SQLUDF_TF_CLOSE:
break;

case SQLUDF_TF_FINAL:
break;

default:
throw new Exception("予期されない呼び出しタイプ: " + callType);
}
}
}

実行はこんなかんじのイメージです。

こんなふうにテーブルから選択した結果であるかのように内容が表示されます。

といった使い方のイメージです。

[Top Pageに戻る]

Ads by TOK2