SQL 解説書

CREATE FUNCTION (OLE DB 外部表)

このステートメントは、OLE DB Provider からデータをアクセスするための、 ユーザー定義の OLE DB 外部表関数をアプリケーション・サーバーに登録する場合に使用します。

表関数 は、SELECT の FROM 文節で使用できます。

呼び出し

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

許可

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

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

構文

>>-CREATE FUNCTION--function-name------------------------------->
 
>----(--+-----------------------------------+---)---*----------->
        '-+-----------------+--data-type1---'
          '-parameter-name--'
 
                       .-,--------------------------.
                       V                            |
>----RETURNS TABLE--(-----column-name--data-type2---+---)---*--->
 
>-----+--------------------------+--*--------------------------->
      '-SPECIFIC--specific-name--'
 
>----EXTERNAL--NAME--'string'--*---LANGUAGE----OLEDB-----*------>
 
      .-NOT DETERMINISTIC--.
>-----+--------------------+--*--------------------------------->
      |               (1)  |
      '-DETERMINISTIC------'
 
      .-RETURNS NULL ON NULL INPUT--.  (2)
>-----+-----------------------------+---------*----------------->
      '-CALLED ON NULL INPUT--------'
 
      .-NO EXTERNAL ACTION--.
>-----+---------------------+--*----+-----------------------+--->
      '-EXTERNAL ACTION-----'       '-CARDINALITY--integer--'
 
>----*---------------------------------------------------------><
 

注:

  1. DETERMINISTIC の代わりに NOT VARIANT を、 また NOT DETERMINISTIC の代わりに VARIANT を指定することができます。

  2. CALLED ON NULL INPUT の代わりに NULL CALL を、 また RETURNS NULL ON NULL INPUT の代わりに NOT NULL CALL を指定できます。

説明

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
関数の入力パラメーターを指定するとともに、 そのパラメーターのデータ・タイプを指定します。 入力パラメーターを指定しないと、データは、 外部ソースから取り出されます (多くの場合、照会最適化によってサブセット化されます)。 入力パラメーターは、任意の文字または漢字ストリング・データ・タイプで、 コマンド・テキストを OLE DB Provider に渡します。

パラメーターのない関数も登録可能です。 この場合、指定するデータ・タイプがない場合でも、括弧はコーディングする必要があります。 たとえば、

  CREATE FUNCTION WOOFER() ...

その対応するすべてのパラメーターのタイプがまったく同じである場合でも、 1 つのスキーマ中に名前が同じ 2 つの関数があってはなりません。 このタイプの比較では、長さは考慮されません。 したがって、CHAR(8) と CHAR(35) は、それぞれ同じタイプとみなされます。 シグニチャーが重複していると、SQL エラー (SQLSTATE 42723) になります。

RETURNS TABLE
関数の出力が表であることを指定します。 このキーワードに続く括弧は、表の列の名前とタイプのリストを区切るもので、 他の指定 (たとえば、制約) のない単純な CREATE TABLE ステートメントの形式と類似しています。

column-name
列の名前を指定します。これは、対応する rowset の列名と同じでなければなりません。 名前を修飾することはできず、 表の複数の列に対して同じ名前を使用することはできません。

data-type2
列のデータ・タイプを指定します (SQL データ・タイプと OLE DB データ・タイプとの間のマッピングについては、 アプリケーション開発の手引き のそれぞれの言語の節を参照してください)。

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

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

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

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

EXTERNAL NAME 'string'
この文節は、外部表と OLE DB Provider を指定します。

'string' オプションは、最大 254 文字のストリング定数です。

指定されるストリングは、OLE DB Provider との接続およびセッションを確立し、 rowset からデータを取り出すときに使われます。 OLE DB Provider とデータ・ソースは、 CREATE FUNCTION ステートメントの実行時には存在している必要はありません。 詳細は、アプリケーション開発の手引き の『OLE DB 表関数』を参照してください。

>>-'--+-server--!--+--------+------------------------------------------------+---'-->
      |            '-rowset-'                                                |
      '-!--+--------+---!--connectstring--+--------------------------------+-'
           '-rowset-'                     '-!--COLLATING_SEQUENCE =--+-N-+-'
                                                                     '-Y-'
 
