SQL 解説書

CREATE FUNCTION (SQL スカラー、表、または行)

このステートメントは、 ユーザー定義の SQL スカラー、表、または行関数を定義するのに使用されます。 スカラー関数 は、呼び出されるたびに 1 つの値を戻し、 SQL 式が有効な個所であればどこでも有効です。 表関数 は、FROM 文節で使用でき、表を戻します。 行関数 は、変形関数として使用でき、行を戻します。

呼び出し

このステートメントは、アプリケーション・プログラムに組み込むか、 または動的 SQL ステートメントを使用して発行することができます。 このステートメントは、動的に準備可能な実行可能ステートメントです。 しかし、バインド・オプション DYNAMICRULES BIND を適用する場合、 ステートメントを動的に準備することはできません (SQLSTATE 42509)。

許可

このステートメントの許可 ID には、以下の特権が少なくとも 1 つ含まれている必要があります。

このステートメントの許可 ID に SYSADM 権限または DBADM 権限がなく、 関数が表または視点を指定する場合、 ステートメントの権限 ID が持つ特権 (GROUP 特権は考慮に入れない) には、 表および視点それぞれに SELECT WITH GRANT OPTION が組み込まれていなければなりません。

関数の定義者が SYSADM 権限を持つために、関数の作成しかできない場合、 関数作成のため、その定義者には暗黙的に DBADM 権限が付与されます。

許可 ID の権限が不十分で、操作を実行できない場合には、エラー (SQLSTATE 42502) になります。

構文

>>-CREATE FUNCTION--function-name------------------------------->
 
>----(--+------------------------------------+---)---*---------->
        |  .-,----------------------------.  |
        |  V                              |  |
        '----parameter-name--data-type1---+--'
 
>----RETURNS--+-data-type2--------------------+--*-------------->
              '--+-ROW---+---| column-list |--'
                 '-TABLE-'
 
                                         .-LANGUAGE SQL--.
>-----+--------------------------+--*----+---------------+--*--->
      '-SPECIFIC--specific-name--'
 
      .-NOT DETERMINISTIC--.       .-EXTERNAL ACTION----.
>-----+--------------------+--*----+--------------------+--*---->
      '-DETERMINISTIC------'       '-NO EXTERNAL ACTION-'
 
      .-READS SQL DATA--.       .-STATIC DISPATCH--.
>-----+-----------------+--*----+------------------+--*--------->
      '-CONTAINS SQL----'
 
                              (1)
      .-CALLED ON NULL INPUT-------.
>-----+----------------------------+--*------------------------->
 
>-----+-----------------------------------------------------+--->
      |                                               (2)   |
      '-PREDICATES--(--| predicate-specification |--)-------'
 
>----RETURN--+-expression----------------------------------------------+>
             +-NULL----------------------------------------------------+
             '-+---------------------------------------+---fullselect--'
               |       .-,--------------------------.  |
               |       V                            |  |
               '-WITH-----common-table-expression---+--'
 
>--------------------------------------------------------------><
 
column-list
 
       .-,--------------------------.
       V                            |
|---(-----column-name--data-type3---+---)-----------------------|
 

注:

  1. CALLED ON NULL INPUT の代わりに NULL CALL を指定できます。

  2. RETURNS がスカラー結果 (data-type2) を指定する場合のみ有効です。

説明

function-name
定義する関数の名前を指定します。 これは、関数を指定する修飾または非修飾の名前です。 function-name (関数名) の非修飾形式は SQL 識別子です (最大長 18)。 動的 SQL ステートメントでは、CURRENT SCHEMA 特殊レジスターは、 修飾子のないオブジェクト名の修飾子として使用されます。 静的 SQL ステートメントでは、 QUALIFIER プリコンパイル / バインド・オプションにより、 修飾子のないオブジェクト名の修飾子が暗黙指定されます。 修飾形式は、schema-name の後にピリオドと SQL 識別子が続きます。

暗黙または明示の修飾子を含む名前、 およびパラメーターの数と各パラメーターのデータ・タイプ (データ・タイプの長さ、 精度、または位取りの各属性には関係なく) は、 カタログに記述されている関数を指定するものであってはなりません (SQLSTATE 42723)。 非修飾名とパラメーターの数およびデータ・タイプとの組み合わせは、 そのスキーマ内では当然固有ですが、複数のスキーマ間で固有である必要はありません。

2 つの部分からなる名前を指定する場合、 "SYS" で始まる schema-name (スキーマ名) は使用できません (SQLSTATE 42939)。

