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 を使用して処理を実装するやり方を簡単にまとめてみました。
パラメーターの受け渡し方には、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 でのスカラー関数の例についてはこちらを参照してください。手順等を実際に行なってみて紹介しています。
表関数の場合、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);
}
}
}
実行はこんなかんじのイメージです。
こんなふうにテーブルから選択した結果であるかのように内容が表示されます。
といった使い方のイメージです。
|
|