>--------------------------------------------------------------><
 

server
CREATE SERVERで定義されているように、データ・ソースのローカル名を指定します。

rowset
OLE DB Provider によって公開された rowset (表) を指定します。 カタログまたはスキーマ名をサポートする OLE DB Provider の、 完全修飾表名を指定する必要があります。

connectstring
データ・ソースへ接続するときに必要な、初期化プロパティーのストリング・バージョン。 接続ストリングの基本形式は、ODBC 接続ストリングに基づいています。 このストリングには、セミコロンで区切られた、一連のキーワード / 値の対が含まれています。 等号 (=) により、各キーワードとその値を区切ります。 キーワードは、OLE DB 初期化プロパティー (プロパティー・セット DBPROPSET_DBINIT) の記述か、 プロバイダー固有のキーワードです。 詳細は、アプリケーション開発の手引き のそれぞれの言語の節を参照してください。

COLLATING_SEQUENCE
データ・ソースが、DB2 ユニバーサル・データベースと同じ照合順序を使うかどうかを指定します。 詳細については、CREATE SERVER を参照してください。 有効な値は、以下のとおりです。

Y = 同じ照合順序

N = 異なる照合順序

COLLATING_SEQUENCE を指定しない場合、 データ・ソースと DB2 ユニバーサル・データベースの照合順序は異なるものと見なされます。

server を指定する場合、 外部名として connectstring または COLLATING_SEQUENCE を使うことはできません。 それらは、サーバー・オプション CONNECTSTRING および COLLATING_SEQUENCE として定義されています。 server を指定しないのであれば、connectstring を指定する必要があります。 rowset を指定しないのであれば、表関数には、 コマンド・テキストを OLE DB Provider に渡すための入力パラメーターが必要です。

LANGUAGE OLEDB
これを指定すると、データベース・マネージャーは、 組み込まれた汎用 OLE DB の消費者情報を展開し、OLE DB Provider からデータを取り出します。 開発者側で表関数を実装する必要はありません。

LANGUAGE OLEDB 表関数は、任意のプラットフォームで作成できますが、 Microsoft OLE DB によってサポートされているプラットフォーム上でのみ実行できます。

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

RETURNS NULL ON NULL INPUT または CALLED ON NULL INPUT
このオプション文節を使用すると、引き数のいずれかがヌル値の場合に、 外部関数を呼び出さないようにすることができます。 パラメーターがないものとしてユーザー定義関数を定義すると、 このヌル引き数条件が引き起こされることはありません。

RETURNS NULL ON NULL INPUT が指定されており、 実行時に関数の引き数のいずれかがヌル値の場合、 ユーザー定義関数は呼び出されず、結果は空表、すなわち行のない表になります。

CALLED ON NULL INPUT が指定されると、 引き数がヌル値か否かに関係なく実行時にユーザー定義関数が呼び出されます。 関数の論理によって、空の表を戻すことも戻さないこともあります。 ただし、ヌルの引き数値の有無のテストは UDF が行う必要があります。

値 NULL CALL は、上位互換またはファミリーの互換性のために、 CALLED ON NULL INPUT の同義語として使うことができます。 同様に、NOT NULL CALL は、RETURNS NULL ON NULL INPUT の同義語として使えます。

NO EXTERNAL ACTION または EXTERNAL ACTION
この文節は任意指定であり、関数が、 データベース・マネージャーによって管理されていないオブジェクトの状態を変更する処置を行うか否かを指定します。 EXTERNAL ACTION を指定すると、関数に外部の影響がないことを前提とした最適化ができなくなります。 (たとえば、メッセージの送信、警報音による通知、ファイルへのレコードの書き込みなど。)

CARDINALITY integer
この文節はオプションで、 関数によって戻されると予想される行の数の見積もりを最適化のために指定します。 integer の値の有効範囲は、 0 〜 2 147 483 647 (両端の値を含む) です。

表関数に対して CARDINALITY 文節の指定がない場合、 DB2 はデフォルト値として有限の値を想定します (RUNSTATS ユーティリティーが 統計を収集していない表に対して想定される値と同じ)。