述部のキーワードとして使用される多くの名前は、システム使用として予約されており、 function-name として使用することはできません (SQLSTATE 42939)。 それに含まれる名前は、SOME、ANY、ALL、NOT、AND、OR、BETWEEN、 NULL、LIKE、EXISTS、IN、UNIQUE、OVERLAPS、SIMILAR、MATCH、 および 基本述部に記載されている比較演算子です。

関数のシグニチャーに何らかの差異があれば、同じ名前を複数の関数に使用することができます。 禁止されてはいませんが、外部ユーザー定義関数の名前として、 組み込み関数と同じ名前を指定すべきではありません。

parameter-name
この関数の他のすべてのパラメーターの名前と異なる名前。

data-type1
パラメーターのデータ・タイプを指定します。

RETURNS
これは必須の文節であり、関数の出力のタイプを指定します。

data-type2
出力のデータ・タイプを指定します。

このステートメントでは、 上記の関数パラメーター data-type1 で説明した SQL 関数のパラメーターと同じ考慮事項が適用されます。

ROW column-list
関数の出力が単一の行であることを指定します。 関数が複数の行を出力する場合、エラーになります (SQLSTATE 21505)。 column-list には、 少なくとも 2 つの列を組み込まなければなりません (SQLSTATE 428F0)。

行関数は、構造タイプの変換関数としてのみ使用できます (1 つの構造タイプをパラメーターとして使用し、基本タイプのみ戻します)。

TABLE column-list
関数の出力が表であることを指定します。

column-list
ROW または TABLE 関数で戻される列名およびデータ・タイプのリスト。

column-name
この列の名前を指定します。 名前を修飾することはできず、 行の複数の列に対して同じ名前を使用することはできません。

data-type3
列のデータ・タイプを指定します。 SQL 関数のパラメーターによりサポートされていれば、どのデータ・タイプでも構いません。

SPECIFIC specific-name
定義する関数のインスタンスに対する固有名を指定します。 この特定名は、この関数をソース関数として使用する場合、 この関数を除去する場合、またはこの関数にコメントを付ける場合に使用することができます。 これは、関数の呼び出しには使用できません。 specific-name (特定名) の非修飾形式は SQL 識別子です (最大長 18)。 修飾形式は、schema-name の後にピリオドと SQL 識別子が続きます。 暗黙または明示の修飾子も含め、その名前が、 アプリケーション・サーバーに存在する別の関数インスタンスを識別するものであってはなりません。 さもないと、エラーになります (SQLSTATE 42710)。

specific-name は、既存の関数名 と同じであっても構いません。

修飾子を指定しない場合、function-name に使用された修飾子が使用されます。 修飾子を指定する場合は、 function-name の明示修飾子または暗黙修飾子と同じでなければなりません。 そうでない場合、エラーになります (SQLSTATE 42882)。

specific-name の指定がない場合、 固有の名前がデータベース・マネージャーによって生成されます。 生成される固有の名前は、 SQL の後に文字のタイム・スタンプが続く名前です (SQLyymmddhhmmssxxx)。

LANGUAGE SQL
関数が SQL を使用して書かれていることを指定します。 サポートされている SQL は、現在のところ RETURN ステートメントに限られています。

DETERMINISTIC または NOT DETERMINISTIC
この文節は任意指定で、 特定の引き数の値に対して関数が常に同じ結果を戻すか (DETERMINISTIC)、 それとも状態値に依存して関数の結果が影響を受けるか (NOT DETERMINISTIC) を指定します。 つまり DETERMINISTIC 関数は、 同じ入力を指定して連続して呼び出した場合に常に同じ表を戻します。 NOT DETERMINISTIC を指定すると、 同じ入力によって常に同じ結果が生じる利点に基づく最適化ができなくなります。

関数の本体が特殊レジスターにアクセスしたり、他の非 deterministic 関数を呼び出す場合には、 NOT DETERMINISTIC を明示的または暗黙的に指定しなければなりません (SQLSTATE 428C2)。

NO EXTERNAL ACTION または EXTERNAL ACTION
この文節は任意選択であり、関数が、 データベース・マネージャーによって管理されていないオブジェクトの状態を変更する処置を行うか否かを指定します。 NO EXTERNAL ACTION を指定すると、 システムは関数に外部の影響がないことを前提とした最適化を使用できます。

関数の本体が外部アクションのある別の関数を呼び出す場合、 EXTERNAL ACTION を明示的または暗黙的に指定しなければなりません (SQLSTATE 428C2)。

READS SQL DATA または CONTAINS SQL
どのタイプの SQL ステートメントを実行できるかを指示します。 サポートされている SQL ステートメントは RETURN ステートメントであるので、 式が副照会であるかどうかで区別を行います。

