データベースの問い合わせ

クエリのタイプ

クエリは、データの取得および結果セットの生成を実行する文です。クエリは、以下のいずれかで構成されます。

• 指定したテーブルまたはビューのデータにアクセスする簡単な SELECT 文。

• 複数のテーブルまたはビューのデータにアクセスする、JOIN 構文を使用した SELECT 文。

• 複数の SELECT 文の結果を組み合わせる UNION 文。

• 囲んでいる SELECT クエリに単一のデータ項目を提供するために SELECT 文を使用するサブクエリ。

• 埋め込み SQL で、FETCH 文を使用して複数行のデータにアクセスするために SQL カーソルを使用する SELECT 文。

SELECT 文の使用法

SELECT 文は、1 つ以上のテーブルまたはビューから 1 つ以上のデータの行を選択します。以下の例では、簡単な SELECT 文を示します。

SELECT Name,DOB FROM Sample.Person WHERE Name %STARTSWITH 'A' ORDER BY DOB

この例では、Name および DOB は Sample.Person テーブル内の列 (データ・フィールド) です。

SELECT 文での節の指定順序は、SELECT DISTINCT TOP ... selectItems INTO ...FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY です。コマンド構文の順序です。SELECT selectItems を除き、これらすべての節はオプションとなります (オプションの FROM 節は、格納されているデータに対する操作の実行に必要なため、クエリではほぼ必ず必要となります)。SELECT 節を指定する際に必要な順序の詳細は、SELECT 文の構文を参照してください。

SELECT 節の実行順序

SELECT 文の操作は、そのセマンティック処理順序に注目すると理解できます (SELECT 構文の順序と同じではありません)。SELECT の節は以下の順序で処理されます。

1. FROM 節 — JOIN 構文またはサブクエリを使用して、1 つまたは複数のテーブルやビューを指定します。

2. WHERE 節 — さまざまな条件を使用して、選択するデータを制限します。

3. GROUP BY 節 — 選択したデータを一致する値によるサブセットに編成します。値ごとに 1 つのレコードのみが返されます。

4. HAVING 節 — さまざまな条件を使用して、グループから選択するデータを制限します。

5. selectItem — 指定したテーブルまたはビューからデータ・フィールドを選択します。selectItem は、特定のデータ・フィールドを参照するまたは参照しない式の場合もあります。

6. DISTINCT 節 — SELECT 結果セットに適用され、返される行を、重複しない値を含む行のみに限定します。

7. ORDER BY 節 — SELECT 結果セットに適用され、返される行を、指定フィールドによる照合順でソートします。

このセマンティック順序は、テーブルのエイリアス (FROM 節で定義される) はすべての節で認識できるが、列のエイリアス (SELECT selectItems で定義される) は ORDER BY 節でしか認識できないことを示しています。

他の SELECT 節で列のエイリアスを使用するには、以下の例に示すように、サブクエリを使用します。

SELECT Interns FROM 
      (SELECT Name AS Interns FROM Sample.Employee WHERE Age<21) 
WHERE Interns %STARTSWITH 'A'

この例では、Name および Age は Sample.Person テーブル内の列 (データ・フィールド) であり、Interns は Name の列エイリアスです。

JOIN 演算

JOIN は、テーブルのデータを別のテーブルのデータと結合する手段を提供し、レポートやクエリの定義に頻繁に使用されます。SQL では、JOIN は、2 つのテーブルからのデータを組み合わせて、制限条件に当てはまる 3 つ目のテーブルを作成する演算です。作成されたテーブルのすべての行は、制限条件を満たす必要があります。

InterSystems SQL は、CROSS JOIN、INNER JOIN、LEFT OUTER JOIN、RIGHT OUTER JOIN、および FULL OUTER JOIN の 5 種類の結合 (一部は複数の構文形式) をサポートしています。外部結合は、条件式の全範囲の述語と論理演算子を使用する ON 節をサポートします。NATURAL 外部結合および USING 節を使用した外部結合は、部分的にサポートしています。これらの結合タイプの定義および詳細は、"InterSystems SQL リファレンス" の "JOIN" のページを参照してください。

クエリに結合が含まれている場合、そのクエリ内のすべてのフィールド参照には、追加されたテーブル・エイリアスが必要です。InterSystems IRIS はデータ列ヘッダ名にテーブル・エイリアスを組み込まないので、データのソースであるテーブルを明確にするために、selectItem フィールドに列エイリアスを指定することもできます。