警告: 関数が事実上無限のカーディナリティーを持っている場合 (すなわち、 呼び出されるといつでも行を戻し、"end-of-table" 条件を戻さない)、 正しく機能するために "end-of-table" 条件を必要とする照会は無限に実行されるので、 照会を中断させる必要があります。 このような照会の例として、GROUP BY および ORDER BY を使用する照会があります。 このような UDF は書かないことをお勧めします。

例 1: 次の例では、OLE DB 表関数を登録し、 Microsoft Access データベースから受注情報を取り出します。 外部名として接続ストリングが定義されています。

  CREATE FUNCTION orders ()
    RETURNS TABLE (orderid INTEGER,
                   customerid CHAR(5),
                   employeeid INTEGER, 
                   orderdate TIMESTAMP,
                   requireddate TIMESTAMP,
                   shippeddate TIMESTAMP,
                   shipvia INTEGER, 
                   freight dec(19,4))
    LANGUAGE OLEDB
    EXTERNAL NAME '!orders!Provider=Microsoft.Jet.OLEDB.3.51;
                            Data Source=c:\sqllib\samples\oledb\nwind.mdb
    !COLLATING_SEQUENCE=Y';			    

例 2: 次の例では、OLE DB 表関数を登録し、 Oracle データベースから顧客情報を取り出します。 接続ストリングは、サーバー定義で指定されています。 外部名では、表名は完全に修飾されたものです。 ローカル・ユーザーである john が、リモート・ユーザーの dave にマップされます。 他のユーザーは、接続ストリングでゲスト・ユーザー ID を使用します。 ステートメントの詳細については、CREATE SERVERCREATE WRAPPER、 および CREATE USER MAPPINGを参照してください。

  CREATE SERVER spirit
    WRAPPER OLEDB
    OPTIONS (CONNECTSTRING 'Provider=MSDAORA;Persist Security Info=False;
                            User ID=guest;password=pwd;Locale Identifier=1033;
                            OLE DB Services=CLIENTCURSOR;Data Source=spirit');
 
  CREATE USER MAPPING FOR john
    SERVER spirit
    OPTIONS (REMOTE_AUTHID 'dave', REMOTE_PASSWORD 'mypwd');
 
  CREATE FUNCTION customers ()
    RETURNS TABLE (customer_id INTEGER,                   
                   name  VARCHAR(20),
                   address VARCHAR(20),
                   city VARCHAR(20),
                   state VARCHAR(5),
                   zip_code INTEGER)
    LANGUAGE OLEDB
    EXTERNAL NAME 'spirit!demo.customer';

例 3: 次の例では、OLE DB 表関数を登録し、 MS SQL Server 7.0 データベースから店舗についての情報を取り出します。 外部名として接続ストリングが指定されています。 表関数には、コマンド・テキストを OLE DB Provider に渡すための入力パラメーターがあります。 外部名として rowset 名を指定する必要はありません。 照会例では、SQL コマンド・テキストを渡し、上位 3 店舗についての情報を取り出します。

  CREATE FUNCTION favorites (varchar(600))
    RETURNS TABLE (store_id CHAR (4),
                   name  VARCHAR (41),
                   sales INTEGER)
    SPECIFIC favorites
    LANGUAGE OLEDB
    EXTERNAL NAME '!!Provider=SQLOLEDB.1;Persist Security Info=False;
                   User ID=sa;Initial Catalog=pubs;Data Source=WALTZ;
                   Locale Identifier=1033;Use Procedure for Prepare=1;
                   Auto Translate=False;Packet Size=4096;Workstation ID=WALTZ;
                   OLE DB Services=CLIENTCURSOR;';
 
    SELECT *
    FROM TABLE (favorites 
               (' select top 3 sales.stor_id as store_id, ' || ' 
                    stores.stor_name as name, ' || ' 
                    sum(sales. qty) as sales  ' || ' 
                 from sales, stores ' || '
                 where sales.stor_id = stores.stor_id ' || '
                 group by sales.stor_id, stores.stor_name' || '
                 order by sum(sales.qty) desc')) as f;


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