CREATE FUNCTION
CREATE FUNCTION ステートメントにより、データベースにスカラー ユーザー定義関数（UDF）を作成します。ユーザー定義関数はクエリから呼び出すことができます。
構文
CREATE FUNCTION 関数名 ([[IN]
{:パラメーター名 スカラー パラメーターのデータ型 [DEFAULT 値 | = 値]} [...]])
RETURNS スカラー戻り値のデータ型
[AS]
BEGIN
関数本体
RETURN スカラー式
END;
関数名 ::= スカラー ユーザー定義関数（UDF）の名前。UDF 名は識別子の規則に従い、データベース内で一意である必要があります。
パラメーター名 ::= スカラー UDF のパラメーター。最大 300 個のパラメーターを使用できます。関数を呼び出すときに値を指定する必要があります。
スカラー パラメーターのデータ型 ::= 指定したパラメーター名のデータ型。
スカラー戻り値のデータ型 ::= スカラー UDF の戻り値。スカラー型のみサポートされます。
値 ::= スカラー戻り値のデータ型に割り当てる値。DEFAULT キーワードまたは等記号のいずれかを使って指定します。
関数本体 ::= スカラー関数を構成するステートメント。
スカラー式 ::= スカラー関数が返すスカラー値を指定します。
備考
セキュリティが設定されているデータベースで関数を作成する場合は、CREATE FUNCTION 権限が必要です。
完全修飾された UDF 名（データベース名.関数名）は、それぞれデータベース内で一意でなければなりません。UDF 名は、同じデータベース内の以下の名前と同じにできません。
•組み込み関数名
•その他すべての UDF 名
•ストアド プロシージャ名
制約
ユーザー定義関数内で CREATE DATABASE または DROP DATABASE ステートメントを使用することはできません。CREATE、ALTER、UPDATE、DELETE および INSERT のテーブル操作はユーザー定義関数内では許可されません。
スカラー入力パラメーターのみサポートされます。OUTPUT および INOUT パラメーターは使用できません。デフォルトで、すべてのパラメーターが入力になります。IN キーワードを指定する必要はありません。
制限
ユーザー定義関数を作成する際は以下の制限に注意してください。
属性 | 制限 |
|---|
パラメーター数 | 300 |
UDF 本体のサイズ | 64 KB |
UDF 名の最大長 | 『 Advanced Operations Guide』の表 1 識別子の種類別の制限を参照してください。 |
UDF 変数名の最大長 | 半角 128 文字 |
サポートされるスカラー入力パラメーターおよび戻り値のデータ型
Zen でサポートされている、スカラー入力パラメーターと戻り値のデータ型を以下の表に示します。
表 20 サポートされる入力パラメーターおよび戻り値のデータ型
AUTOTIMESTAMP | BIGIDENTITY | BIGINT |
BINARY | BIT | BLOB |
CHAR | CHARACTER | CLOB |
CURRENCY | DATE | DATETIME |
DEC | DECIMAL | DOUBLE |
FLOAT | IDENTITY | INT |
INTEGER | LONG | LONGVARBINARY |
LONGVARCHAR | NCHAR | NLONGVARCHAR |
NUMERIC | NVARCHAR | REAL |
SMALLIDENTITY | SMALLINT | TIME |
TIMESTAMP | TIMESTAMP2 | TINYINT |
UBIGINT | UINT | UINTEGER |
UNIQUEIDENTIFIER | USMALLINT | UTINYINT |
VARBINARY | VARCHAR | |
例
このトピックでは、CREATE FUNCTION のいくつかの例を示します。
次の例では、Box テーブルに保存されている四角形のボックスの細目を基に、その四角形の面積を計算する関数を作成します。
CREATE FUNCTION CalculateBoxArea(:boxName char(20))
RETURNS REAL
AS
BEGIN
DECLARE :len REAL;
DECLARE :breadth REAL;
SELECT len, breadth INTO :len, :breadth FROM box
WHERE name = :boxName;
RETURN(:len * :breadth);
END;
============
次の例では、2 つの整数を比較し、小さい方の値を返す関数を作成します。
CREATE FUNCTION GetSmallest(:A integer, :B Integer)
RETURNS Integer
AS
BEGIN
DECLARE :smallest INTEGER
IF (:A < :B ) THEN
SET :smallest = :A;
ELSE
SET :smallest = :B;
END IF;
RETURN :smallest;
END;
============
次の例では、SI = PTR/100 という式を使って単利を計算する関数を作成します。式中の P は元金、T は年数、R は利子率です。
CREATE FUNCTION CalculateInterest(IN :principle float, IN :period real, IN :rate double)
RETURNS DOUBLE
AS
BEGIN
DECLARE :interest DOUBLE;
SET :interest = ((:principle * :period * :rate) / 100);
RETURN (:interest);
END;
スカラー ユーザー定義関数の呼び出し
スカラー式がサポートされる場所ならどこでも、関数名の後にカンマ区切りの引数のリストを指定することによって、ユーザー関数を呼び出すことができます。引数のリストはかっこで囲みます。
UDF は、データベース修飾子のプレフィックスを付けても付けなくても呼び出せます。データベース修飾子を前に付けない場合は、UDF は現在のデータベース コンテキストから実行されます。データベース修飾子を前に付けた場合は、指定したデータベースのコンテキストから実行されます。（下記の例では、データベース修飾子のプレフィックスを使用しているものと使用していないものがあります。）
制限
関数を呼び出すとき、引数にパラメーター名は指定できません。
すべてのパラメーターの引数値（「実引数」とも呼ばれます）は、CREATE FUNCTION ステートメントで定義されたパラメーターの引数値（「仮引数」とも呼ばれます）と同じ順序でなければなりません。
ユーザー定義関数の例
プロシージャ内での UDF
CREATE PROCEDURE procTestUdfInvoke() AS
BEGIN
DECLARE :a integer;
SET :a = 99 + (222 + Demodata.GetSmallest(10, 9)) + 10;
PRINT :a;
END;
CALL procTestUdfInvoke()
============
次の例は、データベース修飾子が省略されている点を除けば、前の例と似ています。
CREATE PROCEDURE procTestUdfInvoke2() AS
BEGIN
DECLARE :a INTEGER;
SET :a = 99 + (222 + GetSmallest(10, 9)) +10;
PRINT :a;
END;
CALL procTestUdfInvoke2
============
Select リスト内での UDF
SELECT GetSmallest(100,99)
============
Where 句での UDF
SELECT name FROM class WHERE id <= GetSmallest(10,20)
============
UDF 内での UDF
CREATE FUNCTION funcTestUdfInvoke() RETURNS INTEGER as
BEGIN
DECLARE :a INTEGER;
SET :a = 99 + (222 - Demodata.GetSmallest(10, 9));
RETURN :a;
END;
============
INSERT ステートメント内での UDF
CREATE TABLE t1(col1 integer, col2 integer, col3 float)
INSERT INTO t1 VALUES (GetSmallest(10,20), 20 , 2.0)
INSERT INTO t1 (SELECT * FROM t1 WHERE col1 = getSmallest(10,20))
============
UPDATE ステートメント内での UDF
UPDATE t1 SET col2 = Demodata.GetSmallest(2,10) WHERE col1 = 2
UPDATE t1 set col1 = 3 WHERE col2 = Demodata.GetSmallest(10, 5)
============
GROUP BY ステートメント内での UDF
SELECT col2 FROM t1 GROUP BY getSmallest(10,2), col2
============
ORDER BY ステートメント内での UDF
SELECT col2 FROM t1 ORDER BY Demodata.getSmallest(10,2), col2
============
再帰 UDF
CREATE FUNCTION factorial(in :n integer) RETURNS double AS BEGIN
DECLARE :fact double;
IF (:n <= 0) THEN
SET :fact = 1;
ELSE
SET :fact = (:n * Demodata.factorial(:n - 1));
END IF;
RETURN :fact;
END;
select Demodata.factorial(20) を使用すると、20 の階乗値を取得できます。
============
デフォルト値を持つ UDF
CREATE FUNCTION testUdfDefault1(:z INTEGER DEFAULT 10) RETURNS INTEGER AS
BEGIN
RETURN :z-1;
END;
select Demodata.testUdfDefault1()。この関数は、パラメーターが提供されなかった場合には、指定したデフォルト値（10）を使用します。
CREATE FUNCTION testUdfDefault2(:a VARCHAR(20) = 'Accounting Report' ) RETURNS VARCHAR(20) as
BEGIN
RETURN :a;
END;
select Demodata.testUdfDefault2()。この関数は、パラメーターが提供されなかった場合には、指定したデフォルト値（Accounting Report）を使用します。
============
動的パラメーターを持つ UDF
SELECT name FROM class WHERE id <= GetSmallest(?,?)
============
式としての UDF
SELECT 10 + Demodata.Getsmallest(10,20) + 15
============
パラメーターとしての UDF
SELECT demodata.calculateinterest (10+demodata.getsmallest(3000, 2000), demodata.factorial(2), demodata.testUdfDefault(3))
関連項目