次の例では、結合操作を使用して、Sample.Person 内の "架空の" (ランダムに割り当てられた) 郵便番号を Sample.USZipCode 内の実際の郵便番号および都市名と照合しています。WHERE 節が指定されている理由は、USZipCode にはすべてのあり得る 5 桁の郵便番号が含まれているわけではないからです。

SELECT P.Home_City,P.Home_Zip AS FakeZip,Z.ZipCode,Z.City AS ZipCity,Z.State
FROM Sample.Person AS P LEFT OUTER JOIN Sample.USZipCode AS Z 
ON P.Home_Zip=Z.ZipCode
WHERE Z.ZipCode IS NOT NULL
ORDER BY P.Home_City

名前付きクエリの定義と実行

名前付きクエリは、以下に示すように定義および実行できます。

• CREATE QUERY を使用してクエリを定義します。このクエリはストアド・プロシージャとして定義され、CALL を使用して実行できます。

• クラス・クエリ (クラス定義で定義されるクエリ) を定義します。クラス・クエリは、ストアド・プロシージャとして投影されます。CALL を使用して実行できます。%SQL.StatementOpens in a new tab %PrepareClassQuery() メソッドを使用してクラス・クエリを準備した後、%Execute() メソッドを使用して実行することもできます。“ダイナミック SQL の使用法” を参照してください。

CREATE QUERY と CALL

CREATE QUERY を使用してクエリを定義し、CALL を使用して、名前でそれを実行できます。以下の例では、1 つ目はクエリ AgeQuery を定義する SQL プログラムであり、2 つ目はそのクエリを実行するダイナミック SQL です。

CREATE QUERY Sample.AgeQuery(IN topnum INT DEFAULT 10,IN minage INT 20)
   PROCEDURE
   BEGIN
   SELECT TOP :topnum Name,Age FROM Sample.Person
   WHERE Age > :minage 
   ORDER BY Age ;
   END

  SET mycall = "CALL Sample.AgeQuery(11,65)"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(mycall)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()

DROP QUERY Sample.AgeQuery

クラス・クエリ

クエリはクラス内で定義できます。多くの場合、そのクラスは %Persistent ですが、その他のクラスでもかまいません。このクラス・クエリは、同じクラスで定義されたデータ、または同じネームスペースの別のクラスで定義されたデータを参照できます。クラス・クエリで参照されるテーブル、フィールド、およびその他のデータ・エンティティは、クエリを含むクラスのコンパイル時に存在している必要があります。

クラス・クエリは、それを含むクラスのコンパイル時にはコンパイルされません。クラス・クエリのコンパイルは、SQL コードの最初の実行時 (ランタイム) に行われます。これは、%PrepareClassQuery() メソッドを使用してダイナミック SQL でクエリが準備される際に行われます。最初の実行によって、実行可能なクエリ・キャッシュが定義されます。

以下に示すクラス定義の例では、クラス・クエリを定義しています。

Class Sample.QClass Extends %Persistent [DdlAllowed]
  {
  Query MyQ(Myval As %String) As %SQLQuery (CONTAINID=1,ROWSPEC="Name,Home_State") [SqlProc]
     {
     SELECT Name,Home_State FROM Sample.Person 
     WHERE Home_State = :Myval  ORDER BY Name
     }

  }

以下に示す例では、前の例で Sample.QClass クラス内に定義した MyQ クエリを実行します。

  SET Myval="NY"
  SET stmt=##class(%SQL.Statement).%New()
  SET status = stmt.%PrepareClassQuery("Sample.QClass","MyQ")
    IF status'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(status) QUIT}
  SET rset = stmt.%Execute(Myval)
  DO rset.%Display()
  WRITE !,"End of data"

以下のダイナミック SQL の例では、%SQL.StatementOpens in a new tab を使用して Sample.Person クラス内で定義されている ByName クエリを実行しています。このとき、文字列を渡すことで、その文字列値で始まる名前を返すように制限しています。

  SET statemt=##class(%SQL.Statement).%New()
  SET cqStatus=statemt.%PrepareClassQuery("Sample.Person","ByName")
    IF cqStatus'=1 {WRITE "%PrepareClassQuery failed:" DO $System.Status.DisplayError(cqStatus) QUIT}
  SET rs=statemt.%Execute("L")
  DO rs.%Display()