READS SQL DATA
SQL データを変更しない SQL ステートメントを、 関数により実行できるように指示します (SQLSTATE 42985)。 ニックネームまたは OLEDB 表関数を SQL ステートメントで参照することはできません (SQLSTATE 42997)。

CONTAINS SQL
SQL データを読み取ることも変更することもしない SQL ステートメントを、 関数により実行できるように指示します (SQLSTATE 42985)。

STATIC DISPATCH
この任意選択の文節は、関数解決時に、 DB2 が関数のパラメーターの静的タイプ (宣言されたタイプ) に基づいて関数を選択することを指定します。

CALLED ON NULL INPUT
なんらかの引き数がヌル値であるかどうかにかかわりなく、 関数が呼び出されることを指定します。 これは、ヌル値を戻す場合も、ヌル値以外の値を戻す場合もあります。 ヌルの引き数値の有無のテストはユーザー定義関数が行う必要があります。

CALLED ON NULL INPUT の代わりに NULL CALL という句を使用できます。

PREDICATES
この関数を使用する述部の場合、この文節は索引拡張を使用できることを示し、 任意選択の SELECTIVITY 文節を使用して述部の検索条件を指定できます。 PREDICATES 節が指定された場合、 関数は NO EXTERNAL ACTION を指定した DETERMINISTIC として定義しなければなりません (SQLSTATE 42613)。

predicate-specification
述部指定の詳細については、CREATE FUNCTION (外部スカラー) を参照してください。

RETURN
関数の戻り値を指定します。 パラメーター名は、RETURN ステートメントで参照できます。 参照をあいまいにしないために、パラメーター名を関数名で修飾することができます。

expression
関数に戻される式を指定します。 式の結果データ・タイプは、 RETURNS 文節で定義されるデータ・タイプに割り当て可能 (保管割り当て規則を使用) でなければなりません (SQLSTATE 42866)。 スカラー式 (スカラー全選択以外) は、表関数に指定することはできません (SQLSTATE 428F1)。

NULL
関数が RETURNS 文節で定義されるデータ・タイプのヌル値を戻すように指定します。

WITH common-table-expression
後に続く全選択で使用する共通表式 (common-table-expression) を定義します。 共通表式 を参照してください。

fullselect
関数に戻される行 (単数または複数) を指定します。 全選択での列の数は、関数結果での列の数と一致しなければならず (SQLSTATE 42811)、 全選択の静的列タイプは、列への割り当ての規則を使用して、 関数結果の宣言された列タイプに割り当て可能でなければなりません (SQLSTATE 42866)。

この関数がスカラー関数である場合、全選択は 1 つの列 (SQLSTATE 42823) と、 最大で 1 つの行を戻さなければなりません (SQLSTATE 21000)。

この関数が行関数の場合、多くて 1 つの行を戻さなければなりません (SQLSTATE 21505)。

この関数が表関数の場合、1 つまたは複数の列で 0 以上の行を戻せます。

例 1: 既存のサインおよびコサイン関数を使用して、 値のタンジェントを戻すスカラー関数を定義します。

     CREATE FUNCTION TAN (X DOUBLE)
        RETURNS DOUBLE
        LANGUAGE SQL
        CONTAINS SQL
        NO EXTERNAL ACTION
        DETERMINISTIC
        RETURN SIN(X)/COS(X)			    

例 2: 構造タイプ PERSON の変形関数を定義します。

     CREATE FUNCTION FROMPERSON (P PERSON)
        RETURNS ROW (NAME VARCHAR(10), FIRSTNAME VARCHAR(10))
        LANGUAGE SQL
        CONTAINS SQL
        NO EXTERNAL ACTION
        DETERMINISTIC
        RETURN VALUES (P..NAME, P..FIRSTNAME)			    

例 3: 指定された部門番号の従業員を戻す表関数を定義します。

     CREATE FUNCTION DEPTEMPLOYEES (DEPTNO CHAR(3))
        RETURNS TABLE (EMPNO CHAR(6),
                       LASTNAME VARCHAR(15),
                       FIRSTNAME VARCHAR(12))
        LANGUAGE SQL
        READS SQL DATA
        NO EXTERNAL ACTION
        DETERMINISTIC
        RETURN
           SELECT EMPNO, LASTNAME, FIRSTNME
             FROM EMPLOYEE
             WHERE EMPLOYEE.WORKDEPT = DEPTEMPLOYEES.DEPTNO			    

この関数の定義者は EMPLOYEE 表で SELECT WITH GRANT OPTION 特権を持っていなければならないことと、 すべてのユーザーが表関数 DEPTEMPLOYEES を呼び出して、 各部門番号の結果列でデータへのアクセスを効果的に行えることに注意してください。


[ ページのトップ | 前ページ | 次ページ | 目次 | 索引 ]