17.10 - 例: XML関数の作成および使用 - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQLデータ定義言語 構文規則および例

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
2021年7月
Content Type
プログラミング リファレンス
Publication ID
B035-1144-171K-JPN
Language
日本語 (日本)

以下のテーブル関数の定義があるとします。

    CREATE FUNCTION xml_extract( xml_text LOCATOR CLOB)
     RETURNS TABLE (cust_id INTEGER,
                    store   INTEGER,
                    item    INTEGER)
     LANGUAGE C
     NO SQL
     EXTERNAL;

この関数は、Webアプリケーションによって特定の顧客が特定の店から注文したすべての品物をCLOB xml_textから抽出します。その後、注文された各品物ごとに1つの結果行を生成します。

XMLデータはすでにデータベース テーブルにあります。このテーブルは次のように定義されています。

    CREATE TABLE xml_tbl (
      store_no INTEGER,
      refnum   INTEGER,
      xml_store_text CLOB)
    UNIQUE PRIMARY INDEX (store_no, refnum);
xml_extractテーブル関数によるXMLデータの分析の基本となっている前提条件は次のとおりです。
  • テーブルxml_tblには次の列が含まれます。
    • 店番号の列store_no
    • 参照番号の列refnum
    • XMLテキストの列xml_store_text
  • xml_store_text列には、店に対してWebに基づく品物を注文した顧客に関するXML形式のテキストが入ります。
  • それぞれのXMLテキスト データの列には、注文を行なった顧客とその顧客が注文した品物に関する情報が入ります。

テーブル関数の目的は、顧客が注文したすべての品物をXML文書から抽出することです。オンラインのWebベースのシステムによって行なわれる各注文ごとに1つのXML行が作成されます。XMLテキストはいくつかの品物によって構成される場合があるので、テーブル関数は、注文される品物ごとに1つの行を抽出するようなデータ抽出のための自然なアプローチです。XMLテキストに10個の品物がある場合、テーブル関数は10行のテーブルを返します。

次のSELECTリクエストは、xml_extractテーブル関数の応用例です。

     SELECT l.customer_id l.store, l.item,  
     FROM (SELECT xml_store_text
           FROM xml_tbl AS x 
           WHERE store_no = 25), TABLE(xml_extract(x.xml_text_store)) 
                                 AS l (cust_id,store,item);

このSELECTリクエストは、すべての顧客が店25から購入した品物ごとに1つの行を生成します。その最初の派生テーブルは、店番号25を持つすべての行からXMLのテスト フィールドを生成します。2番目の派生テーブルは、テーブル関数xml_extractの評価結果です。

SELECT操作の後の論理プロセスは次のとおりです。

  1. テーブルの相関名がxの、FROM句の最初のsubqueryから最初の派生テーブルを作成します。
  2. テーブル関数を評価します。

    関数は、派生テーブルの列x.xml_text_storeを参照する入力引数を持ちます。テーブルxによって作成された各行に対して、データベースがテーブル関数をループで繰り返し呼び出す必要があります。ループはテーブル関数によって「no more data」というメッセージが返されると終了します。

    ループの後に次のプロセスが続きます。

    1. xの行を読み取ります。ここで、store_no = 25です。
    2. そのような行が検出されるかどうかを判別します。
      一致する行の検索結果 THEN …
      検出される場合 処理を継続します。
      検出されない場合 処理を停止します。
    3. テーブル関数xml_extract (x.xml_text_store)を呼び出します。
    4. 処理データが他にもあるかどうか判別します。
      呼び出し THEN …
      SQLSTATEコード'02000'を返さない

      (以後のデータなし)

      処理を継続します。
      SQLSTATEコード'02000'を返す プロセス完了。
    5. xml_extractテーブル関数によって生成される新しい行を書き込みます。
    6. ステップcに進みます。
  3. 結果を要求元に返します。

Vantageは、この問題を解決するときに次のプロセスを実行します。

  1. システムは、派生テーブルSELECTリクエストによって入力として生成されるx.xml_text_store結果をテーブル関数xml_extractに渡します。
  2. システムは、同じ行に対して繰り返しテーブル関数を呼び出します。

    テーブル関数が呼び出されるたびに、cust_idstore、およびitemの列を含む行が生成されます。

  3. テーブル関数がそのxml_text_store CLOBからのすべてのデータを使い果たしたら、SQLSTATE "no data"('02000')を返します。
  4. この"no more data"という結果が返されると、最初の派生テーブルによって生成された次の行の読み取りがシステムによって行なわれます。
  5. システムは別の顧客に対してプロセスを繰り返し、さらに次のxml_text_storeに関する行を生成します。