詳細は、"クラスの定義と使用" の “クラス・クエリの定義と使用” を参照してください。

ユーザ定義関数を呼び出すクエリ

InterSystems SQL では、SQL クエリ内でクラス・メソッドを呼び出すことができます。これにより、SQL の構文を拡張するための便利な機能が実現しました。

ユーザ定義の関数を作成するには、InterSystems IRIS 永続クラス内にクラス・メソッドを定義します。メソッドにはリテラル (非オブジェクト) の返り値が必要です。これは、クラス・メソッドである必要があります。SQL クエリでは、インスタンス・メソッドを呼び出すためのオブジェクト・インスタンスが作成されないためです。また、これは、SQL ストアド・プロシージャとして定義される必要があります。

例えば、MyApp.Person クラスで Cube() メソッドを定義できます。

Class MyApp.Person Extends %Persistent [DdlAllowed]
{
/// Find the Cube of a number
ClassMethod Cube(val As %Integer) As %Integer [SqlProc]
  {
    RETURN val * val * val
  }
}

CREATE FUNCTION 文、CREATE METHOD 文、または CREATE PROCEDURE 文を使用して、SQL 関数を作成できます。

SQL 関数を呼び出すには、SQL プロシージャの名前を指定します。SQL 関数は、SQL コード内でスカラ式を指定できる任意の場所から呼び出すことができます。関数名はそのスキーマ名で修飾できますが、未修飾でもかまいません。未修飾の関数名では、ユーザ指定のスキーマ検索パスまたは既定のスキーマ名が使用されます。関数名は、区切り文字付き識別子とすることができます。

SQL 関数には、括弧で囲んだパラメータ・リストが必要です。パラメータ・リストは空でもかまいませんが、括弧は必ず記述します。指定したパラメータはすべて入力パラメータとして機能します。出力パラメータはありません。

SQL 関数は値を返す必要があります。

例えば、以下の SQL クエリは、ユーザ定義の SQL 関数を組み込み SQL 関数であるかのようにメソッドとして呼び出します。

SELECT %ID, Age, MyApp.Person_Cube(Age) FROM MyApp.Person

このクエリは Age のそれぞれの値に対し Cube() メソッドを呼び出し、返り値を結果に表示します。

SQL 関数は入れ子にして使用できます。

指定された関数が見つからない場合、InterSystems IRIS は SQLCODE -359 エラーを発行します。指定された関数名があいまいな場合、InterSystems IRIS は SQLCODE -358 エラーを発行します。

シリアル・オブジェクト・プロパティのクエリ

既定ストレージ (%Storage.Persistent) を使用してクラスから SQL に子テーブルとして投影されるシリアル・オブジェクト・プロパティは、そのクラスによって投影されたテーブル内の 1 つの列としても投影されます。この列の値は、シリアル・オブジェクト・プロパティのシリアル化された値となります。この 1 つの列プロパティは、SQL の%List フィールドとして投影されます。

例えば、Sample.Person の Home 列は Property Home As Sample.Address; として定義され、Class Sample.Address Extends (%SerialObject) に投影されます。これには、プロパティ Street、City、State、PostalCode が含まれます。シリアル・オブジェクトの定義の詳細は、“テーブルの定義” の章の "埋め込みオブジェクト (%SerialObject)" を参照してください。

以下の例では、個々のシリアル・オブジェクトの列から値が返されます。

SELECT TOP 4 Name,Home_Street,Home_City,Home_State,Home_PostalCode
FROM Sample.Person

以下の例では、すべてのシリアル・オブジェクトの列の値が単一の %List 形式文字列として (各列の値が順に %List の要素として) 返されます。

SELECT TOP 4 Name,$LISTTOSTRING(Home,'^')
FROM Sample.Person

既定では、この Home 列 は非表示で、Sample.Person の列としては投影されません。

コレクションのクエリ

コレクションは、次のように SQL の WHERE 節から参照できます。

 WHERE FOR SOME %ELEMENT(collectionRef) [AS label] (predicate)

