このステートメントは、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--' >----*---------------------------------------------------------><
注:
説明
暗黙または明示の修飾子を含む名前、 およびパラメーターの数と各パラメーターのデータ・タイプ (データ・タイプの長さ、 精度、または位取りの各属性には関係なく) は、 カタログに記述されている関数を指定するものであってはなりません (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、 および 基本述部 に記載されている比較演算子です。
関数のシグニチャーに何らかの差異があれば、同じ名前を複数の関数に使用することができます。 禁止されてはいませんが、外部ユーザー定義関数の名前として、 組み込み関数と同じ名前を指定すべきではありません。
パラメーターのない関数も登録可能です。 この場合、指定するデータ・タイプがない場合でも、括弧はコーディングする必要があります。 たとえば、
CREATE FUNCTION WOOFER() ...
その対応するすべてのパラメーターのタイプがまったく同じである場合でも、 1 つのスキーマ中に名前が同じ 2 つの関数があってはなりません。 このタイプの比較では、長さは考慮されません。 したがって、CHAR(8) と CHAR(35) は、それぞれ同じタイプとみなされます。 シグニチャーが重複していると、SQL エラー (SQLSTATE 42723) になります。
specific-name は、既存の関数名 と同じであっても構いません。
修飾子を指定しない場合、function-name に使用された修飾子が使用されます。 修飾子を指定する場合は、 function-name の明示修飾子または暗黙修飾子と同じでなければなりません。 そうでない場合、エラー (SQLSTATE 42882) になります。
specific-name の指定がない場合、 固有の名前がデータベース・マネージャーによって生成されます。 生成される固有の名前は、 SQL の後に文字のタイム・スタンプが続く名前です (SQLyymmddhhmmssxxx)。
'string' オプションは、最大 254 文字のストリング定数です。
指定されるストリングは、OLE DB Provider との接続およびセッションを確立し、 rowset からデータを取り出すときに使われます。 OLE DB Provider とデータ・ソースは、 CREATE FUNCTION ステートメントの実行時には存在している必要はありません。 詳細は、アプリケーション開発の手引き の『OLE DB 表関数』を参照してください。
>>-'--+-server--!--+--------+------------------------------------------------+---'--> | '-rowset-' | '-!--+--------+---!--connectstring--+--------------------------------+-' '-rowset-' '-!--COLLATING_SEQUENCE =--+-N-+-' '-Y-' >--------------------------------------------------------------><
Y = 同じ照合順序
N = 異なる照合順序
COLLATING_SEQUENCE を指定しない場合、 データ・ソースと DB2 ユニバーサル・データベースの照合順序は異なるものと見なされます。
server を指定する場合、 外部名として connectstring または COLLATING_SEQUENCE を使うことはできません。 それらは、サーバー・オプション CONNECTSTRING および COLLATING_SEQUENCE として定義されています。 server を指定しないのであれば、connectstring を指定する必要があります。 rowset を指定しないのであれば、表関数には、 コマンド・テキストを OLE DB Provider に渡すための入力パラメーターが必要です。
LANGUAGE OLEDB 表関数は、任意のプラットフォームで作成できますが、 Microsoft OLE DB によってサポートされているプラットフォーム上でのみ実行できます。
RETURNS NULL ON NULL INPUT が指定されており、 実行時に関数の引き数のいずれかがヌル値の場合、 ユーザー定義関数は呼び出されず、結果は空表、すなわち行のない表になります。
CALLED ON NULL INPUT が指定されると、 引き数がヌル値か否かに関係なく実行時にユーザー定義関数が呼び出されます。 関数の論理によって、空の表を戻すことも戻さないこともあります。 ただし、ヌルの引き数値の有無のテストは UDF が行う必要があります。
値 NULL CALL は、上位互換またはファミリーの互換性のために、 CALLED ON NULL INPUT の同義語として使うことができます。 同様に、NOT NULL CALL は、RETURNS NULL ON NULL INPUT の同義語として使えます。
表関数に対して 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 SERVER、CREATE 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;