FOR SOME %ELEMENT 節は、STORAGEDEFAULT="list" を指定する配列およびリスト・コレクションに使用できます。predicate は、擬似列 %KEY または %VALUE、あるいはその両方への参照を 1 つ指定できます。以下に、FOR SOME %ELEMENT 節の使用方法の例をいくつか示します。次の例は、FavoriteColors に 'Red' がある人の名前と、それぞれの FavoriteColors のリストを返します。

SELECT Name,FavoriteColors FROM Sample.Person
    WHERE FOR SOME %ELEMENT(FavoriteColors) (%Value = 'Red')

どの SQL 述語も %Value (または %Key) の後に記述できます。例えば、次の例も有効な構文となります。

SELECT Name,FavoriteColors FROM Sample.Person
    WHERE FOR SOME %ELEMENT(Sample.Person.FavoriteColors)
        (%Value IN ('Red', 'Blue', 'Green'))

リスト・コレクションは、1 から始まる連続数値のキーを持つ配列コレクションの特別な場合と見なされます。配列コレクションには、任意の NULL でないキーを指定できます。

 FOR SOME (children) (%Key = 'betty' AND %Value > 5)

組み込みリストと配列コレクション・タイプのほか、任意のプロパティに BuildValueArray() クラス・メソッドを指定することで、汎用コレクションを作成することもできます。BuildValueArray() クラス・メソッドは、プロパティ値をローカル配列に変換します。ここで、配列の各添え字は %KEY、配列の値は対応する %VALUE になります。

%KEY または %VALUE の単純な選択のほか、以下の例のように 2 つのコレクションを論理的に結合することもできます。

   FOR SOME %ELEMENT(flavors) AS f
      (f.%VALUE IN ('Chocolate', 'Vanilla') AND
       FOR SOME %ELEMENT(toppings) AS t
           (t.%VALUE = 'Butterscotch' AND
            f.%KEY = t.%KEY))

この例には、キーによって位置的に関連付けられた flavors と toppings という 2 つのコレクションがあります。このクエリでは、flavors の要素に指定した chocolate と vanilla の行が選択され、対応する toppings としてリストされた butterscotch によって行が絞り込まれ、%KEY によって対応関係が示された結果が作成されます。

$SYSTEM.SQL.Util.SetOption()Opens in a new tab メソッドの CollectionProjection オプションを使用して、この既定値をシステム全体で変更できます。コレクションが子テーブルとして投影されている場合、SET status=$SYSTEM.SQL.Util.SetOption("CollectionProjection",1,.oldval) を使用して、コレクションを列として投影します。既定値は 0 です。このシステム全体に対する設定の変更は、クラスをコンパイルまたはリコンパイルしたときにクラスごとに反映されます。$SYSTEM.SQL.Util.GetOption("CollectionProjection")Opens in a new tab を使用して、現在の設定を返すことができます。

コレクションのインデックス作成の詳細は、"InterSystems SQL 最適化ガイド" の “インデックスの定義と構築” の章にある "コレクションのインデックス作成" を参照してください。

フリー・テキスト検索を呼び出すクエリ

InterSystems IRIS では、以下のサポートを含む “フリー・テキスト検索” をサポートしています。

• ワイルドカード

• 語幹解析

• 複数語による検索 (N-gram)

• 自動分類

• ディクショナリの管理

この機能によって、フル・テキストによるインデックス機能を SQL でサポートできるほか、コレクションを子テーブルとして投影せずに、SQL を使用してコレクションの個々の要素のインデックスを作成して参照できます。コレクション・インデックス機能をサポートする基本メカニズムとフル・テキスト・インデックス機能をサポートする基本メカニズムは密接に関連していますが、テキスト取得には特別なプロパティが多数あるため、特別なクラスと SQL 機能が用意されています。

詳細は、"InterSystems SQL Search の使用法" を参照してください。

疑似フィールド変数

InterSystems SQL クエリは、以下の疑似フィールド値をサポートしています。

• %ID — 実際の RowID フィールドの名前に関係なく、RowID フィールドの値を返します。

• %TABLENAME — FROM 節で指定された既存テーブルの修飾名を返します。この修飾テーブル名は、FROM 節で指定された大文字と小文字の組み合わせではなく、そのテーブルの定義時に使用された大文字と小文字の組み合わせに従って返されます。FROM 節で未修飾のテーブル名が指定された場合、%TABLENAME は修飾テーブル名 (schema.table) を返し、このスキーマ名はユーザ指定のスキーマ検索パスまたはシステム全体の既定のスキーマ名から取得されます。例えば、FROM 節で mytable と指定されている場合は、%TABLENAME 変数は SQLUser.MyTable を返す可能性があります。

• %CLASSNAME — FROM 節で指定された既存テーブルに対応する修飾クラス名 (<パッケージ名>.<クラス名>) を返します。例えば、FROM 節で SQLUser.mytable と指定されている場合は、%CLASSNAME 変数は User.MyTable を返す可能性があります。

  Note:

  %CLASSNAME 疑似フィールド値を %ClassName()Opens in a new tab インスタンス・メソッドと混同しないでください。これらは異なる値を返します。

疑似フィールド変数を返すことができるのは、データが含まれたテーブルについてのみです。

FROM 節で複数のテーブルが指定されている場合は、以下の埋め込み SQL の例に示すように、テーブルのエイリアスを使用する必要があります。

 &sql(SELECT P.Name,P.%ID,P.%TABLENAME,E.%TABLENAME 
      INTO :name,:rid,:ptname,:etname
    FROM Sample.Person AS P,Sample.Employee AS E)
      IF SQLCODE<0 {WRITE "SQLCODE error ",SQLCODE," ",%msg  QUIT}
      ELSEIF SQLCODE=100 {WRITE "Query returns no results"  QUIT}
    WRITE ptname,"Person table Name is: ",name,!
    WRITE ptname,"Person table RowId is: ",rid,!
    WRITE "P alias TableName is: ",ptname,!
    WRITE "E alias TableName is: ",etname,!

%TABLENAME 列と %CLASSNAME 列には、Literal_n という既定の列名が割り当てられます。ここで n は、SELECT 文内の疑似フィールド変数の selectItem 位置です。

クエリ・メタデータ

ダイナミック SQL を使用すると、クエリ内で指定した列の数、クエリ内で指定した列の名前 (またはエイリアス)、クエリ内で指定した列のデータ型など、クエリに関するメタデータが返されます。

以下の ObjectScript のダイナミック SQL の例は、Sample.Person 内の全列の列名および列の ODBC データ型の整数コードを返します。

  SET myquery="SELECT * FROM Sample.Person"
  SET rset = ##class(%SQL.Statement).%New()
  SET qStatus = rset.%Prepare(myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET x=rset.%Metadata.columns.Count()
  WHILE x>0 {
    SET column=rset.%Metadata.columns.GetAt(x)
  WRITE !,x," ",column.colName," ",column.ODBCType
  SET x=x-1 }
  WRITE !,"end of columns"

この例では、列は、列の順番とは逆にリストされます。ODBC では InterSystems IRIS のリスト・データ型値をコンマで区切られた値の文字列で表すので、リスト構造化されたデータが含まれている FavoriteColors 列は、データ型 12 (VARCHAR) を返します。

詳細は、このドキュメントの "ダイナミック SQL" の章、および "インターシステムズ・クラス・リファレンス" の %SQL.StatementOpens in a new tab クラスを参照してください。

クエリと ECP (エンタープライズ・キャッシュ・プロトコル)

分散キャッシュ・クラスタなど、エンタープライズ・キャッシュ・プロトコル (ECP) を使用する InterSystems IRIS の実装では、クエリ結果を同期化することができます。ECP は分散データ・キャッシュ・アーキテクチャであり、異種のサーバ・システムで構成されるネットワークに分散されるデータとロックを管理します。

ECP の同期化がアクティブな場合、SELECT 文が実行されるたびに、InterSystems IRIS ではすべての保留中 ECP 要求が強制的にデータ・サーバに送信されます。これが完了すると、クライアント・キャッシュの同期が保証されます。この同期化はクエリの Open ロジックで行われます。これがカーソル・クエリである場合は、OPEN カーソル実行となります。

システム全体で ECP の同期を有効にするには、次のように $SYSTEM.SQL.Util.SetOption()Opens in a new tab メソッドを SET status=$SYSTEM.SQL.Util.SetOption("ECPSync",1,.oldval) のように使用します。既定値は 0 です。現在の設定を確認するには、$SYSTEM.SQL.CurrentSettings()Opens in a new tab を呼び出します。

詳細は、"スケーラビリティ・ガイド” の “InterSystems 分散キャッシュによるユーザ数に応じたシステムの水平方向の拡張” の章を参照してください。